CentOS7.9 安装 PG13 + repmgr +pgpool 实现负载均衡读写分离

环境规划
Linux 安装
设置开机启动级别（字符界面而不是GUI界面）
关闭防火墙与 selinux
配置IP 与 DNS
linux 系统参数配置
上传 iso 包
挂载
临时挂载
永久挂载
测试挂载是否OK
备份原有 yum 源
新增YUM配置文件
刷新YUM缓存
yum源更新命令
安装相关包
准备工作
环境变量配置
关闭透明大页
克隆虚拟机之后
下载 repmgr [ALL]
配置ip别名 [ALL]
编译安装 repmgr [ALL]
配置 pgsql 互信 5台机器 [node110]
测试
配置防火墙 [ALL]
配置归档等参数 [ALL]
启动数据库 [ALL]
创建相关用户 [ALL]
所有节点执行以 pgsql用户执行
测试配置是否OK
所有个节点分别修改 repmgr.conf
主库注册 [node110]
备库克隆与注册 [node111; node112]
正常switchover [在 node111]
配置自动 fail over
测试自动切换
等node110 修复之后, 重新加入集群
加入集群之后, 在通过 swtichover 手动切换回主库
下载安装 pgpool2
环境变量配置
拷贝参数
修改pgpool.conf
配置 pool_hba.conf
生成pool_passwd文件
配置 pcp.conf
在主库中创建用户
node114
实际操作
连接 pgpool
查看pgpool 后端状态
- 测试读写分离+负载均衡
测试, 开三个窗口
关闭客户端日志打印

CentOS7.9 安装 PG13 + repmgr +pgpool 实现负载均衡读写分离

环境规划

192.168.0.110 node110  master
192.168.0.111 node111  slave1
192.168.0.112 node112  slave2
192.168.0.113 node113  witness
192.168.0.114 node114  pgpool

Linux 安装

最小话安装--> 调试工具
GUI 服务器
硬件监控
java平台
KDE桌面
大系统性能
主框架访问
性能工具
兼容性程序库
开发工具
系统管理工具

设置开机启动级别（字符界面而不是GUI界面）

systemctl get-default 
systemctl set-default multi-user.target
systemctl get-default

关闭防火墙与 selinux

systemctl stop firewalld #临时关闭
systemctl disable firewalld #永久关闭,即设置开机的时候不自动启动
getenforce  
setenforce 0 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

配置IP 与 DNS

 vim /etc/sysconfig/network-scripts/ifcfg-ens32
DNS1="114.114.114.114"
DNS2="8.8.8.8"

service  network restart

linux 系统参数配置

cat >> /etc/sysctl.conf <<"EOF"
kernel.shmall = 4294967296  
kernel.shmmax=135497418752  
kernel.shmmni = 4096  
kernel.sem = 50100 64128000 50100 1280  
fs.file-max = 7672460  
fs.aio-max-nr = 1048576  
net.ipv4.ip_local_port_range = 9000 65000  
net.core.rmem_default = 262144  
net.core.rmem_max = 4194304  
net.core.wmem_default = 262144  
net.core.wmem_max = 4194304  
net.ipv4.tcp_max_syn_backlog = 4096  
net.core.netdev_max_backlog = 10000  
net.ipv4.netfilter.ip_conntrack_max = 655360  
net.ipv4.tcp_timestamps = 0  
net.ipv4.tcp_tw_recycle=1  
net.ipv4.tcp_timestamps=1  
net.ipv4.tcp_keepalive_time = 72   
net.ipv4.tcp_keepalive_probes = 9   
net.ipv4.tcp_keepalive_intvl = 7  
vm.zone_reclaim_mode=0  
vm.dirty_background_bytes = 40960000  
vm.dirty_ratio = 80  
vm.dirty_expire_centisecs = 6000  
vm.dirty_writeback_centisecs = 50  
vm.swappiness=0  
vm.overcommit_memory = 0  
vm.overcommit_ratio = 90  
EOF
	
sysctl -p

cat >> /etc/security/limits.conf   <<"EOF"
* soft    nofile  131072  
* hard    nofile  131072  
* soft    nproc   131072  
* hard    nproc   131072  
* soft    core    unlimited  
* hard    core    unlimited  
* soft    memlock 500000000  
* hard    memlock 500000000  
EOF

rm -f /etc/security/limits.d/*

上传 iso 包

挂载

临时挂载

# mount -o loop,defaults,ro /mnt/iso/CentOS-7-x86_64-Everything-2009.iso /iso

永久挂载

echo "/mnt/iso/CentOS-7-x86_64-Everything-2009.iso /iso iso9660 defaults 0 0" >> /etc/fstab

测试挂载是否OK

mount -a

备份原有 yum 源

cd /etc/yum.repos.d/
mkdir /bak
mv * /bak

新增YUM配置文件

cd /etc/yum.repos.d/  
cat > local.repo  <<"EOF"
[local-yum]  
name=Local Repository  
baseurl=file:///iso
enabled=1  
gpgcheck=0  
EOF

刷新YUM缓存

yum clean all

yum源更新命令

yum clean all
yum makecache
yum update

安装相关包

yum install -y zlib-devel numactl

准备工作

useradd pgsql
echo "passwd" | passwd --stdin pgsql
mkdir -p /postgresql/{pgdata,pgsql,archive,soft}
chown -R pgsql.pgsql /postgresql
su - pgsql
cd /postgresql/soft
wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
tar zxvf postgresql-13.3.tar.gz
cd postgresql-13.3
./configure --prefix=/postgresql/pgsql
make world -j 8  
make install-world

环境变量配置

cat >> ~/.bash_profile <<"EOF"
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export LANG=en_US.utf8  
export PGHOME=/postgresql/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export PATH=$PGHOME/bin:$PATH:.  
export DATE=`date +"%Y%m%d%H%M"`  
export PGUSER=postgres  
export PGDATABASE=postgres  
EOF

source ~/.bash_profile

initdb -D $PGDATA -E UTF8 --locale=C -U postgres  

cat >> $PGDATA/postgresql.conf <<"EOF"
listen_addresses = '*'  
port = 5432 
logging_collector = on  
log_filename = 'postgresql-%u.log' 
log_truncate_on_rotation = on
EOF

cat >> $PGDATA/pg_hba.conf <<"EOF"
host all all 0.0.0.0/0 md5  
EOF

pg_ctl start
psql -h localhost -c "alter user postgres with password 'passwd';"

关闭透明大页

vim /etc/default/grub
修改之前：GRUB_CMDLINE_LINUX="rhgb quiet"
修改之后：GRUB_CMDLINE_LINUX="rhgb quiet transparent_hugepage=never"

grub2-mkconfig -o /boot/grub2/grub.cfg
numactl --show
numactl --hardware
reboot

克隆虚拟机之后

修改主机名
vim /etc/hostname
修改IP地址
vim /etc/sysconfig/network-scripts/ifcfg-ensxx
删除uuid
删除/etc/udev/rules.d下的网络相关文件/

下载 repmgr [ALL]

wget  https://repmgr.org/download/repmgr-5.2.1.tar.gz

配置ip别名 [ALL]

cat >> /etc/hosts <<EOF
192.168.0.110 node110
192.168.0.111 node111
192.168.0.112 node112
192.168.0.113 node113
192.168.0.114 node114
EOF

编译安装 repmgr [ALL]

tar zxvf repmgr-5.2.1.tar.gz 
cd repmgr-5.2.1/
./configure && make -j8 &&  make install

配置 pgsql 互信 5台机器 [node110]

上传文件 sshUserSetup.sh
以 root 用户执行
./sshUserSetup.sh -user pgsql  -hosts "node110 node111 node112 node113 node114" -advanced exverify -confirm
su - pgsql
chmod 600 /home/pgsql/.ssh/config

测试

su - pgsql
ssh node111 
hostname
ssh node112
hostname

ssh node113
hostname

ssh node114
hostname

配置防火墙 [ALL]

cat >> $PGDATA/pg_hba.conf <<"EOF"
local repmgr repmgr md5
host repmgr repmgr 127.0.0.1/32 md5
host repmgr repmgr 192.168.0.0/24 md5
local replication repmgr md5
host replication repmgr 127.0.0.1/32 md5
host replication repmgr 192.168.0.0/24 md5
EOF

配置归档等参数 [ALL]

cat >> $PGDATA/postgresql.conf <<"EOF"
# 归档参数
wal_level='replica'
archive_mode='on'
archive_command='test ! -f /postgresql/archive/%f && cp %p /postgresql/archive/%f'
restore_command='cp /postgresql/archive/%f %p'

# 主从流复制
hot_standby=on
max_wal_senders=10
wal_sender_timeout=60s
wal_keep_size=16MB

# 主从切换参数，启用PG数据库的复制槽，PG12不需要"use_replication_slots=true"这个参数了。
max_replication_slots=10
wal_log_hints=on

# 自动切换
shared_preload_libraries ='repmgr'

EOF

启动数据库 [ALL]

pg_ctl restart

创建相关用户 [ALL]

createuser -s repmgr
createdb repmgr -O repmgr
psql -c "alter user repmgr  with  password 'repmgr';"
psql -c "alter user repmgr set search_path to repmgr, \"\$user\",public;"

所有节点执行以 pgsql用户执行

> ~/.pgpass
echo "*:*:*:repmgr:repmgr" > ~/.pgpass
# 必须授予权限
chmod 600 ~/.pgpass

测试配置是否OK

psql 'host=node110 user=repmgr dbname=repmgr connect_timeout=2'
psql 'host=node111 user=repmgr dbname=repmgr connect_timeout=2'
psql 'host=node112 user=repmgr dbname=repmgr connect_timeout=2'
psql 'host=node113 user=repmgr dbname=repmgr connect_timeout=2'

所有个节点分别修改 repmgr.conf

以 pgsql 用户执行如下命令

cat > $PGHOME/repmgr.conf << "EOF"
node_id=1
node_name=node110
conninfo='host=192.168.0.110 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pgsql/bin'

monitoring_history=yes
monitor_interval_secs=5
failover=automatic
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /postgresql/pgsql/repmgr.conf --log-to-file'
follow-command='repmgr standby follow -f /postgresql/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
log_level=INFO
log_status_interval=10
log_file=/postgresql/pgsql/repmgr.log
EOF


-- 从库1
cat > $PGHOME/repmgr.conf << "EOF"
node_id=2
node_name=node111
conninfo='host=192.168.0.111 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pgsql/bin'

monitoring_history=yes
monitor_interval_secs=5
failover=automatic
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /postgresql/pgsql/repmgr.conf --log-to-file'
follow-command='repmgr standby follow -f /postgresql/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
log_level=INFO
log_status_interval=10
log_file=/postgresql/pgsql/repmgr.log
EOF

-- 从库2
cat > $PGHOME/repmgr.conf << "EOF"
node_id=3
node_name=node112
conninfo='host=192.168.0.112 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pgsql/bin'

monitoring_history=yes
monitor_interval_secs=5
failover=automatic
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /postgresql/pgsql/repmgr.conf --log-to-file'
follow-command='repmgr standby follow -f /postgresql/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
log_level=INFO
log_status_interval=10
log_file=/postgresql/pgsql/repmgr.log
EOF


-- witness节点
cat > $PGHOME/repmgr.conf << "EOF"
node_id=4
node_name=node113
conninfo='host=192.168.0.113 user=repmgr password=repmgr dbname=repmgr connect_timeout=2'
data_directory='/postgresql/pgdata'
pg_bindir='/postgresql/pgsql/bin'

monitoring_history=yes
monitor_interval_secs=5
failover=automatic
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /postgresql/pgsql/repmgr.conf --log-to-file'
follow-command='repmgr standby follow -f /postgresql/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
log_level=INFO
log_status_interval=10
log_file=/postgresql/pgsql/repmgr.log
EOF

主库注册 [node110]

repmgr -f /postgresql/pgsql/repmgr.conf  primary register --dry-run
repmgr -f /postgresql/pgsql/repmgr.conf  primary register
repmgr -f /postgresql/pgsql/repmgr.conf  cluster show

备库克隆与注册 [node111; node112]

pg_ctl stop
rm -rf /postgresql/pgdata

测试克隆

repmgr -h node110 -U repmgr -d repmgr -f /postgresql/pgsql/repmgr.conf standby clone --dry-run

真实执行

repmgr -h node110 -U repmgr -d repmgr -f /postgresql/pgsql/repmgr.conf standby clone --force

启动PG
pg_ctl start
注册从库

repmgr -f /postgresql/pgsql/repmgr.conf standby register

witness节点注册 [node113]

repmgr -f /postgresql/pgsql/repmgr.conf  -h node110 -U repmgr -d repmgr witness register --dry-run
repmgr -f /postgresql/pgsql/repmgr.conf  -h node110 -U repmgr -d repmgr witness register

正常switchover [在 node111]

测试

repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind --dry-run

真实执行

repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind --log-level DEBUG --verbose

-- 可以debug打印详细的切换过程

# repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind --log-level DEBUG --verbose

配置自动 fail over

在所有节点都配置文件 $PGHOME/repmgr.conf，增加内容包括：

cat >> $PGHOME/repmgr.conf << "EOF"
monitoring_history=yes
monitor_interval_secs=5
failover=automatic
reconnect_attempts=6
reconnect_interval=5
promote_command='repmgr standby promote -f /postgresql/pgsql/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /postgresql/pgsql/repmgr.conf --log-to-file --upstream-node-id=%n'
log_level=INFO
log_status_interval=10
log_file='/postgresql/pgsql/repmgr.log'
EOF

root用户下执行

cat >> /etc/logrotate.conf <<"EOF"
/postgresql/pgsql/repmgr.log
{
missingok
compress
rotate 30
daily
dateext
create 0600 pg13 pg13
}
EOF

*在所有节点都启动repmgrd进程

启动

repmgrd -f  /postgresql/pgsql/repmgr.conf  --pid-file /tmp/repmgrd.pid  --daemonize

-- 建议加到开机自动启动:/etc/rc.local

echo "repmgrd -f  /postgresql/pgsql/repmgr.conf --pid-file /tmp/repmgrd.pid --daemonize" >> /etc/rc.local
chmod +x /etc/rc.d/rc.local

停止

kill -9 `cat /tmp/repmgrd.pid`

查看日志
tailf /postgresql/pgsql/repmgr.log

测试自动切换

模拟主机宕机[node110]

pg_ctl stop

node 111:提升为主库
HINT: execute with --verbose option to see connection error messages
pgsql@node111-> repmgr -f /postgresql/pgsql/repmgr.conf  cluster show --verbose
NOTICE: using provided configuration file "/postgresql/pgsql/repmgr.conf"
INFO: connecting to database
ERROR: connection to database failed
DETAIL: 
could not connect to server: Connection refused
	Is the server running on host "192.168.0.110" and accepting
	TCP/IP connections on port 5432?

DETAIL: attempted to connect using:
  user=repmgr password=repmgr connect_timeout=2 dbname=repmgr host=192.168.0.110 fallback_application_name=repmgr options=-csearch_path=
 ID | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                             
----+---------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------
 1  | node110 | primary | - failed  | ?        | default  | 100      |          | host=192.168.0.110 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 2  | node111 | primary | * running |          | default  | 100      | 4        | host=192.168.0.111 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 3  | node112 | standby |   running | node111  | default  | 100      | 3        | host=192.168.0.112 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 4  | node113 | witness | * running | node111  | default  | 0        | n/a      | host=192.168.0.113 user=repmgr password=repmgr dbname=repmgr connect_timeout=2

WARNING: following issues were detected
  - when attempting to connect to node "node110" (ID: 1), following error encountered :
"could not connect to server: Connection refused
	Is the server running on host "192.168.0.110" and accepting
	TCP/IP connections on port 5432?"

等node110 修复之后, 重新加入集群

repmgr -f  /postgresql/pgsql/repmgr.conf node rejoin -d 'host=node111 user=repmgr dbname=repmgr connect_timeout=2'

加入集群之后, 在通过 swtichover 手动切换回主库

repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind --dry-run 


pgsql@node110-> repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind --dry-run 
NOTICE: checking switchover on node "node110" (ID: 1) in --dry-run mode
INFO: prerequisites for using pg_rewind are met
INFO: SSH connection to host "192.168.0.111" succeeded
INFO: able to execute "repmgr" on remote host "192.168.0.111"
INFO: all sibling nodes are reachable via SSH
INFO: 3 walsenders required, 10 available
INFO: demotion candidate is able to make replication connection to promotion candidate
INFO: 0 pending archive files
INFO: replication lag on this standby is 0 seconds
INFO: would pause repmgrd on node "node110" (ID 1)
INFO: would pause repmgrd on node "node111" (ID 2)
INFO: would pause repmgrd on node "node112" (ID 3)
INFO: would pause repmgrd on node "node113" (ID 4)
NOTICE: local node "node110" (ID: 1) would be promoted to primary; current primary "node111" (ID: 2) would be demoted to standby
INFO: following shutdown command would be run on node "node111":
  "/postgresql/pgsql/bin/pg_ctl  -D '/postgresql/pgdata' -W -m fast stop"
INFO: parameter "shutdown_check_timeout" is set to 60 seconds
INFO: prerequisites for executing STANDBY SWITCHOVER are met
pgsql@node110-> 
pgsql@node110-> 
pgsql@node110-> repmgr -f /postgresql/pgsql/repmgr.conf standby switchover --siblings-follow --force-rewind 
NOTICE: executing switchover on node "node110" (ID: 1)
NOTICE: local node "node110" (ID: 1) will be promoted to primary; current primary "node111" (ID: 2) will be demoted to standby
NOTICE: stopping current primary node "node111" (ID: 2)
NOTICE: issuing CHECKPOINT on node "node111" (ID: 2) 
DETAIL: executing server command "/postgresql/pgsql/bin/pg_ctl  -D '/postgresql/pgdata' -W -m fast stop"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/C000028
NOTICE: promoting standby to primary
DETAIL: promoting server "node110" (ID: 1) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "node110" (ID: 1) was successfully promoted to primary
NOTICE: issuing CHECKPOINT on node "node110" (ID: 1) 
ERROR: unable to execute CHECKPOINT
INFO: local node 2 can attach to rejoin target node 1
DETAIL: local node's recovery point: 0/C000028; rejoin target node's fork point: 0/C0000A0
NOTICE: setting node 2\'s upstream to node 1
WARNING: unable to ping "host=192.168.0.111 user=repmgr password=repmgr dbname=repmgr connect_timeout=2"
DETAIL: PQping() returned "PQPING_NO_RESPONSE"
NOTICE: starting server using "/postgresql/pgsql/bin/pg_ctl  -w -D '/postgresql/pgdata' start"
WARNING: node "node111" attached in state "startup"
NOTICE: NODE REJOIN successful
DETAIL: node 2 is now attached to node 1
WARNING: node "node111" attached in state "startup"
INFO: waiting for node "node111" (ID: 2) to connect to new primary; 1 of max 60 attempts (parameter "node_rejoin_timeout")
DETAIL: node "node110" (ID: 2) is currrently attached to its upstream node in state "startup"
NOTICE: node  "node110" (ID: 1) promoted to primary, node "node111" (ID: 2) demoted to standby
NOTICE: executing STANDBY FOLLOW on 2 of 2 siblings
INFO:  node 4 received notification to follow node 1
INFO: STANDBY FOLLOW successfully executed on all reachable sibling nodes
NOTICE: switchover was successful
DETAIL: node "node110" is now primary and node "node111" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully
pgsql@node110-> repmgr -f /postgresql/pgsql/repmgr.conf  cluster show
 ID | Name    | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                                             
----+---------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------------------------------
 1  | node110 | primary | * running |          | default  | 100      | 5        | host=192.168.0.110 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 2  | node111 | standby |   running | node110  | default  | 100      | 4        | host=192.168.0.111 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 3  | node112 | standby |   running | node110  | default  | 100      | 4        | host=192.168.0.112 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
 4  | node113 | witness | * running | node110  | default  | 0        | n/a      | host=192.168.0.113 user=repmgr password=repmgr dbname=repmgr connect_timeout=2
pgsql@node110->

下载安装 pgpool2

mkdir -p /postgresql/pgpool
wget https://www.pgpool.net/mediawiki/images/pgpool-II-4.2.2.tar.gz
tar -zxvf pgpool-II-4.2.2.tar.gz
cd pgpool-II-4.2.2/
./configure --prefix=/postgresql/pgpool --with-pgsql=/postgresql/pgsql
make -j 8 && make install

环境变量配置

echo 'export PATH=/postgresql/pgpool/bin:$PATH' >> /home/pgsql/.bash_profile
source /home/pgsql/.bash_profile

拷贝参数

cp /postgresql/pgpool/etc/pgpool.conf.sample /postgresql/pgpool/etc/pgpool.conf
cp /postgresql/pgpool/etc/pool_hba.conf.sample /postgresql/pgpool/etc/pool_hba.conf
cp /postgresql/pgpool/etc/pcp.conf.sample /postgresql/pgpool/etc/pcp.conf

修改pgpool.conf

cat >> /postgresql/pgpool/etc/pgpool.conf <<"EOF"
# - pgpool Connection Settings -
listen_addresses = '*'


# - Backend Connection Settings -
backend_hostname0 = '192.168.0.110'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/postgresql/pgdata'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'node110'
backend_hostname1 = '192.168.0.111'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/postgresql/pgdata'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'node111'


backend_hostname2 = '192.168.0.112'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/postgresql/pgdata'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'node112'



# - Authentication -
enable_pool_hba = on


# - Where to log -
log_destination = 'syslog'
log_connections = on

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/postgresql/pgpool/pgpool.pid'
logdir = '/tmp'


#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on

EOF

配置 pool_hba.conf

echo "host all all 0.0.0.0/0 md5" >> /postgresql/pgpool/etc/pool_hba.conf

生成pool_passwd文件

pg_md5 --md5auth --username=nobody "passwd"
pg_md5 --md5auth --username=pgpool "passwd"
pg_md5 --md5auth --username=postgres "passwd"

pgsql@node114-> cat pool_passwd 
nobody:md5979f0636f5188c5c037fa6eddf977684
pgpool:md592a6043af66f8da8ff9dda6320b95889
postgres:md55305adaac499dbbc6865a44e4aa5d8b4

配置 pcp.conf

pg_md5  -u=pgpool "passwd"
76a2173be6393254e72ffa4d6df1030a
echo  "pgpool:76a2173be6393254e72ffa4d6df1030a" >> /postgresql/pgpool/etc/pcp.conf

在主库中创建用户

psql -U postgres 
create role nobody login encrypted password 'passwd';
create role pgpool login encrypted password 'passwd';
grant postgres to nobody,pgpool;

node114

设置开机自动启动

cat >> /lib/systemd/system/pgpool.service <<"EOF"
[Unit]
Description=Pgpool-II
After=syslog.target network.target

[Service]
User=pgsql
Group=pgsql
EnvironmentFile=-/etc/sysconfig/pgpool
ExecStart=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -n
ExecStop=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -m fast stop
ExecReload=/postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf reload
LimitNOFILE=65536
KillMode=process
KillSignal=SIGINT
Restart=on-abnormal
RestartSec=30s
TimeoutSec=0

[Install]
WantedBy=multi-user.target
EOF

chmod +x /lib/systemd/system/pgpool.service
systemctl status pgpool
systemctl start pgpool
systemctl enable pgpool
systemctl restart pgpool

实际操作

[root@node114 ~]# 
[root@node114 ~]# systemctl status pgpool
● pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@node114 ~]# systemctl start pgpool
[root@node114 ~]# systemctl enable pgpool
Created symlink from /etc/systemd/system/multi-user.target.wants/pgpool.service to /usr/lib/systemd/system/pgpool.service.
[root@node114 ~]# systemctl status pgpool
● pgpool.service - Pgpool-II
   Loaded: loaded (/usr/lib/systemd/system/pgpool.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2021-07-11 20:02:18 CST; 5s ago
 Main PID: 68556 (pgpool)
   CGroup: /system.slice/pgpool.service
           ├─68556 /postgresql/pgpool/bin/pgpool -f /postgresql/pgpool/etc/pgpool.conf -n
           ├─68558 pgpool: wait for connection request
           ├─68559 pgpool: wait for connection request
           ├─68560 pgpool: wait for connection request
           ├─68561 pgpool: wait for connection request
           ├─68562 pgpool: wait for connection request
           ├─68563 pgpool: wait for connection request
           ├─68564 pgpool: wait for connection request
           ├─68565 pgpool: wait for connection request
           ├─68566 pgpool: wait for connection request
           ├─68567 pgpool: wait for connection request
           ├─68568 pgpool: wait for connection request
           ├─68569 pgpool: wait for connection request
           ├─68570 pgpool: wait for connection request
           ├─68571 pgpool: wait for connection request
           ├─68572 pgpool: wait for connection request
           ├─68573 pgpool: wait for connection request
           ├─68574 pgpool: wait for connection request
           ├─68575 pgpool: wait for connection request
           ├─68576 pgpool: wait for connection request
           ├─68577 pgpool: wait for connection request
           ├─68578 pgpool: wait for connection request
           ├─68579 pgpool: wait for connection request
           ├─68580 pgpool: wait for connection request
           ├─68581 pgpool: wait for connection request
           ├─68582 pgpool: wait for connection request
           ├─68583 pgpool: wait for connection request
           ├─68584 pgpool: wait for connection request
           ├─68585 pgpool: wait for connection request
           ├─68586 pgpool: wait for connection request
           ├─68587 pgpool: wait for connection request
           ├─68588 pgpool: wait for connection request
           ├─68589 pgpool: wait for connection request
           ├─68590 pgpool: PCP: wait for connection request
           ├─68591 pgpool: worker process
           └─68592 pgpool: health check process(0)

Jul 11 20:02:18 node114 pgpool[68556]: [9-1] 2021-07-11 20:02:18: pid 68556: LOG:  pool_discard_o...maps
Jul 11 20:02:18 node114 pgpool[68556]: [10-1] 2021-07-11 20:02:18: pid 68556: LOG:  Setting up so...9999
Jul 11 20:02:18 node114 pgpool[68556]: [11-1] 2021-07-11 20:02:18: pid 68556: LOG:  Setting up so...9999
Jul 11 20:02:18 node114 pgpool[68556]: [12-1] 2021-07-11 20:02:18: pid 68556: LOG:  find_primary_...node
Jul 11 20:02:18 node114 pgpool[68556]: [13-1] 2021-07-11 20:02:18: pid 68556: LOG:  find_primary_...is 0
Jul 11 20:02:18 node114 pgpool[68590]: [14-1] 2021-07-11 20:02:18: pid 68590: LOG:  PCP process: ...rted
Jul 11 20:02:18 node114 pgpool[68591]: [14-1] 2021-07-11 20:02:18: pid 68591: LOG:  process started
Jul 11 20:02:18 node114 pgpool[68556]: [14-1] 2021-07-11 20:02:18: pid 68556: LOG:  pgpool-II suc...shi)
Jul 11 20:02:18 node114 pgpool[68556]: [15-1] 2021-07-11 20:02:18: pid 68556: LOG:  node status[0]: 1
Jul 11 20:02:18 node114 pgpool[68592]: [14-1] 2021-07-11 20:02:18: pid 68592: LOG:  process started
Hint: Some lines were ellipsized, use -l to show in full.
[root@node114 ~]#

连接 pgpool

[root@node114 ~]# 
[root@node114 ~]# su - pgsql
Last login: Sun Jul 11 20:02:49 CST 2021 on pts/0
pgsql@node114-> psql -Upostgres -p 9999
psql (13.3)
Type "help" for help.

postgres=# show pool_nodes;
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replic
ation_delay | replication_state | replication_sync_state | last_status_change  
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------
------------+-------------------+------------------------+---------------------
 0       | 192.168.0.110 | 5432 | up     | 0.333333  | primary | 0          | true              | 0     
            |                   |                        | 2021-07-11 20:06:33
 1       | 192.168.0.111 | 5432 | unused | 0.333333  | standby | 0          | false             | 0     
            |                   |                        | 2021-07-11 20:06:33
 2       | 192.168.0.112 | 5432 | unused | 0.333333  | standby | 0          | false             | 0     
            |                   |                        | 2021-07-11 20:06:33
(3 rows)

查看pgpool 后端状态

pcp_node_info -U pgpool -h localhost -p 9898 -n 0 -v
pcp_node_info -U pgpool -h localhost -p 9898 -n 1 -v
pcp_node_info -U pgpool -h localhost -p 9898 -n 2 -v

pgsql@node114-> pcp_node_info -U pgpool -h localhost -p 9898 -n 0 -v
Password: 
Hostname               : 192.168.0.110
Port                   : 5432
Status                 : 2
Weight                 : 0.333333
Status Name            : up
Role                   : primary
Replication Delay      : 0
Replication State      : 
Replication Sync State : 
Last Status Change     : 2021-07-11 20:06:33
pgsql@node114-> 
pgsql@node114-> pcp_node_info -U pgpool -h localhost -p 9898 -n 1 -v
Password: 
Hostname               : 192.168.0.111
Port                   : 5432
Status                 : 0
Weight                 : 0.333333
Status Name            : unused
Role                   : standby
Replication Delay      : 0
Replication State      : 
Replication Sync State : 
Last Status Change     : 2021-07-11 20:06:33
pgsql@node114-> 
pgsql@node114-> pcp_node_info -U pgpool -h localhost -p 9898 -n 2 -v
Password: 
Hostname               : 192.168.0.112
Port                   : 5432
Status                 : 0
Weight                 : 0.333333
Status Name            : unused
Role                   : standby
Replication Delay      : 0
Replication State      : 
Replication Sync State : 
Last Status Change     : 2021-07-11 20:06:33
pgsql@node114->

可以看到两个备库为 unused
pgpool -m fast stop
pgpool -C -D
可能需要多尝试几次

测试读写分离+负载均衡

测试之前，可以考虑修改文件pgpool.conf中的如下参数：

cat >> /postgresql/pgpool/etc/pgpool.conf << "EOF"
log_statement=all
log_per_node_statement =on
client_min_messages =log
log_min_messages = info
EOF
pgpool reload

测试完成后，修改回原值：

log_statement=off
log_per_node_statement = off
# client_min_messages =notice
# log_min_messages = warning

测试, 开三个窗口

窗口1:
Last Status Change     : 2021-07-11 20:29:00
pgsql@node114-> psql -Upostgres -p 9999
psql (13.3)
Type "help" for help.

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from test_pgpool;
LOG:  statement: select * from test_pgpool;
LOG:  DB node id: 0 backend pid: 73212 statement: SELECT version()
LOG:  DB node id: 0 backend pid: 73212 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
LOG:  DB node id: 0 backend pid: 73212 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test_pgpool' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
LOG:  DB node id: 0 backend pid: 73212 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relpersistence = 'u'
LOG:  DB node id: 0 backend pid: 73212 statement: select * from test_pgpool;
 id 
----
  1
  2
  3
(3 rows)

test=# select * from test_pgpool;


窗口2:
test=# \q
pgsql@node114-> psql -Upostgres -h 192.168.0.114 -p 9999 -d test
Password for user postgres: 
psql (13.3)
Type "help" for help.

test=# select * from test_pgpool;
LOG:  statement: select * from test_pgpool;
LOG:  DB node id: 0 backend pid: 73232 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
LOG:  DB node id: 0 backend pid: 73232 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test_pgpool' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
LOG:  DB node id: 0 backend pid: 73232 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relpersistence = 'u'
LOG:  DB node id: 1 backend pid: 67106 statement: select * from test_pgpool;
 id 
----
  1
  2
  3
(3 rows)


窗口3:
pgsql@node114->  psql -Upostgres -h 192.168.0.114 -p 9999 -d test
Password for user postgres: 
psql (13.3)
Type "help" for help.

test=# show pool_nodes;
LOG:  statement: show pool_nodes;
LOG:  DB node id: 0 backend pid: 73272 statement: SELECT version()
 node_id |   hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replic
ation_delay | replication_state | replication_sync_state | last_status_change  
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------
------------+-------------------+------------------------+---------------------
 0       | 192.168.0.110 | 5432 | up     | 0.333333  | primary | 11         | false             | 0     
            |                   |                        | 2021-07-11 20:29:20
 1       | 192.168.0.111 | 5432 | up     | 0.333333  | standby | 2          | false             | 0     
            |                   |                        | 2021-07-11 20:29:20
 2       | 192.168.0.112 | 5432 | up     | 0.333333  | standby | 0          | true              | 0     
            |                   |                        | 2021-07-11 20:29:20
(3 rows)

test=# select * from test_pgpool;
LOG:  statement: select * from test_pgpool;
LOG:  DB node id: 0 backend pid: 73272 statement: SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog'
LOG:  DB node id: 0 backend pid: 73272 statement: SELECT count(*) FROM pg_catalog.pg_class AS c, pg_namespace AS n WHERE c.relname = 'test_pgpool' AND c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
LOG:  DB node id: 0 backend pid: 73272 statement: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pg_catalog.to_regclass('"test_pgpool"') AND c.relpersistence = 'u'
LOG:  DB node id: 2 backend pid: 67873 statement: select * from test_pgpool;
 id 
----
  1
  2
  3
(3 rows)

关闭客户端日志打印

将之前修改的客户端日志打印配置删除
相当于

log_statement=off
log_per_node_statement = off
# client_min_messages =notice
# log_min_messages = warning

相关阅读:
今日总结
 每日总结
 每日总结
 每日总结
 重返现世
 [PKUWC2018]随机游走
 [HAOI2015]按位或
 [NOI2020] 超现实树
 [NOI2017] 游戏
 [CSACADEMY]Card Groups
原文地址：https://www.cnblogs.com/yldf/p/16293922.html

CentOS7.9 安装 PG13 + repmgr +pgpool 实现负载均衡读写分离

环境规划

Linux 安装

设置开机启动级别（字符界面而不是GUI界面）

关闭防火墙与 selinux

配置IP 与 DNS

linux 系统参数配置

上传 iso 包

挂载

临时挂载

永久挂载

测试挂载是否OK

备份原有 yum 源

新增YUM配置文件

刷新YUM缓存

yum源更新命令

安装相关包

准备工作

环境变量配置

关闭透明大页

克隆虚拟机之后

下载 repmgr [ALL]

配置ip别名 [ALL]

编译安装 repmgr [ALL]

配置 pgsql 互信 5台机器 [node110]

测试

配置防火墙 [ALL]

配置归档等参数 [ALL]

启动数据库 [ALL]

创建相关用户 [ALL]

所有节点执行 以 pgsql用户执行

测试配置是否OK

所有个节点分别修改 repmgr.conf

主库注册 [node110]

备库克隆与注册 [node111; node112]

正常switchover [在 node111]

配置自动 fail over

测试自动切换

等node110 修复之后, 重新加入集群

加入集群之后, 在通过 swtichover 手动切换回主库

下载安装 pgpool2

环境变量配置

拷贝参数

修改pgpool.conf

配置 pool_hba.conf

生成pool_passwd文件

配置 pcp.conf

在主库中创建用户

node114

实际操作

连接 pgpool

查看pgpool 后端状态

测试读写分离+负载均衡

测试, 开三个窗口

关闭客户端日志打印

所有节点执行以 pgsql用户执行