PostgreSQL高可用-pgpool-II+流复制

排骨无年糕

已于 2024-05-17 18:29:31 修改

阅读量1.2k

点赞数 38

文章标签： postgresql 数据库

于 2024-05-11 11:31:50 首次发布

本文链接：https://blog.csdn.net/m0_71902491/article/details/138704399

版权

1.pgpool-ll简介

p gpool-II 是一个在 PostgreSQL 服务器和 PostgreSQL 客户端之间工作的中间件。它是根

据 BSD 许可证授权的。

pgpool-II 的功能

连接池： pgp ool-II 保存与 PostgreSQL 服务器的连接，并在具有相同属性（即用户名，

数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的

整体吞吐量。

复制： pgpool-II 可以管理多个 PostgreSQL 服务器。数据变更会同时发送到所有的后

端数据库上，以保证多个后端数据库的数据完全一样。复制功能可以实现

PostgreSQL 的高可用，如果其中一台节点失效，服务不会中断，可以继续运行。

负载均衡： pgpool-II 利用复制功能，通过在多个服务器之间分配 SELECT 查询来减少

每个 PostgreSQL 服务器的负载，从而提高系统的整体吞吐量。性能与 PostgreSQL

服务器的数量成比例地提高。在许多用户同时执行许多查询的情况下，负载平衡最有

效。

高可用 ：当一个后端数据库不可用时， pgpool-II 会把用户的请求转发到其他可用的后端

数据库上，以保证对外的正常服务。 pgpool-II 通过探测机制检测后端数据库是否正常工

作。

连接限制： PostgreSQL 的最大并发连接数有限制，到达最大连接数后后连接会被拒绝。

但是，设置最大连接数会增加资源消耗并影响系统性能。 pgpool-II 对最大连接数也有

限制，但额外连接将排队，而不是立即返回错误。

看门狗： Watchdog 可以协调多个 pgpool-II ，创建一个强大的集群系统，避免单点故

障或脑裂。看门狗可以对其他 pgpool-II 节点执行健康检查，以检测 pgpoll-II 的故障。

如果活动 pgpool-II 发生故障，则可以将备用 pgpool-II 提升为活动状态，并接管虚拟

IP 。

查询缓存： 在内存中保存 SELECT 语句及其结果。如果有相同的 SELECT ， pgpool-II

将从缓存中返回值。由于不涉及 SQL 解析或访问 PostgreSQL ，因此在内存缓存中使

用速度非常快。另一方面，在某些情况下，它可能比正常路径慢，因为它增加了存储

缓存数据的一些开销。

pgpool-II 识别 PostgreSQL 的后端和前端协议，并在后端和前端之间传递消息。因此，

应用程序（前端）认为 pgpool-II 是实际的 PostgreSQL 服务器，服务器（后端）将

pgpool-II 视为其客户端之一。因为 pgpool-II 对服务器和客户端都是透明的，所以现有

的数据库应用程序可以与 pgpool-II 一起使用，几乎不需要更改其源码。

watchdog 是 pgpool -II 的核心组件， watchdog 在 pgpool -II 方案中扮演非常重要的角

色，当启动 pgpool -II 时会启动 watchdog 子进程，其主要作用为 :

和 pgpool-II 后端 PostgreSQL 数据库节点以及远程 pgpool-II 节点进行通信。
对远程 pgpool-II 节点是否存活进行检查。
当 watchdog 子进程启动时，对本地 pgpool-II 的配置和远程 pgpool-II 的配置参数进行检查，并且输出本地和远程 pgpool-II 不一致的参数。
当 pgpool-II 主节点宕机时,watchdog 集群将选举出新的 watchdog 主节点。
当 pgpool-II 备节点激活成主节点时，watchdog 负责将 VIP 飘移到新的 pgpool-II

节点

2.postgresql+pgpool-ll高可用架构

a.pgpool-II 实现了 PostgreSQL 的连接协议，客户端连接到 pgpool-II 上时与连接到数据

库上一样。 pgpool-ll 作为中间件，将主备 PostgreSQL 节点加入集群，实现读写分离，

负载均衡和 HA 故障自动切换。

b.上面的架构中，为了保证 pgpool-II 自身的高可用，配置了 3 台 pgpool-II，一主二备，

三个 pgpool 节点使用 vip 作为应用程序访问的地址，主备 pgpool-ll 之间通过

watchdog 进行监控，当主 pgpool-II 故障时， vip 会切换到备 pgpool-II 上。

3.环境准备

主机规划

角色	主机名	IP地址	VIP	PG版本	pgpool版本	OS版本
master	pg01	192.168.86.141	192.168.86.145	v16.2	V4.5.1	Centos7.5
slave	pg02	192.168.86.142		v16.2	V4.5.1	Centos7.5
slave	pg03	192.168.86.143		v16.2	V4.5.1	Centos7.5

配置IP&修改主机名

#三台主机分别配置
vi /etc/sysconfig/network-scripts/ifcfg-eth0 
IPADDR="192.168.86.141" 
systemctl restart network

vi /etc/sysconfig/network-scripts/ifcfg-eth0 
IPADDR="192.168.86.142" 
systemctl restart network

vi /etc/sysconfig/network-scripts/ifcfg-eth0 
IPADDR="192.168.86.143" 
systemctl restart network

#三台主机分别配置

hostnamectl set-hostname pg01 
hostnamectl set-hostname pg02 
hostnamectl set-hostname pg03

#三台主机都要配置
vi /etc/hosts
192.168.86.141  pg01 
192.168.86.142  pg02 
192.168.86.143  pg03

端口规划

5432	PostgreSQL 监听端口
9999	pgpool-II 监听端口
9898	pcp 管理端口
9000	watchdog 监听端口
9694	watchdog 心跳端口

脚本样本

作用	脚本	备注
故障转移	/etc/pgpool-II/failover.sh.sample	由 failover_command 运行以执行故障转移
	/etc/pgpool-II/follow_primary.sh.sample	由 follow_primary_command 运行，在故障转移后将备库与新主库同步
在线恢复	/etc/pgpool- II/recovery_1st_stage.sample	由 recovery_1st_stage_command 运行以恢复备库
	/etc/pgpool- II/pgpool_remote_start.sample	recovery_1st_stage_command 运行以启动备库
看门狗	/etc/pgpool-II/escalation.sh.sample	由 wd_escalation_command 运行，以安全地切换主/备 pgpool-

4.配置流复制

修改配置文件

mkdir /home/postgres/archives 

vi postgresql.conf 

listen_addresses = '*' 
wal_log_hints = on 
logging_collector = on
archive_mode = on 
archive_command = 'cp %p /home/postgres/archives/%f'

配置用户

用户名	密码	用途
repl	repl	用于 PostgreSQL 流复制
pgpool	pgpool	用于 pgpool-ll 心跳检测以及复制延迟检测
postgres	postgres	用于在线恢复

--如果要在 show POOL NODES 命令结果中显示 replication_state 和replication_sync_state 列，需要授予 pgpool 用户 pg_monitor 权限。
postgres=# CREATE USER pgpool PASSWORD 'pgpool';
postgres=# GRANT pg_monitor TO pgpool;
postgres=# CREATE USER repl WITH REPLICATION PASSWORD 'repl';

配置pg_hba文件

host     all             all      0.0.0.0/0     scram-sha-256 
host     replication     all      0.0.0.0/0     scram-sha-256

测试复制流是否正常

psql -h pg01 -U repl -c "IDENTIFY_SYSTEM" replication=1

配置.pgpass 密码文件

主要是为了允许 repl 用户在不指定密码的情况下进行流复制和在线恢复；使用 postgres

执行 pg_rewind

#三台都要做
 su - postgres 
[postgres@pg01 ~]$ pwd
/home/postgres
[postgres@pg01 ~]$ vi ~/.pgpass 
pg01:5432:replication:repl:repl 
pg02:5432:replication:repl:repl 
pg03:5432:replication:repl:repl 
pg01:5432:postgres:postgres:postgres 
pg02:5432:postgres:postgres:postgres 
pg03:5432:postgres:postgres:postgres 
[postgres@pg01 ~]$ chmod 600 ~/.pgpass

#其余两台同上述操作
。。。。。。

生成备库

pg_basebackup -h pg01 -Urepl -R -Fp -P -D /home/postgres/pgdata16

--启动备库 
pg_ctl start

5.安装pgpool

文档地址：https://www.pgpool.net/docs/latest/en/html/index.html

下载地址：https://pgpool.net/mediawiki/index.php/Downloads#Source

--安装软件 三台都要装
[root@pg03 local]#  wget -c https://www.pgpool.net/mediawiki/download.php?f=pgpool-II-4.5.1.tar.gz 
tar -xvf download.php?f=pgpool-II-4.5.1.tar.gz
cd pgpool-II-4.5.1 
mkdir -p /usr/local/pgpool-II-4.5.1
./configure --prefix=/usr/local/pgpool-II-4.5.1 --with-openssl --withpgsql=/usr/local/pgsql 
make && make install 
cd /usr/local 
ln -s pgpool-II-4.5.1 pgpool 

vi /etc/profile 
export PATH=$PATH:/usr/local/pgpool/bin/ 
source /etc/profile 

--生成配置文件 
--因为 pgpool 主要用于 pgsql 数据库的管理，推荐使用 postgres 用户管理 
chown -R postgres:postgres /usr/local/pgpool/etc/ 

su - postgres 
cd /usr/local/pgpool/etc 
cp pcp.conf.sample pcp.conf 
cp pgpool.conf.sample pgpool.conf 
cp pool_hba.conf.sample pool_hba.conf 

#创建日志目录，否则报错：Failed to open lock file for query cache 
"/var/log/pgpool_log/memq_lock_file" 
su - root
mkdir /var/log/pgpool_log 
chown postgres:postgres /var/log/pgpool_log/

#其余两台同上述操作一致

6.读写分离配置

pgpool-II 的配置文件主要有：

/etc/pgpool-II/pgpool.conf： pgpool-II 主配置文件。

/etc/pgpool-II/pcp.conf： pcp 工具的用户名、密码配置文件。

/etc/pgpool-II/pool_passwd：访问 pgpool-II 的用户名和密码文件。

/etc/pgpool-II/pool_hba.conf： pgpool-II 的访问控制文件。

注意：所有主机上的所有配置参数都是相同的，因此可以在任何 pgpool 节点上编辑

pgpool.conf，并将编辑后的 pgpool..conf 文件复制到其他 pgpool 结点。

修改pgpool.conf文件

#三台配置一样
su - postgres
cd /usr/local/pgpool/etc

vi pgpool.conf 

# Clustering mode 
backend_clustering_mode = 'streaming_replication' 
# Connection Settings 
listen_addresses = '*' 
port = 9999 

# 在 pgpool.conf 中打开 pool_hba 
enable_pool_hba = on 
pool_passwd = 'pool_passwd' 
# Streaming Replication Check 
sr_check_user = 'repl' 

# sr_check_password 留空，pgpool 会去读取 pool_passwd 文件中条目。 
sr_check_password = '' 

# Health Check 
# 启用运行状况检查，以便 pgpool II 执行故障切换。 
health_check_period = 5 
health_check_timeout = 30 
health_check_user = 'pgpool' 
health_check_password = '' 
health_check_max_retries = 3 

# - pgpool Communication Manager Connection Settings - 
pcp_listen_addresses = '*' 
pcp_port = 9898 
# FILE LOCATIONS 
logdir = '/var/log/pgpool_log' 
pid_file_name = '/tmp/pgpool.pid' 

# - Backend Connection Settings – 
backend_hostname0 = 'pg01' 
backend_port0 = 5432 
backend_weight0 = 1 
backend_data_directory0 = '/home/postgres/pgdata16' 
backend_flag0 = 'ALLOW_TO_FAILOVER' 

# 要在 show POOL_NODES 命令结果中显示 replication_state 和replication_sync_state 列，需要backend_application_name 参数。 

backend_application_name0 = 'pg01' 
backend_hostname1 = 'pg02' 
backend_port1 = 5432 
backend_weight1 = 1 
backend_data_directory1 = '/home/postgres/pgdata16' 
backend_flag1 = 'ALLOW_TO_FAILOVER' 
backend_application_name0 = 'pg02' 
backend_hostname2 = 'pg03' 
backend_port2 = 5432 
backend_weight2 = 1 
backend_data_directory2 = '/home/postgres/pgdata16' 
backend_flag2 = 'ALLOW_TO_FAILOVER' 
backend_application_name0 = 'pg03' 

# LOAD BALANCING MODE 启用读写分离 
load_balance_mode = on

设置密码

pool_passwd 是 pgpool-II 的认证文件。一方面，由于 pgpool-II 无法获取 PostgreSQL

数据库上的用户密码信息，因此通过检查 pool_passwd 内用户名及密码的方式，校验请

求输入的用户名及密码是否正确。另一方面，当请求通过 pgpool-II 认证后，pgpool-II

将利用 pool_passwd 内保存的用户名及密码，连接后端 PostgreSQL 数据库，进行请求

--为 Postgres 用户配置 sudo 权限，watchdog 中 if_up_cmd/if_down_cmd 和arping_cmd 命令需要 root 权限，所有需要允许运行 postgres 的用户能免密执行 sudo。
 
--三台都要做
[pg01]#su - root
[pg01]# vi /etc/sudoers 
## Allow root to run any commands anywhere
root    ALL=(ALL)       ALL
postgres ALL=(ALL) NOPASSWD:ALL
 
--切换用户 为postgres/pgpool/repl用户密码加密
[pg01]# su - postgres 
#将 pgpoolkey 保存至 ~/.pgpoolkey 
[postgres@pg01 ~]$ echo '123' > ~/.pgpoolkey
[postgres@pg01 ~]$ chmod 600 ~/.pgpoolkey
[postgres@pg01 ~]$ sudo rm /usr/local/pgpool/etc/pool_passwd
rm: 无法删除"/usr/local/pgpool/etc/pool_passwd": 没有那个文件或目录
[postgres@pg01 ~]$ sudo /usr/local/pgpool/bin/pg_enc -m -k ~/.pgpoolkey -u postgres -p
db password:
trying to read key from file /home/postgres/.pgpoolkey
[postgres@pg01 ~]$ sudo /usr/local/pgpool/bin/pg_enc -m -k ~/.pgpoolkey -u pgpool -p
db password:
trying to read key from file /home/postgres/.pgpoolkey
[postgres@pg01 ~]$ sudo /usr/local/pgpool/bin/pg_enc -m -k ~/.pgpoolkey -u repl -p
db password:
trying to read key from file /home/postgres/.pgpoolkey
[postgres@pg01 ~]$  cat /usr/local/pgpool/etc/pool_passwd
postgres:AES2kLQQDsh002lZ7ryiHgzqg==
pgpool:AESJl/1UOyja0U5cFk2OqvlFQ==
repl:AESEoPKtts8F2Int7r84Nn2/A==
[postgres@pg01 ~]$


--pg02&pg03同上操作

修改 pool_hba.conf 文件

pool_hba.conf 是 pgpool-II 的客户端认证配置文件，用于对访问 pgpool-II 中间件的请

求实施访问认证控制。同时，因为 pgpool-II 从架构上位于 PostgreSQL 之前，因此请求

需要先通过 pgpool-II 的认证控制，随后通过 PostgreSQL 的认证控制

在/usr/local/pgpool/etc/pool_hba.conf 中配置访问控制 

vi pool_hba.conf 
local  all   all                   trust 
host   all   all   127.0.0.1/32    trust 
host   all   all   ::1/128         trust 
host   all   all   0.0.0.0/0       scram-sha-256

修改 pcp.conf 文件

pcp.conf 文件用于 pgpool 自己登陆管理使用的，一些操作 pgpool 的工具会要求提供

密码。例如登陆 9898pcp 管理端口

#三台都做
[postgres@pg03 ~]$ pg_md5 -p admin
password:admin
21232f297a57a5a743894a0e4a801fc3
[postgres@pg03 ~]$ vim  /usr/local/pgpool/etc/pcp.conf 
admin:21232f297a57a5a743894a0e4a801fc3

启动pgpool

--因为 pgpool 主要用于 PostgreSQL 数据库的管理，推荐使用 postgres 用户管理 

su - postgres 
pgpool 

--启动 pgpool II 时，如果存在 gpool_status 文件，则 gpoolⅡ将从 gpool_state 文件读
取后端状态（up/down）。 
--如果想在 pgpool II 启动时忽略 pgpool_status 文件，在启动时需要添加-D。 

pgpool -D

--停止 pgpool 
pgpool stop 

--强制关闭 pgpool-II 
pgpool -m fast stop

查看后端节点状态

[postgres@pg01 ~]$ psql -p 9999 
postgres=# show pool_nodes;

[postgres@pg01 etc]$ pcp_node_info -U admin -p 9898 -W
Password:
pg01 5432 1 0.333333 waiting up primary primary 0 none none 2024-05-17 17:05:41
pg02 5432 1 0.333333 waiting up standby standby 0 none none 2024-05-17 17:05:41
pg03 5432 1 0.333333 waiting up standby standby 0 none none 2024-05-17 17:05:41
[postgres@pg01 etc]$


status 字段数字含义: 
0：初使化过程中使用 
1：节点已启动，但没有连接。 
2：节点已启动，有连接。 
3：节点 down。

show pool_status：查看 pgpool-II 的配置信息。 
show pool_processes：查看 pgpool-II 的进程信息。 
show pool_pools：查看 pgpool-II 连接池中的连接信息。 
show pool_version：查看 pgpool-II 的版本。

测试读写分离+负载均衡

log_statement = 'all' 

pg_ctl reload

通过连接 pgpool 对表进行插入和查询

postgres=# show pool_nodes;
 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       | pg01     | 5432 | up     | up        | 0.333333  | primary | primary | 0          | true              | 0                 |                   |                        | 2024-05-17 14:31:08
 1       | pg02     | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2024-05-17 14:31:08
 2       | pg03     | 5432 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 |                   |                        | 2024-05-17 14:31:08
(3 rows)


create table t1(id int,info text); 
insert into t1 values (1,'aaa'); --insert 是不会记录 select_cnt 的 
select * from t1; 

show pool_nodes;
postgres=# show pool_nodes;
 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       | pg01     | 5432 | up     | up        | 0.333333  | primary | primary | 10         | true              | 0                 |                   |                        | 2024-05-17 15:07:04
 1       | pg02     | 5432 | up     | up        | 0.333333  | standby | standby | 1          | false             | 0                 |                   |                        | 2024-05-17 15:07:04
 2       | pg03     | 5432 | up     | up        | 0.333333  | standby | standby | 12         | false             | 0                 |                   |                        | 2024-05-17 15:07:04
(3 rows)


#可以看到，读操作被分配到 0、1 和 2 这 3 个节点上，而写操作只分配到 0 节点上。

开启手动事务

postgres=# show pool_nodes;
 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       | pg01     | 5432 | up     | up        | 0.333333  | primary | primary | 10         | true              | 0                 |                   |                        | 2024-05-17 15:07:04
 1       | pg02     | 5432 | up     | up        | 0.333333  | standby | standby | 1          | false             | 0                 |                   |                        | 2024-05-17 15:07:04
 2       | pg03     | 5432 | up     | up        | 0.333333  | standby | standby | 12         | false             | 0                 |                   |                        | 2024-05-17 15:07:04
(3 rows)

postgres=# begin;
BEGIN
postgres=*# insert into t1 values(2,'bbb');
INSERT 0 1
postgres=*# select * from t1;
 id | info
----+------
  1 | aaa
  2 | bbb
(2 rows)

postgres=*# end;
COMMIT
postgres=# show pool_nodes;
 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       | pg01     | 5432 | up     | up        | 0.333333  | primary | primary | 11         | true              | 0                 |                   |                        | 2024-05-17 15:07:04
 1       | pg02     | 5432 | up     | up        | 0.333333  | standby | standby | 1          | false             | 0                 |                   |                        | 2024-05-17 15:07:04
 2       | pg03     | 5432 | up     | up        | 0.333333  | standby | standby | 12         | false             | 0                 |                   |                        | 2024-05-17 15:07:04
(3 rows)

postgres=#


#查看主备日志可以看到读写操作在主库

7.高可用配置

pgpool-II 本身可以做高可用，就是创建 pgpool-II 集群，其中一个 pgpool-II 是主，其

他的 pgpool-II 是备。该功能是通过 watchdog 来实现的。watchlog 还可以配置一个

vip。当主 pgpool-II 出现问题，备 pgpoolII 会成为主 pgpool-II， vip 也会漂移到新主

pgpool-Il 上，从而实现了 pgpool-II 自身的高可用

看门狗配置

创建 pgpool_node_id 如果要启用看门狗功能，则需要一个 pgpool_node_id 文件来区分哪个主机是哪个主机

--pg01 
[pg01]# echo "0" > /usr/local/pgpool/etc/pgpool_node_id 
--pg02 
[pg02]# echo "1" > /usr/local/pgpool/etc/pgpool_node_id 
--pg03 
[pg03]# echo "2" > /usr/local/pgpool/etc/pgpool_node_id

修改 /usr/local/pgpool/etc/pgpool.conf (所有节点)

# - Enabling - 在 pg01、pg02 和 pg03 上启用看门狗功能。 
use_watchdog = on 

# - Virtual IP Setting – 
delegate_ip = '192.168.86.145' 
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' 

# 当 vip 漂移后，需要运行 arping 命令， 否则应用无法连接该 vip。 
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I ens33' 

# - Watchdog communication Settings - 看门狗节点设置 
hostname0 = 'pg01' 
wd_port0 = 9000 
pgpool_port0 = 9999 
hostname1 = 'pg02' 
wd_port1 = 9000 
pgpool_port1 = 9999 
hostname2 = 'pg03' 
wd_port2 = 9000 
pgpool_port2 = 9999 
# method of lifecheck 
wd_lifecheck_method = 'heartbeat' 
wd_interval = 10 

# -- heartbeat mode – 心跳线设置，与备机的通信 
heartbeat_hostname0 = 'pg01' 
heartbeat_port0 = 9694 
heartbeat_device0 = '' 
heartbeat_hostname1 = 'pg02' 
heartbeat_port1 = 9694 
heartbeat_device1 = '' 
heartbeat_hostname2 = 'pg03' 
heartbeat_port2 = 9694 
heartbeat_device2 = '' 

#心跳间隔 
wd_heartbeat_keepalive = 2 

#检测故障时间 
wd_heartbeat_deadtime = 30 

#当 Watchdog 进程异常终止时，旧的和新的活动 pgpool 节点上的虚拟 IP 都可能启动。 
#要防止这种情况发生，需要在启用新的活动 pgpool 节点上的虚拟 IP 之前，先关闭其他
pgpool 结点上的虚拟 IP。 
wd_escalation_command = '/usr/local/pgpool/etc/escalation.sh'

配置 escalation.sh 脚本（所有节点）

[all pg]$ cd /usr/local/pgpool/etc/ 
[all pg]$ cp escalation.sh.sample escalation.sh 
[all pg]$ chmod +x escalation.sh 
[all pg]$ vi escalation.sh
... 
PGPOOLS=(pg01 pg02 pg03) 
VIP=192.168.86.145
DEVICE=eth0 
...

启动所有 pgpool-II，查看 vip

pgpool -d 

ip a 

inet 192.168.86.145/24 scope global secondary eth0:0

查看 watchdog 集群的信息


[postgres@pg01 etc]$ pcp_watchdog_info -U admin -W
Password:admin
3 3 NO pg03:9999 Linux pg03 pg03

pg01:9999 Linux pg01 pg01 9999 9000 7 STANDBY 0 MEMBER
pg02:9999 Linux pg02 pg02 9999 9000 7 STANDBY 0 MEMBER
pg03:9999 Linux pg03 pg03 9999 9000 4 LEADER 0 MEMBER
[postgres@pg01 etc]$

切换 active/standby watchdog

停止 pg01 上的 pgpool-II 服务：

[all pg01]$ pgpool stop 
[postgres@pg02 ~]$ pcp_watchdog_info -Uadmin  -W
Password: 
3 3 YES pg02:9999 Linux pg02 pg02 
pg02:9999 Linux pg02 pg02 9999 9000 4 LEADER 0 MEMBER 
pg01:9999 Linux pg01 pg01 9999 9000 10 SHUTDOWN 0 MEMBER 
pg03:9999 Linux pg03 pg03 9999 9000 7 STANDBY 0 MEMBER

启动 pg01 上的 pgpool-II 服务，该服务会以 standby 角色运行：

[postgres@pg01 ~]$ pcp_watchdog_info -Uadmin  -W
Password: 
3 3 NO pg02:9999 Linux pg02 pg02 
pg01:9999 Linux pg01 pg01 9999 9000 7 STANDBY 0 MEMBER 
pg02:9999 Linux pg02 pg02 9999 9000 4 LEADER 0 MEMBER 
pg03:9999 Linux pg03 pg03 9999 9000 7 STANDBY 0 MEMBER

8.故障切换配置

配置互信[要使用 pgpool II 的故障切换和在线恢复功能，需要设置 SSH 互信，生成的密钥文件名为id_rsa_pgpool](所有节点)

#所有节点都要做
[all pg]# su - postgres 
[all pg]$ mkdir ~/.ssh 
[all pg]$ chmod 700 ~/.ssh 
[all pg]$ cd ~/.ssh 
[all pg]$ ssh-keygen  -t rsa -f id_rsa_pgpool 
[all pg]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg01 
[all pg]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg02 
[all pg]$ ssh-copy-id -i id_rsa_pgpool.pub postgres@pg03 
--验证免密登陆 
[all pg]$ ssh postgres@pg0x -i ~/.ssh/id_rsa_pgpool

例如：
[postgres@pg01 .ssh]$ ssh postgres@pg02 -i ~/.ssh/id_rsa_pgpool
Last failed login: Fri May 17 17:21:04 CST 2024 from pg01 on ssh:notty
There were 11 failed login attempts since the last successful login.
Last login: Fri May 17 16:10:15 2024
[postgres@pg02 ~]$

在 failover_command 参数中指定故障转移后要执行的 failover.sh 脚本，通过该脚本程

序将备库提升为主库。如果使用 3 台 PostgreSQL 服务器，需要指定

follow_primary_command 在主节点故障转移后运行。如果有两个 PostgreSQL 服务器，

则不需要设置 follow_primary_command。

#所有节点都做
vim /usr/local/pgpool/etc/pgpool.conf #最后加入

# - Failover configuration 
failover_command ='/usr/local/pgpool/etc/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S' 
follow_primary_command = '/usr/local/pgpool/etc/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'


[postgres@pg03 etc]$ vim pgpool.conf
[postgres@pg03 etc]$ scp pgpool.conf postgres@pg02:/usr/local/pgpool/etc/pgpool.conf
postgres@pg02's password:
pgpool.conf                                                                                                                                                                                                                                               100% 3382     1.1MB/s   00:00
[postgres@pg03 etc]$ scp pgpool.conf postgres@pg01:/usr/local/pgpool/etc/pgpool.conf
postgres@pg01's password:
pgpool.conf                                                                                                                                                                                                                                               100% 3382     1.8MB/s   00:00
[postgres@pg03 etc]$

示例脚本 failover.sh 和 follow_primary.sh 在/usr/local/pgpool/etc/目录中。可以使用

这些示例文件创建故障转移脚本。

#所有节点都要做
[all pg]# su - postgres 
[all pg]$ cd /usr/local/pgpool/etc 
[all pg]$ cp failover.sh.sample failover.sh 
[all pg]$ cp follow_primary.sh.sample follow_primary.sh 
[all pg]$ chmod +x failover.sh 
[all pg]$ chmod +x follow_primary.sh 
[all pg]$ cp recovery_1st_stage.sample recovery_1st_stage 
[all pg]$ chmod +x recovery_1st_stage 
[all pg]$ mv recovery_1st_stage /home/postgres/pgdata16/ 
[all pg]$ cp pgpool_remote_start.sample pgpool_remote_start 
[all pg]$ chmod +x pgpool_remote_start 
[all pg]$ mv pgpool_remote_start /home/postgres/pgdata16/

##注意脚本 failover.sh 和 follow_primary.sh 中的 PGHOME 是否配置正确。脚本follow_primary.sh 中的 PCP_USER 为 pgpool

#所有节点都要做
[all pg]# vi /usr/local/pgpool/etc/failover.sh 
... 
PGHOME=/usr/local/pgsql 
... 
[all pg]# vi /usr/local/pgpool/etc/follow_primary.sh 
... 
PGHOME=/usr/local/pgsql 
ARCHIVEDIR=/home/postgres/archives 
REPLUSER=repl 
PCP_USER=pgpool 
PGPOOL_PATH=/usr/local/pgpool/bin 
...

由于在 follow_primary_command 脚本中使用 PCP 命令需要进行用户身份验证，因此需

要在 pcp.conf 中配置用户名和密码

#所有节点
# cat /usr/local/pgpool/etc/follow_primary.sh 
... 
PCP_USER=pgpool 
... 
[all pg]# echo 'pgpool:'`pg_md5 pgpool pgpool` >> /usr/local/pgpool/etc/pcp.conf

由于 follow_primary.sh 脚本必须在不输入密码的情况下执行 pcp 命令，因此需要在每台服务器上的 pgpool II 启动用户（postgres 用户）的主目录中创建.pcppass。

#所有节点
[all pg]# su - postgres 
[all pg]$ echo 'localhost:9898:pgpool:pgpool' > ~/.pcppass 
[all pg]$ chmod 600 ~/.pcppass

9.在线恢复配置

使用在线恢复功能需要超级用户权限的用户来执行命令

pgpool 通过 recovery_user 和 recovery_password，连接到主库的 template1 上，调

用 pgpool_recovery 函数，它会执行配置文件 pgpool.conf 中的参数

recovery_1st_stage_command 指定的脚本。然后执行 pgpool_remote_start 参数指定

脚本。注意：该脚本（pgpool_remote_start）需要 SSH 到备库才能启动备库

#所有节点
vi /usr/local/pgpool/etc/pgpool.conf 
recovery_user = 'postgres' 
recovery_password = '' 
recovery_1st_stage_command = 'recovery_1st_stage'

把原主库转换为备库，可以使用 pg_rewind 工具，使用 pcp_recovery_node 命让备库重

新加回集群

修改 recovery_1st_stage 和 pgpool_mote_start 脚本

更改安装目录 PGHOME

#所有节点

[all pg]# vi /home/postgres/pgdata16/recovery_1st_stage 
... 
PGHOME=/usr/local/pgsql 
ARCHIVEDIR=/home/postgres/archives 
... 
#在第 56 行左右的.pgpass 文件路径由/var/lib/pgsql/.pgpass 更改为 
/home/postgres/.pgpass 
primary_conninfo = 'host=${PRIMARY_NODE_HOST} 
port=${PRIMARY_NODE_PORT} user=${REPLUSER} 
application_name=${DEST_NODE_HOST} passfile=''/home/postgres/.pgpass''' 
... 
[all pg]# vi /home/postgres/pgdata/pgpool_remote_start 
... 
PGHOME=/usr/local/pgsql 
...

使用在线恢复功能，需要使用 pgpool_covery 插件中的 pgpool_recovery、

pgpool_mote_start、pgpool_switch_xlog 等函数，因此需要在 PostgreSQL 服务器

pg01 的 template1 上安装 pgpool_covery。

另外，附带工具 pgpoolAdmin 控制 pgpool-II 启停和重新连接后端的 PostgreSQL 节点，

也需要函数 pgpool_pgctl。而且 pgpoolAdmin 需要函数 pgpool_pgctl 来对

PostgreSQL 进行停止/重启/重新加载参数操作。

#主节点创建
[pg01]# su - postgres 
[pg01]$ psql template1 -c "CREATE EXTENSION pgpool_recovery" 

pgpool_recovery 插件在源码包中的 /root/pgpool-II-4.4.2/src/sql/pgpool-recovery 目录中 

cp /root/pgpool-II-4.4.2/src/sql/pgpool-recovery/pgpool_recovery* /usr/local/pgsql/share/extension 

$ psql template1 
CREATE EXTENSION pgpool_recovery; 
ERROR: could not access file "$libdir/pgpool-recovery": No such file or directory 

处理： 
export PATH=/usr/local/pgsql/bin:$PATH 

cd /root/pgpool-II-4.4.2/src/sql/pgpool-recovery 
make 
make install 
这时会自动把 pgpool-recovery.so 移动到$libdir 即/usr/local/pgsql/lib/ 
否则手动移动 
cp /root/pgpool-II-4.4.2/src/sql/pgpool-recovery/pgpool-recovery.so 
/usr/local/pgsql/lib

生成备库( 所有备库节点必须通过 pcp_recovery_node 命令生成 )

首先，应该使用 pgpool II 在线恢复功能来生成 PostgreSQL 备库。确保

pcp_recovery_node 命令使用的 recovery_1st_stage 和 pgpool_mote_start 脚本位于

PostgreSQL 主服务器（pg01）的数据目录中。

2：表示恢复哪个后端数据库。 
# pcp_recovery_node -h 192.168.1.110 -p 9898 -U pgpool -n 2 
Password: 
pcp_recovery_node -- Command Successful 
--修改 myrecovery.conf 
primary_conninfo = 'host=pg01 port=5432 user=repl application_name=pg03 
passfile=''/home/postgres/.pgpass'''

故障切换测试

首先，使用 psql 通过虚拟 IP 连接到 PostgreSQL，并验证后端信息

# psql -h 192.168.86.145 -p 9999 -U pgpool postgres -c "show pool_nodes" 
Password for user 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 | pg01 | 5432 | up | up | 0.333333 | primary | primary | 0 | 
false | 0 | | | 2023-04-17 15:13:46 
 1 | pg02 | 5432 | up | up | 0.333333 | standby | standby | 0 | 
false | 0 | | | 2023-04-17 15:13:46 
 2 | pg03 | 5432 | up | up | 0.333333 | standby | standby | 0 | 
true | 448 | | | 2023-04-17 15:13:46 
(3 rows)

停止主库 pg01，并验证自动故障转移

[pg01]$ pg_ctl stop

在 pg01 上停止 PostgreSQL 后，将发生故障转移，pg02 上的 PostgreSQL 将成为新的

主数据库

# psql -h 192.168.86.145 -p 9999 -U pgpool postgres -c "show pool_nodes" 
Password for user 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 | pg01 | 5432 | down | down | 0.333333 | standby | unknown | 0 
| false | 0 | | | 2023-04-17 15:25:01 
海量视频题库 http://www.myitpub.com vx:myitpub QQ:5565462 
 1 | pg02 | 5432 | up | up | 0.333333 | primary | primary | 0 | true 
| 0 | | | 2023-04-17 15:25:01 
 2 | pg03 | 5432 | up | up | 0.333333 | standby | standby | 0 | 
false | 456 | | | 2023-04-17 15:25:25 
(3 rows)

pg03 要作为新主库 pg02 的备库运行。

[pg03]# psql -h pg03 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()" 
pg_is_in_recovery 
------------------- 
t 
[pg02]# psql -h pg02 -p 5432 -U pgpool postgres -c "select pg_is_in_recovery()" 
pg_is_in_recovery 
------------------- 
f 
[pg02]# psql -h pg02 -p 5432 -U pgpool postgres -c "select * from 
pg_stat_replication" -x 
Password for user pgpool: 
(0 rows) 

--查询为空，需要修改 myrecovery.conf 文件

primary_conninfo = 'host=pg01 port=5432 user=repl application_name=pg03 
passfile=''/home/postgres/.pgpass''' 
[pg02]# psql -h pg02 -p 5432 -U pgpool postgres -c "select * from 
pg_stat_replication" -x 
Password for user pgpool: 
Password for user pgpool: 
-[ RECORD 1 ]----+------------------------------ 
pid | 10263 
usesysid | 16385 
usename | repl 
application_name | pg03 
client_addr | 192.168.1.104 
client_hostname | 
client_port | 36934 
backend_start | 2023-04-17 15:30:26.997494+08 
backend_xmin | 
state | streaming 
sent_lsn | 0/D000268 
write_lsn | 0/D000268 
flush_lsn | 0/D000268 
replay_lsn | 0/D000268 
write_lag | 
flush_lag | 
replay_lag | 
sync_priority | 0 
sync_state | async 
reply_time | 2023-04-17 15:36:37.796975+08

在线恢复

使用 pgpoolII 在线恢复功能将 pg01（旧主库）恢复为备库。在恢复旧主库之前，请确保

recovery_1st_stage 和 pgpool_remote_start 脚本存在于当前主库 pg02 的数据目录中

# pcp_recovery_node -h 192.168.86.145 -p 9898 -U pgpool -n 0 
Password: 
pcp_recovery_node -- Command Successful

然后验证 pg01 是否已作为备用服务器启动

# psql -h 192.168.86.145 -p 9999 -U pgpool postgres -c "show pool_nodes" 
Password for user 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 | pg01 | 5432 | up | up | 0.333333 | standby | standby | 0 | 
true | 96 | | | 2023-04-17 15:40:41 
 1 | pg02 | 5432 | up | up | 0.333333 | primary | primary | 0 | 
false | 0 | | | 2023-04-17 15:25:01 
 2 | pg03 | 5432 | up | up | 0.333333 | standby | standby | 0 | 
false | 0 | | | 2023-04-17 15:25:25 
(3 rows)

排骨无年糕

关注

38
点赞
踩
15

收藏

觉得还不错? 一键收藏
1
评论
PostgreSQL高可用-pgpool-II+流复制

pgpool-II是一个在PostgreSQL服务器和PostgreSQL客户端之间工作的中间件。它是根据BSD许可证授权的。pgpool-II的功能pgpool-II保存与PostgreSQL服务器的连接，并在具有相同属性（即用户名，数据库，协议版本）的新连接进入时重用它们。它减少了连接开销，并提高了系统的整体吞吐量。pgpool-II可以管理多个PostgreSQL服务器。数据变更会同时发送到所有的后端数据库上，以保证多个后端数据库的数据完全一样。
复制链接

扫一扫