pgpool-II + postgressql 集群高可用搭建(保姆级文档)_pgpool-ii搭建集群

文档背景:用公司集群实际搭建过程,用心整理文档.

文档目的:让更多的开发者避开坑点快速将集群搭建成功!

正文:

我们使用 CentOS 7.4 的 3 台服务器。让这些服务器为server1、 server2、server3。我们在每台服务器上 安装PostgreSQL和Pgpool-II 。

表 8-2。主机名和 IP 地址

表 8-3。PostgreSQL 版本和配置

表 8-4。Pgpool-II 版本及配置

注:由于在服务器上9000端口被clickhouse使用,这里我们看门狗的端口改成90001

8.3.3. 安装
在这个例子中，我们使用 YUM 安装Pgpool-II和PostgreSQL RPM 包。

使用PostgreSQL YUM 存储库 安装PostgreSQL 。

[所有服务器]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
[所有服务器]# yum install -y postgresql11-server
1
2

由于Pgpool-II相关软件包也包含在PostgreSQL YUM 存储库中，因此将“排除”设置添加到/etc/yum.repos.d/pgdg-redhat-all.repo 以便不会从PostgreSQL YUM 存储库 安装Pgpool-II 。

[所有服务器]# vi /etc/yum.repos.d/pgdg-redhat-all.repo
1

以下是/etc/yum.repos.d/pgdg-redhat-all.repo的设置示例。

[pgdg-common]
...
exclude=pgpool*

[pgdg14]
...
exclude=pgpool*

[pgdg13]
...
exclude=pgpool*

[pgdg12]
...
exclude=pgpool*

[pgdg11]
...
exclude=pgpool*

[pgdg10]
...
exclude=pgpool*

[pgdg96]
...
exclude=pgpool*
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

从 Pgpool-II YUM 存储库 安装Pgpool-II 。

[所有服务器]# yum install -y http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
[所有服务器]# yum install -y pgpool-II-pg11-*
1
2

8.3.4. 在开始之前
在开始配置过程之前，请检查以下先决条件。

在主服务器上 设置PostgreSQL流式复制。在这个例子中，我们使用 WAL 归档。

首先，我们创建目录/var/lib/pgsql/archivedir来存储 所有服务器上的WAL段。在这个例子中，只有 Primary 节点 在本地 归档WAL 。

[所有服务器]# su - postgres
[所有服务器]$ mkdir /var/lib/pgsql/archivedir
1
2

在主服务器上 初始化PostgreSQL 。

[server1]# su - postgres
[server1]$ /usr/pgsql-11/bin/initdb -D $PGDATA
1
2

然后我们编辑server1 (primary) 上的配置文件$PGDATA/postgresql.conf如下。启用wal_log_hints 以使用pg_rewind。由于 Primary 可能稍后会成为 Standby，因此我们设置hot_standby = on。

listen_addresses = '*'
archive_mode = on
archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
hot_standby = on
wal_log_hints = on
1
2
3
4
5
6
7
8

我们使用Pgpool-II的在线恢复功能在主服务器启动后设置备用服务器。

由于安全原因，我们创建了一个用户repl仅用于复制目的，以及一个用户pgpool用于Pgpool-II的流复制延迟检查和健康检查。

表 8-5。用户

[server1]# psql -U postgres -p 5432
postgres=# SET password_encryption = 'md5';
postgres=# CREATE ROLE pgpool WITH LOGIN;
postgres=# CREATE ROLE repl WITH REPLICATION LOGIN;
postgres=# \password root
postgres=# \password root
postgres=# \password root
1
2
3
4
5
6
7

如果要在 SHOW POOL NODES命令结果中显示“replication_state”和“replication_sync_state”列，角色pgpool 需要是 PostgreSQL 超级用户或pg_monitor组（ Pgpool -II 4.1 或更高版本）。将pg_monitor 授予pgpool：

GRANT pg_monitor TO pgpool;
1

假设所有Pgpool-II服务器和 PostgreSQL服务器在同一个子网中，编辑pg_hba.conf以启用MD5身份验证方法。(关键)

直接将替换如下:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
host all all 0.0.0.0/0 trust
host replication repl samenet trust
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

要使用Pgpool-II 的自动故障转移和在线恢复，允许Pgpool-II执行用户（默认 root 用户）和postgres用户之间以及postgres用户和postgres用户之间的所有后端服务器的无密码SSH 设置是必要的。在所有服务器上执行以下命令以设置无密码SSH。生成的密钥文件名为id_rsa_pgpool。

[all servers]# mkdir ~/.ssh
[all servers]# chmod 700 ~/.ssh
[all servers]# cd ~/.ssh
[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]# ssh-copy-id postgres@server1
[all servers]# ssh-copy-id postgres@server2
[all servers]# ssh-copy-id postgres@server3

[all servers]# su - postgres
[all servers]$ mkdir ~/.ssh
[all servers]$ chmod 700 ~/.ssh
[all servers]$ cd ~/.ssh
[all servers]$ ssh-keygen -t rsa -f id_rsa_pgpool
[all servers]$ ssh-copy-id  postgres@server1
[all servers]$ ssh-copy-id  postgres@server2
[all servers]$ ssh-copy-id  postgres@server3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

最新版本没有ssh-copy-id命令 用如下命令替代

cat ~/.ssh/id_*.pub|ssh postgres@172.16.1.185 'cat>> ~/.ssh/authorized_keys'
cat ~/.ssh/id_*.pub|ssh postgres@172.16.1.193 'cat>> ~/.ssh/authorized_keys'
cat ~/.ssh/id_*.pub|ssh postgres@172.16.1.198 'cat>> ~/.ssh/authorized_keys'
1
2
3

vi /etc/ssh/ssh.config
1

添加一行 配置读取秘钥文件

IdentityFile ~/.ssh/id_rsa_pgpool
1

设置好后，使用ssh postgres@serverX -i ~/.ssh/id_rsa_pgpool命令确保不输入密码也能登录。如果执行ssh-copy-id失败，请为postgres用户设置密码并暂时允许密码验证。

为了允许repl用户在不指定密码的情况下进行流复制和在线恢复，并 使用postgres执行pg_rewind，我们在postgres用户的主目录中创建.pgpass文件，并将每个PostgreSQL服务器 上的权限更改为 600 。

[all servers]# su - postgres
[all servers]$ vi /var/lib/pgsql/.pgpass
server1:5432:replication:repl:<repl user password>
server2:5432:replication:repl:<repl user password>
server3:5432:replication:repl:<repl user password>
server1:5432:postgres:postgres:<postgres user password>
server2:5432:postgres:postgres:<postgres user password>
server3:5432:postgres:postgres:<postgres user password>
[all servers]$ chmod 600  /var/lib/pgsql/.pgpass
1
2
3
4
5
6
7
8
9

连接Pgpool-II和PostgreSQL服务器时，必须通过启用防火墙管理软件来访问目标端口。以下是CentOS/RHEL7的示例。

[all servers]# firewall-cmd --permanent --zone=public --add-service=postgresql
[all servers]# firewall-cmd --permanent --zone=public --add-port=9999/tcp --add-port=9898/tcp --add-port=9001/tcp --add-port=9694/udp
[all servers]# firewall-cmd --reload
1
2
3

8.3.5。Pgpool-II配置
8.3.5.1。常用设置
以下是server1、server2和server3上的常用设置。

从 RPM 安装Pgpool-II时，所有Pgpool-II配置文件都在/etc/pgpool-II中。在此示例中，我们复制流复制模式的示例配置文件。

# cp -p /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf
1

为了允许 Pgpool-II 接受所有传入的连接，我们设置了listen_addresses = ‘*’。

listen_addresses = '*'
1

配置服务器node节点信息

[all servers]vi /etc/pgpool-II/pgpool_node_id
1

根据设备顺序，填入数字，0，1，2…

指定复制延迟检查用户和密码。在此示例中，我们将 sr_check_user留空，并在pool_passwd中创建条目。从Pgpool-II 4.0 开始，如果这些参数留空， Pgpool-II将首先尝试从sr_check_password文件中获取该特定用户的密码，然后再使用空密码。

sr_check_user = 'pgpool'
sr_check_password = ''
1
2

启用健康检查，以便Pgpool-II执行故障转移。此外，如果网络不稳定，即使后端运行正常，健康检查也会失败，可能会出现故障转移或退化操作。为了防止这种健康检查的错误检测，我们设置health_check_max_retries = 3。以与sr_check_user和sr_check_password相同的方式指定health_check_user和health_check_password。

health_check_period = 5
health_check_timeout = 30
health_check_user = 'pgpool'
health_check_password = ''
health_check_max_retries = 3
1
2
3
4
5

指定PostgreSQL后端信息。可以通过在参数名称末尾添加一个数字来指定多个后端。

- Backend Connection Settings -

backend_hostname0 = 'server1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/11/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_application_name0 = 'server1'
backend_hostname1 = 'server2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/11/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server2'

backend_hostname2 = 'server3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/11/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
backend_application_name2 = 'server3'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

8.3.5.2。故障转移配置
在failover_command 参数 中指定故障转移后要执行的 failover.sh 脚本。如果我们使用 3 个 PostgreSQL 服务器，我们需要指定 follow_master_command 在主节点故障转移后运行。如果有两个 PostgreSQL 服务器，follow_master_command 设置不是必需的。

Pgpool-II在执行脚本时将以下特殊字符替换为后端特定信息。有关每个字符的更多详细信息，请参见failover_command。

failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_master_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %m %H %M %P %r %R'
1
2

注意： %N和%S是在Pgpool-II 4.1 中添加的。请注意，如果使用 Pgpool-II 4.0 或更早版本，则无法指定这些字符。

示例脚本failover.sh 和follow_master.sh 安装在/etc/pgpool-II/中。使用这些示例文件创建故障转移脚本。

[all servers]# cp -p /etc/pgpool-II/failover.sh{.sample,}
[all servers]# cp -p /etc/pgpool-II/follow_master.sh{.sample,}
[all servers]# chown postgres:postgres /etc/pgpool-II/{failover.sh,follow_master.sh}
1
2
3

基本上，如果您根据 PostgreSQL 安装目录 更改PGHOME ，它应该可以工作。

[server1]# vi /etc/pgpool-II/failover.sh
...
PGHOME=/usr/pgsql-11
...

[server1]# vi /etc/pgpool-II/follow_master.sh
...
PGHOME=/usr/pgsql-11
...
1
2
3
4
5
6
7
8
9

将ssh脚本文件改成 默认名称
由于使用follow_master_command脚本中的PCP命令 需要用户认证，我们需要在pcp.conf中以“用户名：加密密码” 的格式指定用户名和md5加密密码。

如果在follow_master.sh的PCP_USER中指定了pgpool用户，

# cat /etc/pgpool-II/follow_master.sh
1

...
PCP_USER=pgpool
...
1
2
3

将ssh脚本文件改成 默认名称
然后我们使用pg_md5为pgpool用户创建加密密码条目，如下所示：

[all servers]# echo 'pgpool:'`pg_md5  passowrd` >> /etc/pgpool-II/pcp.conf
1

由于follow_master.sh脚本必须在不输入密码的情况下执行PCP命令，所以我们需要在每台服务器的Pgpool -II启动用户（postgres用户） 的主目录下 创建.pcppass 。

[all servers]# su - postgres
[all servers]$ echo 'localhost:9898:pgpool:<pgpool user password>' > ~/.pcppass
[all servers]$ chmod 600 ~/.pcppass
1
2
3

注意：follow_master.sh 脚本不支持表空间。如果您正在使用表空间，则需要修改脚本以支持表空间。

8.3.5.3。Pgpool-II 在线恢复配置
接下来，为了使用Pgpool-II执行在线恢复，我们指定PostgreSQL用户名和在线恢复命令 recovery_1st_stage。因为 执行在线恢复需要PostgreSQL中的超级用户权限，所以我们在recovery_user中指定postgres用户。然后，我们在PostgreSQL主服务器（server1） 的数据库集群目录下创建recovery_1st_stage和pgpool_remote_start，并添加执行权限。

recovery_user = 'postgres'
recovery_password = ''
recovery_1st_stage_command = 'recovery_1st_stage'
1
2
3

在线恢复示例脚本recovery_1st_stage 和pgpool_remote_start 安装在/etc/pgpool-II/中。将这些文件复制到主服务器 (server1) 的数据目录。

[server1]# cp -p /etc/pgpool-II/recovery_1st_stage.sample /var/lib/pgsql/11/data/recovery_1st_stage
[server1]# cp -p /etc/pgpool-II/pgpool_remote_start.sample /var/lib/pgsql/13/data/pgpool_remote_start
[server1]# chown postgres:postgres /var/lib/pgsql/11/data/{recovery_1st_stage,pgpool_remote_start}
1
2
3

基本上，如果您根据 PostgreSQL 安装目录 更改PGHOME ，它应该可以工作。

[server1]# vi /var/lib/pgsql/11/data/recovery_1st_stage
...
PGHOME=/usr/pgsql-11
...
[server1]# vi /var/lib/pgsql/11/data/pgpool_remote_start
...
PGHOME=/usr/pgsql-11
...
1
2
3
4
5
6
7
8

为了使用在线恢复功能，pgpool_recovery所以我们需要安装 在PostgreSQL服务器 server1的 template1 上。

[server1]# su - postgres
[server1]$ psql template1 -c "CREATE EXTENSION pgpool_recovery"
1
2

注意： recovery_1st_stage脚本不支持表空间。如果您正在使用表空间，则需要修改脚本以支持表空间。

8.3.5.4。客户端认证配置
因为在开始前一节中，我们已经将PostgreSQL认证方式设置为 scram-sha-256，所以需要通过 Pgpool-II设置客户端认证来连接后端节点。使用 RPM 安装时， Pgpool -II配置文件 pool_hba.conf位于/etc/pgpool-II中。默认情况下，pool_hba 身份验证被禁用，设置enable_pool_hba = on 以启用它。

enable_pool_hba = on
1

pool_hba.conf文件 的格式非常接近 PostgreSQL 的 pg_hba.conf格式。将pgpool和postgres用户的身份验证方法设置为scram-sha-256。(关键)

直接替换如下:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all pgpool 0.0.0.0/0 trust
host all postgres 0.0.0.0/0 trust
host all all 0.0.0.0/0 trust
host replication repl 0.0.0.0/0 trust
1
2
3
4
5
6
7
8
9
10
11

生成pool_passwd文件，它将被创建在与 pgpool.conf相同的目录中。

[all servers]# su - postgres
[all servers]$ pg_md5 -p -m -u postgres pool_passwd
password: [pgpool user's password]
[all servers]$ pg_md5 -p -m -u pgpool pool_passwd
password: [postgres user's passowrd]
1
2
3
4
5

# cat /etc/pgpool-II/pool_passwd 
1

postgres:md52a29a4f7eb0a98abca0992ca3fb555b6
pgpool:md5bc458983af9a98798fefe59c1a81b8bd

8.3.5.5。看门狗配置
在server1、server2、server3 上启用看门狗功能。

use_watchdog = on
1

指定接受来自 server1、server2、server3上的客户端连接的虚拟 IP 地址。确保尚未使用设置为虚拟 IP 的 IP 地址。

delegate_IP = '172.16.1.252'
1

为了启动/关闭虚拟 IP 并发送 ARP 请求，我们设置了 if_up_cmd、if_down_cmd和arping_cmd。此示例中使用的网络接口是“enp0s8”。由于执行if_up/down_cmd或 arping_cmd命令需要 root 权限，因此在这些命令上使用 setuid 或允许 Pgpool-II启动用户、postgres用户（Pgpool-II 4.1 或更高版本）在没有密码的情况下 运行sudo命令。

注意： 如果使用 RPM 安装Pgpool-II，则postgres 用户已配置为通过sudo运行ip/arping ，无需密码。

postgres ALL=NOPASSWD: /sbin/ip
postgres ALL=NOPASSWD: /usr/sbin/arping
1
2

这里我们配置以下参数以使用 sudo 运行if_up/down_cmd或arping_cmd。

if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev em1 label em1:0'
if_down_cmd = '/usr/bin/sudo /sbin/$_IP_$/24 dev em1 的 IP 地址'
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I em1'
1
2
3

注:em1 根据实际网卡填写 ifconfig命令查看

根据命令路径 设置if_cmd_path和arping_path 。如果if_up/down_cmd或arping_cmd以“/”开头，这些参数将被忽略。

if_cmd_path = '/sbin'
arping_path = '/usr/sbin'
1
2

指定每个Pgpool-II服务器 的主机名和端口号。

server1

wd_hostname = 'server1'
wd_port = 9001
1
2

server2

wd_hostname = 'server2'
wd_port = 9001
1
2

server3

wd_hostname = 'server3'
wd_port = 9001
1
2

指定每个Pgpool- II 服务器上受监控的 Pgpool-II服务器的主机名、 Pgpool-II端口号和看门狗端口号。

server1

- 其他 pgpool 连接设置 -

other_pgpool_hostname0 = 'server2'
other_pgpool_port0 = 9999
other_wd_port0 = 9001
other_pgpool_hostname1 = 'server3'
other_pgpool_port1 = 9999
other_wd_port1 = 9001
1
2
3
4
5
6

服务器2

- 其他 pgpool 连接设置 -

other_pgpool_hostname0 = 'server1'
other_pgpool_port0 = 9999
other_wd_port0 = 9001
other_pgpool_hostname1 = 'server3'
other_pgpool_port1 = 9999
other_wd_port1 = 9001
1
2
3
4
5
6

服务器3

- 其他 pgpool 连接设置 -

other_pgpool_hostname0 = 'server1'
other_pgpool_port0 = 9999
other_wd_port0 = 9001                                          
other_pgpool_hostname1 = 'server2'
other_pgpool_port1 = 9999
other_wd_port1 = 9001
1
2
3
4
5
6

在server1、server2、server3 上指定发送心跳信号的目标主机名和端口号。

server1

heartbeat_destination0 = 'server2'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
heartbeat_destination1 = 'server3'
heartbeat_destination_port1 = 9694
heartbeat_device1 = ''
1
2
3
4
5
6

server2

heartbeat_destination0 = 'server1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
heartbeat_destination1 = 'server3'
heartbeat_destination_port1 = 9694
heartbeat_device1 = ''
1
2
3
4
5
6

server3

heartbeat_destination0 = 'server1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
heartbeat_destination1 = 'server2'
heartbeat_destination_port1 = 9694
heartbeat_device1 = ''
1
2
3
4
5
6

8.3.5.6。/etc/sysconfig/pgpool 配置
如果您想在Pgpool -II启动时忽略pgpool_status文件， 请在/etc/sysconfig/pgpool的启动选项 OPTS 中添加“-D” 。

[all servers]# vi /etc/sysconfig/pgpool 
1

...
OPTS=" -D -n"
1
2

8.3.5.7。日志记录
在示例中，我们将Pgpool-II的日志输出到syslog。

log_destination = 'syslog'
syslog_facility = 'LOCAL1'
1
2

创建Pgpool-II日志文件。

[all servers]# mkdir /var/log/pgpool-II
[all servers]# touch /var/log/pgpool-II/pgpool.log
1
2

编辑 syslog /etc/rsyslog.conf的配置文件。

[all servers]# vi /etc/rsyslog.conf
1

…

*.info;mail.none;authpriv.none;cron.none;LOCAL1.none    /var/log/messages
LOCAL1.*                                                /var/log/pgpool-II/pgpool.log
1
2

设置 logrotate 与/var/log/messages相同。

[all servers]# vi /etc/logrotate.d/syslog
1

...
/var/log/messages
/var/log/pgpool-II/pgpool.log
/var/log/secure
1
2
3
4

重新启动 rsyslog 服务。

[all servers]# systemctl restart rsyslog
1

Pgpool-II 的设置完成。

8.3.6. 启动/停止 Pgpool-II
接下来我们启动Pgpool-II。在启动 Pgpool-II之前，请先启动 PostgreSQL服务器。另外，停止PostgreSQL时，需要先停止 Pgpool-II。

启动Pgpool-II

在开始之前的 部分，我们已经设置了Pgpool-II的自动启动。要启动 Pgpool-II，请重新启动整个系统或执行以下命令。

# systemctl start pgpool.service
1

停止Pgpool-II

# systemctl stop pgpool.service
1

8.3.7.1。设置 PostgreSQL 备用服务器
首先，我们应该使用Pgpool-II在线恢复功能 设置PostgreSQL备用服务器。确保pcp_recovery_node命令使用的recovery_1st_stage和pgpool_remote_start 脚本位于PostgreSQL主服务器 ( server1 ) 的数据库集群目录中。

# pcp_recovery_node -h 172.16.1.221 -p 9898 -U pgpool -n 1
1

密码：
pcp_recovery_node – Command Successful

# pcp_recovery_node -h 172.16.1.221 -p 9898 -U pgpool -n 2
1

密码：
pcp_recovery_node – Command Successful

数据库数据地址:

/var/lib/pgsql/11/data/
1

pgpool数据地址:

/etc/pgpool_II
1

相关命令:

初始化数据库:

/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data
1

查看数据库集群状态:

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

查看pool集群状态:

pcp_watchdog_info -h 172.16.1.252 -p 9898 -U pgpool
1

启动数据库服务:

/usr/pgsql-11/bin/pg_ctl start -D /var/lib/pgsql/11/data
1

启动停止pgpool:

systemctl stop pgpool.service
systemctl start pgpool.service
1
2

手动在线恢复恢复数据库:

pcp_recovery_node -h 172.16.1.252 -p 9898 -U pgpool -n 1
1

查看pgpool日志:

tail -n 500 /var/log/pgpool-II/pgpool.log
1

查看数据库日志:

tail -n 500 /var/lib/pgsql/11/data/log/postgresql-Mon.log
1

手动将节点添加到集群:

pcp_attach_node -d -U postgres -h 172.16.1.252 -p 9898 -n 1
1

快速停掉数据库:

/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data -m immediate stop
1