PostgreSQL主从流复制+pgpool高可用实战指南

简介：本资源是一份面向数据库运维工程师与PostgreSQL高可用架构实践者的完整技术方案文档，聚焦Postgres主从流复制与pgpool-II协同构建生产级高可用数据库集群。内容系统覆盖流复制原理（WAL日志实时同步、同步/异步模式对比）、pgpool-II核心能力（连接池管理、自动故障切换、读写分离与负载均衡）及全链路部署实操，包含环境准备、主备库配置、pgpool参数调优、健康检查与故障模拟等关键环节。资源为单个773KB的Word文档（.docx），结构清晰，含四章二十余节，含版本控制表、详细目录、配置命令示例与网络/权限/时钟等前置检查清单，便于按步骤落地验证。目前已有472人学习下载，适合中高级DBA快速掌握PostgreSQL+pgpool高可用方案的设计逻辑、配置要点与排错路径。

1. PostgreSQL主从流复制+pgpool高可用方案：不是堆机器，而是让两台PostgreSQL真正“互为备份、各司其职”

你有没有遇到过这种场景：线上PostgreSQL主库突然夯住，DBA手抖敲下systemctl restart postgresql，结果应用连不上、监控告警炸屏、业务方电话直接打到值班手机上？更糟的是，备库明明开着，却没人敢切——因为不确定它到底同步到哪一秒、wal是否堆积、连接池会不会把读请求发错地方。这不是玄学，是配置没闭环。

这份《Postgres主从流复制+pgpool高可用方案》文档，不是教你怎么装软件，而是把一套在RHEL/CentOS生产环境里跑过半年以上、经受过3次真实主库宕机切换的双节点高可用链路，掰开揉碎讲清楚：WAL日志怎么从master实时推到standby、pgpool如何识别主备状态并自动漂移VIP、为什么备库能接只读但绝不能接写、以及最关键的——failover后怎么确保新主库的sequence和replication slot不丢不乱。它面向的是已经能独立部署单实例PostgreSQL、但还没亲手搭过跨组件高可用链路的中级DBA或后端工程师。如果你正卡在“配完pgpool连不上”“备库显示in recovery但查不到数据”“切换后应用报错duplicate key”这些具体翻车点，这篇就是为你写的血泪复盘笔记。

---

2. 流复制底层机制：WAL不是日志文件，而是数据库状态的“原子快照流”

2.1 WAL的本质：不是磁盘日志，而是内存状态的持续快照

很多人误以为WAL是“写完SQL再落盘的日志”，其实完全相反：WAL是PostgreSQL事务提交前必须完成的强制步骤。当一个INSERT执行时，数据页变更先写入shared_buffers内存，同时对应的WAL record（含LSN、XID、page offset、变更内容）被强制刷入WAL buffer；事务COMMIT时，walsender进程立即将该WAL record通过TCP stream推送给standby的walreceiver；只有walreceiver返回ACK，master才允许事务返回成功。这个过程绕过了文件系统缓存，直通磁盘（或由wal_sync_method控制），所以WAL才是数据库一致性的唯一权威来源。

提示：pg_wal目录下的文件名（如000000010000000000000001）本质是LSN的十六进制编码，前8位是timeline ID，后16位是log segment编号。备库通过pg_stat_replication中的sent_lsn、write_lsn、flush_lsn、replay_lsn四个值，精确判断自己落后master多少字节——这才是“同步延迟”的真实定义，不是看pg_is_in_recovery()返回true就万事大吉。

2.2 同步 vs 异步：不是开关，而是LSN确认策略的硬约束

文档里提到synchronous_commit = on和synchronous_standby_names，但没说清它们如何协同工作。实际机制是：

• 当synchronous_commit = remote_write时，master必须等到至少一个standby的walreceiver将WAL写入本地磁盘（write_lsn追上），才返回客户端成功；
• 当synchronous_commit = on（等价于remote_apply）时，master必须等到standby的startup进程将WAL replay到内存（replay_lsn追上），才允许事务提交。

这意味着：同步模式下，standby的replay_lsn必须严格等于master的flush_lsn，否则事务会hang住。而异步模式下，master只管自己flush_lsn，standby的replay_lsn可能落后几秒甚至几分钟（取决于网络和I/O）。生产环境选同步还是异步，本质是在“数据零丢失”和“写入延迟可控”之间做取舍，没有中间态。

2.3 12.x版本的配置断层：recovery.conf消失，但逻辑没变

PostgreSQL 12彻底移除了recovery.conf，把所有恢复参数合并进postgresql.conf。但这不是简化，而是把隐式逻辑显性化。关键变化有三点：

1. primary_conninfo不再写在独立文件，而是作为postgresql.conf中primary_conninfo = 'host=192.168.1.10 port=5432 user=repl password=xxx'存在；
2. trigger_file参数被废弃，改用promote_trigger_file = '/tmp/pg_failover.trigger'，且该文件必须由外部脚本（如pgpool的failover_command）创建；
3. recovery_target_timeline = 'latest'成为默认行为，无需手动配置。

注意：如果你在12.x备库的postgresql.conf里还看到recovery_target_timeline = 'current'，说明配置模板没更新，这会导致failover后新主库无法接受新WAL，直接卡死。

2.4 备库只读的底层限制：不是配置项，而是WAL replay的天然属性

hot_standby = on只是打开只读查询的开关，真正的限制来自WAL replay机制：standby的startup进程在replay WAL时，会对涉及的page加BufferPin锁，此时任何DML都会因无法获取AccessExclusiveLock而报错cannot execute INSERT in a read-only transaction。但这里有个坑：如果standby的max_standby_streaming_delay设置过大（如30s），当master产生大量WAL时，standby会主动cancel掉长时间运行的SELECT，导致应用报错canceling statement due to conflict with recovery。这不是bug，是为避免查询阻塞WAL replay的保护机制。

2.5 验证流复制健康度的三板斧命令

别只信pg_is_in_recovery()，要交叉验证三个维度：

# 1. 查master当前WAL位置和同步状态 psql -U postgres -c "SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn()), pg_stat_replication.* FROM pg_stat_replication;" # 2. 查standby的replay进度（必须在standby节点执行） psql -U postgres -c "SELECT pg_last_wal_replay_lsn(), pg_last_wal_receive_lsn(), pg_is_in_recovery();" # 3. 计算延迟字节数（master上执行，需替换standby的application_name） psql -U postgres -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS byte_lag FROM pg_stat_replication WHERE application_name = 'standby1';"

• byte_lag< 1MB：健康；
• byte_lag> 10MB且持续增长：检查standby磁盘I/O或网络丢包；
• replay_lsn为空：standby未启动recovery或WAL接收中断。

---

3. pgpool-II配置核心：不是代理层，而是PostgreSQL集群的“状态感知中枢”

3.1 pgpool如何发现主备？靠的是show pool_nodes，不是DNS或VIP

很多新手以为pgpool通过VIP或hostname自动识别主库，其实完全错误。pgpool的health_check_period参数会定期（默认10秒）向每个backend节点发送SELECT 1，根据返回结果和backend_flag标记节点状态。但真正的主库识别依赖watchdog或failover_command脚本的输出。例如，在pgpool.conf中：

failover_command = '/var/log/pgpool/failover.sh %d %H %P %r %R'

当pgpool检测到backend 0（原master）失联时，会执行该脚本，并传入%d=0 %H=192.168.1.10 %P=5432 %r=192.168.1.11 %R=5432，脚本内部必须完成：

• 在standby节点执行pg_ctl promote；
• 修改/etc/hosts或DNS指向新VIP；
• 更新pgpool的pool_hba.conf允许新主库连接。

没有这一步，pgpool永远认为backend 0是主库，即使它已宕机。

3.2 连接池的“连接复用”真相：不是长连接，而是连接池生命周期管理

connection_cache = on常被误解为“保持与PostgreSQL的长连接”，实际机制是：pgpool为每个client connection维护一个backend connection池，当client断开时，pgpool不会立即关闭backend connection，而是将其放回空闲池；当下一个client以相同user/db参数连接时，pgpool直接复用该backend connection。但backend connection本身仍受PostgreSQL的idle_in_transaction_session_timeout控制。如果应用开启事务后不提交也不rollback，pgpool的backend connection会被PostgreSQL强制kill，导致pgpool日志出现backend connection is closed by remote host。

3.3 读写分离的边界：pgpool不解析SQL，只按规则路由

pgpool的load_balance_mode = on仅对SELECT语句生效，且不区分是否带FOR UPDATE、是否在事务中。这意味着：

• BEGIN; SELECT * FROM users; UPDATE orders SET status='done'; COMMIT;—— 第一条SELECT被路由到standby，第二条UPDATE因非SELECT被发往master；
• SELECT * FROM users FOR UPDATE;—— 因FOR UPDATE是写操作语义，pgpool会将其发往master，即使语法是SELECT。

更危险的是：如果应用在事务中混合读写，pgpool无法保证事务一致性。解决方案只有两个：

1. 在应用层显式指定/*NO LOAD BALANCE*/ SELECT ...注释；
2. 关闭load_balance_mode，所有流量走master，用pgpool仅作连接池和故障转移。

3.4 watchdog机制：不是可选插件，而是双pgpool节点高可用的刚需

单pgpool节点是单点故障。要实现pgpool自身高可用，必须启用watchdog。其原理是：两个pgpool节点通过wd_hostname互相心跳，选举出一个active节点负责VIP漂移和failover决策，另一个standby节点监听active状态。关键配置是delegate_IP（VIP地址）和ifconfig_path（如/sbin/ifconfig）。如果ifconfig_path路径错误，watchdog会静默失败，VIP永不漂移。验证命令：

# 在任意pgpool节点执行 pgpool -m fast stop && pgpool -n -d 2>&1 | grep -i watchdog # 正常应输出"watchdog is running"和"delegate IP is assigned"

3.5 避坑：pgpool常见问题排查（现象→原因→解决）

现象1：psql -h 127.0.0.1 -p 9999 -U postgres连接超时，但直连5432端口正常

原因：pgpool未启动，或listen_addresses = 'localhost'未包含客户端IP，或防火墙拦截9999端口。
解决：检查pgpool.conf中listen_addresses是否为'*'或包含客户端网段；执行ss -tlnp | grep 9999确认端口监听；用iptables -L -n | grep 9999检查规则。

现象2：show pool_nodes;显示所有节点status = down，但psql -h standby_ip可连

原因：pgpool的health_check_user用户无权限，或health_check_password未在pool_passwd中加密。
解决：在master执行pg_md5 -m -f /etc/pgpool-II/pool_passwd health_check_user生成密码；确认health_check_user在PostgreSQL中存在且有CONNECT权限。

现象3：failover后新主库无法接受写入，报错FATAL: database system is starting up

原因：failover_command脚本未正确执行pg_ctl promote，或promote_trigger_file路径与postgresql.conf中配置不一致。
解决：登录新主库，检查pg_is_in_recovery()返回f；查看pg_log中是否有database system is ready to accept connections；确认postgresql.conf中promote_trigger_file指向的文件已被删除（promote后自动清理）。

现象4：show pool_status;中num_init_children远大于max_pool，连接数爆炸

原因：应用未正确关闭连接，pgpool的child_life_time（默认300秒）未触发清理。
解决：调小child_life_time = 60；在应用层确保connection.close()被调用；启用connection_life_time = 300强制回收空闲连接。

现象5：VIP漂移后，旧master重启变成新standby，但WAL同步停滞

原因：旧master的recovery.conf（或12.x的postgresql.conf）中primary_conninfo仍指向已失效的旧standby IP。
解决：在旧master的配置中，将primary_conninfo的host参数改为新主库IP；执行pg_ctl reload；检查pg_stat_replication确认新主库有该节点的连接记录。

---

4. pg_rman备份实战：不是dump，而是WAL+basebackup的原子快照组合

4.1 pg_rman与pg_dump的本质区别：时间点恢复能力

pg_dump导出的是SQL文本，只能恢复到dump时刻的逻辑状态，无法应对误删表、drop database等DDL事故；而pg_rman基于pg_basebackup和WAL归档，能实现PITR（Point-in-Time Recovery）。其原理是：

• pg_rman backup --backup-mode=full：调用pg_basebackup生成基础备份（basebackup），同时记录起始LSN；
• pg_rman backup --backup-mode=incremental：只备份自上次full backup以来变更的WAL段；
• 恢复时，pg_rman restore --recovery-target-time="2023-10-01 12:00:00"：先解压basebackup，再按时间顺序重放WAL，直到目标时间戳。

提示：pg_rman show命令列出的所有备份，其TLI（Timeline ID）必须连续，否则PITR会失败。如果主库发生过failover，新主库的TLI会+1，此时必须用pg_rman validate检查备份链完整性。

4.2 归档配置的致命细节：archive_command必须返回0

postgresql.conf中archive_mode = on和archive_command是WAL归档的开关。但很多文档忽略一点：archive_command脚本执行后，shell exit code必须为0，否则PostgreSQL会停止归档并报错archive command failed with exit code 1。典型错误配置：

# 错误：rsync失败时exit code非0，但脚本未捕获 archive_command = 'rsync -a %p /backup/wal/%f' # 正确：强制返回0，归档失败日志到pg_log archive_command = 'rsync -a %p /backup/wal/%f 2>>/var/log/postgresql/archive.log || true'

4.3 pg_rman初始化：不是建目录，而是注册备份仓库

pg_rman init命令会在指定目录创建.pg_rman元数据库，存储所有备份的LSN、时间、大小信息。但必须确保该目录位于PostgreSQL数据目录之外，且postgres用户有读写权限。常见错误：

• 目录在/var/lib/pgsql/data下，导致pg_rman与PostgreSQL争抢文件锁；
• 目录属主为root，postgres用户无法写入。

正确做法：

# 创建独立备份目录 sudo mkdir -p /backup/pg_rman sudo chown postgres:postgres /backup/pg_rman sudo chmod 700 /backup/pg_rman # 切换到postgres用户初始化 sudo -u postgres pg_rman init -B /backup/pg_rman

4.4 全量+增量备份脚本的关键逻辑

文档附件中的pg_rman_full.sh和pg_rman_archive.sh需按周期执行。但生产环境必须加入校验环节：

#!/bin/bash # pg_rman_full.sh - 每周日凌晨执行 BACKUP_DIR="/backup/pg_rman" DATE=$(date +%Y%m%d) # 1. 执行全量备份 sudo -u postgres pg_rman backup -B "$BACKUP_DIR" --backup-mode=full --with-server-log # 2. 验证备份完整性（关键！） if sudo -u postgres pg_rman validate -B "$BACKUP_DIR"; then echo "Full backup $DATE validated OK" # 3. 清理7天前的备份（保留最近3个full） sudo -u postgres pg_rman delete -B "$BACKUP_DIR" --oldest --force else echo "Full backup $DATE validation FAILED!" | mail -s "PG_RMAN ALERT" [e-mail] exit 1 fi

没有validate步骤的备份，等于没备份。

4.5 PITR恢复的四步不可逆操作

恢复不是pg_rman restore一条命令，而是原子流程：

1. 停库：sudo systemctl stop postgresql（必须停止所有PostgreSQL进程）；
2. 清空数据目录：sudo rm -rf /var/lib/pgsql/data/*（注意不是data目录本身）；
3. 执行restore：sudo -u postgres pg_rman restore -B /backup/pg_rman --recovery-target-time="2023-10-01 12:00:00"；
4. 创建recovery.signal：touch /var/lib/pgsql/data/recovery.signal（12.x必需，10.x用recovery.conf）。

注意：第3步的--recovery-target-time必须早于误操作时间，且不能晚于备份链中最后一个WAL的时间。用pg_rman show查START_TIME和END_TIME确认范围。

---

5. 故障切换全流程验证：从模拟宕机到业务回归的12分钟实操

5.1 主库PostgreSQL进程级宕机测试（最常见场景）

这是最贴近真实的故障：主库PostgreSQL服务崩溃，但服务器和pgpool仍在运行。步骤如下：

# Step 1: 在master节点，模拟进程崩溃 sudo systemctl stop postgresql # 或 kill -9 `pgrep -f "postgres -D /var/lib/pgsql/data"` # Step 2: 立即在pgpool节点检查状态（30秒内） psql -h 127.0.0.1 -p 9999 -U postgres -c "show pool_nodes;" # 预期：node_id=0 status=down, node_id=1 status=up and role=primary # Step 3: 检查VIP是否漂移到standby ssh standby_node "ip addr show | grep 192.168.1.100" # 预期：输出包含"inet 192.168.1.100/24" # Step 4: 验证新主库可写（在standby节点执行） psql -h 127.0.0.1 -p 5432 -U postgres -c "CREATE TABLE test_failover(id SERIAL); INSERT INTO test_failover VALUES(1);"

关键观察点：从systemctl stop到show pool_nodes显示新主库，应在45秒内完成（取决于health_check_timeout和failover_command执行时间）。如果超时，检查pgpool.conf中failover_command脚本是否卡在pg_ctl promote权限问题。

5.2 pgpool节点级宕机：VIP漂移的黄金5分钟

当运行pgpool的服务器宕机，watchdog必须在5分钟内完成VIP漂移。验证方法：

# 在active pgpool节点，模拟宕机 sudo systemctl stop pgpool # 在standby pgpool节点，每10秒检查 watch -n 10 'ip addr show | grep 192.168.1.100 && echo "VIP acquired" || echo "waiting..."' # 预期：2-3分钟后输出"VIP acquired" # 同时检查新active节点的pgpool日志 tail -f /var/log/pgpool/pgpool.log | grep -i "delegate IP" # 预期：出现"delegate IP is assigned to this node"

血泪经验：如果VIP不漂移，90%概率是watchdog配置中other_pgpool_hostname指向了错误IP，或heartbeat_destination端口（默认9000）被防火墙拦截。用nc -zv standby_pgpool_ip 9000测试连通性。

5.3 切换后应用连接恢复的三个必检项

failover成功不等于业务恢复。必须验证：

1. 连接字符串是否指向VIP：应用配置的jdbc:postgresql://192.168.1.100:9999/mydb，而非直连192.168.1.10:5432；
2. pgpool的allow_sql_comments = on是否开启：某些ORM框架（如Hibernate）生成的SQL带/* generated by hibernate */注释，若此参数关闭，pgpool会拒绝该SQL；
3. 新主库的max_connections是否足够：原standby的max_connections可能小于master，切换后并发连接数突增会导致too many clients错误。

5.4 旧主库重入集群的“消毒”流程

旧master恢复后，不能直接systemctl start postgresql，必须重置为standby：

# 在旧master节点（原master，现需变standby） sudo systemctl stop postgresql sudo rm -rf /var/lib/pgsql/data/* # 从新主库拉取basebackup（关键！） sudo -u postgres pg_basebackup -h 192.168.1.100 -D /var/lib/pgsql/data -P -U repl -R # 启动，自动进入recovery模式 sudo systemctl start postgresql

-R参数会自动生成standby.signal和postgresql.auto.conf，比手动配置可靠十倍。

5.5 一份能放进巡检脚本的健康检查清单

把以下命令写成/usr/local/bin/pg_health_check.sh，每天凌晨cron执行：

#!/bin/bash # 检查1：流复制延迟 Lag=$(psql -U postgres -t -c "SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication WHERE application_name='standby1';" 2>/dev/null) if [ -z "$Lag" ] || [ "$Lag" -gt 10485760 ]; then # >10MB echo "REPLICATION LAG CRITICAL: ${Lag} bytes" | mail -s "PG ALERT" [e-mail] fi # 检查2：pgpool节点状态 Nodes=$(psql -h 127.0.0.1 -p 9999 -U postgres -t -c "show pool_nodes;" 2>/dev/null | wc -l) if [ "$Nodes" -ne 2 ]; then echo "PGPOOL NODES MISMATCH: expected 2, got $Nodes" | mail -s "PG ALERT" [e-mail] fi # 检查3：最近备份有效性 if ! sudo -u postgres pg_rman validate -B /backup/pg_rman 2>/dev/null; then echo "PG_RMAN BACKUP INVALID" | mail -s "PG ALERT" [e-mail] fi

从那以后我每次上线新集群，都强制走一遍这个脚本，再加一次手动failover演练。不是信不过文档，是信不过自己漏掉的那一个chmod 700。希望帮到你。

本文还有配套的精品资源，点击获取