CentOS 7 建置 PostgreSQL故障轉移負載平衡—pgPool2

在postgresql配置叢集後（1-PrimaryNode 、2-StandbyNode、1-WitnessNode），應用程式如何存取資料庫IP該如何配置呢？在故障轉移後如何連接到新的Primary Node，這邊使用pgPool2的負載平衡功能。

延續上一篇 將 pgPool2 安裝在 WitnessNode

yum install https://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
yum install pgpool-II-pg12
# 在所有PostgreSQL Node安裝
yum install pgpool-II-pg12-extensions

配置 pgpool.conf

# vi /etc/pgpool-II/pgpool.conf 這邊只列出修改的部分
listen_addresses = '*'
# DB1
backend_hostname0 = '192.168.10.61'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
# DB2
backend_hostname1 = '192.168.10.62'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
# DB3
backend_hostname2 = '192.168.10.63'
backend_port2 = 5432
backend_weight2 = 1
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
connection_cache = on
load_balance_mode = on
master_slave_mode = on
sr_check_period = 10
sr_check_user = 'postgres'
sr_check_password = 'StrongPassword'
sr_check_database = 'postgres'

配置環境

# 建立pgPool密鑰
pg_md5 -u postgres StrongPassword
>> 0f6e4a1df0cf5ee97c2066953bed21b2
# vi /etc/pgpool-II/pcp.conf 新增
postgres: 0f6e4a1df0cf5ee97c2066953bed21b2
# vi /etc/pgpool-II/pool_hba.conf 新增
host    all         all         192.168.0.0/16        md5
# vi /etc/pgpool-II/pool_passwd 修改
pg_md5 --md5auth --username=postgres StrongPassword

啟動服務

# 建立資料夾
mkdir /var/log/pgpool
chown postgres /var/log/pgpool
systemctl enable pgpool.service
systemctl start pgpool.service

查看狀態

systemctl status pgpool.service -l

連接測試

psql -p 9999 -Upostgres
psql (12.4)
Type "help" for help.
postgres=# show pool_nodes;

正常情況 status 應該都是 “up”，下方處理某節點是down的狀況

pcp_detach_node -n0 -Upostgres # 卸載 node_id 0
<<<輸出>>>
pcp_detach_node -- Command Successful
pcp_attach_node -n0 -Upostgres # 裝載 node_id 0
<<<輸出>>>
pcp_attach_node -- Command Successful
# 重啟服務
systemctl restart pgpool.service

— 完成 —