PostgreSQL(二)基于Pgpool-II实现一主二从集群的高可用与负载均衡

痛仰乐队

405人浏览 · 2026-02-14 00:02:01

痛仰乐队 · 2026-02-14 00:02:01 发布

1. 为什么需要Pgpool-II？从单点风险到高可用集群

在上一篇文章里，我们成功搭建了一个PostgreSQL一主二从的集群。主节点负责写，两个从节点负责读，数据通过流复制保持同步。看起来很美，对吧？但这里有个大问题：应用怎么连接？

如果应用直连主库，一旦主库宕机，整个系统就“写瘫痪”了。如果应用手动配置读写分离，代码会变得复杂，而且从库故障时还得自己处理。更麻烦的是，主库故障后，需要人工介入去提升一个从库为新主，这个时间窗口业务是中断的。

这就是我们需要 Pgpool-II 的原因。你可以把它想象成数据库集群的“智能交通指挥中心”。所有应用程序都连接到Pgpool-II，由它来负责：

自动故障转移（Failover）：当主库挂掉时，自动选择一个从库提升为新主，应用几乎无感知。
负载均衡（Load Balance）：将读请求（SELECT）智能地分发到多个从库上，充分利用硬件资源，提升整体查询吞吐量。
连接池（Connection Pool）：复用数据库连接，避免频繁创建和销毁连接的开销，这对Web应用性能提升非常明显。

我经历过一次没有Pgpool的线上故障，半夜爬起来手动切换主从，那滋味可不好受。所以，给已有的PostgreSQL集群加上Pgpool-II，是从“能用”到“可靠、高效”的关键一步。接下来，我们就手把手把它部署起来。

2. 部署准备：理清架构与安装依赖

在开始敲命令之前，我们先明确最终的架构。我们有三台已经部署好PostgreSQL的服务器（假设沿用上一篇文章的配置）：

node1 (192.168.149.236): PostgreSQL 主节点
node2 (192.168.149.237): PostgreSQL 从节点1
node3 (192.168.149.238): PostgreSQL 从节点2

现在，我们要在每一台服务器上都安装Pgpool-II。是的，Pgpool-II本身也需要以集群模式运行，避免自己成为单点。它们之间通过“看门狗”（Watchdog）相互监控，并协商出一个“主”Pgpool节点来持有和管理一个虚拟IP（VIP），例如 192.168.149.200。应用程序最终连接的就是这个VIP。

第一步，在所有节点上安装Pgpool-II的YUM源和软件包：

# 在三台机器上分别执行
# 添加Pgpool-II官方YUM源（以CentOS 7和pgpool-II 3.7为例，版本请根据实际情况调整）
sudo yum install -y https://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm

# 安装pgpool-II核心包及扩展
sudo yum install -y pgpool-II-pg96 pgpool-II-pg96-extensions

这里注意，pgpool-II-pg96 表示适配PostgreSQL 9.6版本。请根据你实际的PostgreSQL版本选择对应的包名（如 pgpool-II-pg10, pgpool-II-pg11 等）。安装完成后，配置文件默认在 /etc/pgpool-II 目录下。

第二步，配置SSH互信（关键步骤）： Pgpool-II在执行故障转移脚本时，可能需要通过SSH登录到其他节点执行命令（如提升从库）。为了方便，我们为 postgres 系统用户配置免密登录。

# 在三台机器上，分别以 postgres 用户操作
sudo su - postgres
ssh-keygen -t rsa # 一路回车
# 将公钥拷贝到另外两台机器（需要输入postgres用户密码）
ssh-copy-id postgres@node2
ssh-copy-id postgres@node3
# 在每台机器上测试一下
ssh node2 date
ssh node3 date

配置成功后，postgres 用户在三台机器之间可以免密SSH访问，这是后续自动故障切换能顺利进行的基础。

3. 核心配置详解：手把手编写pgpool.conf

Pgpool-II的配置主要集中在 /etc/pgpool-II/pgpool.conf。这个文件有点长，但别怕，我们分段搞定。建议先备份原文件。

第一部分：基础连接与后端定义 找到并修改以下参数，这定义了Pgpool自己如何监听，以及它身后有哪些PostgreSQL服务器。

# 监听所有IP地址，允许任何来源的连接
listen_addresses = '*'
# Pgpool的服务端口，应用就连接这个端口
port = 9999

# 定义后端数据库节点 0 (主节点)
backend_hostname0 = 'node1'
backend_port0 = 5432
backend_weight0 = 1 # 负载均衡权重，主库写，读权重可设低或不读
backend_data_directory0 = '/var/lib/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER' # 允许此节点故障转移

# 定义后端数据库节点 1 (从节点1)
backend_hostname1 = 'node2'
backend_port1 = 5432
backend_weight1 = 1 # 读权重，可以按机器性能调整，比如性能好的设2
backend_data_directory1 = '/var/lib/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

# 定义后端数据库节点 2 (从节点2)
backend_hostname2 = 'node3'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

第二部分：启用负载均衡与流复制检测 这是实现读写分离的核心。Pgpool-II需要知道如何判断主从，以及如何检查从库的复制延迟。

# 关闭复制模式（我们用的是PostgreSQL自身的流复制，不是Pgpool的复制）
replication_mode = off
# 开启负载均衡模式
load_balance_mode = on
# 开启主从模式
master_slave_mode = on
# 子模式设为流复制
master_slave_sub_mode = 'stream'

# 流复制检查配置
sr_check_period = 10 # 每10秒检查一次复制状态
sr_check_user = 'repl' # 一个用于检查的数据库用户，需要在所有库存在
sr_check_password = 'repl_password' # 该用户的密码
sr_check_database = 'postgres' # 检查连接的数据库
delay_threshold = 10000000 # 复制延迟阈值（字节），超过此值的从库不会接收读请求

这里 sr_check_user 我们用了之前做主从复制时创建的 repl 用户，非常合适。你需要确保它的密码正确。

第三部分：健康检查 Pgpool-II需要定期检查后端PostgreSQL是否活着。

health_check_period = 10 # 每10秒健康检查一次
health_check_timeout = 20 # 检查超时时间
health_check_user = 'repl' # 执行健康检查的用户
health_check_password = 'repl_password'
health_check_database = 'postgres'

第四部分：看门狗与高可用（最关键） 这是让Pgpool-II自己实现高可用的部分。我们以node1上的配置为例，其他节点类似，但 wd_hostname 和部分IP需要改变。

# 启用看门狗
use_watchdog = on
# 本节点在看门狗集群中的标识
wd_hostname = 'node1'
wd_port = 9000
wd_priority = 1 # 优先级，数字越大越优先成为主Watchdog

# 虚拟IP (VIP) 配置，这是应用真正连接的地址
delegate_IP = '192.168.149.200'
if_up_cmd = 'ip addr add $_IP_$/24 dev ens33 label ens33:0' # 启用VIP的命令
if_down_cmd = 'ip addr del $_IP_$/24 dev ens33' # 停用VIP的命令
arping_cmd = 'arping -U $_IP_$ -w 1 -I ens33' # 广播VIP地址

注意： ens33 是你的网卡名称，请用 ip addr 命令查看并替换。 $_IP_$ 是Pgpool-II的内部变量，会自动替换为上面的 delegate_IP。

第五部分：看门狗心跳与节点互认 看门狗节点之间需要互相发送心跳包，并知道彼此的存在。

# 心跳检查方式
wd_lifecheck_method = 'heartbeat'
wd_heartbeat_port = 9694
# 心跳目标：其他两个节点
heartbeat_destination0 = 'node2'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'ens33'

heartbeat_destination1 = 'node3'
heartbeat_destination_port1 = 9694
heartbeat_device1 = 'ens33'

# 识别其他Pgpool节点
other_pgpool_hostname0 = 'node2'
other_pgpool_port0 = 9999 # 其他节点的Pgpool服务端口
other_wd_port0 = 9000     # 其他节点的看门狗端口

other_pgpool_hostname1 = 'node3'
other_pgpool_port1 = 9999
other_wd_port1 = 9000

对于node2和node3，配置文件几乎相同，只需将 wd_hostname 改为 node2/node3，并将 heartbeat_destination 和 other_pgpool_hostname 中的IP指向正确的其他节点即可。

4. 配置认证与故障转移脚本

配置pool_hba.conf和密码文件： Pgpool-II可以有自己的客户端认证机制。我们简单配置一下，或者可以直接使用后端PostgreSQL的认证。

# 编辑 /etc/pgpool-II/pool_hba.conf，在末尾添加类似行，允许应用网段连接
host    all         all         192.168.149.0/24          md5

为了让Pgpool-II能验证MD5密码，需要生成密码文件。首先在所有PostgreSQL中创建一个专门给Pgpool连接用的用户（如果已有则跳过）：

CREATE USER pgpooluser WITH PASSWORD 'your_strong_password';

然后在Pgpool节点上生成密码文件：

pg_md5 -m -u pgpooluser your_strong_password

这会在 /etc/pgpool-II 下创建或更新 pool_passwd 文件。

编写故障转移脚本： 这是自动切换的“大脑”。创建一个脚本 /etc/pgpool-II/failover.sh，并赋予执行权限 (chmod +x)。

#!/bin/bash
# Pgpool-II故障转移脚本
# 参数: %d 故障节点ID, %h 故障节点主机名, %p 故障节点端口, %D 故障节点数据库目录, %m 新主节点ID, %H 新主节点主机名, %M 旧主节点ID, %P 旧主节点端口

failed_node_id=$1
failed_node_host=$2
new_primary_id=$6
new_primary_host=$7

# 日志记录
log_file="/var/log/pgpool/failover.log"
echo "`date`: Failover triggered. Failed Node: $failed_node_host[$failed_node_id], New Primary: $new_primary_host[$new_primary_id]" >> $log_file

# 只有当故障的是主节点时，我们才需要提升新主
# 检查故障节点ID是否等于旧主节点ID（通过第7个参数判断，这里假设旧主ID通过环境或方式传递，简化逻辑）
# 更健壮的脚本会判断故障节点的角色。这里我们用一个简单判断：如果故障节点是node1（我们初始的主），则触发提升。
if [ "$failed_node_host" = "node1" ]; then
    echo "`date`: Failed node is the original master. Promoting $new_primary_host." >> $log_file
    # 使用SSH免密登录到新主节点，执行提升命令
    ssh -T postgres@$new_primary_host "/usr/pgsql-9.6/bin/pg_ctl promote -D /var/lib/pgsql/data"
    if [ $? -eq 0 ]; then
        echo "`date`: Successfully promoted $new_primary_host." >> $log_file
    else
        echo "`date`: Failed to promote $new_primary_host!" >> $log_file
        exit 1
    fi
else
    echo "`date`: Failed node is a standby. No promotion needed." >> $log_file
fi

exit 0

这是一个简化版脚本。在生产环境中，你需要一个更严谨的脚本，可能包括：

检查故障节点的确切角色。
更新其他从库的 recovery.conf 或 primary_conninfo 指向新的主库。
更完善的错误处理和日志记录。

将编写好的脚本拷贝到三台机器的相同位置。

配置pgpool.conf调用脚本： 在 pgpool.conf 中指定故障转移脚本路径：

failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P'

5. 启动集群与功能验证

启动顺序很重要：

确保所有PostgreSQL实例都已启动。
按顺序启动Pgpool-II服务。先启动你希望成为初始Watchdog主节点的机器（比如node1），然后再启动其他节点。

# 在三台机器上分别执行
sudo systemctl start pgpool.service
sudo systemctl enable pgpool.service # 设置开机自启

检查集群状态： 使用 pcp 命令或SQL命令查看状态。

# 查看看门狗集群状态 (需要在某台机器上执行，使用pcp.conf中配置的用户密码)
pcp_watchdog_info -h node1 -p 9898 -U pgpooluser -w

输出应显示三个节点，其中一个状态为 MASTER，并且 VIP up on local node 为 YES。这个MASTER节点就是当前持有VIP的Pgpool主节点。

通过VIP连接并验证负载均衡： 现在，应用程序可以连接到 192.168.149.200:9999 了。

psql -h 192.168.149.200 -p 9999 -U postgres -d postgres

在psql中，执行Pgpool-II特有的管理命令：

SHOW pool_nodes;

你会看到类似下面的输出：

 node_id | hostname | port | status | lb_weight |  role   | select_cnt
---------+----------+------+--------+-----------+---------+------------
 0       | node1    | 5432 | up     | 0.333333  | primary | 0
 1       | node2    | 5432 | up     | 0.333333  | standby | 5
 2       | node3    | 5432 | up     | 0.333333  | standby | 3

注意 select_cnt 列，它显示了每个节点处理过的读查询数量。多执行几次 SELECT 查询，你会看到 select_cnt 在node2和node3上增长，而node1（主库）的 select_cnt 可能不变（取决于你的负载均衡权重设置），这证明了读负载均衡在工作。而执行 INSERT/UPDATE 操作，则一定会落到 role 为 primary 的节点上。

模拟故障转移测试（谨慎操作！）： 这是验证高可用的关键时刻。我们模拟主库（node1）的PostgreSQL进程崩溃。

在node1上：sudo systemctl stop postgresql
等待大约10-30秒（健康检查周期+故障转移时间）。
再次通过VIP连接数据库，执行 SHOW pool_nodes;。
你应该会看到，原来的某个从节点（比如node2）的 role 变成了 primary，而node1的 status 变成了 down。
尝试执行写操作（如 CREATE TABLE test_failover (id int);），应该能成功，证明新主库可以正常工作。
检查 /var/log/pgpool/failover.log，可以看到脚本执行的日志。

恢复故障节点： 将node1的PostgreSQL修复后（例如，以从库身份重新同步数据），可以使用 pcp_attach_node 命令将其重新加入集群。

6. 避坑指南与生产环境建议

踩过不少坑后，我总结了一些关键点：

版本兼容性：Pgpool-II小版本和PostgreSQL大版本之间可能存在兼容性问题。尽量选择经过验证的版本组合，并查阅官方文档的兼容性列表。
网络与防火墙：确保三台服务器之间的所有相关端口（5432, 9999, 9000, 9898, 9694）在防火墙上是互通的。很多诡异的问题都源于网络不通。
时间同步：集群内所有服务器必须保持时间同步（使用NTP），否则看门狗的心跳检测和故障判断会出问题。
脚本权限与路径：故障转移脚本必须对运行Pgpool-II的用户（通常是root或postgres）可执行，且脚本内的命令（如 ssh, pg_ctl）需要使用绝对路径。
连接池与客户端：Pgpool-II的连接池功能有时会与某些客户端库（如JDBC）的某些设置冲突。如果遇到连接异常，可以尝试在客户端连接字符串中添加 pgpool 相关的参数，或者暂时调整Pgpool的 pool_idle_timeout 等设置。
监控：一定要监控 SHOW pool_nodes; 的结果，以及Pgpool的日志 (/var/log/messages 或指定的log目录)。可以将其集成到Zabbix、Prometheus等监控系统中。
更健壮的故障转移：本文的故障转移脚本是示例。生产环境建议使用更成熟的方案，例如利用 pg_rewind 工具来修复旧主库，或者使用 trigger_file 的方式提升备库，这些方式比直接调用 pg_ctl promote 在某些场景下更安全。