简介围绕 PostgreSQL 主从流复制与 pgpool-II 高可用落地这份技术方案文档面向数据库运维、架构设计及集群部署人员重点解决主备数据同步、故障切换、读写分离和连接池管理等实际问题兼顾生产部署与学习验证场景。压缩包仅含 1 个 docx 文件约 773KB内容以方案说明、环境规划与配置记录为主便于按章节查阅和复用。文档从流复制的同步/异步模式及 WAL 日志传输机制讲起说明同步复制对一致性与写性能的影响、异步复制在写入速度与数据丢失风险之间的取舍并结合 pgpool-II 的连接池、复制、负载均衡/读写分离、连接数限制与备份模块覆盖软件环境、主备节点准备、主库配置、备库初始化、pgpool-II 配置、启动测试及故障模拟切换等环节还涉及防火墙、SELinux、hosts、时钟同步与 sysctl 等系统资源调整适合需要搭建高可用 PostgreSQL 集群的读者参考。目前已有 471 人学习。1. 数据库只靠单机运行业务峰值一来就只剩两种结局很多团队第一次遇到数据库瓶颈时第一反应是换更大的机器加内存、换 SSD、把 CPU 从 8 核升到 32 核。硬件升级能解决性能问题却解决不了另一类问题主库磁盘损坏、进程被误终止、机房单点断电任何一个环节出问题业务就会直接停摆。这时即使机器配置再高数据也只有一份恢复只能靠备份而备份的 RPO恢复点目标往往按小时计算。这份「Postgres 主从流复制 pgpool 高可用方案」要解决的正是从单机数据库走向双节点在线集群的完整落地路径用 PostgreSQL 原生流复制把 WAL 日志实时送到备节点用 pgpool-II 做连接池、读写分离与故障切换再用 pg_rman 做离线归档备份兜底。它适合正在做数据库高可用毕设、课程设计的学生也适合需要给一套可直接参考的部署文档的运维和 DBA。下面按需求、设计、实现、避坑、验收的顺序拆开讲。2. 总体架构设计三个组件如何分工这套方案的价值不只在于「装了三个软件」而在于把在线同步、离线备份、流量调度三件事拆给三个组件各管一段。理解分工之后再看配置文件里的上百个参数才知道每个参数在替谁做决策。2.1 组件职责划分与数据流向整体是一条主链路加一条旁路。主链路上客户端不直接连数据库而是连到 pgpool-II 暴露的虚拟 IPpgpool-II 根据 SQL 类型把写请求发给主库、只读请求分散到主备主库的 walsender 进程把 WAL 页推给备库的 walreceiver备库的 startup 进程持续重放日志保持数据一致。旁路上pg_rman 周期性做全量备份并持续归档 WAL主备同时失效时从这里恢复。组件部署位置核心职责失效影响PostgreSQL 主库节点 1承接写请求、产生 WAL触发 failover虚拟 IP 漂移PostgreSQL 备库节点 2重放 WAL、承接只读查询读能力下降主库继续写pgpool-II双节点各一套连接池、读写分离、健康检查、VIP 管理客户端无法接入需排查中间件pg_rman独立备份目录全量备份 WAL 归档支持 PITR不影响在线业务但失去误删恢复能力一个容易忽略的设计点pgpool-II 本身也是双节点部署并启用 watchdog两个 pgpool 互相监控、争抢虚拟 IP。如果只部署一个 pgpool它自己就是新的单点高可用等于没做。通常的做法是让 pgpool 与 PostgreSQL 同机部署本方案就是这个思路省机器也省网络跳数代价是单机故障时两个角色同时漂移切换逻辑要更谨慎。2.2 同步复制与异步复制的取舍PostgreSQL 流复制默认异步主库提交事务后不等备库确认延迟通常在毫秒到 1 秒之间主库突然崩溃可能丢少量事务。同步复制则要求synchronous_standby_names非空且synchronous_commit on主库必须等备库写完 WAL 才返回提交成功代价是每次写事务都多一个网络往返。选型判断很直接金融账务、订单扣减这类不能丢数据的场景选同步日志、埋点、报表类能容忍秒级丢失的选异步。本方案在新版本环境里给了synchronous_commit的配置位置实际交付时我一般先按异步上线观察主备延迟稳定后再切同步避免一开始就因为网络抖动把写入拖垮。# postgresql.conf 主库关键参数12.x 示例 wal_level replica # 至少 replica才能支撑流复制 max_wal_senders 10 # 允许的 WAL 发送进程数需大于备库数量 wal_keep_segments 128 # 保留的 WAL 段数防止备库落后被清理 synchronous_commit on # 开同步提交 synchronous_standby_names * # 匹配任意一个备库参数说明wal_level决定 WAL 记录的信息量设成minimal就无法做流复制max_wal_senders要和实际备库数留出余量wal_keep_segments是防翻车的关键备库短暂断连再回来时如果主库已经把需要的 WAL 段清掉复制就只能重建。synchronous_standby_names写*表示任意一个备库确认即可也可以写具体应用名做精确控制。3. 主从流复制的落地实现从环境准备到切换验证这一章是整份方案里最需要照着敲的部分。原文把流程拆成环境准备、资源调整、安装、配置复制、切换验证几段这里按实际执行顺序重新组织把每一步的判断依据补上。3.1 系统层准备那些装库之前必须先做完的事装 PostgreSQL 之前有四件事必须先处理顺序错了后面全要返工。第一关闭防火墙或放行 5432、9999pgpool、9898pcp端口第二关闭 selinux否则数据目录权限会被拦截第三配置/etc/hosts写好主备节点的主机名解析因为后面 SSH 免密和复制连接都依赖主机名第四主备节点做时钟同步否则日志时间戳对不上排查问题时基本等于蒙眼。# 关闭防火墙与 selinuxRHEL/CentOS systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i s/^SELINUX.*/SELINUXdisabled/ /etc/selinux/config # 内核参数共享内存与信号量按物理内存调整 cat /etc/sysctl.conf EOF kernel.shmmax 68719476736 kernel.shmall 4294967296 vm.swappiness 10 EOF sysctl -p # 资源限制postgres 用户的进程数与文件句柄 echo postgres soft nofile 65536 /etc/security/limits.conf echo postgres hard nofile 65536 /etc/security/limits.conf设计说明kernel.shmmax是单个共享内存段的最大字节数通常设成物理内存的一半左右太小会导致could not resize shared memory segment报错vm.swappiness调低是为了让数据库尽量不走 swap避免出现响应时间忽高忽低的玄学问题nofile不放开的话连接数一多就会报too many open files。这些参数原文归在「资源调整」一章实际执行必须放在安装之前。安装环节两个节点要做完全一致的动作建postgres用户、把安装包解压到统一目录、修改目录属主、配置PGDATA和PATH环境变量、用initdb初始化主库。备库不需要单独 initdb直接从主库拉一份基础备份即可这是流复制区别于逻辑复制的地方物理层面完全一致连数据块布局都一样。3.2 主库配置与备库拉取pg_basebackup 的正确用法主库侧要改两个文件。postgresql.conf打开流复制相关参数pg_hba.conf放行备库的复制连接这一条漏了就会出现经典报错no pg_hba.conf entry for host。# pg_hba.conf允许备库以复制用户连接 host replication repl_user 192.168.1.0/24 md5 host all all 192.168.1.0/24 md5接着在主库创建复制用户并授权CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD your_password;然后到备库执行基础备份拉取。这里有个版本坑PostgreSQL 12 之前备库靠recovery.conf标识12 之后这个文件被取消改成在postgresql.conf里写primary_conninfo并在数据目录放一个空的standby.signal文件。用错版本的做法备库要么起不来要么起来之后是个独立主库复制关系根本没建立。# 备库执行从主库拉基础备份 pg_basebackup -h 192.168.1.10 -p 5432 -U repl_user \ -D /data/pgdata -F p -X stream -P -R # -F p 表示明文格式-X stream 表示同时拉取所需 WAL # -R 会自动生成 standby.signal 和 primary_conninfo12 版本-R参数是省事的关键它会自动把连接信息写进postgresql.conf并创建standby.signal但连接串里的密码不会自动补全需要在primary_conninfo里手动加上passwordxxx或者配.pgpass。如果是 12 以下版本则要手写recovery.conf内容包含standby_mode on和primary_conninfo。3.3 复制状态检查与主备切换两个实例都起来之后不要急着装 pgpool先在数据库里确认复制真的在跑。主库上查pg_stat_replication能看到备库的state是streaming、sync_state符合预期才算成功备库上查pg_stat_wal_receiverstatus应为streaming。用select pg_current_wal_lsn()和备库的pg_last_wal_replay_lsn()对比两个值差距持续扩大就说明复制卡住了通常是网络或 WAL 被清理导致。进程层面也要看主库应有walsender进程备库应有walreceiver和startup进程。备库处于恢复状态时是只读的可以接查询但不能写入这一点在读写分离配置里必须让 pgpool 知道否则写请求被分发到备库就会直接报错。手动切换的做法是先停主库用pg_ctl promote把备库提升为新主库再把原主库改配置反向接回来。这套操作在生产上应该由 pgpool 的 failover 自动完成手动只用于演练和应急。演练时必须确认原主库已经彻底停止否则会出现双主写入导致数据分裂——这类故障基本没有后悔药。4. pgpool-II 配置连接池、读写分离与故障切换pgpool-II 是这套方案里参数最密的一层配置文件动辄上千行。不用全看抓住连接、后端节点、健康检查、看门狗四组参数即可。4.1 pgpool.conf 的核心参数分组连接池相关参数决定复用效率后端节点参数告诉 pgpool 主备分别是谁健康检查参数决定多久发现一次故障watchdog 参数决定两个 pgpool 之间怎么选主。# 连接池 listen_addresses * port 9999 num_init_children 100 # 预启动的子进程数决定最大并发连接 max_pool 4 # 每个子进程对每个后端维持的连接数 # 后端节点 backend_hostname0 192.168.1.10 backend_port0 5432 backend_flag0 ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER backend_hostname1 192.168.1.11 backend_port1 5432 backend_flag1 DISALLOW_TO_FAILOVER # 健康检查 health_check_period 10 # 每 10 秒检查一次 health_check_timeout 20 health_check_user pgpool_check # 读写分离 load_balance_mode on参数说明num_init_children × max_pool大致决定了 pgpool 能占用的后端连接总数这个值必须小于 PostgreSQL 的max_connections否则连接池反而成为瓶颈backend_flag里标ALWAYS_PRIMARY的节点不会被自动降级适用于主库必须固定的场景health_check_period设太短会造成频繁误判设太长则故障发现慢10 秒是常见起点。health_check_user需要在数据库里真实存在且有连接权限否则健康检查全部失败pgpool 会认为所有后端都挂了。读写分离依赖load_balance_mode onpgpool 根据 SQL 是否在事务块内、是否为写语句来判断路由。注意写在显式事务里的读请求不会被分发到备库这是为了保证一致性理解这点才能解释为什么某些场景下负载均衡「看起来没生效」。4.2 watchdog、VIP 与 failover 的联动故障切换能不能真的一分钟以内完成取决于这三者配合。watchdog 让两个 pgpool 互相探活主 pgpool 持有虚拟 IP后端 PostgreSQL 主库挂掉时pgpool 触发 failover把备库提升为主库failover_command里调用pg_ctl promote同时更新后端节点角色虚拟 IP 不变客户端连接串完全不用改。# failover_command 示例提升备库并重载配置 failover_command /usr/local/pgpool/bin/failover.sh %d %H %P %D %R %M%d是失效节点编号%H是失效主机名%P是旧主库端口%D是新主库数据目录%R是新主库端口%M是旧主库 ID。脚本里要根据这些参数决定是「提升备库」还是「把原主库重新接入」。我一般把提升动作写成幂等脚本重复执行不会出错因为 failover 在某些网络抖动下可能被触发多次。VIP 漂移要在 pgpool 的if_up_cmd/if_down_cmd里绑定网卡名和虚拟地址并在arping_cmd里配置免费 ARP否则交换机缓存不更新客户端会短暂连到旧节点。这一步在虚拟机环境里表现尤其明显经常出现「pgpool 显示切换成功但应用连不上」的假象。4.3 启动顺序与连接测试正确的启动顺序是先起 PostgreSQL 主备确认复制正常再起两个 pgpool最后用pcp_watchdog_info看 watchdog 状态、用show pool_nodes看后端节点状态。通过 pgpool 端口连进去执行select inet_server_addr()能看出当前请求被路由到哪台机器反复执行几次观察是否在主备之间轮转读请求应该两边都有。# 查看后端节点状态与角色 psql -h 192.168.1.100 -p 9999 -U postgres -c show pool_nodes; # 确认当前连接落在哪个实例 psql -h 192.168.1.100 -p 9999 -U postgres -c select inet_server_addr(), pg_is_in_recovery();pg_is_in_recovery()返回f说明落在主库返回t说明落在备库。如果所有查询都只落在一台机器上先检查load_balance_mode再检查语句是否被包在事务里。5. 避坑与排查五类最容易翻车的问题5.1 备库拉取失败或复制中断现象pg_basebackup报错退出或复制跑一段时间后备库pg_stat_wal_receiver状态变成stopped。原因通常是三类pg_hba.conf没放行复制连接主库max_wal_senders用完备库落后太多需要的 WAL 段已被主库清理。解决先在主库确认pg_hba.conf有replication条目并pg_ctl reloadshow max_wal_senders确认余量把wal_keep_segments调大长期方案是配复制槽replication slot但复制槽会让主库无限保留 WAL必须配合磁盘监控否则磁盘被撑爆是另一个事故。5.2 双主写入导致数据分裂现象切换之后两边都在接受写入数据对不上。原因原主库没有真正停掉就被提升或者 pgpool 的健康检查误判导致主库被降级但应用还连着它。解决切换前强制确认原主库进程已终止backend_flag给主库加ALWAYS_PRIMARY防止被自动降级failover 脚本里加互斥锁避免并发触发。这类问题一旦发生只能靠备份做时间点恢复代价极高。5.3 pgpool 报 all backend nodes are down现象客户端连 pgpool 直接报错show pool_nodes里所有节点status为 down。原因健康检查用户不存在或密码错pgpool 与数据库之间的pool_hba.conf没放行pgpool.conf里的后端地址写错。解决先用psql以健康检查用户的身份直连后端验证检查pool_hba.conf是否包含 pgpool 所在网段确认backend_hostname解析正确。还有一个隐蔽原因pgpool 启动时数据库还没起好健康检查连续失败后节点被标记为 down需要pcp_attach_node手动拉回。5.4 虚拟 IP 漂移后客户端连不上现象failover 日志显示成功VIP 已在新节点但应用连接超时。原因ARP 缓存未刷新或if_up_cmd里的网卡名与实际不符比如配置里写eth0机器上实际是ens33。解决配置arping_cmd发送免费 ARP用ip addr确认网卡名后再写配置切换后手动在客户端arp -d清缓存验证。这个坑在云主机和虚拟化环境里出现频率极高。5.5 归档没开导致备份不可用现象pg_rman 备份成功但恢复时提示找不到需要的 WAL 段。原因archive_mode没开或archive_command指向的目录不可写WAL 归档悄悄失败。解决archive_mode onarchive_command用cp或rsync并保证返回码正确定期查pg_stat_archiver的failed_count这个值大于 0 就说明归档在丢。备份能不能恢复只有在真正恢复一次之后才知道这一点后面还会提。6. 备份与验收怎么证明这套方案真的可用6.1 pg_rman 全量与归档备份脚本pg_rman 的价值在于把基础备份和 WAL 归档串成一条链。配置上先开归档模式再初始化备份目录然后写两个脚本一个做周期全量一个做增量归档。#!/bin/bash # pg_rman_full.sh每周全量备份保留最近 4 份 export PGDATA/data/pgdata export BACKUP_PATH/backup/pgrman pg_rman backup --backup-modefull --with-blocksize32768 -Z pg_rman validate pg_rman delete 4#!/bin/bash # pg_rman_archive.sh每小时归档备份保留 24 份 export PGDATA/data/pgdata export BACKUP_PATH/backup/pgrman pg_rman backup --backup-modearchive --with-blocksize32768 -Z pg_rman validate pg_rman delete 24参数说明--backup-modefull做全量archive只备份新增 WAL-Z开启压缩节省空间validate校验备份文件完整性这一步不能省否则备份出来是坏的就白做了delete N按保留份数清理。注意archive_command里的逻辑运算符要用而不是;否则复制失败也会返回 0PostgreSQL 会以为归档成功继续清理 WAL。6.2 验收清单与答辩要点验收不是「服务起来了」就算完要有可复现的检查项。下面这张表是我交付时必走的顺序学生答辩时也可以照着演示。验收项操作通过标准复制状态主库查pg_stat_replicationstate streaming延迟在秒级内读写分离多次执行只读查询看inet_server_addr()主备均有命中备库只读在备库执行 insert报错拒绝写入主库故障切换杀掉主库 postgres 进程VIP 漂移到节点 2应用连接不中断pgpool 故障停掉持有 VIP 的 pgpool另一节点接管 VIP备份可恢复在测试实例上用 pg_rman 恢复到指定时间点数据与预期一致答辩或交付演示时重点讲三件事为什么选流复制而不是逻辑复制物理一致、延迟低、无需额外组件为什么 pgpool 要双节点部署避免中间件成为新单点备份为什么必须做恢复演练备份成功不等于能恢复。这三问答清楚方案的说服力就够用了。从那以后我每次做完高可用方案都会强制走一遍「拔电源式」验收不是优雅停止服务而是直接 kill 进程看系统在真实故障下的表现。优雅停止掩盖的问题太多了只有真故障才能暴露切换脚本的漏洞。希望这套结构能帮到你把方案从文档变成能跑起来、也敢演示的系统。本文还有配套的精品资源点击获取
