Categories
Uncategorized

Postgresql availability (pgpool asynchronous stream replication)

Document:

 

 

 

Since before blogger is Oracle dba, oracle RAC general availability, and to build ADG, an example high availability is based, is based on a data disaster, there rac + adg even manner. Postgres has synchronous and asynchronous synchronization mode, I found, mysql, oracle, postgresql these two modes of synchronization principle is a virtue, it may have been not open around a relational database it.

Asynchronous transactions on the primary database does not wait for the standby database to accept a confirmation message log stream emperor library was returned to the client is successful, the transaction will synchronously wait at least one main library by the library to accept a confirmation message log stream after the successful return; clearly, there is an asynchronous delay time, the synchronous response time will increase, but to ensure data consistency, in the case where resources allow, and may be taken from a master a multiple database synchronization apparatus, a plurality of asynchronously by the library.

In addition pgpool asynchronous availability, it may also be employed keepalived embodiment, this simple experiment it pgpool asynchronously.

What is pgpool-Ⅱ

Pgpool-II is intermediate between a working PostgreSQL PostgreSQL database client and the server. It is licensed under the BSD license. It provides the following functions.

 

connection pool

Reuse them when entering Pgpool-II PostgreSQL server connected to the storage, and having the same attribute (i.e., user name, database, protocol version) of the new connection. It reduces the connection overhead, and improves the overall throughput of the system.

copy

Pgpool-II can manage multiple servers PostgreSQL. Use the copy feature can create real-time backup on two or more physical disks, so that the service can continue to run without stopping the server in the event of disk failure.

Load Balancing

If you copy the database, perform a SELECT query on any server will return the same results. Pgpool-II using the copy function, to reduce the load of each server by allocating PostgreSQL SELECT query among a plurality of servers, thereby increasing the overall throughput of the system. At best, the number, performance, and the PostgreSQL server increases in proportion. In the case of many users simultaneously execute many queries, the most effective load balancing.

Connection limit exceeded

The maximum number of concurrent connections PostgreSQL has a limit, after so many connections in connection refused. However, setting the maximum number of connections increases resource consumption and affect system performance. pgpool-II also has a limit on the maximum number of connections, but extra connections will be queued instead of returning an error immediately.

Watchdog

Watchdog can coordinate multiple Pgpool-II, to create a powerful cluster system to avoid single points of failure or split-brain. The watchdog can be performed to check the life pgpool-II to other nodes to detect a fault Pgpoll-II. If the activity Pgpool-II fails, you can spare Pgpool-II upgrade is active and takes over the virtual IP.

In the memory query cache

In-memory query cache allows keep a SELECT statement and its results. If the return value is the same as SELECT, Pgpool-II from the cache. Or because they do not involve access parsing SQL PostgreSQL, so the memory cache using very fast. On the other hand, in some cases, it may be slower than normal path, because it adds some overhead to store cached data.

Pgpool-II speaks PostgreSQL protocol front end and a rear end, and pass messages between the rear and front ends. Therefore, the database application (front-end) think Pgpool-II is the actual PostgreSQL server, the server (back-end) will Pgpool-II as one of its clients. Because Pgpool-II of the server and the client are transparent, so existing databases can be used with applications Pgpool-II. Pgpool-II PostgreSQL rear and front ends about the protocol, and transmitting connection therebetween. Therefore, the database application (front-end) think Pgpool-II is the actual PostgreSQL server, the server (back-end) will Pgpool-II as one of its clients. Because Pgpool-II of the server and the client are transparent, so existing databases can be used with applications Pgpool-II, almost no change its source.

The following tests mainly from Tan peak (francs) teacher “postgres real”

One. Installation of Pgpool-Ⅱ

CPU name

Explanation

IP addresses

port

version

10pg1

Main library

192.168.10.41

5432

Postgres10.8

pgpool main

5555

 

VIP

192.168.10.61

10pg2

Library equipment

192.168.10.51

5433

Postgres10.8

Pgpool preparation

5555

 

 

Among them, the application connection vip, recommended to turn off the firewall, while production is certainly not postgres this library, and certainly when the business library, this configuration according to the actual situation.

 

1. Download

https://www.pgpool.net/mediawiki/index.php/Downloads

I am using the latest version of rpm 4.0.6.

 

2. Deployment (standby)

yum -y install libmemcached

export PATH=$PATH:/usr/local/pgsql/bin/

mkdir /pgpool

./configure –prefix=/pgpool –with-pgsql=/pgpool/

make

make install

 

Under / pgpool / directory generated by the installation configuration file.

 

 

 

Note If you are prompted:

configure: error: libpq is not installed or libpq is old

If you configure the environment variables (PATH, PGHOME, PGDATA) or more errors, it is recommended to use

./configure –prefix=/pgpool

make

make install

and configure pgpool into environment variables (under root, postgres user)

 

1. Configure pgpool primary and secondary servers trust (non-root set)

I execute a failover script with postgres, so make mutual trust settings put

 

vi /etc/hosts

 

192.168.10.41  10pg1

192.168.10.52  10pg2

 

ssh-keygen 

 

 

 

ssh-copy-id [email protected] 

 

 

 

 

 

 

The same is performed on the reserve

ssh-keygen

ssh-copy-id [email protected]

Testing:

 ssh [email protected]

 

 

 

ssh [email protected]

 

 

 

 

Configuring the connection profile pool_hba.conf

 

Postgres pg_hba.conf configuration file for the connection policy needs, pgpool connection because the application is connected to pgpool line, and then connect to the back-end database through pgpool, and therefore need to be connected configuration pgpool level.

Pgpool pg_hba.conf with the same configuration.

 

cd /pgpool/etc/

cp pool_hba.conf.sample pool_hba.conf

vi pool_hba.conf

Plus the segment 192.168.10.0 ip can access

host    all         all         0.0.0.0/0      md5

1. Configure the password configuration file

Default file is not present, generate pool_passwd configuration file

pg_md5 command generates

 

 

 

pg_md5 -u postgres -m password

Or by hand to build a file, afraid password disclosure

select rolpassword from pg_authid where rolname=’postgres’;

 

 

 

vi pool_passwd

 

postgres:md53175bce1d3201d16594cebf9d7eb3f9d

rep:md5df2c887bcb2c49b903aa33bdbc5c2984

 

 

 

 

1. Configure pgpool.conf profile

cp /pgpool/etc/pgpool.conf.sample-stream /pgpool/etc/pgpool.conf

vi /pgpool/etc/pgpool.conf

Need to modify (write direct result of too much of it because the parameters):

Main Library configuration parameters:

 

port = 5555

listen_addresses = ‘*’ # denote Listen to all addresses connected (with postgres parameters the same meaning)

backend_hostname0 = ‘192.168.10.41’ # the configuration node hostname 0

backend_port0 = 5432 # port of the main library

backend_weight0 = 1 # parameters would suit the mode is not turned on can

backend_data_directory0 = ‘/ pgsql / pg_data’ # 0 data directory node

backend_flag0 = ‘ALLOW_TO_FAILOVER’ # whether to allow the primary database failover

backend_hostname1 = ‘192.168.10.51’

backend_port1 = 5433

backend_weight1 = 1

backend_data_directory1 = ‘/pgsql/pg_data’

backend_flag1 = ‘ALLOW_TO_FAILOVER’

 

enable_pool_hba = on # enable expressed pool_hba.conf

pool_passwd = ‘pool_passwd’ # Set MD5 authentication password file

 

log_destination = ‘syslog’ # log

pid_file_name = ‘/pgpool/pgpool.pid’

 

 

load_balance_mode = off # off the load balancing (if turned on, select statements will be executed by the library)

 

master_slave_mode = on

master_slave_sub_mode = ‘stream’

sr_check_period = 10 # stream copying inspection 10s

sr_check_user = ‘rep’ # rep users I used sync

sr_check_password = ‘rep’

sr_check_database = ‘postgres’ # Check connection flow database replication

delay_threshold = 10000000 This is delayed by the library wal / xlog log greater than 10 million bytes, not select statement to be distributed by the library

 

health_check_period= 5

health_check_timeout = 20

health_check_user = ‘rep’

health_check_password = ‘rep’

health_check_database = ‘postgres’

health_check_max_retries = 3

health_check_retry_delay = 3

 

failover_command = ‘/pgpool/etc/failover_stream.sh %d %P %H %R’

 

use_watchdog = on # enable watchdog

wd_hostname = ‘192.168.10.41’

wd_port = 9000

wd_priority = 1 ## represents the priority of the watchdog, the higher level were selected from the master node, a master provided as from a

 

delegate_IP = ‘192.168.10.61’ # provided VIP

if_cmd_path = ‘/sbin’

if_up_cmd = ‘ip addr add $ _IP _ $ / 24 dev eth0 label eth0: 0’ My environment is eth0

if_down_cmd = ‘ip addr del $_IP_$/24 dev eth0’

 

heartbeat_destination0 = ‘192.168.10.51’

heartbeat_destination_port0 = 9694

heartbeat_device0 = ‘eth0’

 

wd_life_point = 3 # probe set when retry after node failures pgpool

wd_lifecheck_query = ‘SELECT 1’

wd_lifecheck_dbname = ‘postgres’

wd_lifecheck_user = ‘rep’

wd_lifecheck_password = ‘rep’

 

other_pgpool_hostname0 = ’10pg2′ # Set the remote node host pgpool

other_pgpool_port0 = 5555 # Set the remote port number pgpool

other_wd_port0 = 9000 # watchdog disposed remote node port number pgpool

 

Configuration parameters from the library:

 

port = 5555

listen_addresses = ‘*’ # denote Listen to all addresses connected (with postgres parameters the same meaning)

backend_hostname0 = ‘192.168.10.41’ # the configuration node hostname 0

backend_port0 = 5432 # port

backend_weight0 = 1 # parameters would suit the mode is not turned on can

backend_data_directory0 = ‘/ pgsql / data’ # 0 data directory node

backend_flag0 = ‘ALLOW_TO_FAILOVER’ # 0 node is allowed failover

backend_hostname1 = ‘192.168.10.51’ # 1 of the node hostname

backend_port1 = 5433 # 1 to node port

backend_weight1 = 1 # parameters would suit the mode is not turned on can

backend_data_directory1 = ‘/ pgsql / data’ # 1 data directory node

backend_flag1 = ‘ALLOW_TO_FAILOVER’ # 1 Allow node failover

 

enable_pool_hba = on # enable expressed pool_hba.conf

pool_passwd = ‘pool_passwd’ # Set MD5 authentication password file

 

log_destination = ‘syslog’ # log

pid_file_name = ‘/pgpool/pgpool.pid’

 

 

load_balance_mode = off # off the load balancing (if turned on, select statements will be executed by the library)

 

master_slave_mode = on

master_slave_sub_mode = ‘stream’

sr_check_period = 10 # stream copying inspection 10s

sr_check_user = ‘rep’ # rep users I used sync

sr_check_password = ‘rep’

sr_check_database = ‘postgres’ # Check connection flow database replication

delay_threshold = 10000000 This is delayed by the library wal / xlog log greater than 10 million bytes, not select statement to be distributed by the library

 

health_check_period= 5

health_check_timeout = 20

health_check_user = ‘rep’

health_check_password = ‘rep’

health_check_database = ‘postgres’

health_check_max_retries = 3

health_check_retry_delay = 3

 

failover_command = ‘/pgpool/etc/failover_stream.sh’

 

use_watchdog = on # enable watchdog

wd_hostname = ’10pg2′

wd_port = 9000

wd_priority = 1 ## represents the priority of the watchdog, the higher level were selected from the master node, a master provided as from a

 

delegate_IP = ‘192.168.10.61’ # provided VIP

if_cmd_path = ‘/sbin’

if_up_cmd = ‘ip addr add $ _IP _ $ / 24 dev eth0 label eth0: 0’ My environment is eth0

if_down_cmd = ‘ip addr del $_IP_$/24 dev eth0’

 

heartbeat_destination0 = ‘192.168.10.41’

heartbeat_destination_port0 = 9694

heartbeat_device0 = ‘eth0’

 

wd_life_point = 3 # probe set when retry after node failures pgpool

wd_lifecheck_query = ‘SELECT 1’

wd_lifecheck_dbname = ‘postgres’

wd_lifecheck_user = ‘rep’

wd_lifecheck_password = ‘rep’

 

other_pgpool_hostname0 = ‘192.168.10.41’ # Set the remote node host pgpool

other_pgpool_port0 = 5555 # Set the remote port number pgpool

other_wd_port0 = 9000 # watchdog disposed remote node port number pgpool

 

 

1. Configure failover_stream.sh script

cat /pgpool/etc/failover_stream.sh

 

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#! /bin/bash

# Executes this command after master failover

# Special values:

#   %d = node id

#   %h = host name

#   %p = port number

#   %D = database cluster path

#   %m = new master node id

#   %H = hostname of the new master node

#   %M = old master node id

#   %P = old primary node id

#   %r = new master port number

#   %R = new master database cluster path

#   %% = ‘%’ character

falling_node=$1

old_primary=$2

new_primary=$3

pgdata=$4

pghome=/usr/local/pgsql/

log=/pgpool/failover.log

date >> $log

 

# Debugging output variables to log to facilitate this script abnormal

echo “falling_node=$falling_node” >> $log

echo “old_primary=$old_primary” >> $log

echo “new_primary=$new_primary” >> $log

echo “pgdata=$pgdata” >> $log

## If a failure of the main library database and executes an operating system for the root user script

if [ $falling_node = $old_primary ] && [ $UID -eq 0 ];then

    if [ -f $pgdata/recovery.conf ];then

        su  postgres -c “$pghome/bin/pg_ctl promote -D $pgdata”

        echo “Local promote” >> $log

    else

        su postgres -c “ssh -T [email protected]$new_primary $pghome/bin/pg_ctl promote -D $pgdata”

    echo “Remote promote” >> $log

    fi 

fi

exit 0;

 

 

Mainly through the script there is no recovery.conf file to determine the main equipment, calls the ip addr add or delete an IP address, use the root pgpool maintenance program can be very convenient. We need to add an environment variable to root:

export PGPOOL_HOME=/pgpool

export PATH=$PATH:$HOME/bin:/pgpool/bin/:$PGPOOL_HOME/bin

 

Start pgpool (root user) in the main library

pgpool

 

 

 

Check the main library started vip

 

 

 

View Log:

 

 

 

View p’g’pool parameters used:

 

Personal feeling is similar with pg_ctl, especially the load parameters:

pgpool reload

 

Or close pgpool -m fast stop

Shutdown modes are:

  smart       quit after all clients have disconnected

  fast        quit directly, with proper shutdown

  immediate   the same mode as fast

 

These three states are closed

Log Viewer is:

tail -100f /var/log/messages

 

Start from the back of the library pgpool:

Main Library log:

 

 

 

 

Logs from the library:

 

 

 

 

If an error or get up from the library, it is certainly wrong parameters, to modify the parameters according to the log, it is worth noting that there are a lot of parameters of the library, there are a lot distal library, be sure to configure the right.

 

View pgpool status vip:

psql -h 192.168.10.61 -p5555 postgres

show pool_nodes;

 

 

 

The tragedy is actually the main library pgpool status is down, that is, the log said node 0 0, normal should be node 0 2;

 

 

 

 

Repeated examination parameters is not wrong, then execute (typically after restart standby database, the following statement may be performed in an abnormal state):

pcp_attach_node -h 192.168.10.61 -U pgpool 0

 

 

 

Note that 0 is node 1, if it is prepared by the library in question is pcp_attach_node -h 192.168.10.61 -U pgpool 1, then it is normal

 

As shown below:

 

 

 

If the primary standby database closed too, need to reconnect pgpool

pcp_attach_node -h 192.168.10.61 -U pgpool 0 or 1

 

8. PCP Interface Configuration Management

pgpool provides a system for managing layer pgpool command tools, users belonging pgpool PCP level, database user, and does not matter, such as viewing pgpool node information, the node increases pgpool, disconnection and the like pgpool node.

For example, I set the password for the PCP level pgpool

 

 

 

 

[[email protected] ~]# pg_md5 pgpool

ba777e4c2f15c11ea8ac3be7e0440aa0

cd /pgpool/etc/

cp pcp.conf.sample pcp.conf

add to

vi pcp.conf

 

 

 

 

two. High Availability test

 

1. Check the status pgpool

View pcp pcp command node, watchdog information.

pcp_node_info –verbose -h 192.168.10.61 -U pgpool 0

 

 

 

 

Status field values ​​mean:

0 to initialize

1 is not connected to start

2 has a link to start

Node 3 is closed

 

View watchdog cluster information:

pcp_watchdog_info –verbose -h 192.168.10.61 -U pgpool 

 

 

 

2. High Availability test

a. Close program pgpool

Program stopped pgpool master node

 pgpool -m stop fast

 

 

 

 

Check the main library IP:

 

 

 

Main library has been deleted VIP 61

Check the system log:

Main Library:

 

 

 

Library equipment:

 

 

 

Library equipment took over the VIP

Log into view:

psql -h 192.168.10.61 -p5555 postgres postgres

 

 

 

Although taken over by the library VIP, failover switch but does not occur, a separate pgpool database program failure does not occur and the standby switching (except host database down, will be tested).

b. postgres closed only flow replication master library

Pgpool on the main library opened, the main library server does not have to take over the VIP, restart the standby database pgpool, so that the main library to take over the vip, and then start the standby database pgpool do to close the database test.

 

 

 

pg_ctl stop -D /pgsql/pg_data  -m fast

 

 

 

Check the standby database log:

 

 

 

View VIP

 

 

 

You can view that although yet again 10pg1 on VIP, but in fact when the new main library is now connected:

 

 

 

test:

 

 

 

failover has occurred.

 

View the new primary database:

 

 

 

Failover success. View closed library failover log:

Then failover logs generated on the original primary database when this log

 

 

 

c. Close the main library hosts postgres

Re-build master-slave synchronization.

 

Turn off the main repository host the equivalent of shutting down the database and pgpool, and personal vip guess when will it coming, it will be executed by the library to enhance the main library.

select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

show pool_nodes;

 

 

Turn off the main database server.

After a few seconds, VIP drift over, and library equipment to enhance the main library.

 

 

View new main library pgpool log:

 

 

View the new primary database log failover occurs:

 

 

View new main library information

 

 

 

d. summary

Follow the steps to install and test, if no error parameter configuration, the experiment still would be more successful. It is worth noting that you want to configure pool_passwd pcp and password, and when pgpool file configuration parameters, do not mismatch from the main parameters, and finally more noteworthy when failover script, the transfer would not occur if the wrong failure, and then restart too pgpool or database program, if pgpool node status is not normal, you can use pcp_attach_node to pgpool node added to the list, prepared by the library feeling when switching the main library, not immediately, a little delayed, it should be with the time parameter configuration related.

 

Leave a Reply