安装部署postgresql-15 高可用（pgpool）
前言
装部署postgresql-15 高可用（pgpool）一主二从
部署环境：
centos7 x86_64
数据库版本： postgresql 15
服务器IP：
10.10.3.11
10.10.3.12
10.10.3.13
vip : 10.10.3.100
下载地址：
官网地址：
https://www.postgresql.org/download/linux/redhat/
RPM包下载：
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
安装pg15
sudo yum install -y postgresql15-server
1 创建pg15的安装目录
mkdir -p /u01/apps/pgsql15/data
赋权
chown -R postgres:postgres /u01/apps/pgsql15/
2 yum安装数据库
yum install -y postgresql15-server
yum install epel-release – 依赖
3 初始化数据库
su - postgres
/usr/pgsql-15/bin/initdb -D /u01/apps/pgsql15/data
4.修改服务启动脚本
用root用户修改 /usr/lib/systemd/system/postgresql-15.service 文件，将其中的PGDATA修改为新的数据目录
vim /usr/lib/systemd/system/postgresql-15.service
# Location of database directory
Environment=PGDATA=/u01/apps/pgsql15/data/
sed -i "/^Environment=PGDATA/cEnvironment=PGDATA=\/u01\/apps\/pgsql15\/data\/" /usr/lib/systemd/system/postgresql-15.service
打开端口
firewall-cmd --permanent --zone=public --add-service=postgresql
firewall-cmd --permanent --zone=public --add-port=5432/tcp
firewall-cmd --reload
安装pgpool
官网下载地址：
https://www.pgpool.net/yum/rpms/4.3/redhat/rhel-7-x86_64/
1 创建文件目录
mkdir -p /u01/apps/pgpool
chown -R postgres:postgres /u01/apps/pgpool/
2.yum安装 pgpool
yum localinstall *.rpm -y
配置自启
systemctl enable postgresql-15
systemctl disable pgpool.service
systemctl disable postgresql-15
配置参数
修改 postgresql.conf 文件
su - postgres
mkdir -p /u01/apps/pgsql15/archivedir/ --归档日志目录
-- 配置允许PG远程登录，注意版本：
cat >> /u01/apps/pgsql15/data/postgresql.conf << "EOF"
listen_addresses = '*'
port=5432
logging_collector = on
log_truncate_on_rotation = on
archive_mode = on
archive_command = 'cp "%p" "/u01/apps/pgsql15/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
password_encryption = 'md5'
EOF
修改 pg_hba.conf文件
sed -i '$a host\tall\t\tall\t\t0.0.0.0\/0\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf
sed -i '$a host\treplication\t\all\t\t10.10.3.0\/24\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf
启动数据库
systemctl start postgresql-15
创建用户，并修改密码
psql -U postgres -p 5432
CREATE ROLE pgpool WITH LOGIN;
CREATE ROLE repl WITH REPLICATION LOGIN;
ALTER USER postgres ENCRYPTED PASSWORD ‘postgres123’;
alter user postgres with encrypted password ‘postgres123’;
alter user pgpool with encrypted password ‘postgres123’;
alter user repl with encrypted password ‘postgres123’;
GRANT pg_monitor TO pgpool;
配置互信
配置SSH，实现无密码登录效果
需要在每台服务器都配置一遍
########################root用户
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13
#########################postgres用户
su - postgres
cd ~/.ssh
ssh-keygen -t rsa -f id_rsa_pgpool
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12
ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13
[root@pg1 .ssh]# su - postgres
上一次登录：五 3月 10 18:26:41 CST 2023pts/0 上
最后一次失败的登录：一 3月 13 10:27:37 CST 2023从 pg1ssh:notty 上
最有一次成功登录后有 1 次失败的登录尝试。
-bash-4.2$ cd ~/.ssh
-bash-4.2$ ll
总用量 20
-rw-------. 1 postgres postgres 5484 3月 13 10:27 authorized_keys
-rw-------. 1 postgres postgres 1679 3月 10 18:27 id_rsa_pgpool
-rw-r--r--. 1 postgres postgres 394 3月 10 18:27 id_rsa_pgpool.pub
-rw-r--r--. 1 postgres postgres 516 3月 3 11:03 known_hosts
-bash-4.2$ ssh-keygen -t rsa -f id_rsa_pgpool
Generating public/private rsa key pair.
id_rsa_pgpool already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in id_rsa_pgpool.
Your public key has been saved in id_rsa_pgpool.pub.
The key fingerprint is:
SHA256:757CudETkbmp7nZJgXTx1ZMww5/DSGGW3nTjpc7QPbY postgres@pg1
The key's randomart image is:
+---[RSA 2048]----+
| ...B=..|
| . .+o=o=+|
| . o+ +.Bo*|
| . .+.ooX.|
| S +. +. +|
| +.. oE |
| .oo+. |
| .=ooo |
| o+=+ |
+----[SHA256]-----+
-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.11
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@10.10.3.11's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@10.10.3.11'"
and check to make sure that only the key(s) you wanted were added.
-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.12
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@10.10.3.12's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@10.10.3.12'"
and check to make sure that only the key(s) you wanted were added.
-bash-4.2$ ssh-copy-id -i id_rsa_pgpool.pub postgres@10.10.3.13
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "id_rsa_pgpool.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
postgres@10.10.3.13's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'postgres@10.10.3.13'"
and check to make sure that only the key(s) you wanted were added.
-bash-4.2$ exit
使用ssh postgres@10.10.3.12-i ~/.ssh/id_rsa_pgpool指令检验是否需要密码
若需要密码，上述目录均执行restorecon -R -v ~/.ssh
配置pgpass文件
-- 配置psql的密码
cat > /u01/apps/pgsql15/.pgpass <<"EOF"
10.10.3.11:5432:replication:repl:postgres123
10.10.3.12:5432:replication:repl:postgres123
10.10.3.13:5432:replication:repl:postgres123
10.10.3.100:9999:postgres:pgpool:postgres123
10.10.3.100:9999:postgres:postgres:postgres123
10.10.3.11:5432:postgres:postgres:postgres123
10.10.3.12:5432:postgres:postgres:postgres123
10.10.3.13:5432:postgres:postgres:postgres123
EOF
chown postgres:postgres /u01/apps/pgsql15/data/.pgpass
chmod 600 /u01/apps/pgsql15/data/.pgpass
echo 'localhost:9898:pgpool:postgres123' > /u01/apps/pgsql15/data/.pcppass
chmod 600 /u01/apps/pgsql15/data/.pcppass
chown postgres.postgres /u01/apps/pgsql15/data/.pcppass
echo 'pgpool:'`pg_md5 -u=pgpool postgres123` >> /etc/pgpool-II/pcp.conf
创建pgpool_node_id
10.10.3.11
echo “0” > /etc/pgpool-II/pgpool_node_id
10.10.3.12
echo “1” > /etc/pgpool-II/pgpool_node_id
10.10.3.13
echo “2” > /etc/pgpool-II/pgpool_node_id
Pgpool-II参数配置
cat > /etc/pgpool-II/pgpool.conf <<"EOF"
backend_clustering_mode = 'streaming_replication'
# - pgpool Connection Settings -
listen_addresses = '*'
port=9999
socket_dir = '/u01/apps/pgsql15/data/'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/u01/apps/pgsql15/data/'
# - Streaming Replication Check
sr_check_user = 'pgpool'
sr_check_password = ''
# - Health Check
health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = 'postgres123'
health_check_max_retries = 3
search_primary_node_timeout = 10min
# - Backend Connection Settings -
backend_hostname0 = '10.10.3.11'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/u01/apps/pgsql15/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
#backend_application_name0 = 'pg1'
backend_hostname1 = '10.10.3.12'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/u01/apps/pgsql15/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#backend_application_name0 = 'pg2'
backend_hostname2 = '10.10.3.13'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/u01/apps/pgsql15/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
#backend_application_name0 = 'pg3'
# - Failover configuration
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
# - Online Recovery Configurations
recovery_user = 'postgres'
recovery_password = 'postgres123'
recovery_1st_stage_command = 'recovery_1st_stage'
# - Client Authentication Configuration
enable_pool_hba = on
pool_passwd = '/etc/pgpool-II/pool_passwd'
# - Watchdog Configuration
use_watchdog = on
delegate_IP = '10.10.3.100'
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:1'
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
hostname0 = '10.10.3.11'
wd_port0 = 9000
pgpool_port0 = 9999
hostname1 = '10.10.3.12'
wd_port1 = 9000
pgpool_port1 = 9999
hostname2 = '10.10.3.13'
wd_port2 = 9000
pgpool_port2 = 9999
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
heartbeat_hostname0 = '10.10.3.11'
heartbeat_port0 = 9694
heartbeat_device0 = ''
heartbeat_hostname1 = '10.10.3.12'
heartbeat_port1 = 9694
heartbeat_device1 = ''
heartbeat_hostname2 = '10.10.3.13'
heartbeat_port2 = 9694
heartbeat_device2 = ''
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
wd_escalation_command = '/etc/pgpool-II/escalation.sh'
# - Where to log -
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/pgpool-II'
log_filename = 'pgpool-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
pid_file_name = '/var/run/postgresql/pgpool.pid'
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
EOF
##修改配置
cp -p /etc/pgpool-II/failover.sh{.sample,}
cp -p /etc/pgpool-II/follow_primary.sh{.sample,}
chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_primary.sh}
chmod +x /etc/pgpool-II/{failover.sh,follow_primary.sh}
## Pgpool-II在线恢复配置
##**把recovery_First_stage和pgpool_remote_start脚本放在pgpool安装目录上**
cp -p /etc/pgpool-II/recovery_1st_stage.sample /u01/apps/pgsql15/data/recovery_1st_stage
cp -p /etc/pgpool-II/pgpool_remote_start.sample /u01/apps/pgsql15/data/pgpool_remote_start
chown postgres:postgres /u01/apps/pgsql15/data/{recovery_1st_stage,pgpool_remote_start}
chmod +x /u01/apps/pgsql15/data/{recovery_1st_stage,pgpool_remote_start}
#注意脚本recovery_1st_stage和pgpool_remote_start中的PGHOME是否配置正确。脚本follow_primary.sh中的PCP_USER为pgpool。
#为了使用在线恢复功能，需要pgpool_recovery、pgpool_remote_start、pgpool_switch_xlog等功能，因此我们需要在PostgreSQL server server1的template1上安装pgpool_recovery。
# 创建pg插件
su - postgres
psql template1 -c "CREATE EXTENSION pgpool_recovery"
psql postgres -c "CREATE EXTENSION pgpool_recovery"
## 修改 pool_hba.conf文件
echo "host all all 0.0.0.0/0 md5" >> /etc/pgpool-II/pool_hba.conf
## 配置pool_passwd
-- cat /etc/pgpool-II/pool_passwd
pg_md5 --md5auth --username=pgpool "postgres123"
pg_md5 --md5auth --username=postgres "postgres123"
## 看门狗
cat > /etc/pgpool-II/escalation.sh <<"EOF"
#!/bin/bash
# This script is run by wd_escalation_command to bring down the virtual IP on other pgpool nodes
# before bringing up the virtual IP on the new active pgpool node.
set -o xtrace
PGPOOLS=(10.10.3.11 10.10.3.12 10.10.3.13)
VIP=10.10.3.100
DEVICE=eth0
for pgpool in "${PGPOOLS[@]}"; do
[ "$HOSTNAME" = "$pgpool" ] && continue
ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@$pgpool -i ~/.ssh/id_rsa_pgpool "
/usr/bin/sudo /sbin/ip addr del $VIP/24 dev $DEVICE
"
done
exit 0
EOF
chown postgres:postgres /etc/pgpool-II/escalation.sh
chmod +x /etc/pgpool-II/*.sh
chown postgres:postgres /etc/pgpool-II/*.conf
参数分析：
故障转移参数：
# - Failover configuration
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
pgpool在线恢复功能：
为了使用Pgpool II执行在线恢复，我们指定PostgreSQL用户名和在线恢复命令recovery_1st_stage。因为执行在线恢复需要PostgreSQL中的超级用户权限，所以我们在recovery_user中指定postgres_user。然后，我们在PostgreSQL主服务器（server1）的数据库集群目录中创建recovery_1st_stage和pgpool_remote_start，并添加执行权限
# - Online Recovery Configurations
recovery_user = 'postgres'
recovery_password = 'postgres123'
recovery_1st_stage_command = 'recovery_1st_stage'
配置PostgreSQL主备库
1. 启动Pgpool-II
systemctl start pgpool.service
systemctl status pgpool.service
节点1已经有VIP了。
2. 3个节点都启动pgpool
systemctl start pgpool.service
systemctl status pgpool.service
3. 利用pgpool的在线恢复功能配置数据库主从
确保pcp_recovery_node命令使用的recovery_1st_stage和pgpool_remote_start脚本位于PostgreSQL主服务器（server1）的数据库群集目录中。
[postgres@lhrpg30 data]$ pcp_recovery_node -h 10.10.3.12 -p 9898 -U pgpool -n 1
Password:
pcp_recovery_node -- Command Successful
[postgres@lhrpg30 data]$ pcp_recovery_node -h 172.72.6.30 -p 9898 -U pgpool -n 2
Password:
pcp_recovery_node -- Command Successful
验证节点2和节点3是否作为PostgreSQL备用服务器启动
4. 配置从的服务器
scp -p /etc/pgpool-II/pgpool.conf root@10.10.3.12:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/pgpool.conf root@10.10.3.13:/etc/pgpool-II/pgpool.conf
scp -p /etc/pgpool-II/pool_passwd root@10.10.3.12:/etc/pgpool-II/pool_passwd
scp -p /etc/pgpool-II/pool_passwd root@10.10.3.13:/etc/pgpool-II/pool_passwd
scp -p /etc/pgpool-II/pcp.conf root@10.10.3.12:/etc/pgpool-II/pcp.conf
scp -p /etc/pgpool-II/pcp.conf root@10.10.3.13:/etc/pgpool-II/pcp.conf
scp -p /etc/pgpool-II/pool_hba.conf root@10.10.3.12:/etc/pgpool-II/pool_hba.conf
scp -p /etc/pgpool-II/pool_hba.conf root@10.10.3.13:/etc/pgpool-II/pool_hba.conf
scp -p /etc/pgpool-II/escalation.sh.sample root@10.10.3.12:/etc/pgpool-II/escalation.sh.sample
scp -p /etc/pgpool-II/escalation.sh.sample root@10.10.3.13:/etc/pgpool-II/escalation.sh.sample
scp -p /etc/pgpool-II/failover.sh.sample root@10.10.3.12:/etc/pgpool-II/failover.sh.sample
scp -p /etc/pgpool-II/failover.sh.sample root@10.10.3.13:/etc/pgpool-II/failover.sh.sample
scp -p /etc/pgpool-II/follow_primary.sh.sample root@10.10.3.12:/etc/pgpool-II/follow_primary.sh.sample
scp -p /etc/pgpool-II/follow_primary.sh.sample root@10.10.3.13:/etc/pgpool-II/follow_primary.sh.sample
测试 切换active/standby watchdog
pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpool文章来源：https://www.toymoban.com/news/detail-440579.html
停止server1节点或者停止server1上的Pgpool-II服务，vip切换到其他服务器，在此启动
server1上的Pgpool-II服务， 该服务会以standby角色运行。文章来源地址https://www.toymoban.com/news/detail-440579.html
[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpool
Password:
3 3 YES 10.10.3.13:9999 Linux pg13 10.10.3.13
10.10.3.13:9999 Linux pg13 10.10.3.13 9999 9000 4 LEADER 0 MEMBER
10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER
10.10.3.12:9999 Linux pg12 10.10.3.12 9999 9000 7 STANDBY 0 MEMBER
[root@pg13 pgpool-II]# systemctl stop pgpool
[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpool
Password:
FATAL: authentication failed for user "pgpool"
DETAIL: username and/or password does not match
[root@pg13 pgpool-II]# systemctl start pgpool
[root@pg13 pgpool-II]# pcp_watchdog_info -h 10.10.3.100 -p 9898 -U pgpool
Password:
3 3 YES 10.10.3.12:9999 Linux pg12 10.10.3.12
10.10.3.12:9999 Linux pg12 10.10.3.12 9999 9000 4 LEADER 0 MEMBER
10.10.3.11:9999 Linux pg1 10.10.3.11 9999 9000 7 STANDBY 0 MEMBER
10.10.3.13:9999 Linux pg13 10.10.3.13 9999 9000 7 STANDBY 0 MEMBER
root@pg12 pgpool-II]# psql -h 10.10.3.100 -p 9999 -U pgpool postgres -c "show pool_nodes"
用户 pgpool 的口令：
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.10.3.11 | 5432 | up | up | 0.333333 | primary | primary | 0 | false | 0 | | | 2023-03-14 08:44:33
1 | 10.10.3.12 | 5432 | up | up | 0.333333 | standby | standby | 0 | true | 0 | | | 2023-03-14 08:44:33
2 | 10.10.3.13 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-03-14 08:44:33
(3 行记录)
到了这里，关于安装部署postgresql-15 高可用（pgpool）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
