PostgreSQL主从流复制与pgpool高可用实战：配置、切换与避坑指南

简介PostgreSQL主从流复制pgpool高可用方案是一份面向数据库运维与架构设计人员的技术文档聚焦主从流复制与pgpool-II结合使用解决生产环境中数据库故障切换、读写分离和负载均衡等常见问题。资源仅包含1个docx文件压缩包大小773KB文档章节清晰涵盖方案综述、同步与异步复制模式对比、WAL日志同步原理、pgpool-II连接池与复制机制、客户环境准备、主备库配置步骤、启动与故障模拟测试等内容。目前已有467人学习/下载。读者可据此掌握从wal_level参数开启、备库初始化到pgpool-II监控与故障切换策略的完整部署流程同时理解walsender与walreceiver进程如何协作、备库如何支持只读请求以及同步复制与异步复制在一致性和性能上的权衡。文档还整理了关闭防火墙、配置hosts、主备时钟同步等环境细节并体现出流复制仅需基于WAL日志、无需额外软件即可搭建主备库的思路适合作为构建高可用PostgreSQL集群的实用参考。1. 生产环境里“主从流复制 pgpool高可用”到底解决什么问题你花 20 分钟把 PostgreSQL 主备搭起来看到pg_stat_replication里显示streaming觉得高可用已经搞定了——这种想法我在生产环境里见过太多次最后都逃不过“切换时翻车”的结局。Postgres主从流复制 pgpool高可用方案并不是在讲“怎么配复制”而是回答一个更扎心的问题当主库物理机宕机、网络抖动、磁盘故障时你的业务连接能不能在 10 秒内被导向一个数据不丢的从库而不是等着 DBA 半夜起来手工 promote。我见过最典型的一个场景某业务库主库磁盘写满服务挂了。团队按文档跑pg_ctl promote提升从库业务却仍然连不上旧地址——因为他们没有做虚拟 IP 漂移应用连接串指着的 IP 已经不存在了。Postgres主从流复制解决的是“数据在一个地方有多份实时副本”pgpool 解决的是“当主库没了客户端流量自动走到那个还活着的副本上”。这两者缺一不可只有复制没有流量切换高可用是假的只有 pgpool 没有可靠的流复制切换过去数据是缺的照样不可用。这篇文章面向的是那些已经决定用 PostgreSQL 承载核心业务、但还在犹豫要不要配对 pgpool 的团队以及那些配好了复制但从来没演练过切换的人——我会把配置参数、切换脚本、踩坑记录按可复现的方式讲清楚。2. 先让数据实时同步Postgres主从流复制部署与关键参数2.1 流复制为什么是“高可用地基”以及它和异步、同步的边界PostgreSQL 的主从流复制Streaming Replication本质上是主库把预写日志WAL以数据流的方式持续发送给从库从库收到后再做恢复redo从而保持数据一致。和 9.x 时代基于文件级 WAL 归档的 log shipping 相比流复制的优势在于实时性——主库每生成一段 WAL立即通过网络推给从库而不是等文件归档完再让从库去拉。这让从库的延迟通常在秒级甚至毫秒级是 pgpool 能实现自动切换的前提。但“实时”分两种异步复制和同步复制。异步复制下主库提交事务不等待从库确认性能损耗小但主库宕机时从库可能少收最后一小段 WAL数据会有损失同步复制下主库要等至少一个从库确认 WAL 已落盘才返回成功能保证不丢数据但会增大提交延迟从库挂了还会拖垮主库写入。生产环境最常见的做法是把大部分场景跑在异步模式上同时用synchronous_commit remote_apply或on加synchronous_standby_names只对关键事务开同步。pgpool 高可用方案本身并不强制要求同步复制但你要心里有数你的方案承诺的是“高可用”还是“高可用且零丢失”。这也决定了你后面怎么设置 pgpool 的延迟阈值和切换条件。2.2 最小可跑通的主从流复制从建用户到 pg_basebackup 的一整条命令我一般会在干净的 PostgreSQL 16 环境上做这件事15/14 也都适用9.6 之后的 wal_level 参数写法略有差异但思路一致。假设主库 IP 是 192.168.1.10从库是 192.168.1.11PG 版本一致这条路是最短路径。主库上先建一个有复制权限的最小账号-- 在主库上执行创建复制专用账号不赋予业务权限 CREATE ROLE replica LOGIN REPLICATION PASSWORD your_secure_password;然后改主库的postgresql.conf这几个参数不调对流复制根本起不来# 主库 postgresql.conf wal_level replica max_wal_senders 8 max_replication_slots 4 wal_keep_size 1GB # 40MB 之前的写法对应 wal_keep_segments16 用 wal_keep_size hot_standby onwal_level replica是最低要求logical可以做逻辑复制但流复制场景下用它没必要max_wal_senders决定最多允许几个从库同时连上来拉 WAL我习惯预留一两个给备份工具wal_keep_size是给那些还没配置 replication slot 的从库留后路——如果从库断连过久主库的 WAL 已经被回收从库就只能重新pg_basebackup这个值设太大占磁盘设太小又容易断档。主库的pg_hba.conf加一行# 允许从库 IP 以复制身份连接 host replication replica 192.168.1.11/32 md5接下来在从库上拉全量数据这是最常用的方式比先拷贝数据目录再用pg_rewind干净# 从库上执行用 pg_basebackup 直接做一份基础备份 pg_basebackup -h 192.168.1.10 -U replica -D /var/lib/postgresql/16/main -X stream -P -R末尾的-R会自动生成standby.signal文件并写入primary_conninfo这一步省掉了很多手写配置的步骤。接着启动从库# 从库上执行启动从库服务 pg_ctl start -D /var/lib/postgresql/16/main systemctl status postgresql # 或者直接看日志确认处于恢复状态验证是否进入流复制状态-- 在主库查询复制状态 SELECT client_addr, state, sync_state, write_lag, flush_lag, replay_lag FROM pg_stat_replication;如果看到state streaming那说明这台从库已经跟着主库跑了。注意备用库上执行pg_is_in_recovery()应该返回true——我见过有人把从库当独立库给启用起来然后两边同时写数据搞得主从数据分叉这是最基础的误操作。2.3 从库断档重连replication slot 才是防断档的后悔药上面那套配置里有个隐患如果从库停机超过一定时间主库的 WAL 文件被wal_keep_size之外的空间回收掉从库重连时找不到起始点只能重新做全量恢复。解决这个问题最可靠的办法是主库上先建一个 replication slot从库通过 slot 消费 WAL主库就永远不会回收从库还没拿走的 WAL-- 主库执行创建物理复制 slot SELECT * FROM pg_create_physical_replication_slot(standby_1);然后在从库的postgresql.conf或自动生成的primary_conninfo里带上 slot 名# 从库配置primary_conninfo 中追加 slot 参数 primary_conninfo host192.168.1.10 port5432 userreplica passwordxxx application_namestandby_1 primary_slot_name standby_1加了 slot 之后主库就要为从库保留全部未消费的 WAL。如果从库长时间离线又没被移除主库的磁盘会被 WAL 涨满——所以 slot 用起来之后必须有监控我的习惯是每 10 分钟查一次pg_replication_slots里每个 slot 的restart_lsn和当前 WAL 写入位置的距离超过阈值就直接告警。这里还有个容易翻车的地方hot_standby_feedback on可以降低从库查询与主库 VACUUM 的冲突但它也可能导致主库上该清理的元组清理不掉表膨胀。生产上我通常是关掉它、靠应用侧控制长事务或者在从库上设置合理max_standby_streaming_delay而不是盲目开。另一个常被忽略的参数是synchronous_standby_names。如果你想做“同步优先”的高可用在主库上配synchronous_commit on synchronous_standby_names standby_1这会让主库的事务提交等standby_1这一台从库确认。注意如果这台从库挂了主库写入会直接挂起业务全部阻塞。所以生产里更稳的写法是给一个候选列表并接受部分事务异步。pgpool 在这种配置下工作会更复杂因为切换时synchronous_standby_names可能还指向已经死掉的旧库名需要脚本去更新主库参数这一步不要忘。3. 引入 pgpool-II连接池、读写分离和健康检查怎么配3.1 pgpool-II 在高可用里到底扮演什么角色你不是多装了一个中间件而是多了一个调度层很多人第一次接触 pgpool 以为是反向代理或负载均衡器但它解决的问题比这个更精细。一张表说清楚这几个角色你就知道为什么有了流复制仍然需要它pgpool 角色解决什么问题涉及的核心特性连接池应用每建一条连接都要 fork 一个新进程pgpool 复用后端连接降低资源消耗max_pool、connection_life_time读写分离主库处理写从库处理读分散负载load_balance_mode、backend_weight负载均衡读请求按权重分给多个从库load_balance_mode on自动故障切换主库不可达时自动提升一台从库、把流量切过去failover_command、health_check_period对于一个小型生产环境单主单从pgpool 可以只做连接池和故障切换如果从库扩展到了两三台再考虑把读流量分出去。连接池参数num_init_children决定 pgpool 对应用暴露多少个前端连接槽位它乘上max_pool不能超过后端 PostgreSQL 的max_connections否则后端连接数被打满应用反而报 too many clients——这是我在很多项目里第一眼就会核对的计算。3.2 从零开始写一份可用的 pgpool.conf那些不调就踩坑的参数先讲部署方式我是建议 pgpool 独立部署在专门节点上至少部署两台并启用自带 watchdog而不是跟 PostgreSQL 挤在同一台机器。因为如果主库挂了它的操作系统也可能已经异常这时候你还指望同一台机器上的 pgpool 去执行切换不现实。样例假定 pgpool 节点 IP 是 192.168.1.20后端是上面配置好的主192.168.1.10、从192.168.1.11两台 PG。# pgpool.conf 核心配置pgpool-II 4.4 之后建议用 pcppool.conf 管理 listen_addresses * port 9999 socket_dir /var/run/pgpool pcp_listen_addresses * pcp_port 9898 pcp_socket_dir /var/run/pgpool # 后端数据库节点列表0 是主1 是从 backend_hostname0 192.168.1.10 backend_port0 5432 backend_weight0 1 backend_data_directory0 /var/lib/postgresql/16/main backend_flag0 ALWAYS_PRIMARY backend_hostname1 192.168.1.11 backend_port1 5432 backend_weight1 1 backend_data_directory1 /var/lib/postgresql/16/main backend_flag1 ALLOW_TO_FAILOVER # 连接池 max_pool 4 num_init_children 40 # 前端最多 40 个应用连接后端最多 160 条连接 # 健康检查 health_check_period 5 health_check_timeout 3 health_check_user pgpoolcheck health_check_password checkpass # 故障切换 failover_command /etc/pgpool/failover.sh %H %h %p %d %M %m %P %R follow_primary_command /etc/pgpool/follow_primary.sh %h %p %H %M %P %R # 自动恢复 recovery_user postgres recovery_password recovery_1st_stage_command pgpool_recovery # 读写分离 load_balance_mode on read_only_function_list pg_is_in_recovery(),version()关键参数里backend_flag0 ALWAYS_PRIMARY的作用是告诉 pgpool 哪台是主库正常情况下所有写请求都只会发到带这个 flag 的节点。注意这里有个坑你之前在流复制里配置过synchronous_standby_names那么故障切换时后台脚本必须同步更新主库的这个参数否则新的主库可能一直等一个不存在的同步从库确认提交直接卡死。follow_primary_command是主库切换后用来把其余从库重新指向新主库的钩子如果你有后续新增从库这个脚本必须写了之后才靠谱。3.3 读写分离逻辑怎么区分读和写哪些请求会被错误路由pgpool 判断一条 SQL 是否需要路由到主库靠的是它在内部做语法解析——SELECT默认走读库INSERT/UPDATE/DELETE走主库。但这也暴露出一堆边界SELECT nextval(sequence_name)、SELECT pg_last_commit_timestamp()、SELECT now()这类语句都包含“副作用”一旦被路由到从库业务上就会看到主从序列不一致。解决办法就是在read_only_function_list里把这些函数全部列出来让 pgpool 把涉及它们的语句强制送到主库类似这样read_only_function_list pg_is_in_recovery(),version(),now(),current_timestamp,nextval,setval更麻烦的是事务内的读——比如应用先写后读如果不加控制读到的是从库的旧数据。pgpool 的默认策略是在事务中执行过写操作后该事务后续的读也只会走主库但前提是你没有在事务里显式设置只读标识。还有一种常见翻车点如果应用用了WITH公共表表达式或者调用存储过程pgpool 并不总能准确判断里面是否包含写操作保守的做法是在白名单之外全走主库。我的建议是不要在读写分离模式下允许所有裸 SQL 自由行驶先让应用侧把可选读的报表请求和强一致的业务读写分类再用black_query_pattern_list把高风险操作钉死到主库。负载均衡的权重因子backend_weight不是越高越好。在我的项目里从库通常还要承载报表和异步计算负载能力跟主库并不完全对等——权重设成主:从 1:1 在流量大的时候反而会让从库 CPU 最先被打满拖垮恢复进程。我会按从库的实际规格来调权重比如主库 8C16G、从库 4C8G那就设 2:1。这些参数是热生效的但关闭连接后新连接才会完全应用新权重。4. 故障切换的自动化failover 脚本、VIP 漂移和防脑裂4.1 failover_command 是如何被调用的传给脚本的那 9 个参数分别是什么pgpool 的故障切换机制本质上是一个状态机健康检查连续失败超过阈值pgpool 就把这个节点标记为 down然后执行failover_command指定的外部脚本。脚本不是由 pgpool 进程直接执行而是 fork 一个子进程去跑可以写任意 shell 脚本。它传给脚本的一串参数很多人在网上抄了一个版本不知道怎么调看表占位符含义典型值%H新主库的主机名192.168.1.11%h故障节点的主机名192.168.1.10%p故障节点的端口5432%d故障节点的数据目录/var/lib/postgresql/16/main%M旧主库节点 ID0%m新主库节点 ID1%P旧主库的数据库名postgres%R新主库的数据目录/var/lib/postgresql/16/main最关键的判断逻辑就藏在%M和%m里当%M等于%m时说明故障节点本来就是主库你需要执行 promote如果这两个值不同说明这次只是某个从库挂了主库没变那么脚本只需要把这个从库从 pgpool 的节点列表里暂时移出即可不要去动主库。很多人写脚本偷懒只看 %h 就 promote结果从库短暂抖动导致主库被误切——生产事故就是这么来的。4.2 一个直接用得上的 failover.sh 脚本雏形脚本放在 pgpool 节点上failover_command /etc/pgpool/failover.sh %H %h %p %d %M %m %P %R对应的是脚本位置和占位符顺序。我这里写一个我常用的最小版本然后我们拆开讲里面每一步为什么要这么干#!/bin/bash # failover.shpgpool 故障切换执行脚本 # 传入参数说明$1%H 新主库主机名, $2%h 故障节点主机名, $3%p 故障端口 # $4%d 故障数据目录, $5%M 旧主节点id, $6%m 新主节点id NEW_MASTER_HOST$1 OLD_NODE_HOST$2 OLD_NODE_ID$5 NEW_NODE_ID$6 # 只有当故障节点是主库时才执行提升否则可能是从库抖动 if [ $OLD_NODE_ID $NEW_NODE_ID ]; then # 通知业务方记录日志 echo $(date) promote $NEW_MASTER_HOST /var/log/pgpool/failover.log # 在新主库上执行提升触发 standby.signal 文件所在节点成为可写主库 ssh postgres$NEW_MASTER_HOST /usr/lib/postgresql/16/bin/pg_ctl promote -D /var/lib/postgresql/16/main # 把虚拟IP绑定到新的主库节点让应用无感知漂移 ssh postgres$NEW_MASTER_HOST ip addr add 192.168.1.100/24 dev eth0 ssh postgres$NEW_MASTER_HOST arping -c 3 -A 192.168.1.100 -I eth0 # 把旧主库上的VIP释放掉如果还能登录避免IP冲突 if ping -c 1 -W 1 $OLD_NODE_HOST /dev/null 21; then ssh postgres$OLD_NODE_HOST ip addr del 192.168.1.100/24 dev eth0 || true fi fi exit 0这里面每一步的逻辑说明先判断%M和%m是否相等是为了排除“只挂从库”的普通场景执行pg_ctl promote是把新主库从只读恢复状态变成可写状态这一步必须在 VIP 接管之前完成否则应用流量已经切过去但库还是只读的等于业务不可用把 VIP 加到新主库是为了让应用原来的连接串不需要改动——这是整个方案里唯一让客户端无感知的关键手段最后尝试在旧主库上删掉 VIP避免两台机器持有同一个 IP 导致网络冲突。如果你不用 VIP 而用 DNS 做切换那就要接受 DNS 缓存 TTL 的延迟生产上我不推荐。VIP 方案在云环境里可能不能直接用内网普通 IP 漂移而需要用云厂商的私有 IP 配置——这不是脚本问题是网络模型问题上云前先确认虚拟 IP 是否被允许手动绑定。4.3 原主库恢复后如何归队pg_rewind 才是防止数据分叉的那颗后悔药故障切换完成后旧主库如果只是断电重启或者网络恢复它本身的 WAL 会比新主库落后——如果直接把它作为从库挂到新主库它上面那些在故障期间自己产生的 WAL 会跟新主库冲突出现数据分叉。正确处理顺序是这样的旧主库先不要启动用pg_rewind把它的数据目录同步到新主库的时间线再把它变成从库重新加入集群。我一般会在 pgpool 的follow_primary_command里放一个脚本让它自动处理这个归队动作大致的核心命令# 在待重建的旧主库节点上执行必须先停库 systemctl stop postgresql # 以新主库192.168.1.11为源做时间线回退恢复到故障前的一致点 /usr/lib/postgresql/16/bin/pg_rewind \ --target-pgdata/var/lib/postgresql/16/main \ --source-serverhost192.168.1.11 port5432 userreplica dbnamepostgres \ --write-recovery-conf # 启动变成从库的原主库 systemctl start postgresql参数解释--target-pgdata指向本地数据目录--source-server连接新主库获取时间线信息-P可以打印进度--write-recovery-conf会自动写入主库连接信息省得你手写primary_conninfo。重接之后记得在主库上验证新从库的pg_stat_replication状态是 streaming 才算真正归队。这里有个防火墙级的坑pg_rewind 需要从新主库读取 WAL如果新主库上没有开max_wal_senders或者 replication 用户的权限不对它会报could not connect to server而不是告诉你权限不足。所以在执行之前先在从库上用psql用 replica 账号连一次新主库确认能查pg_stat_replication再跑 rewind会省掉很多排查时间。5. 避坑从“套上 pgpool 就算高可用”到真正能扛故障的五个实战坑5.1 现象主库故障后pgpool 日志显示切换成功但业务应用仍然连不上库报的是旧 IP 的 connection refused原因pgpool 做了节点状态切换但虚拟 IP 没有成功漂移到新主库。常见原因是在 pgpool 节点上执行ip addr add需要 root 权限而调用 failover 脚本的用户不是 root或者新主库绑定的网卡名跟脚本里写死的eth0不符。解决先把 failover 脚本的ssh和ip命令改为全路径并为 postgres 用户配置sudo -n ip addr add的免密权限。在测试切换时执行完脚本后必须登录新主库执行ip addr查看 VIP 是否真的绑上去了不能只看脚本的 exit code。5.2 现象主库一直正常运行但某天从库的pg_stat_replication停在catchup状态从不进入streaming数据延迟越来越大原因从库断连过久主库上 WAL 已被回收slot 里也没有及时推动 restart_lsn。更隐蔽的原因是max_wal_senders被其他备份任务占满从库没有空闲 WAL 发送进程可以连。解决检查主库pg_replication_slots视图的restart_lsn是否长时间不变是则视为 slot 失联先删掉这个 slot 再重建排查有没有周期性pg_basebackup占据了 wal sender。在日常监控里对pg_stat_replication的state和replay_lag做阈值告警比只检查服务存活要有用得多——等到业务报数据不对才发现延迟已经来不及了。5.3 现象pgpool 健康检查配置了 5 秒一次从库因为维护需要手工重启结果每次重启都触发了 failover主库被误切原因健康检查的判定太灵敏。从库重启时会有几十秒处在无响应状态但 pgpool 配置的health_check_timeout只有 3 秒连续两次失败就把从库当成故障节点由于我第一版 failover 脚本里没有区分 %M 和 %m 是否相等误把从库宕机当成主库故障执行了 promote。解决把health_check_timeout调大到可覆盖 PG 正常重启窗口20 秒以上health_check_max_retries调成 3 次并在 failover 脚本里严格按照%M %m 才 promote做判断同时把从库的恢复attach交给 pgpool 的自动恢复机制而不是手动操作。这类误切换在二次开发和测试环境里不致命在生产环境里却会引入真实的数据分叉风险。5.4 现象主库故障切换成功后新主库各项功能正常但原主库被拉回集群做从库之后业务数据出现主从不一致的错乱原因没有用 pg_rewind 直接把旧主库的数据目录回退到新主库时间线而是直接把它挂上去。旧主库上宕机前未落盘的 WAL 与新主库已经推进的数据相互覆盖逻辑上出现“两条腿走路”。解决彻底统一“旧主库恢复流程”——先停库、再 pg_rewind、再primary_conninfo指到新主库。而且我建议每个季度做一次完整的主备切换演练每次都执行这套流程才不会在真正故障时因为生疏而漏掉 rewind 步骤。5.5 现象pgpool 启动了应用查询报错 “cannot execute INSERT in a read-only transaction”但主库明明活着原因pgpool 把新主库识别成从库或者新主库数据目录里残留了standby.signal文件启动后一直处于只读恢复状态。很多人在手工提升从库时直接touch一个信号文件或忘记移除standby.signal但提升成功后也不删导致后续重启又回到从库模式。解决在执行pg_ctl promote成功后立刻检查新主库数据目录下的standby.signal是否存在并删除它pgpool 切换完成后也去pcp_node_info确认新主库的 status 是primary而不是standby。用SELECT pg_is_in_recovery();查一下如果返回f一切正常如果返回t就是这个文件的锅。6. 验证这套方案是否真能扛事故障注入的六个动作与一颗平常心写完切换脚本最忌讳的就是“贴到配置里然后就去睡觉”。我每次上线前三周都会做一轮故障注入演练按下面这张表逐项打勾每一项都对应不同的真实故障类型验证动作模拟的故障预期结果kill -9主库 postmaster 进程数据库进程崩溃pgpool 5 秒后标记 downfailover 提升从库VIP 漂移业务连接自动切换拔掉主库网线网络分区从库与 pgpool 保持连接VIP 必须仍然可达从库不提升为双主从库磁盘写满从库损坏从库被标记 down主库不受影响业务读写不中断主库磁盘写满存储故障主库被迫宕机另一台从库提升但要注意主库磁盘满时 WAL 可能未完全归档从库手工重启维护操作pgpool 只把该从库摘掉不切换主库恢复后自动重连并追增量直接停掉 pgpool 进程中间件故障页面连接失败但数据库本身无损重启 pgpool 后重新接管后端节点每一次演练我都会同时在看三个地方pgpool 日志/var/log/pgpool/pgpool.log、PostgreSQL 日志postgresql-*.log、以及应用侧连接是否出现 “connection reset” 或长时间 hang。真正的高可用不是“主库挂了三十分钟业务还能跑”而是“切换那几秒钟里应用重连后能立刻拿到新的连接并且没有报错”。如果你发现切换后应用池子里还握着旧连接不放那问题不在 pgpool而在应用连接池没有配置连接超时和重试逻辑。用第一人称讲一个教训我早期做过一次切换演练主库 kill 掉之后pgpool 提升从库成功、VIP 也漂移了。所有人都觉得万事大吉。结果我发现应用连的还是旧库因为应用连接池的最小空闲连接是 50这些连接一直活着且指向旧 VIP 绑定的地址而 VIP 已经迁移到新主库上了——好的IP 层面没问题。但应用配置里的负载均衡器缓存了旧的节点地址列表它不会自动感知 VIP 变更。那一次“切换成功但业务失败”的教训让我从此把应用层的重连逻辑和数据库切换一起放进演练范围。后来我把所有应用连接串改成一个不带 IP 的域名由 DNS 指向 VIP这样切换时只需要更新 DNS 记录数据库这边就不用指望应用主动配合了。希望这些配置和踩坑记录能让你少走几趟弯路。把流复制搭到 streaming 只是开始把 failover 脚本练到按下开关就能稳定切换那才叫 Postgres主从流复制 pgpool高可用方案真正落地了。本文还有配套的精品资源点击获取