PostgreSQL流复制+pgpool-II高可用集群搭建与故障切换实战

简介面向PostgreSQL数据库运维与架构设计人员围绕主从流复制与pgpool-II高可用这一经典组合系统讲解WAL日志实时同步原理、同步/异步复制差异以及连接池、负载均衡、自动故障切换等核心机制可直接用于生产环境高可用方案参考。资源压缩包内共1个docx文档容量773KB正文包含方案综述、实践环境要求、客户环境准备、配置步骤及测试建议等完整章节结构清晰目录涵盖环境调整、网络配置、系统参数优化与资源限制等细节。文档对WAL写入、发送、接收与重放进程间的协作做了梳理可帮助理解数据同步的内在链路。目前已有469人学习下载适合正在规划PostgreSQL主备架构或需要落地pgpool-II读写分离的工程师。通过阅读可掌握主库wal_level配置、备库初始化、pgpool-II监控与切换策略等关键操作并参考其中的故障模拟测试方法验证集群可用性。1. 这套“流复制pgpool”方案到底在解决什么问题数据库半夜挂了等我们从备份里恢复数据、再把业务拉起来经常已经过去了大半小时。如果只是备份数据还在但业务中断的时间扛不住如果只有主从备库能查却不能写流量切不过去如果只有pgpool前面挂一堆连接池后面单库一台挂了照样全灭。把Postgres主从流复制和pgpool-II放在一起才是真正把“数据有备份”升级成“业务不中断”的完整答案。这套方案的核心思路很清楚Postgres自身用流复制把WAL日志实时传给备库备库处于热备状态pgpool-II坐在应用和数据库之间统一接流量、做读写分离并且通过健康检查和failover脚本在主库挂掉时把一个备库提升为新主库。对读多写少、数据量在百GB到几TB、不想引入分布式中间件的中小型业务这是目前最成熟也最省心的高可用落地方式。整个部署只要两台数据库节点加一个pgpool节点成本可控运维边界清晰。接下来我会按一个真实项目的推进顺序来拆先搭流复制把数据底座做扎实再部署pgpool-II把流量管起来然后写清楚故障切换和旧主回归的完整闭环最后把我在实战里踩过的坑和一次必要的“假死演练”都摊开讲。2. 先把底座打牢Postgres主从流复制搭建2.1 为什么是流复制而不是逻辑复制或单纯WAL归档主从方案选型时很多人会在流复制、逻辑复制和WAL归档之间犹豫。我的经验是做主备高可用流复制是唯一不需要犹豫的选项。WAL归档的恢复延迟取决于归档频率通常是分钟级而且只能做到“恢复到某个时间点”不能持续对外服务逻辑复制虽然灵活但它本质是表级同步DDL处理麻烦序列、大对象、整库一致性都容易翻车。流复制基于数据库原生的WAL传输机制备库直接接收并回放主库的WAL记录能做到秒级甚至毫秒级延迟备库可以随时提升为主库并继续提供服务。而且流复制支持同步模式和异步模式两档选择这直接决定了你的RPO最多丢多少数据和写入性能之间的平衡。一句话如果你要的是一个数据库级别的完整热备不是几个业务表的同步那就选流复制。我这边集群的规划是两台Postgres节点一台主一台备数据目录挂在本地磁盘性能压力不大。操作系统环境用主流Linux发行版即可Postgres版本建议选当前还在长期支持内的版本避免踩到老版本的已知复制缺陷。2.2 搭建主从的最小操作主库配置、备库拉取、状态验证先给主库开流复制相关的参数。编辑postgresql.conf下面这组参数是做流复制的基础我用的是Postgres 15 的写法老版本要把wal_keep_size换成wal_keep_segments# 主库 postgresql.conf listen_addresses * # 允许备库和pgpool连接 wal_level replica # 流复制必须开启logical级别为准 max_wal_senders 10 # 最多允许几个WAL发送进程至少大于备库数 wal_keep_size 1GB # 保留最近1GB的WAL兜底用 max_replication_slots 8 # 复制槽位上限 hot_standby on # 备库允许只读查询 synchronous_commit on # 如果走同步复制先开着 synchronous_standby_names # 空字符串表示当前只有异步复制逻辑说明wal_level replica是流复制的最低要求它让WAL里包含足够重建数据页的信息max_wal_senders决定了能同时有几个备库拉取WAL每台备库要占一个发送进程wal_keep_size是在没有复制槽的情况下兜底保留WAL防止备库断连太久后追不上hot_standby on让备库在恢复状态下也能处理只读查询这是后面读写分离的前提。接着在主库上创建一个复制专用账号不要用超级用户直接跑复制方便做权限收敛CREATE ROLE replica LOGIN REPLICATION PASSWORD YourStrongPassword;注意这个REPLICATION关键字它赋予了账号流复制相关的权限普通LOGIN权限是不能执行pg_basebackup的。配置完主库后重启Postgres使参数生效。备库这边不需要手动初始化数据目录直接用pg_basebackup从主库拉一份全量基础备份。我建议加-R参数它会自动生成standby.signal文件并把主库连接信息写进postgresql.auto.conf省去手动创建信号文件这一步pg_basebackup -h 192.168.1.10 -U replica -D /var/lib/pgsql/15/data \ -P -R -X stream --checkpointfast chown -R postgres:postgres /var/lib/pgsql/15/data参数说明-X stream表示在备份过程中所需的WAL通过流复制实时接收而不是拷贝归档文件--checkpointfast让主库快速执行检查点以加快备份启动-R写入复制配置相当于替你做完了“把备库指向主库”这一步。备份完成后直接启动备库。启动后看一下WAL接收进程是否活跃以及主库那边能否查到备库的连接# 在备库上执行正常应该返回 t psql -c SELECT pg_is_in_recovery(); # 在主库上执行应该看到一行 state streaming psql -c SELECT client_addr, state, sync_state FROM pg_stat_replication;这个验证是主从搭建成功与否的分水岭。pg_is_in_recovery() t说明备库正处于恢复回放状态pg_stat_replication里能看到备库IP、state streaming说明WAL正在实时传输。如果state是catchup或backup说明还在追赶或正在做基础备份等一会儿再查。如果这里查不到任何行优先检查主库的listen_addresses、pg_hba.conf里是否放行了replication连接。2.3 同步复制还是异步复制RPO与性能的取舍异步复制下主库提交即返回备库延迟通常在毫秒级但如果主库瞬间宕机最后那几笔事务可能还没传到备库丢数据的量取决于延迟大小。同步复制则要求备库先刷完WAL主库才向客户端返回提交成功RPO严格为零但写入延迟会翻倍且备库一旦故障主库写入会一直阻塞等待。我的建议是如果业务对数据丢失极其敏感并且主备之间的网络延迟在毫秒级内直接开同步复制同时打开synchronous_commit remote_apply。这个级别表示备库不仅收到WAL还已经把事务回放到可见状态比默认的on只要求备库写入WAL更严格适合强一致场景。配置方式是把上一节里那行空字符串改成synchronous_commit remote_apply synchronous_standby_names standby1这里standby1是备库的集群名称需要在备库的连接参数里用application_name指定。如果备库没起来或追不上主库会卡住所有写事务这个行为在生产环境要提前告知业务方。保险做法是把synchronous_commit设置成remote_apply还是on取决于你的可用性目标优先保可用就选异步优先保数据零丢失就选同步没有两头都占的免费午餐。3. pgpool-II 接管流量连接池、读写分离与健康检查3.1 pgpool在集群里到底管什么说了你可能不信pgpool-II本身完全不参与数据同步它只做三件事连接池、读写分离、故障转移触发。连接池解决的是应用频繁创建和销毁数据库连接的开销能把几百个前端连接收敛到几十个后端连接读写分离则是把非事务内的SELECT路由到备库把INSERT/UPDATE/DELETE和事务内的查询固定路由到主库故障转移是它最核心的价值——通过健康检查发现主库失联然后执行你配置好的脚本把备库提升为主库。理解这个分工很重要数据安全靠的是Postgres流复制不是pgpoolpgpool只负责“发现故障”和“切换流量”。所以配置pgpool时重点不在它的数据库能力而在它的检测参数、节点状态管理和脚本接口。3.2 最小可用配置从安装到两节点集群跑通pgpool-II的安装路径取决于操作系统源码编译或直接装发行版仓库里的版本都行。装完包后进入配置目录把pgpool.conf.sample-stream复制成pgpool.conf这个示例自带流复制模式的主从配置。我用的关键参数如下# pgpool.conf 关键配置 listen_addresses * port 9999 socket_dir /var/run/pgpool # 后端数据库节点列表 backend_hostname0 192.168.1.10 backend_port0 5432 backend_weight0 1 backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 192.168.1.11 backend_port1 5432 backend_weight1 1 backend_flag1 ALLOW_TO_FAILOVER # 主从模式 master_slave_mode on master_slave_sub_mode stream load_balance_mode on connection_cache on # 流复制延迟检查 sr_check_period 10 sr_check_user postgres delay_threshold 10000 # 健康检查 health_check_period 5 health_check_timeout 3 health_check_user postgres health_check_max_retries 3 health_check_retry_delay 1逻辑说明backend_hostname0/1定义了两台PG节点ALLOW_TO_FAILOVER标记该节点允许在故障时被pgpool摘除如果某节点是只做备份不允许切换的要去掉这个标记。master_slave_sub_mode stream告诉pgpool我们用的是流复制而不是逻辑复制这会影响它判断谁是主库的方式。health_check_*是pgpool的探活参数周期越短故障发现越快但太激进容易误判。配好后还要改pg_hba.conf的访问来源因为这个文件是pgpool自身的访问控制不是PG的。以我们生产环境为例只允许应用服务器网段连接pgpool# pool_hba.conf host all all 0.0.0.0/0 scram-sha-256启动pgpool后用psql -p 9999 -h 127.0.0.1 -c show pool_nodes查看节点状态正常应该看到两台节点都是up其中一台role primary另一台role standby。命令输出里node_id 0和node_id 1的状态列如果出现down先别急着调参数按第5章的排查思路走。这里补充一个容易被忽略的操作pgpool首次启动之前确保它连接数据库用的用户名在PG里有对应角色并且密码写入了pool_passwd文件否则健康检查会一直失败报认证错误。3.3 读写分离的生效逻辑与验证pgpool的读写分离策略不是简单地按SQL关键字判断它有内部规则。判断顺序大致是事务块内的语句全部去主库带FOR UPDATE的SELECT去主库SELECT开头的走负载均衡其他DML/DDL全去主库。这个策略对大多数业务是透明的但如果你的应用遇到“刚写完马上读”且备库复制延迟偏大就可能读到旧数据。延迟阈值由delay_threshold控制超过阈值的备库会被pgpool自动剔除出负载均衡列表只留主库响应读请求。验证负载均衡是否生效最直接的方法是在两台PG节点的postgresql.conf里分别开启log_statement all然后通过pgpool的9999端口反复执行简单的SELECT 1。观察主备两边的日志能看到查询被轮流分发到两台节点而主库日志里则出现所有INSERT/UPDATE语句。验证完后记得把log_statement改回去不然生产环境日志会爆炸。另外一个必须做的事是通过pgpool连接后检查show pool_nodes的输出node_id | hostname | port | status | lb_weight | role 0 | 192.168.1.10| 5432 | up | 0.5 | primary 1 | 192.168.1.11| 5432 | up | 0.5 | standbylb_weight是负载均衡权重默认两个0.5是均摊如果备机性能差一些可以调成主库0.7、备库0.3。role列是pgpool根据流复制状态推断出来的主备角色它告诉你pgpool当前把谁当作主库failover后这列会变化是观察切换是否生效的第一入口。4. 故障切换的完整闭环failover脚本、自动提升与旧主回归4.1 failover时到底发生了什么应用通过pgpool连接数据库应用根本不感知后端有两台PG。当主库宕机pgpool的健康检查连续失败会先把该节点标记为down然后执行failover_command里配置的脚本。这个脚本的职责只有一个判断挂掉的节点是不是当前主库如果是就在活着的备库上执行pg_ctl promote把它升为新主库。提升完成后pgpool会把新主库标记为primary并开始把写流量转发给新主。整个过程不涉及数据拷贝因为流复制已经把WAL实时同步到了备库promote只是把恢复模式停掉让备库开始接受读写。理解这个时序对写脚本至关重要pgpool不替你做promote它只负责触发——如果你没配脚本主库挂了之后所有节点都会被标记为down整个集群对外表现为全挂。4.2 failover_command与follow_primary_command脚本failover脚本是整套高可用的执行核心我用的脚本如下。它接收pgpool传入的若干参数判断失败节点号是否等于当前主节点号决定是否提升备库#!/bin/bash # failover_command.sh # 参数由pgpool按顺序传入 # $1: 数据库名 $2: 失败节点ID $3: 新主节点ID $4: 旧主节点ID PGPOOL_DATABASE$1 FAILED_NODE_ID$2 NEW_MASTER_ID$3 OLD_PRIMARY_ID$4 PGBIN/usr/pgsql-15/bin PGBACKEND_HOST$(grep backend_hostname${NEW_MASTER_ID} /etc/pgpool/pgpool.conf | awk {print $3}) # 只有当失败节点恰好是旧主库时才需要提升新主 if [ ${FAILED_NODE_ID} ${OLD_PRIMARY_ID} ]; then echo Failover: promote backend ${NEW_MASTER_ID} on ${PGBACKEND_HOST} /usr/bin/ssh postgres${PGBACKEND_HOST} ${PGBIN}/pg_ctl promote -D /var/lib/pgsql/15/data exit 0 fi echo Failover: failed node was not primary, no promote required. exit 0这里有几个容易踩的点。第一脚本里的主机名必须能通过SSH免密登录建议配置postgres用户的SSH密钥否则脚本执行promote时会卡在密码输入上。第二判断条件必须是失败节点ID等于旧主节点ID否则备库故障时也会误执行promote造成双主脑裂。第三pg_ctl promote需要在备库所在机器上执行所以脚本里用了SSH到备库主机的写法数据目录也要改成你自己实际的路径。除了failover脚本pgpool还需要一个follow_primary_command它负责在新主产生后把其他存活的备库重新指向新主库。否则备库还连在已经挂掉的旧主上复制永久中断。这个脚本一般是调用pg_rewind或者重新pg_basebackup具体在下一节展开。4.3 旧主回归用pg_rewind或重新basebackup恢复主库挂了被备库顶替之后原来的主库经过修复重新开机。此时不能直接把它以主库身份接入否则两台主库同时在写集群瞬间脑裂。正确的做法是把旧主降级为备库重新挂到新主下面。两种路径我的习惯是旧主数据目录还在且距离宕机时间不久优先用pg_rewind它只增量同步差异WAL分钟级搞定如果旧主数据目录已经损坏或不便操作直接删除数据目录重新pg_basebackup虽然要全量拷贝但胜在干净。pg_rewind的执行逻辑是让旧主连接到新主比对两边的时间线把旧主落后或分叉的WAL和变更回放到新主状态。执行前必须先停掉旧主的Postgres进程命令如下# 在旧主机器上执行连接新主 pg_rewind --target-pgdata/var/lib/pgsql/15/data \ --source-serverhost192.168.1.11 port5432 userreplica dbnamepostgres \ -P # 生成standby.signal标记为备库 touch /var/lib/pgsql/15/data/standby.signal # 配置连接新主的信息 cat /var/lib/pgsql/15/data/postgresql.auto.conf EOF primary_conninfo host192.168.1.11 port5432 userreplica application_namestandby1 EOF # 启动旧主Postgres它会自动作为新主的最新备库开始追数据 systemctl start postgresql-15pg_rewind有一个硬性前提旧主上要能连接到新主且新主还保留着旧主分叉之前的WAL。如果新主已经运行很久旧主落后太多新主里的WAL可能早就被清理了pg_rewind会失败。这时候别硬刚老实删目录重新pg_basebackup更稳。回归完成后在pgpool里执行pcp_attach_node把旧主重新加入集群pcp_attach_node -h 127.0.0.1 -U pgpool -p 9898 -n 0 -w这里的-n 0指定节点ID也就是旧主在backend_hostname0里的编号。附加完成后用show pool_nodes确认状态为up、角色为standby。如果这一步不执行pgpool会一直把该节点当作故障节点即使数据库本身已经恢复了。4.4 在线恢复不用手动跑basebackup的偷懒路径如果不想手动经历“停库→rewind→启动→attach”这一套pgpool提供了半自动化的在线恢复机制。它通过pcp_recovery_node触发一次恢复流程按照你配置的recovery_1st_stage_command脚本去执行基础备份或增量回放。我的做法是写一个恢复脚本内容就是把上一节的pg_rewind流程包进去pgpool负责控制节点状态切换我只需要敲一条命令pcp_recovery_node -h 127.0.0.1 -U pgpool -p 9898 -n 0 -w这个命令会让pgpool自动停止旧主节点的数据库、调用恢复脚本、重新拉取数据、标记节点为up。整个过程不用SSH到旧主机器手动操作对日常运维来说能显著降低误操作概率。不过在线恢复脚本的调试比较折磨人建议先在测试环境完整跑通一次故障演练再把脚本投入生产不然恢复失败时pgpool显示的报错信息非常有限排查全靠看日志。5. 主从pgpool集群的常见坑与排查思路5.1 备库一直不追数据pg_stat_replication 里查不到行现象备库启动后主库的pg_stat_replication视图里空空如也备库日志反复提示无法连接主库或认证失败。原因九成是pg_hba.conf没有放行replication连接。很多人以为放行了host all all就行但实际上流复制连接使用专门的数据库类型replication必须单独加一行。另一种可能是wal_level改完后没重启主库参数还在旧值上。解决在主库的pg_hba.conf里确认存在以下行然后 reloadhost replication replica 192.168.1.0/24 scram-sha-256接着在主库执行SELECT * FROM pg_stat_replication;若依然为空用ss -ltnp | grep 5432确认主库监听地址。备库这边tail -f日志能看到具体的拒绝原因提示按提示排查比瞎猜快得多。5.2 同步复制下主库写入卡死业务直接熔断现象主备正常但某一天主库写入延迟暴涨事务提交耗时几十秒应用端超时报错。原因同步复制模式下备库任一时刻掉线或网络中断主库都会等待备库确认默认行为是无限期等待直到备库恢复。这是最典型的“高可用反而拖垮可用性”的翻车现场。解决如果业务无法接受写入完全停顿把synchronous_commit改回on或local让主库在备库失联时自动退化为本地提交如果必须零丢失那就给同步复制配一个“法定人数”的概念用synchronous_standby_names FIRST 1 (standby1, standby2)实现多备仲裁只要有一个备库确认就不阻塞。这次教训让我把生产环境的synchronous_commit调成了on比remote_apply更适合可用性优先的系统。5.3 pgpool频繁自动切换健康检查误判现象没有发生任何故障但pgpool日志里反复出现节点down又up的记录严重时发生一次自动failover备库被莫名提升为主库。原因健康检查参数配置太激进。health_check_period 1加health_check_timeout 1意味着每秒探活一次、1秒无响应即判定故障数据库在checkpoint或大查询时瞬间无法响应就会触发误判。更隐蔽的是pgpool所在机器到数据库的网络偶尔抖动也会导致探活失败。解决适当放宽检查周期和超时时间并开启重试。我个人线上配置是health_check_period 10、health_check_timeout 5、health_check_max_retries 3、health_check_retry_delay 2。这样单次探活失败会连续重试3次避免单次抖动切换。如果误判仍然频繁检查pgpool节点和PG节点之间是否有防火墙丢包必要时在系统层做网络质量监控不要指望靠缩短检查周期来“更敏感地发现故障”那只会更频繁地误切。5.4 WAL堆积导致磁盘打满备库追不上现象主库的pg_wal目录占用越来越大磁盘告警查pg_stat_replication发现备库replay_lsn与主库sent_lsn差距持续拉大。原因备库断连时间长主库不断产生新WAL而wal_keep_size设置的1GB早被覆盖此时备库重连后无法从断点继续拉取需要重新做全量备库。这个过程会短暂加重WAL占用形成恶性循环。如果开了复制槽max_slot_wal_keep_size设置过小也会让主库提前清理掉备库还需要的WAL。解决把wal_keep_size调大同时开启归档archive_mode onarchive_command让WAL有第二份落盘去处。关键操作是定期监控备库的复制延迟可以用下面这条查询SELECT client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag FROM pg_stat_replication;如果replay_lag持续超过阈值优先检查备库磁盘IO和CPU是否扛不住回放压力。解决掉线后备库能重新跟上时再考虑是否需要清理复制槽注意不要在生产环境随手pg_drop_replication_slot槽里可能还有备库需要的未接收WAL。5.5 重启顺序出错集群状态雪崩现象机房断电或系统维护后我们按“先把所有机器启动”的直觉依次开机结果pgpool显示所有节点down数据库连不上业务彻底瘫痪。原因这个集群的启动顺序有硬性要求。pgpool启动时会立刻对后端节点做健康检查并尝试获取节点状态如果PG还没起来或备库还在恢复中pgpool会把节点标记为down。更糟的是如果备库先于主库启动备库不知道主库在哪只能在恢复状态空转pgpool此时探测不到主库可能触发一次不必要的failover把备库当成新主等原主库再起来时形成双主冲突。解决固定一个启动顺序并写进运维手册先启动原主库确认它能正常接受连接再启动原备库确认pg_is_in_recovery()返回t且复制状态是streaming最后启动pgpool启动后用show pool_nodes复核节点角色。如果顺序错了导致pgpool把节点标down不要慌登录pgpool用pcp_attach_node逐个重新挂载即可但前提是底下PG角色确实正确。6. 上线前做一次“假死演练”验证高可用真的可用配置全部完成、脚本全部就位不代表高可用真的可用。我见过太多“脚本写了但语法错误”“SSH没配免密”“手工执行能通过但pgpool触发时静默失败”的情况。上线前必须做一次完整的故障演练最好的方式就是挑一个业务低峰期在主库机器上执行pg_ctl stop -m fast模拟主库宕机然后按以下顺序观察# 第一步在pgpool节点持续观察节点状态变化 watch -n 1 psql -h 127.0.0.1 -p 9999 -c show pool_nodes # 第二步在主库宕机后检查备库日志是否出现promote记录 tail -f /var/log/postgresql/postgresql-15.log | grep -i promote # 第三步验证新主可写造一张测试表试着插入数据 psql -h 127.0.0.1 -p 9999 -c CREATE TABLE failover_test(id int); INSERT INTO failover_test VALUES (1);演练的预期结果有两个层次。最低要求pgpool在几十秒内把备库提升为新主应用连接pgpool执行写操作成功业务无感知或仅感知到一次连接重连。更高要求主库恢复后通过pg_rewind把它重新纳入集群成为新备库整个集群恢复初始的两节点主备形态show pool_nodes显示两个节点都是up且角色分工正确。如果你用的failover_command里包含SSH远程执行演练前手动跑一遍脚本里的SSH命令确认免密是否生效。如果脚本有任何一行用了不存在的路径或未定义的变量bash默认不会报错而是带着空值执行这最容易出“看起来execute了但什么都没发生”的诡异问题属于高可用领域的玄学现场。我建议在脚本开头加一行set -x把它执行过的每一条命令都打印到日志文件里排查时能看到完整的执行轨迹。演练最后一步是验证数据一致性我习惯在切换前后各执行一次主库的SELECT count(*)来对比同时查看pg_stat_replication里新旧主的时间线变化。如果你在演练中发现failover后应用连接pgpool时报 “no PostgreSQL user exists” 或 “sorry, too many clients”大概率是pool_passwd文件没更新或连接池最大连接数到了上限调整后重跑演练。经历过一次真实故障切换被脚本坑到的事故后我现在的习惯是每次修改pgpool.conf或failover脚本都会在测试环境模拟一次主库宕机确认切换成功后才部署到生产。这个流程需要花费的时间不到半小时但能省掉事后数小时的紧急恢复和业务道歉。希望这篇笔记能帮你把主从流复制和pgpool这套方案的每个环节都钉死真正把高可用从口号变成可验证的日常。本文还有配套的精品资源点击获取