PostgreSQL主从流复制+pgpool高可用：原理配置与故障切换解析

简介文档面向数据库运维与架构设计人员整理了一套基于 PostgreSQL 流复制与 pgpool-II 的高可用部署方案。全文围绕 WAL 日志实时传输这一核心先对比同步复制与异步复制的数据一致性及性能取舍再说明备库 walreceiver、startup 进程如何完成日志接收与重放并解释 pgpool-II 在连接池、自动故障切换、负载均衡与读写分离中的实际作用。方案部分给出了完整落地步骤从软件版本确认、pg_rman 备份工具准备、postgres 用户与权限设置到主库 postgresql.conf 参数调整、备库初始化、pgpool-II 配置及启动联调同时覆盖防火墙、SELinux、时钟同步等系统环境项并包含故障切换模拟测试方法。压缩包内含单份 docx 文档大小约 773KB目前已有 469 人学习。文档兼具原理讲解与实操指引适合需要搭建 Postgres 高可用集群或排查主备同步问题的运维人员参考。1. 一句话讲清 Postgres 主从流复制 pgpool数据不丢切换不慌很多团队把 Postgres 跑进生产后第一个睡不着觉的问题就是“数据库挂了怎么办”。Postgres 主从流复制解决了数据冗余和读能力扩展但应用不会自动把流量切到新主机pgpool 在高可用方案里补上的正是连接管理、读写分离和故障切换这三块拼图。这个组合不是装上就完事的组件而是需要把参数、脚本、切换策略都设计清楚的一套高可用体系。它适合已经有主从基线、想做自动 failover 和统一数据库入口的团队也适合那些被“半夜主库宕机、应用连不上”折腾过的运维同学。2. 主从流复制搭建从 WAL 原理到备库落地的完整路径2.1 为什么选物理流复制而不是逻辑复制或触发器双写Postgres 主从方案里“流复制”三个字指的是物理复制主库把 WAL 日志实时传给备库备库拿到日志后按原样回放。它的好处是数据一致性由底层存储引擎保证主库上执行的所有变更备库都会按相同顺序重放不会出现某张表漏同步、某条索引对不上的情况。常见替代方案各有硬伤。逻辑复制PostgreSQL 10 起内置适合异构数据库同步、跨版本迁移、按表订阅但它依赖逻辑解析和重放遇到大事务、DDL、序列同步会有不少边界问题做主备高可用并不划算。触发器双写更是玩具级方案应用层向两张表各写一份任何一端失败数据就分叉日常维护比收益大得多。所以生产环境选物理流复制本质上是选“底层保证一致性”而不是“应用层凑一致性”。代价也很明确备库必须与主库版本一致、操作系统一致、架构一致但这些约束在主备场景里本来就应该满足。2.2 从主库到备库的最小配置步骤以 PostgreSQL 14/15、pgpool-II 4.x 为基准说明。先改主库postgresql.conf# postgresql.conf — 主库 wal_level replica max_wal_senders 10 max_replication_slots 10 wal_keep_size 1024wal_level replica是流复制的最低要求logical只在需要逻辑解码时才用。max_wal_senders决定最多能挂几个备库每个流复制连接占一个 sender默认 10 够绝大多数场景。wal_keep_size单位是 MB表示备库断线时主库额外保留多少 WAL属于兜底手段如果开了复制槽真正的保障是槽本身这个参数只是给没开槽的备库一个追赶窗口。接着建复制用户并在pg_hba.conf放行CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 换成你自己的强密码;# pg_hba.conf host replication repl_user 192.168.1.0/24 md5注意这里的地址段要按实际网络环境收紧host replication是专门给流复制用的连接类型普通应用连接不会走到这条规则。配完主库记得pg_ctl reload。备库不要手动初始化数据目录直接用pg_basebackup从主库拉一份全量基线pg_basebackup -h 192.168.1.10 -p 5432 -U repl_user \ -D /data/pgdata -Fp -Xs -P -R-Xs表示在备份过程中流式传输 WAL避免备份期间产生的日志堆积在主库-R会自动生成standby.signal文件和primary_conninfo配置这一步省掉了手动写恢复配置的功夫。PostgreSQL 10 之前用的是recovery.conf10 之后改成了standby.signal文件加postgresql.conf里的primary_conninfo。备库启动后primary_conninfo里建议显式写application_name# postgresql.conf — 备库pg_basebackup -R 会自动生成这里手动补上 application_name primary_conninfo host192.168.1.10 port5432 userrepl_user application_namestandby1application_name在配置同步复制时是主库识别备库的身份标识后面 2.4 会用到。2.3 怎么确认复制真的在跑而不是“假装在跑”主库上执行-- 主库查看流复制连接状态 SELECT client_addr, state, sync_state, pg_current_wal_lsn() AS current_lsn FROM pg_stat_replication;state是streaming说明连接活着sync_state是async或sync。备库上执行-- 备库确认当前处于恢复模式并查看接收/回放位点 SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();pg_is_in_recovery()返回true表示备库正在回放 WAL。判断“备库有没有追上主库”比较的就是主库的pg_current_wal_lsn()和备库的pg_last_wal_replay_lsn()差值为空或极小才说明是健康状态。多数监控系统容易犯的错是只看pg_stat_replication里连接还在就认为数据没延迟实际上备库回放可能已经落后很久了。2.4 异步复制的丢数据窗口以及同步复制付的代价默认配置下 Postgres 流复制是异步的主库提交事务不需要等备库确认性能最好但主库突然宕机时最后提交的少量事务可能没来得及传到备库。这个窗口有多小取决于网络延迟和 WAL 刷盘频率生产里通常是毫秒到秒级。要彻底砍掉这个窗口配置同步复制# postgresql.conf — 主库 synchronous_standby_names FIRST 1 (standby1) synchronous_commit onFIRST 1 (standby1)表示等待名为standby1的备库确认后再向客户端返回提交成功。代价是如果standby1宕机主库写入会阻塞直到超时或备库恢复。很多团队接受异步复制加复制槽监控的折衷也有团队在金融类场景直接上同步复制。选哪个没有标准答案但必须明确一点同步复制解决的是“客户端已收到提交成功但数据丢了”的问题纯异步复制解决不了。3. 接入 pgpool-II连接池、读写分离、健康检查三件事3.1 pgpool 在方案里到底扮演什么角色主从流复制只解决了数据层冗余应用并不会自动知道“现在主库是谁”。pgpool-II 插在应用和 Postgres 之间干了三件实事第一连接池应用只连 pgpool 的 9999 端口pgpool 维护到后端数据库的连接复用避免每个应用连接都直连数据库第二读写分离SELECT按权重分到备库INSERT/UPDATE/DELETE固定在主库第三故障协调主库健康检查失败时由 pgpool 决定触发哪个动作。理解这一点很重要pgpool 本身不复制数据它只是“知道”谁在复制、谁还活着、流量往哪发。它所有的主备判断都建立在数据层状态之上所以第 2 章的流复制没配好pgpool 再聪明也没用。3.2 后端节点配置与健康检查参数pgpool.conf里最核心的是后端节点列表# pgpool.conf listen_addresses * port 9999 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_FAILOVERbackend_weight是负载均衡权重读写分离下SELECT按这个比例分发1:1 就是五五开如果备库硬件弱可以改成 2:1。backend_flag的ALLOW_TO_FAILOVER表示这个节点可以被 failover主库和备库都要保留这个标志否则节点异常时 pgpool 不会做任何处理。健康检查是 pgpool 判断节点生死的依据health_check_period 10 health_check_timeout 20 health_check_user pgpool_health health_check_password pgpool_health的密码 health_check_max_retries 3 health_check_retry_delay 1health_check_period是两次检查的间隔秒数10 表示每 10 秒探一次health_check_timeout是单次检查最大等待时间max_retries和retry_delay组合起来决定“连续失败几次才判定节点挂了”生产里建议至少 3 次避免数据库短暂抖动就被误杀。health_check_user需要在每个后端节点上存在并且能登录postgres数据库。只给CONNECT权限就够不要拿超级用户当健康检查账号。流复制模式下还要配置sr_check_user让 pgpool 能查询节点的主备状态sr_check_period 10 sr_check_user pgpool_check sr_check_password pgpool_check的密码这个用户需要能执行SELECT pg_is_in_recovery()和查询pg_stat_replicationpgpool 靠它判定哪个节点是主、哪个是备而不是靠猜。3.3 读写分离与连接池参数怎么调master_slave_mode on master_slave_sub_mode stream load_balance_mode onmaster_slave_mode on告诉 pgpool 后端是主备架构配合master_slave_sub_mode stream让它按流复制规则识别主备。load_balance_mode on开启 SELECT 分发写操作永远只发到主库。这里有个隐含前提所有表都要有主键或唯一索引因为备库上的SELECT如果走的是修改数据的语句比如SELECT ... FOR UPDATEpgpool 会识别并强制发到主库但表没主键时一些工具生成的查询可能被错误分发。连接池相关参数connection_cache on num_init_children 32 max_pool 4num_init_children是 pgpool 预建的应用连接数上限32 意味着同时最多 32 个应用连接。max_pool是每个子进程到后端的连接池大小连接数不够时应用会等待调大num_init_children是最直接的手段但注意每个子进程都会占内存32 个子进程乘上每个进程的内存开销小机器会吃力。验证读写分离是否生效简单方法是分别在主备库执行-- 主库和备库分别执行 SELECT count(*) FROM pg_stat_activity WHERE client_addr pgpool所在主机IP;对比两个节点的连接数分布如果全部压在主库上多半是load_balance_mode没生效或者配置被 reload 掉了。另一个快速验证方式是在主库上开启log_statement all观察读请求是否出现在备库日志里。4. 故障切换链路从 health check 失败到新主库接管4.1 主库宕机时 pgpool 实际执行了什么先理清顺序。主库异常后pgpool 会在下一次健康检查周期发现问题连续失败达到health_check_max_retries后把这个节点标记为down。如果宕机的节点是主库pgpool 从存活的备库里选一个作为新主候选触发failover_command。很多初次搭建的人以为 pgpool 会自动完成备库提升其实在 pgpool-II 4.x 里节点状态更新由 pgpool 自己接管但“让备库变成新主”这个动作是failover_command里的脚本完成的。pgpool 的角色更像调度器它知道该切换了但具体怎么切换由你写的脚本决定。如果宕机的是备库处理就简单得多pgpool 只把它标down主库继续服务等备库修好后再挂回。这两种场景务必在脚本里区分开很多翻车事故就是脚本没判断节点角色备库挂掉时把主库也误杀了。4.2 一份可以直接改用的 failover 脚本pgpool.conf里这样声明failover_command /etc/pgpool/failover.sh %d %D %m %M %h %H各占位符含义按 pgpool 文档约定%d是被降级节点的数据库名%D是被降级节点的数据目录%m是新主节点的节点 ID%M是旧主节点的节点 ID%h是新主节点的主机名%H是旧主节点的主机名。顺序完全由你在配置里定义脚本里拿到的参数顺序必须和这里一致。#!/bin/bash # /etc/pgpool/failover.sh # 入参顺序对应 pgpool.conf 里的 failover_command 定义 FAILED_DB$1 FAILED_DATA_DIR$2 NEW_MASTER_ID$3 OLD_MASTER_ID$4 NEW_MASTER_HOST$5 OLD_MASTER_HOST$6 # 只有旧主节点才需要切换备库失败直接退出 if [ $OLD_MASTER_ID -1 ]; then # 没有旧主节点说明挂掉的是备库不需要提升 exit 0 fi # 新主节点为空说明没有可用备库只告警不动作 if [ $NEW_MASTER_HOST ]; then logger -t pgpool_failover no standby available, skip promote exit 1 fi # 远程提升新主 ssh -T postgres$NEW_MASTER_HOST \ pg_ctl promote -D /data/pgdata \ /var/log/pgpool/failover.log 21 logger -t pgpool_failover promoted $NEW_MASTER_HOST as new primary说明几个关键点。第一脚本需要在 pgpool 所在主机上能以postgres用户执行ssh到新主且新主上pg_ctl promote要能免密执行SSH 密钥规划是这套方案的隐性前置条件。第二-D指定的数据目录要和备库实际路径一致否则 promote 会失败。第三脚本一定要写日志切换完成后排查问题靠的就是这条日志。第四也是血泪经验如果配了同步复制synchronous_standby_names里的备库名是旧备库名promote 之后新主库上这个配置要同步修改否则后续写入可能被一个不存在的备库阻塞。常见做法是在 failover 脚本里顺手更新主库配置并 reload。4.3 原主库回归集群pg_rewind 是后悔药也是双刃剑原主库恢复后不能直接挂回集群。它宕机期间新主库可能有新写入两个节点的时间线已经分叉原主库上那些“多出来”的事务必须回退。PostgreSQL 提供的pg_rewind就是干这个的它把旧主库的数据回退到新主库的时间线再作为备库重新加入。# 在旧主库上执行目标是把本地数据目录回退到新主库的时间线 pg_rewind --target-pgdata/data/pgdata \ --source-serverhost192.168.1.11 port5432 userrepl_user dbnamepostgres -P执行前必须把旧主库停掉target-pgdata指向本地数据目录source-server指向新主库。pg_rewind通过对比新旧主库的 WAL 记录找到分叉点把旧主库上分叉之后的变更全部回退。之后按第 2 章流程从新主库重新拉 WAL原主库就以备库身份回到集群。如果pg_rewind执行失败原因通常是分叉点之前的 WAL 已经被清理或者新旧主库的wal_log_hints/checksum配置不一致。彻底的办法是清空数据目录重新pg_basebackup代价是重新传全量数据数据量大时耗时较长。我的习惯是能 rewind 就 rewindrewind 失败的回报路径永远是好后端到 back 方案。4.4 让 pgpool 自己也不成为单点watchdog 与虚拟 IP单台 pgpool 虽然接管了数据库故障但它自己挂了应用一样连不上数据库。生产环境至少部署两台 pgpool用自带 watchdog 机制选主并配合虚拟 IP 对外服务。# pgpool.conf — 两台 pgpool 节点上都配置 use_watchdog on wd_hostname 192.168.1.21 wd_port 9000 delegate_ip 192.168.1.100 wd_escalation_command /etc/pgpool/escalation.shdelegate_ip是虚拟 IPwd_escalation_command是这台 pgpool 被选举为主时执行的脚本脚本里通常写 VIP 添加动作#!/bin/bash # /etc/pgpool/escalation.sh ip addr add 192.168.1.100/24 dev eth0 label eth0:1 arping -q -c 3 -A -I eth0 192.168.1.100另一台 pgpool 节点在故障恢复后执行ip addr del移除 VIP。应用连接的是delegate_ip加 pgpool 端口而不是某台物理机的 IP这样无论哪台 pgpool 活着应用入口都不变。watchdog 的坑集中在网卡名和权限上escalation.sh里的网卡名必须和实际环境一致执行用户要有ip命令权限否则 VIP 加不上去pgpool 看着是主实际对外不可达。5. 主从 pgpool 避坑五个翻车现场与排错路径5.1 pg_wal 目录爆满磁盘直接写满现象主库的pg_wal目录占用持续上涨磁盘告警数据库开始报错无法写入。原因备库长期断连时如果配置了复制槽主库会认为备库还需要这些 WAL于是无限保留max_replication_slots里那个槽一直处于active false状态。没有复制槽的场景则是wal_keep_size兜不住备库追上不后重连需要从更早的 WAL 开始但那些 WAL 已经被回收。解决先查pg_replication_slots里active为false的槽确认没有恢复希望后手动pg_drop_replication_slot。再给pg_wal目录做容量监控告警阈值设在磁盘的 70%。如果备库经常断连把wal_keep_size适当调大作为缓冲但要清楚它只是缓冲复制槽才是机制保证。5.2 health check 把活着的备库踢出集群现象pgpool 日志出现节点被标记down但登录数据库一看进程正常、查询正常纯属误杀。原因health_check_user不存在、密码错误或者pg_hba.conf没有放行 pgpool 所在主机的连接。更隐蔽的一种是连接数打满pgpool 的 health check 连不上数据库被计为一次失败三次重试后判定节点挂掉。解决先看 pgpool 日志确认具体报错独立建pgpool_health用户并只授权CONNECT确认pg_hba.conf里对 pgpool 来源 IP 有host all pgpool_health ... md5的记录。连接数打满的场景用pg_stat_activity看有没有异常连接堆积必要时调大max_connections但更重要的是找出连接不释放的源头应用。5.3 切换后应用报错连接还指向旧主库现象failover 脚本执行成功新主库已经 promote但应用报“connection refused”或“server is not accepting connections”。原因应用没有连 pgpool 的 9999 端口而是直连了数据库的 5432 端口。主库宕机后 5432 端口当然不可用pgpool 的自动切换对应用完全无感知前提是应用必须把连接串指向 pgpool。解决统一改应用连接串为 pgpool 地址。另一个常被忽略的点是连接池应用侧的连接池比如 HikariCP、Druid缓存了旧连接切换后需要等待连接失效才能重建。把连接池的connectionTimeout和validationQuery调短或者切换后主动通知应用侧刷新连接都是可行手段。5.4 备库没追平就被提升切换后丢数据现象主库宕机failover 脚本立即 promote 备库事后发现新主库上缺了最后几秒的事务。原因异步复制下备库本身就有延迟promote 动作没有检查备库是否追平主库的最后位点。健康检查只保证“连接还在”不保证“数据追平”。解决在 failover 脚本里加位点检查。脚本读取旧主库的pg_current_wal_lsn()不太现实因为旧主已经挂了可行做法是在健康检查正常期间由监控系统把主库位点记录到外部文件切换时脚本对比新主库的pg_last_wal_replay_lsn()差距过大时报警并延迟提升。接受数据丢失风险的低配方案是直接提升但要有明确的 RPO 认知别把异步复制吹成零丢失。5.5 pgpool 单节点部署数据库没挂 pgpool 先挂了现象数据库主备都健康但 pgpool 所在机器宕机应用彻底连不上数据库高可用方案形同虚设。原因只部署了一个 pgpool 节点没有 watchdog没有虚拟 IP。解决按第 4.4 节部署双 pgpool watchdog VIP。注意两台 pgpool 的wd_hostname要写各自真实 IPdelegate_ip两台必须一致否则 watchdog 会互相打架。升级后还要做一次演练直接拔掉主 pgpool 机器的电源观察 VIP 是否在几十秒内漂移到另一台。6. 验证这套方案的几条命令和日常巡检习惯先给一组每星期应该跑一次的验证命令直接存成脚本放 crontab 里也行#!/bin/bash # /usr/local/bin/pg_ha_check.sh # 检查 pgpool 节点状态 pcp_node_count -h 127.0.0.1 -p 9898 -U pgpool pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool -n 0 pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool -n 1 # 检查复制是否在跑 psql -h 192.168.1.100 -p 9999 -U postgres -d postgres -c \ SELECT client_addr, state, sync_state FROM pg_stat_replication;pcp_node_info输出里的status字段1表示节点 up2表示 down。巡检时重点看两点第一节点状态是不是都是 up第二pg_stat_replication里有没有 streaming 状态的连接。脚本里加一句“如果某节点状态异常就发告警”比人肉盯监控靠谱得多。切换演练是这套方案唯一能验证“真能切”的手段建议每季度做一次。演练前约定好时间窗口先拔备库验证健康检查不会误杀再拔主库验证完整切换。注意几个演练纪律切完一定要写复盘报告演练后原主库的回归路径要走一遍别让pg_rewind成为“从没跑过的黑匣子”日志保留时间至少一个季度排错时那些failover.sh的日志比任何文档都有用。我自己经历过一次“切换成功但应用全断”的事故根因就是应用连接串写的数据库实际 IP而不是 pgpool 的虚拟 IP。那次之后我在巡检脚本里加了一条连接 9999 端口后执行SELECT pg_is_in_recovery()并和实际主库 IP 做对比不一致立刻告警。这套 Postgres 主从流复制加 pgpool 的方案配置一次不算难难的是把切换这件事当成“会经常发生”来对待脚本有日志、位点有检查、入口只有一个、演练有复盘做到这四件事夜间被数据库故障叫醒的概率会小很多。希望帮到你。本文还有配套的精品资源点击获取