安装部署postgresql-15 高可用（pgpool）_postgresql15 + pgpool 构建容灾高可用集群

安装部署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
1

安装pg15

sudo yum install -y postgresql15-server
1

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/
1
2

sed -i "/^Environment=PGDATA/cEnvironment=PGDATA=\/u01\/apps\/pgsql15\/data\/" /usr/lib/systemd/system/postgresql-15.service
1

打开端口

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

修改 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
1

sed -i '$a host\treplication\t\all\t\t10.10.3.0\/24\t\tmd5' /u01/apps/pgsql15/data/pg_hba.conf
1

启动数据库
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

使用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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

创建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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186

参数分析：
故障转移参数：

# - 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'
1
2
3

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'
1
2
3
4

配置PostgreSQL主备库

1. 启动Pgpool-II

systemctl start pgpool.service
systemctl status pgpool.service
1
2

节点1已经有VIP了。

2. 3个节点都启动pgpool

systemctl start pgpool.service
systemctl status pgpool.service
1
2

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
1
2
3
4
5
6
7

验证节点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

停止server1节点或者停止server1上的Pgpool-II服务，vip切换到其他服务器，在此启动
server1上的Pgpool-II服务， 该服务会以standby角色运行。

[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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 行记录)
1
2
3
4
5
6
7
8