測試環境：
172.16.212.61，主機名master
172.16.212.62，主機名slave
172.16.212.100，VIP

1 安裝和基本配置

所有主機，安裝postgresql 11.8

yum -y install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-42.0-11.noarch.rpm yum -y install postgresql11 postgresql11-libs postgresql11-server

所有主機，安裝pgpool 4.1.1

yum install http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-1.noarch.rpm -y yum install pgpool-II-pg11-* -y

所有主機，初始化數據庫

/usr/pgsql-11/bin/postgresql-11-setup initdb systemctl start postgresql-11 systemctl enable postgresql-11 /usr/pgsql-11/bin/psql -V

所有主機上postgresql數據庫的基本配置，/var/lib/pgsql/11/data/postgresql.conf

listen_addresses = '*' shared_buffers = 128MB wal_level = replica wal_log_hints = on archive_mode = on archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"' max_wal_senders = 10 max_replication_slots = 10 hot_standby = on log_directory = 'log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

所有主機上修改postgres用戶的密碼并添加用于記錄wal日志的文件夾

passwd postgres su - postgres mkdir /var/lib/pgsql/archivedir

所有主機，修改認證信息，/var/lib/pgsql/11/data/pg_hba.conf

local all all trust host all all 127.0.0.1/32 ident host all all 172.16.212.61/32 md5 host all all 172.16.212.62/32 md5 host all all ::1/128 ident local replication all trust host replication all 127.0.0.1/32 ident host replication all ::1/128 ident host replication all 172.16.212.61/32 md5 host replication all 172.16.212.62/32 md5

所有主機，配置ssh認證

cd ~/.ssh ssh-keygen -t rsa -f id_rsa_pgpool ssh-copy-id -i id_rsa_pgpool.pub postgres@master ssh-copy-id -i id_rsa_pgpool.pub postgres@slave su - postgres cd ~/.ssh ssh-keygen -t rsa -f id_rsa_pgpool ssh-copy-id -i id_rsa_pgpool.pub postgres@master ssh-copy-id -i id_rsa_pgpool.pub postgres@slave

所有主機，創建認證文件，/var/lib/pgsql/.pgpass

su - postgresvim /var/lib/pgsql/.pgpass master:5432:replication:pgpool:pgpool slave:5432:replication:pgpool:pgpool master:5432:postgres:postgres:postgres slave:5432:postgres:postgres:postgres master:5432:replication:repl:repl slave:5432:replication:repl:replchmod 600 /var/lib/pgsql/.pgpass

master主機，創建數據庫用戶和密碼

psql -U postgres -p 5432 postgres=# SET password_encryption = 'md5'; postgres=# CREATE ROLE pgpool WITH LOGIN; postgres=# CREATE ROLE repl WITH REPLICATION LOGIN; postgres=# \password pgpool postgres=# \password repl postgres=# \password postgres postgres=# GRANT pg_monitor TO pgpool;

2 集群簡介

3 配置pgpool

3.1 所有pgpool節點的共同配置

cp -p /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf#通用配置 listen_addresses = '*'sr_check_user = 'pgpool' sr_check_password = ''health_check_period = 5 health_check_timeout = 30 health_check_user = 'pgpool' health_check_password = '' health_check_max_retries = 3backend_hostname0 = 'master' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/var/lib/pgsql/11/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_application_name0 = 'master'backend_hostname1 = 'slave' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/var/lib/pgsql/11/data' backend_flag1 = 'ALLOW_TO_FAILOVER' backend_application_name1 = 'slave'enable_pool_hba = onuse_watchdog = on delegate_IP = '172.16.212.100' if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev ens33 label ens33:0' if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev ens33' arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33'wd_hostname = '本機的主機名/IP' //比如，master主機填master，slave主機填slave wd_port = 9000other_pgpool_hostname0 = '寫集群中其他節點的主機名' //比如，master主機就寫 slave，slave主機就寫master other_pgpool_port0 = 9999 other_wd_port0 = 9000heartbeat_destination0 = '寫集群中其他節點的主機名' //比如，master主機就寫 slave，slave主機就寫master heartbeat_destination_port0 = 9694 heartbeat_device0 = 'ens33'log_destination = 'syslog' syslog_facility = 'LOCAL1'

3.2 master主機Failover

1）/etc/pgpool-II/pgpool.conf

#master主機 failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'#slave主機，沒有該設置 #failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'

2）腳本內容

vi /etc/pgpool-II/failover.sh #!/bin/bash # This script is run by failover_command. set -o xtrace exec > >(logger -i -p local1.info) 2>&1# Special values: # %d = failed node id # %h = failed node hostname # %p = failed node port number # %D = failed node database cluster path # %m = new master node id # %H = new master node hostname # %M = old master node id # %P = old primary node id # %r = new master port number # %R = new master database cluster path # %N = old primary node hostname # %S = old primary node port number # %% = '%' characterFAILED_NODE_ID="$1" FAILED_NODE_HOST="$2" FAILED_NODE_PORT="$3" FAILED_NODE_PGDATA="$4" NEW_MASTER_NODE_ID="$5" NEW_MASTER_NODE_HOST="$6" OLD_MASTER_NODE_ID="$7" OLD_PRIMARY_NODE_ID="$8" NEW_MASTER_NODE_PORT="$9" NEW_MASTER_NODE_PGDATA="${10}" OLD_PRIMARY_NODE_HOST="${11}" OLD_PRIMARY_NODE_PORT="${12}"PGHOME=/usr/pgsql-11logger -i -p local1.info failover.sh: start: failed_node_id=$FAILED_NODE_ID old_primary_node_id=$OLD_PRIMARY_NODE_ID failed_host=$FAILED_NODE_HOST new_master_host=$NEW_MASTER_NODE_HOST## If there's no master node anymore, skip failover. if [ $NEW_MASTER_NODE_ID -lt 0 ]; thenlogger -i -p local1.info failover.sh: All nodes are down. Skipping failover.exit 0 fi## Test passwrodless SSH ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/nullif [ $? -ne 0 ]; thenlogger -i -p local1.info failover.sh: passwrodless SSH to postgres@${NEW_MASTER_NODE_HOST} failed. Please setup passwrodless SSH.exit 1 fi## If Standby node is down, skip failover. if [ $FAILED_NODE_ID -ne $OLD_PRIMARY_NODE_ID ]; thenlogger -i -p local1.info failover.sh: Standby node is down. Skipping failover.ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$OLD_PRIMARY_NODE_HOST -i ~/.ssh/id_rsa_pgpool "${PGHOME}/bin/psql -p $OLD_PRIMARY_NODE_PORT -c \"SELECT pg_drop_replication_slot('${FAILED_NODE_HOST}')\""if [ $? -ne 0 ]; thenlogger -i -p local1.error failover.sh: drop replication slot "${FAILED_NODE_HOST}" failedexit 1fiexit 0 fi## Promote Standby node. logger -i -p local1.info failover.sh: Primary node is down, promote standby node ${NEW_MASTER_NODE_HOST}.ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null \postgres@${NEW_MASTER_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ${PGHOME}/bin/pg_ctl -D ${NEW_MASTER_NODE_PGDATA} -w promoteif [ $? -ne 0 ]; thenlogger -i -p local1.error failover.sh: new_master_host=$NEW_MASTER_NODE_HOST promote failedexit 1 filogger -i -p local1.info failover.sh: end: new_master_node_id=$NEW_MASTER_NODE_ID started as the primary node exit 0

3.3 master主機配置Online Recovery

1）/etc/pgpool-II/pgpool.conf

#master主機 recovery_user = 'postgres' recovery_password = 'postgres' recovery_1st_stage_command = 'recovery_1st_stage' #slave主機，無特殊配置，默認即可

2）創建相應腳本

su - postgres touch /var/lib/pgsql/11/data/recovery_1st_stage touch /var/lib/pgsql/11/data/pgpool_remote_start chmod +x /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}

3）recovery_1st_stage腳本內容

#!/bin/bash # This script is executed by "recovery_1st_stage" to recovery a Standby node.set -o xtrace exec > >(logger -i -p local1.info) 2>&1PRIMARY_NODE_PGDATA="$1" DEST_NODE_HOST="$2" DEST_NODE_PGDATA="$3" PRIMARY_NODE_PORT="$4" DEST_NODE_ID="$5" DEST_NODE_PORT="$6"PRIMARY_NODE_HOST=$(hostname) PGHOME=/usr/pgsql-11 ARCHIVEDIR=/var/lib/pgsql/archivedir REPLUSER=repllogger -i -p local1.info recovery_1st_stage: start: pg_basebackup for Standby node $DEST_NODE_ID## Test passwrodless SSH ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/nullif [ $? -ne 0 ]; thenlogger -i -p local1.info recovery_1st_stage: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.exit 1 fi## Get PostgreSQL major version PGVERSION=`${PGHOME}/bin/initdb -V | awk '{print $3}' | sed 's/\..*//' | sed 's/\([0-9]*\)[a-zA-Z].*/\1/'` if [ $PGVERSION -ge 12 ]; thenRECOVERYCONF=${DEST_NODE_PGDATA}/myrecovery.conf elseRECOVERYCONF=${DEST_NODE_PGDATA}/recovery.conf fi## Create replication slot "${DEST_NODE_HOST}" ${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ SELECT pg_create_physical_replication_slot('${DEST_NODE_HOST}'); EOQ## Execute pg_basebackup to recovery Standby node ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool "set -o errexitrm -rf $DEST_NODE_PGDATArm -rf $ARCHIVEDIR/*${PGHOME}/bin/pg_basebackup -h $PRIMARY_NODE_HOST -U $REPLUSER -p $PRIMARY_NODE_PORT -D $DEST_NODE_PGDATA -X streamif [ ${PGVERSION} -ge 12 ]; thensed -i -e \"\\\$ainclude_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'\" \-e \"/^include_if_exists = '$(echo ${RECOVERYCONF} | sed -e 's/\//\\\//g')'/d\" ${DEST_NODE_PGDATA}/postgresql.confficat > ${RECOVERYCONF} << EOT primary_conninfo = 'host=${PRIMARY_NODE_HOST} port=${PRIMARY_NODE_PORT} user=${REPLUSER} application_name=${DEST_NODE_HOST} passfile=''/var/lib/pgsql/.pgpass''' recovery_target_timeline = 'latest' restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p' primary_slot_name = '${DEST_NODE_HOST}' EOTif [ ${PGVERSION} -ge 12 ]; thentouch ${DEST_NODE_PGDATA}/standby.signalelseecho \"standby_mode = 'on'\" >> ${RECOVERYCONF}fised -i \"s/#*port = .*/port = ${DEST_NODE_PORT}/\" ${DEST_NODE_PGDATA}/postgresql.conf "if [ $? -ne 0 ]; then${PGHOME}/bin/psql -p ${PRIMARY_NODE_PORT} << EOQ SELECT pg_drop_replication_slot('${DEST_NODE_HOST}'); EOQlogger -i -p local1.error recovery_1st_stage: end: pg_basebackup failed. online recovery failedexit 1 filogger -i -p local1.info recovery_1st_stage: end: recovery_1st_stage complete exit 0

4）pgpool_remote_start腳本內容

#!/bin/bash # This script is run after recovery_1st_stage to start Standby node.set -o xtrace exec > >(logger -i -p local1.info) 2>&1PGHOME=/usr/pgsql-11 DEST_NODE_HOST="$1" DEST_NODE_PGDATA="$2"logger -i -p local1.info pgpool_remote_start: start: remote start Standby node $DEST_NODE_HOST## Test passwrodless SSH ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@${DEST_NODE_HOST} -i ~/.ssh/id_rsa_pgpool ls /tmp > /dev/nullif [ $? -ne 0 ]; thenlogger -i -p local1.info pgpool_remote_start: passwrodless SSH to postgres@${DEST_NODE_HOST} failed. Please setup passwrodless SSH.exit 1 fi## Start Standby node ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$DEST_NODE_HOST -i ~/.ssh/id_rsa_pgpool "$PGHOME/bin/pg_ctl -l /dev/null -w -D $DEST_NODE_PGDATA start "if [ $? -ne 0 ]; thenlogger -i -p local1.error pgpool_remote_start: $DEST_NODE_HOST PostgreSQL start failed.exit 1 filogger -i -p local1.info pgpool_remote_start: end: $DEST_NODE_HOST PostgreSQL started successfully. exit 0

5）master主機上需要在template1上安裝用于recovery的模版

su - postgres psql template1 -c "CREATE EXTENSION pgpool_recovery"

3.4 所有主機，配置認證

/etc/pgpool-II/pool_hba.conflocal all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 172.16.212.0/24 md5 /etc/pgpool-II/pool_passwd pg_md5 -p -m -u postgres pool_passwd pg_md5 -p -m -u pgpool pool_passwd#會生成密碼文件 cat /etc/pgpool-II/pool_passwd postgres:md53175bce1d3201d16594cebf9d7eb3f9d pgpool:md5f24aeb1c3b7d05d7eaf2cd648c307092

3.5 所有主機，/etc/sysconfig/pgpool Configuration

OPTS=" -D -n"

3.6 所有主機，配置日志

mkdir /var/log/pgpool-II touch /var/log/pgpool-II/pgpool.logvi /etc/rsyslog.conf LOCAL1.* /var/log/pgpool-II/pgpool.logvi /etc/logrotate.d/syslog /var/log/pgpool-II/pgpool.logsystemctl restart rsyslog

3.7 所有主機，配置pcp

echo 'pgpool:'`pg_md5 pgpool` >> /etc/pgpool-II/pcp.conf echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass chmod 600 ~/.pcppass

3.8 管理pgpool

systemctl start pgpool.service systemctl stop pgpool.service

3.9 功能驗證用到的命令

pcp_recovery_node -h 172.16.212.100 -p 9898 -U pgpool -n 1 pcp_watchdog_info -h 172.16.212.100 -p 9898 -U pgpool psql -h 172.16.212.100 -p 9999 -U pgpool postgres -c "show pool_nodes" pg_ctl -D /var/lib/pgsql/11/data -m immediate stop psql -h slave -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()"

4 功能驗證

4.1 pgpool服務狀態

master主機：

slave主機：

4.2 模擬primary節點故障，將standby節點提升為primary節點

當前狀態：master主機即為primary節點

故障模擬：停止master主機的postgresql

slave主機已經變成了primary節點，測試一下是否可寫——可以

啟動master主機的postgresql數據庫，然后
將處于down狀態的standby節點恢復正常狀態：pcp_recovery_node -h 172.16.212.100 -p 9898 -U pgpool -n 1

查看master主機的數據庫上是否同步了數據——成功同步了數據

再看一下slave主機上，數據是一樣的：

4.3 恢復master主機為primary節點

停止slave主機上的postgresql，查看pool_nodes狀態——master主機立馬切換成了primary

啟動slave主機上的postgresql，然后恢復standby節點的狀態為up

這樣就恢復了！

4.4 模擬備節點故障，恢復備節點后同步數據

停止slave主機上的postgresql

更新數據庫，刪除2個庫，插入一條新數據

啟動slave主機的postgresql并恢復其狀態為up

查看數據是否同步成功——數據已經同步成功！

4.5 在主節點新增數據，查看另外一個節點是否同步

在master上創建sonar數據庫：

在slave上查看，已經同步成功：

總結

以上是生活随笔為你收集整理的数据库-PostgreSQL搭建PgPool-II集群的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。