简介PostgreSQL 主从流复制与 pgpool-II 高可用方案详解面向数据库管理员、运维工程师及需要部署高可用环境的技术人员覆盖主备同步、读写分离、连接池管理与故障切换等问题。方案基于预写日志WAL实现主备库数据实时同步主库日志发送进程持续将 WAL 记录传给备库备库接收进程完成落盘与回放pgpool-II 在此基础上提供连接池、负载均衡、读写分离和自动故障切换并说明同步/异步复制模式的选择与影响。资源为单份 docx 文档包体约 773KB便于快速查阅与留存。已有 466 人学习。内容包含方案综述、实践环境、客户环境准备、资源调整、主备库与 pgpool 配置、启动及故障模拟测试等章节同时强调防火墙、SELinux、时钟同步、文件夹权限等前置操作章节按实施流程组织既能作为生产部署的参考模板也可作为内部培训讲义或方案汇报附件使用。1. 先想清楚这套 Postgres 主从流复制 pgpool 方案到底解决了什么问题“Postgres主从流复制pgpool高可用方案”看着是两样东西拼在一起实际是把两类问题分开处理流复制保证主库坏了数据还在pgpool 保证应用还能连得上、读写不中断。很多团队从单机 Postgres 迁过来第一反应是上 Patroni etcd 这类重型方案结果发现维护成本比想象中高不少反而主从流复制加 pgpool 是更贴近现状的折中路线和 MySQL 那边用 MHA、Redis 那边用哨兵的思路类似。这套方案尤其适合已有 Postgres 业务、想让故障切换自动化、又不想重写连接层的 DBA 和运维。文章接下来会从数据冗余讲到入口切换再细说配置、切换演练和容易翻车的地方。2. 把地基打牢Postgres 主从流复制的两种模式与三处必调参数2.1 同步复制与异步复制先回答“主库挂了丢不丢数据”流复制的核心是 WAL 日志。主库每做一次变更都会先写 WAL从库通过网络把 WAL 拉过来再在本地回放。异步复制模式下主库只要写完 WAL 就返回客户端 commit 成功从库落后主库多少完全取决于网络和从库磁盘速度主库真宕机时最近几秒甚至更久的事务可能没到从库。同步复制则要求主库等至少一个从库完成接收和 flush才返回成功这个模式基本不丢数据但主库写入延迟会跟着从库的网络抖动走。选同步还是异步本质是在问业务能不能接受“丢最近一小段时间的提交”。账单、订单、支付回调这类数据我会开同步普通读多写少的内部系统异步就够了。注意 pgpool 不负责数据级保护它只管入口和切换所以这一步必须在 Postgres 层做决定不要指望 pgpool 帮你兜底。提示Postgres 里可以通过pg_stat_replication直接看到每个从库的同步状态。sync_state列为sync表示该从库参与了同步确认async表示只收 WAL 不参与确认。2.2 主库三处必调参数wal_level、max_wal_senders、wal_keep_size在postgresql.conf里最影响流复制能否跑起来的是下面这几个参数。我把常用配置和说明放在一起# postgresql.conf主库节点 wal_level replica # 最低要求是 replicalogical 更全但 WAL 更占空间 max_wal_senders 8 # 允许几个从库同时连主库拉 WAL wal_keep_size 1024 # 额外保留的最小 WAL 量单位 MBPostgres 13 用这个 synchronous_commit off # 异步模式要同步复制时改成 remote_apply listen_addresses * # 千万别写 localhost远端从库连不上先看wal_level。流复制至少需要replica它会在 WAL 里带上足够重建数据页的信息。你如果没做逻辑复制或 CDC不要随意开logicalWAL 体积会明显变大。max_wal_senders控制同时连过来的 WAL 发送进程数三节点集群设 8 已经有余量不是越大越好每个 sender 都要占内存。wal_keep_size是最容易设错的地方。它表示 pg_wal 目录里额外保留的 WAL 最小值设太小从库断线时间一长主库的 WAL 被 checkpoint 清掉从库就只能重新做一次全量基础备份设太大pg_wal 会把磁盘塞满。更稳妥的做法是开复制槽把wal_keep_size保持在较小值复制槽会确保从库没消费掉的 WAL 不被清理。注意wal_level改动后必须重启主库才生效热 reload 不会应用这个参数。2.3 从库配置与首次建立流复制的命令从库其实不需要安装两遍软件它需要的是主库的一份基础备份加一个standby.signal信号文件。Postgres 12 以后不再用recovery.conf识别“我是从库”的标志就是数据目录下存在standby.signal以及primary_conninfo指向主库。先把主库的 pg_hba.conf 加上复制权限# pg_hba.conf主库 host replication repl_user 192.168.1.0/24 scram-sha-256在 pg_hba.conf 里类型要写replication普通用户靠这条规则是连不上的。然后在主库创建复制账号CREATE USER repl_user REPLICATION LOGIN PASSWORD Changeme_123;REPLICATION是专用角色权限普通LOGIN用户即使有超级权限也不能走 replication 协议连流复制。接下来在从库执行基础备份# 从库先把数据目录清空 systemctl stop postgresql-16 rm -rf /var/lib/pgsql/16/data/* # 从主库拉全量数据-R 自动生成 standby.signal 和 primary_conninfo su - postgres -c /usr/pgsql-16/bin/pg_basebackup -h 192.168.1.10 -U repl_user -D /var/lib/pgsql/16/data -P -R -X stream chown -R postgres:postgres /var/lib/pgsql/16/data systemctl start postgresql-16-X stream表示在基础备份过程中同步接收 WAL保证备份数据的一致性。-R会往数据目录里写入standby.signal并自动生成包含主库连接信息的postgresql.auto.conf。如果你后续手动改连接串优先级最高的是postgresql.auto.conf里的配置不要改完postgresql.conf发现不生效就以为是玄学。启动后从主库一侧确认状态SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;正常情况state是streaming。如果一直显示catchup或长时间停在startup优先看从库日志多数是连接串密码错误或者从库磁盘空间不足。另外记住一个注意事项主从的max_connections必须保证从库大于等于主库不然从库回放 WAL 时会因为连接资源不足而不断重启恢复进程。3. pgpool 在方案里的真实角色读写分离、连接池和故障转移的边界3.1 为什么 Postgres 主从之上还要再叠一层 pgpoolPostgres 自身没有“集群管理”的概念。从库只是一直在回放 WAL即使主库宕机它也不会自动把自己提升成主库应用也不会知道该去连谁。虽然你可以人工执行pg_ctl promote但生产环境没法接受“人盯着故障再动手”。pgpool-II 就是来补这一层的它对外提供统一入口内部不断探测后端节点状态一旦发现主库异常就触发切换脚本提升从库并把连接导向新主库。同时 pgpool 是连接池。Postgres 每接一个连接就 fork 一个后端进程几十上百个应用连接对单机压力不大但配合高并发和短连接场景会非常浪费资源。pgpool 用有限的子进程复用连接效果和 Redis 哨兵、MySQL 的 MHA 是同一类思路但注意它不管数据复制。SQL Server 的 Always On 也没法照搬到 Postgres原因就在这层组件只解决“入口和切换”不解决“数据怎么多副本”。3.2 pgpool.conf 核心参数master_slave_mode 与 replication_mode 的区别这里有个非常容易踩混的坑pgpool 自带的replication_mode是“把同一条 SQL 发给所有后端节点各执行一遍”属于它自己的复制方案不是配合 Postgres 流复制用的。在“主从流复制”这套架构里正确做法是把replication_mode关掉开启master_slave_mode让 pgpool 只在主库上执行写请求读请求可以按权重分发给主从节点。# pgpool.conf listen_addresses * port 9999 backend_hostname0 192.168.1.10 backend_port0 5432 backend_weight0 1 backend_hostname1 192.168.1.11 backend_port1 5432 backend_weight1 1 master_slave_mode on master_slave_sub_mode stream replication_mode off load_balance_mode onbackend_hostname0和backend_port0这块是后端的编号规则从 0 开始每个后端一组三行配置。backend_weight表示读流量的权重两个节点都是 1 就是五五开。master_slave_sub_mode stream必须和流复制配套不能写成别的。load_balance_mode打开后读请求会分流到多个后端但注意事务内的读不会被分流只有自动提交的简单查询才会走负载均衡。连接池相关的参数同样会影响稳定性。num_init_children默认是 32表示 pgpool 预留的子进程数max_pool是每个子进程里缓存的连接数。并发上来以后如果日志报no free pgpool child优先调大num_init_children而不是无脑加应用侧连接数。3.3 故障转移的触发链路从“发现主库挂了”到“应用重连”pgpool 默认通过健康检查探测后端每个 backend 每隔health_check_period秒做一次探测。连续失败达到health_check_max_retries后pgpool 把该节点标记为 down然后执行failover_command配置的脚本。脚本里要做的事情就是找到候选从库执行提升命令让原本只回放 WAL 的从库变成新主库。提升完成后pgpool 重新检测各节点状态把连接调度到新主库上。但如果 pgpool 只有单节点它本身就成了新的单点。生产上至少起两个 pgpool 节点配合watchdog和delegate_IP提供入口高可用。watchdog 之间互相做心跳当主 pgpool 挂了备 pgpool 会接管虚拟 IP应用连接串始终指向那个 VIP。高可用场景下写后端代码时需要注意的点也在这里连接串应该配置成 pgpool 的虚拟 IP 端口而不是具体某一台数据库服务器地址应用侧要开启连接自动重连不要在代码里缓存后端 IP。提示pgpool 的 failover 只负责“把从库提起来”不负责“把老主库自动加回集群”。老主库恢复后必须手动处理否则会出现两个节点同时可写的脑裂风险。4. 从零跑通一套最小集群Rocky Linux 9 上的安装、配置与切换演练4.1 环境规划与安装清单建议用三个节点起步一个主库、一个从库、一个 pgpool 节点。pgpool 节点如果后续要加高可用再补一台相同角色即可。操作系统以 Rocky Linux 9 为例PostgreSQL 按官方 yum 源安装pgpool 也走同一套源版本对应关系不要搞混。主机名IP角色核心组件pg-primary192.168.1.10主库PostgreSQL 16pg-standby192.168.1.11从库PostgreSQL 16pg-pool01192.168.1.20接入层pgpool-II 16 虚拟 IP 192.168.1.30先装 PostgreSQL 官方源dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm dnf install -y postgresql16-server postgresql16pgpool-II 的包名带版本后缀dnf install -y pgpool-II-16安装完成后PostgreSQL 主从两台节点都先初始化并启动主库/usr/pgsql-16/bin/postgresql-16-setup initdb systemctl enable --now postgresql-16pgpool 节点不需要初始化数据目录它只做转发和调度。4.2 主从流复制初始化pg_basebackup 与 standby.signal主库创建复制账号并在 pg_hba.conf 加 replication 规则这些在第 2 章写过这里直接落到完整命令su - postgres -c psql -c \CREATE USER repl_user REPLICATION LOGIN PASSWORD Changeme_123;\主库postgresql.conf至少包含wal_level replica、max_wal_senders 8、listen_addresses *然后重启主库让参数生效。接着到从库节点停掉服务清空数据目录用pg_basebackup拉取基础备份systemctl stop postgresql-16 rm -rf /var/lib/pgsql/16/data/* su - postgres -c /usr/pgsql-16/bin/pg_basebackup -h 192.168.1.10 -U repl_user -D /var/lib/pgsql/16/data -P -R -X stream chown -R postgres:postgres /var/lib/pgsql/16/data systemctl start postgresql-16备份完成后从库数据目录里会出现standby.signal文件这就是它身份的证明。如果没看到说明-R参数没生效或者数据目录非空复制不可能工作。启动从库后回到主库执行pg_stat_replication查询能看到state streaming就说明流复制已经建立。4.3 pgpool 接入与 VIP 漂移让应用只认一个入口pgpool 安装后会提供示例配置文件最省事的方式是从 stream 模板拷一份cp /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf然后把第 3 章里的后端节点信息、master_slave_mode、load_balance_mode填进去。同时打开 watchdog 和虚拟 IP 相关配置# pgpool.confpgpool 节点 watchdog on delegate_IP 192.168.1.30 wd_hostname pg-pool01 wd_port 9000 # 另一台 watchdog 节点如果只有单 pgpool先注释掉 # wd_hostname pg-pool02 # wd_port 9000delegate_IP就是应用连接串里使用的虚拟 IP。pgpool 启动后这个 IP 会绑定在当前 pgpool 节点的网卡上。如果这台 pgpool 挂了另一台 pgpool 会通过 watchdog 机制把同一 IP 抢过来。虚拟 IP 的漂移本质上依赖 ARP 广播物理机环境通常没问题云环境要单独确认平台是否允许实例漂移 IP。启动 pgpool 并确认后端状态systemctl enable --now pgpool-II-16 pcp_attach_node -h 127.0.0.1 -p 9898 -w -n 0 pcp_attach_node -h 127.0.0.1 -p 9898 -w -n 1pcp_attach_node用于把后端节点设为可用-n 0 和 -n 1 分别对应 backend0 和 backend1。刚启动时节点可能处于 down 状态不加这一步应用会连不上。应用侧连接串写法psql host192.168.1.30 port9999 dbnamemydb userapp_user应用只需要认 192.168.1.30 这个入口后端哪台机器在跑主库应用完全不需要关心。4.4 故障切换演练kill 主库看流量几分钟内恢复配置一套高可用方案不演练等于没配。在测试环境做一次主库宕机演练先随便建一张表写入数据然后直接停掉主库服务systemctl stop postgresql-16不要用kill -9模拟生产主库崩溃更多是进程异常退出systemctl stop 更能反映“进程没了但机器还在”的场景。停主库后观察 pgpool 日志tail -f /var/log/pgpool-II/pgpool.log日志里会出现 health check 失败、节点标记 down、failover 脚本执行等记录。走到最后一步用 VIP 连数据库检查从库是否已经提升psql -h 192.168.1.30 -p 9999 -U postgres -c SELECT pg_is_in_recovery();返回f表示当前后端节点已经是主库。数据是否完整取决于你用的是同步还是异步复制这就是第 2 章选型决定的。演练结束后把原主库作为新从库重新加入集群用pg_rewind比重新pg_basebackup快得多具体写法放在第 6 章。5. 高可用最容易翻车的五个坑复制追不上、脑裂、VIP 漂不走的排查记录5.1 从库复制延迟只增不减问题不在网络在磁盘现象是pg_stat_replication里state一直显示streaming但pg_wal_lsn_diff算出来的延迟越来越大从库数据越落越多。初期容易怀疑网络带宽但真正原因多半是从库磁盘性能和回放进程吞吐跟不上主库写入速度。Postgres 从库回放 WAL 是单线程串行操作从库磁盘 IO 一旦被读业务占满回放就会排队。解决分两步。第一步是从库上执行iostat -x 1看%util如果磁盘繁忙考虑给从库单独用独立磁盘别把数据目录和应用日志放在同一块盘。第二步是检查是否存在长事务或慢查询拖住了hot_standby的回放max_standby_streaming_delay 30000这类参数可以缓解但治本还是给回放留出 IO 余量或者把读流量再分流。5.2 pgpool 认为主库没挂从库却已被提升这是脑裂的黑匣子最危险的情况是应用通过 pgpool 写主库而另一个维护人员手动把从库 promoted 了两个节点同时接受写入数据开始分叉。pgpool 的健康检查不会发现这种问题因为它的探测对象和真实主库状态没有强关联。还有一种变体是两个 pgpool watchdog 节点同时认为自己是主各自绑定同一个 VIP。原因基本都在切换触发条件上health_check_timeout设得太大主库已经 hang 住但 pgpool 迟迟不判 down或者 promote 脚本执行前没有二次确认候选从库确实还处于 standby 状态。解决方法是调小探测参数并发告警同时把wd_lifecheck的心跳检测独立配置不要和 health check 共用一条网络路径。promote 脚本里先执行一次pg_is_in_recovery判断确认从库还没被提升再动手。5.3 VIP 漂移失败ARP 缓存和云平台安全组拦路现象是 pgpool 日志显示delegate_IP已切换但应用访问虚拟 IP 超时或连接被拒绝。物理机环境下最常见原因是漂移后没有发 ARP 通告交换机或网关的缓存还指向旧网卡的 MAC 地址。云环境更直接多数云主机不支持 ARP 广播实例上的虚拟 IP 只能在单台机器上用根本漂不起来。解决要看部署形态。物理机或虚拟机在 VIP 接管脚本里加一次arping -I eth0 -c 3 192.168.1.30强制刷新邻居缓存。云上不要做本机 VIP改用云负载均衡的 VIPpgpool 节点只作为后端服务器加入负载均衡组流量由负载均衡分发数据库故障切换后自动把新主库挂到负载均衡后端即可。5.4 WAL 把磁盘写满复制槽没开wal_keep_size 又设太大现象是主库pg_wal目录不断膨胀磁盘使用率告警而复制延迟监控看起来正常。原因通常是wal_keep_size设得过大或者从库断开时间较长时主库没有复制槽来精确控制 WAL 保留checkpoint 只能按保守策略保留尽可能多的 WAL。解决建议是把wal_keep_size调回一个较小的保底值比如 1024MB另外为主库和从库一对一定制复制槽SELECT pg_create_physical_replication_slot(standby1);同时给max_slot_wal_keep_size设个上限防止从库长期断线时 WAL 无限堆积。日常巡检把pg_wal目录大小和复制延迟一起看不要只看延迟。5.5 failover 脚本权限不足pgpool 切换成功但 promote 失败现象是主库宕机后 pgpool 日志里能看到 failover 被执行但脚本没有真正把新主库拉起来应用仍然连不上。原因分两种脚本没有可执行权限pgpool 进程以 postgres 用户身份调用时直接 permission denied或者脚本内部使用了需要 root 执行的命令比如ip addr add绑定 VIP而 pgpool 的子进程没有对应权限。解决方法是先chmod x /etc/pgpool-II/failover.sh手动以 postgres 用户跑一遍确认链路通畅。脚本里用到的pg_ctl、psql路径全写绝对路径开头统一export PGHOME和PATH。需要 root 权限的操作放到 sudoers 白名单里避免为了省事直接把整个 pgpool 进程跑成 root。6. 让这套高可用配得上生产验证脚本、自动恢复与巡检的写法6.1 复制健康检查脚本延迟超过阈值就报警只看不验证的高可用不值得信任。写一个最简单的巡检脚本每天定时检查复制延迟#!/bin/bash # check_repl.sh LAG$(psql -h 192.168.1.30 -p 9999 -U postgres -tAc SELECT CASE WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) 52428800 THEN lag_exceeded ELSE ok END FROM pg_stat_replication;) if [ $LAG lag_exceeded ]; then echo $(date) replication lag too high /var/log/pgpool-II/repl_check.log exit 1 fipg_wal_lsn_diff算的是主库当前 WAL 位置和从库回放位置之间的字节差50MB 以上触发告警。复制延迟超过阈值往往意味着从库回放跟不上或者主库 WAL 在堆积早发现能避免切换后丢数据。6.2 老主库加回的正确姿势pg_rewind 而不是重新搭主库宕机切换后老主库恢复时不能再以原来的方式启动否则它会带着旧数据成为独立节点。正确做法是先用pg_rewind把它同步到新主库的最新状态# 老主库已停止执行 /usr/pgsql-16/bin/pg_rewind \ --target-pgdata /var/lib/pgsql/16/data \ --source-server host192.168.1.11 port5432 userrepl_user dbnamepostgres \ -Ppg_rewind会把老主库回放到新主库的 WAL 位置再配合standby.signal和primary_conninfo重新作为从库加入集群。比pg_basebackup重建快很多前提是新主库的 WAL 还保留着对应的历史。这也是为什么前面强调要开复制槽。我的习惯是每个集群至少两个 pgpool 节点复制延迟每天自动查一次每季度手动 kill 一次主库做切换演练。这套方案值不值得上答案在投入和收益之间很容易判断如果你的业务已经不能接受单机故障后人工恢复几小时那就值得但上了之后一定要定期演练高可用是练出来的不是配出来的。希望帮到你。本文还有配套的精品资源点击获取