The erection of Pgpool-ii master and backup stream replication

1. Environment

OS:  CentOS release 6.4 (Final) db:postgresql 9.3.6pgpool server: Pgpool 172.16.0.240 database master server: Master 172.16.0.241 database from server: Slave  172.16.0.242

where the master-slave database uses the stream replication and has been configured, the newly configured Pgpool is managed using Postgres users. The new architecture uses the main standby mode plus stream replication, which supports stream replication, load balancing, failure recovery, replication and parallel queries, and the main library can support read-write and read-only from the library.

2.pgpool Installation

[email protected] opt]# Yum install-y http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/ Pgdg-redhat93-9.3-1.noarch.rpm[[email protected] opt]# yum install postgresql93-devel[[email protected] opt]# wget http ://www.pgpool.net/download.php?f=pgpool-ii-3.4.2.tar.gz[[email protected] opt]# mv download.php\?f\= pgpool-ii-3.4.2.tar.gz Pgpool-ii-3.4.2.tar.gz[[email protected] opt]# tar-zxvf pgpool-ii-3.4.2.tar.gz [email Protected] opt]# CD Pgpool-ii-3.4.2[[email protected] pgpool-ii-3.4.2]#./configure--with-pgsql=/usr/pgsql-9.3

If you do not add--with-pgsql=/usr/pgsql-9.3, you may receive CONFIGURE:ERROR:LIBPQ is not installed or LIBPQ is an old error

[[email protected] pgpool-ii-3.4.2]# Make[[email protected] pgpool-ii-3.4.2]# make install

3. Modify the Pgpool configuration

Enable and modify the configuration file pgpool.conf

[Email protected] etc]# cp/usr/local/etc/pgpool.conf.sample-stream/usr/local/etc/pgpool.conf

Modify the contents of the pgpool.conf file

#-Pgpool Communication Manager Connection settings-listen_addresses = ' * '---default 0 is the main library, others are from the library, Backend_ Weight can control the proportion of database reads on two machines #-backend Connection SETTINGS-BACKEND_HOSTNAME0 = ' 172.16.0.241 ' backend_port0 = 5432backend _weight0 = 1backend_data_directory0 = '/var/lib/pgsql/9.3/data ' backend_flag0 = ' allow_to_failover ' backend_hostname1 = ' 172.16.0.242 ' backend_port1 = 5432backend_weight1 = 1backend_data_directory1 = '/data1 ' backend_flag1 = ' ALLOW_TO_ FAILOVER ' backend_data_directory0 = '/var/lib/pgsql/9.3/data ' #-authentication----Turn on Pgpool HBA authentication Enable_pool_hba = On #------------------------------------------------------------------------------# master/slave mode#------------- -----------------------------------------------------------------sr_check_user = ' postgres ' Sr_check_password = ' 123456 ' #------------------------------------------------------------------------------# health check#----------- -------------------------------------------------------------------Health_check_period = 1health_Check_user = ' postgres ' Health_check_password = ' 123456 ' #---------------------------------------------------------- --------------------# FAILOVER and failback#--------------------------------------------------------------------- ------------used to cut read-only from the library to the primary library Failover_command = '/usr/local/bin/failover_stream.sh%d%h/tmp/trigger_file0 ' when the main library fails

Enable and modify the configuration file pcp.conf

[Email protected] etc]# cp/usr/local/etc/pcp.conf.sample/usr/local/etc/pcp.conf

This file is a management port and can be temporarily used without configuration.

Enable and modify the configuration file pool_hba.conf

[[email protected] etc]# cp/usr/local/etc/pool_hba.conf.sample/usr/local/etc/pool_hba.conf add one line---: Host all         all         172.16.0.0/24   MD5---Remove one row of host all         127.0.0.1/32          Trust

The access policy for the Pgpool, to be set to MD5 mode.

Enable configuration file pool_passwd

[Email protected] etc]# pg_md5-m-p-u postgres Pool_passwdpassword:

The password is 123456, and the file is automatically generated after the file is executed. This password is required to access the Pgpool from the remote connection, and Pgpool uses the user's password to access the following real database.

Add the main backup script switch script failover_stream.sh

[[email protected] bin]# vi/usr/local/bin/failover_stream.sh # Failover command for streaming replication.# this script a Ssumes This DB node 0 is primary, and 1 are standby.# # If standby goes down, does nothing. If primary goes down, create a# trigger file So, Standby takes over primary node.## Arguments: $1:failed Node ID. $2:new master hostname. $3:path to# Trigger file.failed_node=$1new_master=$2trigger_file=$3# do nothing if standby goes down.if [$failed _node = 1]; Then        exit 0;fi# Create the trigger file./usr/bin/ssh-t $new _master/bin/touch $trigger _fileexit 0;[ [Email protected] bin]# chmod failover_stream.sh

The corresponding recovery.conf configuration from the library slave:

[[email protected] data]# cat recovery.conf standby_mode = ' on ' primary_conninfo = ' host=172.16.0.241 port=5432 user=rep_u Ser ' trigger_file = '/tmp/trigger_file0 '

The parameters of the trigger_file are consistent with the corresponding Failover_command files in the pgpool.conf file.

4. Set up host Trust

Modify the hosts file

Add the following to the/etc/hosts file for each host:

172.16.0.240 pgpool172.16.0.241 master172.16.0.242 Slave

Pgpool host generates public and private keys

[Email protected] bin]# su-postgres-bash-4.1$ ssh-keygen-t rsagenerating public/private RSA key pair. Enter file in which to save the key (/VAR/LIB/PGSQL/.SSH/ID_RSA): Created directory '/var/lib/pgsql/.ssh '. Enter passphrase (empty for no passphrase): Enter same passphrase Again:your identification have been saved IN/VAR/LIB/PG Sql/.ssh/id_rsa. Your public key have been saved in/var/lib/pgsql/.ssh/id_rsa.pub.the key fingerprint is:a0:93:d4:b5:ed:26:d0:94:a5:e7:99 : 95:6b:d6:18:af [email protected]the key ' s randomart image is:+--[RSA 2048]----+|        oo.      | |     . +.+   .   ||    . + + O +    | |   . O + + *   | |    +   S * = o  | |     .   O.   | |            E    | | | |                 | +-----------------+

Enter the command and continue to enter. The above actions are also performed on the master and slave nodes.

Synchronizing the public key

Execute on the Pgpool node

-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email protected]-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email Protected

Executes on the master node

-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email protected]-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email Protected

Execute on the slave node

-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email protected]-bash-4.1$ ssh-copy-id-i ~/.ssh/id_rsa.pub [email Protected

　　

After the above operation, in each machine check, whether you can use SSH [email protected], the other nodes for password-free SSH access, the first time the visit will be logged known_hosts, otherwise SSH will have to trust the node prompt.

5. Start Pgpool

If you use Postgres user to start the Pgpool service, you need to modify the permissions of the corresponding directory, otherwise pgpool will fail to start

[Email protected] etc]# mkdir/var/run/pgpool[[email protected] run]# chown-r Postgres:postgres/var/run/pgpool[[email Protected] local]# chown-r postgres:postgres/usr/local/etc[[email protected] local]# chown-r Postgres:postgres/usr/lo Cal/bin

　　

Verify that the script for master-slave database switching is functioning properly, executing commands on the Pgpool server to detect if the Trigger_file0 file is generated on the slave server

Shell script executes in Pgpool

-bash-4.1$/usr/local/bin/failover_stream.sh 0 Slave/tmp/trigger_file0

Switch to slave host check

[Email protected] ~]# ls/tmpssh-rangwc1783  trigger_file0  yum.log

Start Pgpool

-bash-4.1$ Pgpool-n-d >/tmp/pgpool.log 2>&1 &[1] 10474

6. Detecting Pgpool functions

Any node (such as a slave node) is connected Pgpool

[[email protected] ~]# psql-h 172.16.0.240-p 9999-d test-u postgrespassword for user postgres:psql (9.3.6) Type ' help ' For help.test=# select * from T4; ID----  1 (1 row) test=# show pool_nodes; node_id |   Hostname   | port | status | lb_weight |  Role   ---------+--------------+------+--------+-----------+---------0       | 172.16.0.241 | 5432 | 2      | 0.500000  | Primary 1       | 172.16.0.242 | 5432 | 2      | 0.500000  | standby (2 rows)

You can see that at this point the 241 database is the primary node and242 is from the node.

Shut down the primary node, you can shut down the service, or you can kill the process. Impersonation database crashes

[[Email protected] data]# service postgresql-9.3 stopstopping postgresql-9.3 service: [  OK  ]---Master shuts down, After the connection from the node is interrupted and successfully connected to the server closed the connection unexpectedly this        probably means the server terminated abnormally        Before or while processing the request. The connection to the server was lost. Attempting reset:succeeded.test=# show Pool_nodes; node_id |   Hostname   | port | status | lb_weight |  Role   ---------+--------------+------+--------+-----------+---------0       | 172.16.0.241 | 5432 | 3      | 0.500000  | Standby 1       | 172.16.0.242 | 5432 | 2      | 0.500000  | primarytest=# insert INTO T4 values (6); SERT 0 1test=# select * from T4; ID----  1  6 (2 rows) test=#

After the master-slave switchover occurs, the 242 node becomes the primary node, and the 241 node shuts down (status is 3). Pgpool

The above query show Pool_nodes the meaning of the Status field

Status is represented by the number [0-3]. 0-The state is only used for initialization, and the PCP never displays it. 1-The node has been started and is not connected. 2-The node is started and the connection is buffered. 3-the node is closed.

You can see that the main backup has been switched.

7. Other precautions

• If the master-slave switch script on Pgpool forgets to write, or fails to perform properly, the show Pool_nodes command shows that two nodes are standby, and the cluster is read-only, you can shut down and start the node's database service, and then restart Pgpool.
• When testing the trigger file, be careful to delete the generated trigger file in time, otherwise it will destroy the master-slave architecture, resulting in subsequent experiments failing.

The erection of Pgpool-ii master and backup stream replication