简介

Pgpool-II 是一个专门为 PostgreSQL 提供中间层功能的软件，位于 PostgreSQL 客户端与后端数据库服务器之间，提供：连接池（Connection Pooling）、负载均衡（Load Balancing）、主备切换（Failover / Watchdog）、读写分离、并行查询（Parallel Query）、复制功能（基于流复制）、自动故障转移。
在安装方便，我觉得pgpool是PG高可用方案里面最复杂的一个。配置文件也是比较多，运维起来也是非常复杂。先初步了解一下相关参数及其功能

集群部署的默认配置

Pgpool-II 配置参数说明

1. 后端集群模式（BACKEND CLUSTERING MODE）

2. 连接设置（CONNECTIONS）

3. 身份认证（AUTHENTICATION）

4. SSL 支持（SSL CONNECTIONS）

5. 连接池（POOLS）

6. 日志（LOGS）

7. 复制模式（REPLICATION MODE）

8. 负载均衡（LOAD BALANCING MODE）

9. 流复制检测（STREAMING REPLICATION MODE）

10. 健康检查（HEALTH CHECK）

11. 故障转移与在线恢复（FAILOVER & ONLINE RECOVERY）

12. Watchdog 高可用（WATCHDOG）

13. 其它与查询缓存（OTHERS & QUERY MEMORY CACHE）

postgresql安装

由于rel7已经结束生命周期，所以rel7只能下载到PG15、pgpool-II-4.4。更高版本需要进行源码安装。

## 检查是否已经存在PGDG
rpm -ql pgdg-redhat-repo
yum remove -y pgdg-redhat-repo
userdel postgres 
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql15-server postgresql15-contrib  
sudo sed -i '/^\[.*\]/a exclude=pgpool*' /etc/yum.repos.d/pgdg-redhat-all.repo  ## PGDG中禁用pgpool包

创建归档目录
mkdir -p /var/lib/pgsql/archivedir
chown -R postgres:postgres /var/lib/pgsql/archivedir

pgpool安装

rpm -e pgpool-II-release
rpm -Uvh --force https://www.pgpool.net/yum/rpms/4.4/redhat/rhel-7-x86_64/pgpool-II-release-4.4-1.noarch.rpm
yum clean all
yum makecache
yum install -y pgpool-II-pg15 pgpool-II-pg15-extensions 
chown -R postgres:postgres /etc/pgpool-II/

初始化数据库

## 选用一个节点作为主节点进行初始化数据库
/usr/pgsql-15/bin/postgresql-15-setup initdb

cat >> /var/lib/pgsql/15/data/postgresql.conf << EOF
shared_buffers =300MB
wal_buffers = 16MB
max_wal_size=1GB
min_wal_size=250MB
wal_keep_size = 1GB
shared_preload_libraries = 'pg_stat_statements'
listen_addresses = '*'
port = 5432
superuser_reserved_connections = 20
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
vacuum_cost_delay = 10
bgwriter_delay = 10ms
synchronous_commit = off
wal_writer_delay = 10ms
logging_collector = on
log_directory = 'log'
log_filename ='postgresql-%Y-%m-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
wal_level = logical
wal_log_hints=on
checkpoint_timeout = 5min
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/archivedir/%f && cp %p /var/lib/pgsql/archivedir/%f'
max_wal_senders = 3
wal_level = hot_standby
max_wal_senders = 3
password_encryption = md5
EOF

##选用一个节点作为主节点修改白名单

## 由于postgresql采用的rpm安装 hba对于本地访问的链接 method采用的是peer。所以这里采用覆盖hba文件的方式
cat > /var/lib/pgsql/15/data/pg_hba.conf << 'EOF'
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host all postgres 10.0.0.141/32 trust
host all postgres 10.0.0.142/32 trust
host all postgres 10.0.0.143/32 trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all        0/0  md5
host	  all		 all        0/0     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
EOF

cat /var/lib/pgsql/15/data/pg_hba.conf

 systemctl start postgresql-15.service 
 systemctl status postgresql-15.service ## 集群不采用systdm的方式启动postgresql，这里启动只是才看是否启动正常。
 systemctl stop postgresql-15.service

配置postgres用户免密（所有节点执行）

cat >> /etc/hosts << EOF
10.0.0.141 vm141
10.0.0.142 vm142
10.0.0.143 vm143
EOF


cat >> ~postgres/.bash_profile << 'EOF'  
export PGPORT=5432
export PGDATA=/var/lib/pgsql/15/data/
export PGHOME=/usr/pgsql-15
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGDATABASE=postgres
EOF

选用一个节点作为主节点，创建集群管理相关用户和辅助插件,只需要在主节点

su - postgres 
pg_ctl start 
 psql -U postgres template1 -c "CREATE EXTENSION pgpool_recovery;CREATE EXTENSION pgpool_adm;" 
 psql -U postgres postgres  -c "CREATE EXTENSION pgpool_recovery;CREATE EXTENSION pgpool_adm;" 
psql -U postgres -d postgres 
set password_encryption = 'md5';
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE repl WITH REPLICATION LOGIN;
alter user postgres with password 'postgres';
alter user pgpool with password 'pgpool';
alter user repl with password 'repl';
GRANT pg_monitor TO pgpool;  

root 免密（所有节点同步执行）

rm -rf ~root/.ssh
mkdir ~root/.ssh
cd  ~root/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool

ssh-copy-id -i id_rsa_pgpool.pub root@vm141
ssh-copy-id -i id_rsa_pgpool.pub root@vm142
ssh-copy-id -i id_rsa_pgpool.pub root@vm143
chmod 600 ~root/.ssh/* 
chmod 644 ~root/.ssh/*.pub 
chmod 700 ~root/.ssh
##效验免密（所有节点同步执行）
ssh root@vm141  -i ~root/.ssh/id_rsa_pgpool    date
ssh root@vm142  -i ~root/.ssh/id_rsa_pgpool    date
ssh root@vm143  -i ~root/.ssh/id_rsa_pgpool    date

## 为postgres添加sudo权限

echo "postgres ALL=(ALL) NOPASSWD:ALL" | sudo EDITOR='tee -a' visudo
sudo grep "^postgres" /etc/sudoers

postgres 免密（所有节点同步执行）

##设置postgres密码
echo "postgres:postgres" |chpasswd 

su - postgres 
rm -rf ~postgres/.ssh
mkdir ~postgres/.ssh
chmod 700 ~postgres/.ssh
cd ~postgres/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool

ssh-copy-id -i id_rsa_pgpool.pub postgres@vm141
ssh-copy-id -i id_rsa_pgpool.pub postgres@vm142
ssh-copy-id -i id_rsa_pgpool.pub postgres@vm143
chmod 600 ~postgres/.ssh/* 
chmod 644 ~postgres/.ssh/*.pub 
chmod 700 ~postgres/.ssh

#效验免密（所有节点同步执行）
ssh postgres@vm141  -i ~postgres/.ssh/id_rsa_pgpool    date
ssh postgres@vm142  -i ~postgres/.ssh/id_rsa_pgpool    date
ssh postgres@vm143  -i ~postgres/.ssh/id_rsa_pgpool    date

配置pgpass（所有节点同步执行）

如果你是手工创建的postgres用户，其工作目录默认为/home/postgres,需要执行以下命令修改其postgres主目录。如果是安装postgresql是默认添加的用户，其postgres主目录为/var/lib/pgsql ，不需要修改。
usermod -d /var/lib/pgsql -m postgres ### 看情况操作

su - postgres 
cat > ~postgres/.pgpass << EOF
localhost:5432:replication:repl:repl
localhost:9999:postgres:pgpool:pgpool
localhost:5432:postgres:postgres:postgres
vm141:5432:replication:repl:repl
vm142:5432:replication:repl:repl
vm143:5432:replication:repl:repl
vm141:9999:postgres:pgpool:pgpool
vm142:9999:postgres:pgpool:pgpool
vm143:9999:postgres:pgpool:pgpool
vm141:5432:postgres:postgres:postgres
vm142:5432:postgres:postgres:postgres
vm143:5432:postgres:postgres:postgres

EOF

chmod 600 ~postgres/.pgpass

follow_primary.sh 脚本会使用到，执行PCP命令必须不输入密码， 创建 ~postgres/.pcppass （Pgpool-II用户的主目录运行为）。 ~postgres/.pcppass的格式为 “ hostname:port:username:password ”。在本例中，PCP用户 pgpool 密码为 pgpool 。

cat >  ~postgres/.pcppass  << EOF
localhost:9898:pgpool:pgpool
vm141:9898:pgpool:pgpool
vm142:9898:pgpool:pgpool
vm143:9898:pgpool:pgpool
EOF

chmod 600 ~postgres/.pcppass

chown -R postgres:postgres ~postgres

如果开启 watchdog 特性，为了区分哪个主机是哪个主机， 需要一个 pgpool_node_id 文件。 需要创建“ pgpool_node_id ”文件，并指定pgpool （watchdog）节点号 （例如0,1,2…）来唯一标识pgpool（watchdog ）主机。

## vm141
echo 0 > /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id

## vm142
echo 1 > /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id

## vm143
echo 2 > /etc/pgpool-II/pgpool_node_id
cat /etc/pgpool-II/pgpool_node_id

使用YUM安装Pgpool-II时， Pgpool-II配置文件 pgpool.conf 安装在 /etc/pgpool-II 。从Pgpool-II 4.2开始，所有配置参数都是 在所有主机上都一样，以下是pgpool.conf相关参数解析

pgpool配置demo

复用failover.sh.sample、follow_primary.sh.sample

cp -p /etc/pgpool-II/sample_scripts/failover.sh.sample /etc/pgpool-II/failover.sh
cp -p /etc/pgpool-II/sample_scripts/follow_primary.sh.sample /etc/pgpool-II/follow_primary.sh
cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /etc/pgpool-II/recovery_1st_stage
cp -p /etc/pgpool-II/sample_scripts/escalation.sh.sample /etc/pgpool-II/escalation.sh
cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/15/data/pgpool_remote_start
chmod +x /var/lib/pgsql/15/data/pgpool_remote_start
chown postgres:postgres /var/lib/pgsql/ -R
chown postgres:postgres /etc/pgpool-II/ -R

cp -p /etc/pgpool-II/sample_scripts/recovery_1st_stage.sample /var/lib/pgsql/15/data/recovery_1st_stage
cp -p /etc/pgpool-II/sample_scripts/pgpool_remote_start.sample /var/lib/pgsql/15/data/pgpool_remote_start
cp -p /etc/pgpool-II/sample_scripts/escalation.sh.sample  /etc/pgpool-II/escalation.sh
cp /etc/pgpool-II/sample_scripts/failover.sh.sample  /etc/pgpool-II/failover.sh
cp /etc/pgpool-II/sample_scripts/follow_primary.sh.sample  /etc/pgpool-II/follow_primary.sh
chown -R postgres:postgres /var/lib/pgsql/15/data/{recovery_1st_stage,pgpool_remote_start}   ## postgres用户执行cp 无需再授权
chown -R postgres:postgres /etc/pgpool-II   ## postgres用户执行cp 无需再授权
chmod +x /etc/pgpool-II/*.sh
## 检查配置项目得核心变量是否准确
grep -HnE '^(PGHOME|ARCHIVEDIR|primary_conninfo|REPLUSER|PCP_USER|PGPOOL_PATH|PCP_PORT|REPL_SLOT_NAME|PGUSER_SUPER|PGUSER_SUPER_PWD|PGUSER_SUPER_DBNAME|PGUSER_REPLI|PGUSER_REPLI_PWD)' \
/etc/pgpool-II/failover.sh \
/etc/pgpool-II/follow_primary.sh \
/etc/pgpool-II/escalation.sh \
/var/lib/pgsql/15/data/recovery_1st_stage \
/var/lib/pgsql/15/data/pgpool_remote_start


## 确认与一下路径保持一致
## PGHOME=/usr/pgsql-15
## REPLUSER=repl
## PCP_USER=pgpool
## PGPOOL_PATH=/usr/bin
## PCP_PORT=9898
## ARCHIVEDIR=/var/lib/pgsql/archivedir
## REPLUSER=repl

## 检查备份参数正确
grep -HnE '/bin/pg_basebackup*' \
/etc/pgpool-II/failover.sh \
/etc/pgpool-II/follow_primary.sh \
/etc/pgpool-II/escalation.sh \
/var/lib/pgsql/15/data/recovery_1st_stage \
/var/lib/pgsql/15/data/pgpool_remote_start

为pg_basebackup 增加参数R
sed -i ‘s|/bin/pg_basebackup|/bin/pg_basebackup -R|g’
/etc/pgpool-II/follow_primary.sh
/var/lib/pgsql/15/data/recovery_1st_stage

检查.pgpass 目录正确

grep -HnE '*passfile*' \
/etc/pgpool-II/failover.sh \
/etc/pgpool-II/follow_primary.sh \
/etc/pgpool-II/escalation.sh \
/var/lib/pgsql/15/data/recovery_1st_stage \
/var/lib/pgsql/15/data/pgpool_remote_start

对于以上三个文件中得pg_basebackup指令 增加参数 R

配置escalation.sh

## 修改PGPOOLS、VIP、DEVICE参数值

sed -i 's/^VIP=.*/VIP=10.0.0.144/' /etc/pgpool-II/escalation.sh
sed -i 's/^DEVICE=.*/DEVICE=ens33/' /etc/pgpool-II/escalation.sh
sed -i 's/^PGPOOLS=.*/PGPOOLS=(10.0.0.141 10.0.0.142 10.0.0.143)/' /etc/pgpool-II/escalation.sh
cat  /etc/pgpool-II/escalation.sh

配置pool_hba.conf

cat >> /etc/pgpool-II/pool_hba.conf << EOF
host all postgres 10.0.0.141/32 trust
host all postgres 10.0.0.142/32 trust
host all postgres 10.0.0.143/32 trust
host    replication     all        0/0  md5
host	  all		 all        0/0     md5
EOF

cat  /etc/pgpool-II/pool_hba.conf 

使用PCP命令时，PCP用户名和md5加密密码必须为 在 pcp.conf 中声明 “ username:encrypted password ”。在本例中，我们将PCP用户名设置为“ pgpool ” 密码为“ pgpool ”。 使用pg_md5创建加密的密码条目 pgpool 用户如下所示：

pg_md5 -p -m -u postgres 
password:   ## (输入密码postgres)
pg_md5 -p -m -u pgpool 
password: (输入密码pgpool )

cat /etc/pgpool-II/pool_passwd

配置pcp.conf文件

echo 'pgpool:'`pg_md5 pgpool` >> /etc/pgpool-II/pcp.conf
cat /etc/pgpool-II/pcp.conf
## 指令的第二个postgres、pgpool为对应用户访问数据库的实际密码

配置pgpool.conf

cat > /etc/pgpool-II/pgpool.conf << 'EOF'
# [CONNECTIONS] 连接相关
listen_addresses = '*'
port = 9999
pcp_listen_addresses = '*'
backend_clustering_mode = 'streaming_replication'

# [BACKEND CONNECTION SETTINGS] 后端节点设置
backend_hostname0 = 'vm141'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/15/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'vm141'

backend_hostname1 = 'vm142'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/15/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'vm142'

backend_hostname2 = 'vm143'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/15/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'vm143'

# [AUTHENTICATION] 认证相关
enable_pool_hba = on
pool_passwd = '/etc/pgpool-II/pool_passwd'

# [LOGS] 日志设置
log_destination = 'stderr'
logging_collector = on
log_directory = '/etc/pgpool-II/log'
log_filename = 'pgpool-%d.log'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 100MB

# [STREAMING REPLICATION CHECK] 流复制健康检查
sr_check_user = 'postgres'
sr_check_password = ''
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R' 
# 如果使用 Pgpool-II 4.0 或更早版本，则无法指定这些字符。低版本中此参数名为follow_master_command，请注意区分

# [HEALTH CHECK] 健康检查设置
health_check_period = 5
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = ''
failover_on_backend_error = on
health_check_max_retries = 3

# [FAILOVER AND FAILBACK] 故障切换与恢复
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'  
# %N 和 %S 是在 Pgpool-II 4.1 中添加的。 请注意，如果使用 Pgpool-II 4.0 或更早版本，则无法指定这些字符。
#Special values:
#%d = 故障节点的 ID
#%h = 故障节点的主机名
#%p = 故障节点的端口号
#%D = 故障节点的数据库集群路径
#%m = 新的主节点（主库）ID
#%H = 新的主节点主机名
#%M = 原主节点的 ID
#%P = 原主节点（Primary 节点）的 ID
#%r = 新主节点的端口号
#%R = 新主节点的数据库集群路径
#%N = 原主节点的主机名
#%S = 原主节点的端口号
#%% = 字符 % 本身

# [ONLINE RECOVERY] 联机恢复
recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'

# [WATCHDOG] 看门狗设置
use_watchdog = on
hostname0 = 'vm141'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = 'vm142'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = 'vm143'
wd_port2 = 9000
pgpool_port2 = 9999
wd_ipc_socket_dir = '/tmp'

# [VIRTUAL IP CONTROL] 虚拟IP设置
delegate_ip = '10.0.0.144' ## VIP节点

# [ESCALATION SETTINGS] 主节点提升设置
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
wd_interval = 10
wd_escalation_command = '/etc/pgpool-II/escalation.sh'

# [LIFECHECK SETTINGS] 节点存活检测
wd_lifecheck_method = 'heartbeat'

# [HEARTBEAT MODE] 心跳模式参数
heartbeat_hostname0 = 'vm141'
fheartbeat_port0 = 9694
heartbeat_device0 = ''

heartbeat_hostname1 = 'vm142'
heartbeat_port1 = 9694
heartbeat_device1 = ''

heartbeat_hostname2 = 'vm143'
heartbeat_port2 = 9694
heartbeat_device2 = ''

# [INTERFACE SETTINGS] 网络接口设置
if_cmd_path = '/sbin'
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_path = '/usr/sbin'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33' #注意修改网卡名

EOF

cat /etc/pgpool-II/pgpool.conf

启动pgpool服务(所有节点执行)

sudo systemctl start pgpool.service
sudo systemctl status pgpool.service
sudo systemctl enable pgpool.service

备份从节点(主节点执行)

su - postgres   

psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"
pcp_recovery_node -h 10.0.0.144 -p 9898 -U pgpool -W -n 2

psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"
pcp_recovery_node -h 10.0.0.144 -p 9898 -U pgpool -W -n 1

psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"

## 等价于
psql -U postgres template1 -c 'SELECT pgpool_recovery('recovery_1st_stage', 'vm142', '/var/lib/pgsql/15/data', '5432', 2, '5432', 'vm141');'
psql -U postgres template1 -c 'SELECT pgpool_recovery('recovery_1st_stage', 'vm142', '/var/lib/pgsql/15/data', '5432', 2, '5432', 'vm141');'
psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"
## 此时从节点会自动被拉起来

recovery之后会出现pgpool状态down 的情况  使用以下指令使其恢复
pcp_attach_node -h 10.0.0.144 -p 9898 -U pgpool -n 1
pcp_attach_node -h 10.0.0.144 -p 9898 -U pgpool -n 2

切换测试

## 当前主节点停库
pg_ctl stop 
## 几秒中之后可以看到切换成功
psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"

重新拉起旧主之后，旧的主并不会自动加入集群中，保持其停机状态，使用recovery让其恢复。

pcp_recovery_node -h 10.0.0.144 -p 9898 -U pgpool -W -n 0

plus

三节点为备库

测试过程中发现pcp检测到三个节点都为从库，实际上pg只有vm142（从）、vm141（主）互为主从。

## 拉取vm143加入集群
pcp_detach_node -h 10.0.0.144 -p 9898 -U pgpool  -n 2 -v  

psql -h 10.0.0.144  -p 9999 -U pgpool postgres -c "show pool_nodes"