| 角色 | 主機名 | IP | PostgreSQL | PgPool |
|---|---|---|---|---|
| Node 1 | node1 | 192.168.0.211 | Primary | Standby |
| Node 2 | node2 | 192.168.0.212 | Standby | Master (VIP) |
| Node 3 | node3 | 192.168.0.213 | Standby | Standby |
| VIP | - | 192.168.0.220 | - | 浮動 |
192.168.0.211 pgsql_primary (Slave 復制連線用固定主機名)synchronous_standby_names = 'node2,node3')recovery.conf 設定 primary_conninfo = 'host=pgsql_primary ...'yum install https://www.pgpool.net/yum/rpms/3.5/redhat/rhel-7-x86_64/pgpool-II-release-3.5-1.noarch.rpm
yum install pgpool-II-pg95 pgpool-II-pg95-extensionsbackend_hostname0 = 'node1'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/data/postgresql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'node2'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/data/postgresql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = 'node3'
backend_port2 = 5433
backend_weight2 = 1
backend_data_directory2 = '/data/postgresql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'master_slave_mode = on
master_slave_sub_mode = 'stream'
load_balance_mode = on
sr_check_period = 10
sr_check_user = 'admin'
sr_check_password = 'admin'
sr_check_database = 'postgres'
delay_threshold = 10000000health_check_period = 3
health_check_timeout = 20
health_check_user = 'admin'
health_check_password = 'admin'
health_check_database = 'postgres'
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = '/home/postgres/failover.sh %h %H %d %P'
fail_over_on_backend_error = on
search_primary_node_timeout = 10use_watchdog = on
wd_hostname = 'node3'
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = '192.168.0.220' # VIP
if_cmd_path = '/usr/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eno16777736 label eno16777736:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eno16777736'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1 -I eno16777736'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
# 心跳目標節點
heartbeat_destination0 = 'node1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eno16777736'
heartbeat_destination1 = 'node2'
heartbeat_destination_port1 = 9694
heartbeat_device1 = 'eno16777736'
# 其他 PgPool 節點
other_pgpool_hostname0 = 'node1'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
other_pgpool_hostname1 = 'node2'
other_pgpool_port1 = 9999
other_wd_port1 = 9000# 產生密碼雜湊
pg_md5 pgpool
# ba777e4c2f15c11ea8ac3be7e0440aa0
# pcp.conf
root:ba777e4c2f15c11ea8ac3be7e0440aa0
# 免密檔案 ~/.pcppass (chmod 600)
localhost:9898:root:pgpool核心邏輯：
change_replication_source.sh 修改 /etc/hosts 切換 pgsql_primary 解析pg_ctl promote#!/bin/bash
new_primary=$1
cat /etc/hosts | grep -v ' pgsql_primary$' > /tmp/hosts.tmp
echo "`resolveip -s $new_primary` pgsql_primary" >> /tmp/hosts.tmp
cp -f /tmp/hosts.tmp /etc/hosts# 三節點啟動
service pgpool start
# 檢查 Watchdog 狀態
pcp_watchdog_info -w -v
# 確認 Quorum EXIST、VIP 掛載節點、Master/Standby 狀態
# 檢查後端狀態
psql -h node3 -p 9999 -U admin postgres -c "show pool_nodes;"
# 確認 role: primary/standby、status: 2 (up)killall -9 postgres# 1. 更新 /etc/hosts 指向新 Primary
vi /etc/hosts
192.168.0.212 pgsql_primary
# 2. 從新 Primary 拉取基礎備份
su - postgres
cp /data/postgresql/data/recovery.done /tmp/
rm -rf /data/postgresql/data
pg_basebackup -h pgsql_primary -p 5433 -U replication -D /data/postgresql/data -X stream -P
cp /tmp/recovery.done /data/postgresql/data/recovery.conf
# 3. 啟動 PostgreSQL
pg_ctl -D /data/postgresql/data start
# 4. 掛載回 PgPool
pcp_attach_node -w 0
# 5. 驗證
show pool_nodes; # Node1 status=1 (starting) → 2 (up)、role=standbyAddress already in use：rm -f /tmp/.s.PGSQL.9999本架構透過 PgPool Watchdog 實現 VIP 浮動與自動 Failover，配合 /etc/hosts 切換復制源實現 Slave 追新 Primary。生產環境強烈建議：**PgPool 獨立部署於應用層**，避免與資料庫爭搶資源並規避單點故障擴散。
