简介面向 PostgreSQL 运维与架构学习者这份方案文档聚焦主从流复制与 pgpool-II 组合下的高可用落地适合需要搭建主备库、实现故障切换、读写分离与连接池管理的数据库从业者参考。文档围绕流复制的同步与异步模式、WAL 日志传输机制、pgpool-II 的连接池、复制与负载均衡能力展开并延伸到备份、环境规划、软件安装、postgres 用户与目录权限配置以及防火墙、SELinux、网络、hosts、时钟同步、sysctl 和资源限制等部署前调整目录层次较完整。包内共 1 个 docx 文件约 773KB便于集中阅读与按章节检索。该文档已有 470 人学习可帮助读者理解主备同步链路、节点初始化、pgpool-II 配置要点与故障模拟验证思路也能作为实际部署前的方案核对清单。1. 从一次主库被 OOM 干掉说起Postgres 主从流复制加 pgpool 到底解决什么半夜两点主库进程被系统 OOM 干掉业务方电话打过来才发现备份还躺在计划里没落地。Postgres 主从流复制解决的是数据这一层WAL 日志实时送到备机主机物理损坏时备机还有一份可读可提升的副本。pgpool 高可用解决的是流量这一层连接池复用、读写分离分摊查询、主库失联时把虚拟 IP 漂走并把备库提起来应用端甚至不用改连接串。这份方案把两条线串成一条完整路径PostgreSQL 10.3 与 12.2 两套版本的主备搭建、pgpool-II 3.6 与 4.1 的双节点 watchdog、pg_rman 的离线全量与归档备份还附了整套脱离网络环境的安装脚本。适合手上有两台机器、要在一两天内把单机库推到能扛住一次主库掉线的 DBA 和后端开发。2. 看懂 WAL 那条链路同步异步怎么选、双节点环境怎么备动手改配置之前得先知道改的那些参数到底作用在哪一段链路上。流复制的所有参数、所有报错几乎都能回溯到「WAL 怎么产生、怎么传、怎么重放」这三件事上。这一章把链路和取舍讲透下一章再落到具体文件。2.1 walsender 到 walreceiverWAL 是怎么从主库跑到备库的主库上任何一次数据变更都会先写 WALwalwriter 进程周期性地把 WAL page 刷到磁盘上的 pg_wal 目录10 之前的版本叫 pg_xlog目录名改了但参数名当时没跟着改。配置了备库之后主库会额外拉起一个 walsender 进程它盯着 WAL 段文件一旦有新记录就顺着 TCP 连接推给备库。备库这边接活的是 walreceiver它收到 WAL 先落本地 pg_wal然后由恢复进程按顺序重放把数据块改成本地一致的状态。注意这两步是分开的——日志落到备库磁盘和备库数据真正变一致之间存在时间差这个差值就是后面排查延迟时要盯的replay_lag。把 WAL 送到备库有两条路。一条是基于文件的归档传递主库写完一个完整的 16MB WAL 段才 cp 到备库备库天然落后一个段恢复粒度粗。另一条就是流复制walsender 主动推送不用等段文件写满延迟通常在一秒以内。9.0 之后有了流复制这条路径基本是默认选择。归档不是被淘汰了它还有别的用处第 6 章会用到。有个坑从这里就该记住如果备库断连期间主库把 WAL 段清理掉了备库再连上来会因为找不到起点而挂掉只能重做基础备份。常见做法是开复制槽max_replication_slots或者用wal_keep_segments多留一些段。但复制槽是个双刃剑——备库长期不回来主库的 pg_wal 会一直涨到把磁盘吃满这是血泪经验监控必须跟上。2.2 同步还是异步synchronous_commit 的五个档位怎么选流复制默认是异步的。主库提交事务时把 WAL 写给 walsender 就返回不等备库确认。好处是写入性能不受备库影响代价是主库突然断电时那部分还在网络上的 WAL 就丢了通常是一秒内的数据。同步复制要两个条件同时成立synchronous_standby_names设成非空值synchronous_commit不能是 off。满足之后主库的提交要等到指定的备库确认才算成功事务响应时间里就多了一次网络往返。档位等待到哪一步适用场景off只写本地 WAL 缓冲不等刷盘批量导入、可重跑的计算任务local等本地 WAL 刷盘单机可靠性兜底remote_write等备库把 WAL 写进操作系统缓存折中方案性能损失小on等备库把 WAL 刷到磁盘默认大多数主备场景remote_apply等备库把 WAL 重放完成读一致性要求极高的场景synchronous_standby_names的写法有讲究。1 (node2)表示列表里任意一个备库确认即可*表示所有列出的备库都确认2 (node2, node3)是至少两个。单主单备直接用1 (node2)其中 node2 要和备库primary_conninfo里的application_name对上对不上就会一直等表现出来就是主库所有写操作卡死不返回。我一般会跟业务方确认一句话能不能接受主库宕机丢最后一秒的数据。能接受就走异步性能最好不能接受再上同步同时把wal_sender_timeout和wal_receiver_timeout调好避免网络抖动时主库被一个失联的备库拖死。2.3 双节点的系统层准备关服务、配 hosts、调内核PostgreSQL 对系统层其实不挑但几个默认值不改装完跑起来会莫名其妙。防火墙和 SELinux 是第一个要处理的双节点之间 5432、9999、9898、9694 这些端口都要通与其一条条放行不如在纯内网环境里直接关掉省掉后面一半的排查成本。# 双节点统一执行关闭防火墙与 SELinux内网环境 systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i s/^SELINUXenforcing/SELINUXdisabled/ /etc/selinux/config # hosts 必须双向解析pgpool 的 watchdog 和 failover 脚本都依赖主机名 cat /etc/hosts EOF 192.168.100.11 node1 192.168.100.12 node2 192.168.100.100 pgpool-vip EOFhosts 这一条看着不起眼但 failover 脚本里用ssh postgresnode2做远程操作主机名解析不通就会卡在切换那一步日志里只留一句连接超时很难往 DNS 方向想。内核参数按机器内存调整下面的值对应 64GB 内存的机器vm.overcommit_memory 2要配合overcommit_ratio一起用否则 PostgreSQL 启动时申请共享内存会直接被拒。cat /etc/sysctl.conf EOF kernel.shmmax 68719476736 kernel.shmall 4294967296 vm.overcommit_memory 2 vm.overcommit_ratio 80 vm.swappiness 10 net.ipv4.tcp_keepalive_time 60 net.ipv4.tcp_keepalive_intvl 10 net.ipv4.tcp_keepalive_probes 6 EOF sysctl -p # 资源限制连接数一多默认 1024 的 nofile 会先崩 cat /etc/security/limits.conf EOF postgres soft nofile 65535 postgres hard nofile 65535 postgres soft nproc 65535 postgres hard nproc 65535 EOFtcp_keepalive_time从默认的 7200 秒降到 60 秒是为了让主备之间半死不活的连接尽快被内核探测到并断开不然备库那边进程还在、网络已经不通主库的 walsender 会挂很久。2.4 目录、用户与免密给后续所有脚本铺路所有组件统一用 postgres 用户跑安装目录、数据目录、归档目录、备份目录一次规划到位。目录权限是个高频翻车点数据目录必须是 700 且属主为 postgrespg_basebackup 往一个 755 或者属主不对的目录里写会直接失败。# 双节点统一建用户、建目录、设权限 groupadd -g 1001 postgres useradd -g postgres -u 1001 -m -s /bin/bash postgres echo Pg_Init#2024 | passwd --stdin postgres mkdir -p /usr/local/pgsql /usr/local/pgpool mkdir -p /data/pgsql/data /data/pgsql/archive /backup/pgrman chown -R postgres:postgres /usr/local/pgsql /usr/local/pgpool /data /backup chmod 700 /data/pgsql/data chmod 750 /data/pgsql/archiveSSH 免密是 pgpool 的recovery_1st_stage_command和 failover 脚本能跑起来的前提两个节点之间必须能互信。很多人只做了 postgres 用户对自己的免密忘了对端结果故障切换时脚本静默失败——pgpool 认为切换完成了实际上备库根本没被提升。# 双节点各执行一次然后互发公钥 su - postgres -c ssh-keygen -t rsa -N -f ~/.ssh/id_rsa su - postgres -c ssh-copy-id -i ~/.ssh/id_rsa.pub postgresnode1 su - postgres -c ssh-copy-id -i ~/.ssh/id_rsa.pub postgresnode2 # 验证必须两个方向都不需要密码 su - postgres -c ssh -o BatchModeyes node2 dateBatchModeyes这个参数建议一直带着它会让 SSH 在需要交互时直接失败而不是等待输入脚本里用它做连通性预检比事后翻日志快得多。3. 把备库拉起来postgresql.conf、pg_hba.conf 到 pg_basebackup 的完整改动环境层铺好之后主备搭建本身并不复杂麻烦的是版本差异和参数遗漏。这一章按「改主库配置 → 建复制用户 → 拉备库 → 自检」的顺序走一遍参数逐行说明白。3.1 主库参数postgresql.conf 与 pg_hba.conf 的逐行改动先改主库的postgresql.conf。下面这段是 12.x 的写法10.x 基本通用差别在 13 之后wal_keep_segments改名为wal_keep_size单位从段数变成容量。# /data/pgsql/data/postgresql.conf listen_addresses * port 5432 max_connections 500 # --- 流复制核心 --- wal_level replica # 至少要 replicalogical 是为逻辑复制准备的 max_wal_senders 10 # 允许的 walsender 上限要比备库数多留余量 max_replication_slots 10 wal_keep_segments 512 # 13 起改为 wal_keep_size 8GB hot_standby on # 备库可读的关键不设 true 备库连查询都接不了 # --- 归档第 6 章做 PITR 要用 --- archive_mode on archive_command test ! -f /data/pgsql/archive/%f cp %p /data/pgsql/archive/%f # --- 同步复制先注掉搭完跑通再打开 --- # synchronous_standby_names 1 (node2) # synchronous_commit onwal_level改完必须重启不能只 reload。archive_command里那个test ! -f的判断不要省重放归档时同一段 WAL 可能被请求两次没有判断会覆盖失败并让归档进程一直报错。接着是pg_hba.conf。这里最容易出错的地方在于pgpool 也需要以普通用户身份连后端库去查pg_stat_replication所以除了给备库开复制权限还得给 pgpool 节点开查询权限。# /data/pgsql/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host replication repl 192.168.100.12/32 md5 host all pgpool_check 192.168.100.0/24 md5 host all appuser 192.168.100.0/24 md5repl是专用于流复制的账号pgpool_check是 pgpool 的健康检查账号两个分开出事时看连接来源就能判断是哪一层在连库。改完执行pg_ctl reload即可不用重启。3.2 建复制用户、拉备库pg_basebackup 的两个关键开关主库启动后先建账号。复制权限必须显式给光有 LOGIN 是不够的。-- 主库执行 CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD Repl_2024#pg; CREATE ROLE pgpool_check WITH LOGIN PASSWORD Chk_2024#pg; GRANT pg_monitor TO pgpool_check; -- 10 及以上版本查 pg_stat_replication 需要pg_monitor这个内置角色是 10 才有的10 之前的版本只能给 SUPERUSER 或者手工 GRANT SELECT 到各个视图。不给权限的话pgpool 判断不出主备角色读写分离会直接失效。然后到备库上拉数据。备库的$PGDATA必须是空的且属主是 postgres。# 备库执行$PGDATA /data/pgsql/data su - postgres pg_basebackup -h 192.168.100.11 -p 5432 -U repl \ -D /data/pgsql/data \ -Fp -Xs -P -R -v-Fp是普通文件格式-Xs表示流式传输 WAL备份过程中产生的 WAL 一并带走不用额外配归档-P显示进度-R是最省事的一个开关——它会自动在备库生成standby.signal并把主库连接信息写进postgresql.conf。只有 12 及以上版本支持-R10.x 上执行会直接报错得手工写recovery.conf。-R生成的连接串长这样密码会明文落在文件里所以postgresql.conf的权限要保持 600或者改用.pgpass。# 由 -R 自动写入备库 postgresql.conf 的内容 primary_conninfo userrepl passwordRepl_2024#pg host192.168.100.11 port5432 application_namenode2application_namenode2必须手工补上-R默认不带。少了它主库上配的synchronous_standby_names 1 (node2)就永远匹配不到备库同步复制形同虚设。3.3 12 版本前后差在哪recovery.conf 与 standby.signal这套方案同时覆盖 10.3 和 12.2两个版本在备库标识上的做法完全不同混着配是最常见的翻车原因。对比项12 之前如 10.x12 及以后如 12.2备库标志$PGDATA/recovery.conf内写standby_mode on$PGDATA/standby.signal空文件连接串位置recovery.conf里的primary_conninfopostgresql.conf里的primary_conninfo提升主库pg_ctl promote生成recovery.donepg_ctl promote删除standby.signalWAL 目录名10 起已是pg_wal9.6 及以前为pg_xlogpg_wal保留 WAL 参数wal_keep_segments段数12 仍是wal_keep_segments13 起改wal_keep_size10.x 上手工写recovery.conf的内容standby_mode on primary_conninfo host192.168.100.11 port5432 userrepl passwordRepl_2024#pg application_namenode2 recovery_target_timeline latestrecovery_target_timeline latest这一行是给主备切换准备的备库会顺着时间线往上找切主之后不用改配置就能重新跟上。12.x 上则是先确认standby.signal存在再检查postgresql.conf里的primary_conninfo# 备库执行 ls -l /data/pgsql/data/standby.signal # 必须存在且为空文件 grep primary_conninfo /data/pgsql/data/postgresql.conf有个反直觉的点12 上如果误建了recovery.conf数据库启动时会直接报错退出日志里写得很含糊。看到备库起不来又查不出原因第一件事就是看$PGDATA下有没有多出这个文件。3.4 复制状态自检四条 SQL 判断链路是否健康备库用pg_ctl start起来之后别急着装 pgpool先把复制链路验证透。主库上执行下面这条能看到备库的 IP、状态、同步模式和字节差。-- 主库执行 SELECT client_addr, state, sync_state, sent_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication;state应该是streaming如果是startup或者catchup说明备库还在追。sync_state在异步模式下是async配好同步之后主库变sync、备库变potential这个变化说明同步链路通了。lag_bytes持续增长到几十 MB 以上就要查网络和备库 IO 了。备库侧再确认两件事-- 备库执行 SELECT status, receive_start_lsn, written_lsn, flushed_lsn FROM pg_stat_wal_receiver; SELECT pg_is_in_recovery(); -- 必须返回 tpg_stat_wal_receiver返回空行说明 walreceiver 没起来绝大多数是primary_conninfo写错或者pg_hba.conf没放行。pg_is_in_recovery()返回f说明这个实例是主库角色备库标识文件没生效这时候它会拒绝接收任何流过来的 WAL。最后做个写入验证-- 主库 CREATE TABLE repl_check(id int primary key, ts timestamptz default now()); INSERT INTO repl_check(id) VALUES (1); -- 备库只读 SELECT * FROM repl_check;备库能查到那条记录流复制这条链路就算通了。查不到但pg_stat_replication显示 streaming通常是备库还在应用 WAL等一两秒再查。4. pgpool-II 接管 9999 端口连接池、读写分离与 watchdog 双活配置流复制解决了数据副本但应用还是得自己知道连哪台。pgpool-II 插在应用和 PostgreSQL 之间对外只暴露 9999 一个口内部按语句类型分发。这一章的配置量最大也最容易配出一堆「看起来启动了但什么都不工作」的假象。4.1 它到底做了什么连接池、读写分离、超限排队、故障切换连接池是最实在的一项。PostgreSQL 每个连接是一个独立进程500 个连接就是 500 个进程上下文切换开销很大。pgpool 保持一批到后端的常驻连接前端来一个请求就借一个走用完还回去连接建立的开销被摊掉了。读写分离靠 SQL 语句解析。SELECT走备库INSERT/UPDATE/DELETE走主库主库压力能降下来一大截。但要注意它不是万能的——带写操作的 CTE、调用了写函数的 SELECT、SELECT ... FOR UPDATE这些必须走主库pgpool 靠/*NO LOAD BALANCE*/注释来人工干预判断失误会导致读到脏数据或者写请求被发到只读备库。超过连接数的处理方式也值得说。PostgreSQL 自己的max_connections到顶就直接拒绝新连接pgpool 则是把请求放进队列等等不到再报错。对于瞬时并发突增的场景排队比直接拒绝体验好但队列长度要设num_init_children和max_pool的乘积决定了实际能撑多少并发。4.2 pgpool.conf 核心参数后端节点、健康检查与读写分离pgpool 4.1 的配置文件在/usr/local/pgpool/etc/pgpool.conf3.6 的参数名基本一致个别项有出入。下面是主库节点上的关键片段。# 对外监听 listen_addresses * port 9999 pcp_listen_addresses * pcp_port 9898 # --- 后端节点0 是主库1 是备库 --- backend_hostname0 192.168.100.11 backend_port0 5432 backend_weight0 1 backend_data_directory0 /data/pgsql/data backend_flag0 ALLOW_TO_FAILOVER backend_hostname1 192.168.100.12 backend_port1 5432 backend_weight1 1 backend_data_directory1 /data/pgsql/data backend_flag1 ALLOW_TO_FAILOVER # --- 主备角色识别读写分离全靠它 --- sr_check_user pgpool_check sr_check_password Chk_2024#pg sr_check_period 10 sr_check_database postgres # --- 健康检查 --- health_check_period 10 health_check_timeout 20 health_check_user pgpool_check health_check_password Chk_2024#pg health_check_database postgres health_check_max_retries 3 # --- 连接数 --- num_init_children 100 max_pool 4 child_life_time 300 connection_life_time 0 # --- 负载均衡 --- load_balance_mode on ignore_leading_white_space on statement_level_load_balance off # --- 故障切换 --- failover_command /usr/local/pgpool/bin/failover.sh %d %H %P %D %m %M %H %R follow_master_command /usr/local/pgpool/bin/follow_master.sh %d %H %P %D %m %M %H %R recovery_1st_stage_command basebackup.sh recovery_2nd_stage_command search_primary_node_timeout 300 auto_failback on # --- 认证 --- enable_pool_hba on pool_passwd pool_passwdbackend_weight0/1都是 1 的时候读请求会被均分到两个节点。如果备库配置明显比主库差把备库的权重调低比如 0.3能避免备库被读请求压垮。sr_check_period是 pgpool 判断谁是主谁是备的周期靠查pg_stat_replication实现。这个值设太小会增加后端负担设太大会让切换后角色识别变慢10 秒是个常用值。num_init_children × max_pool是实际能接的并发上限100 × 4 400 个后端连接。超过这个数的请求会在 pgpool 里排队队列满了才报错。这个乘积不能超过后端max_connections否则会出现 pgpool 认为还有余量、后端已经拒绝的尴尬局面。4.3 failover 脚本切换那一刻到底执行了什么failover_command是 pgpool 检测到节点宕机后调用的脚本参数是位置占位符含义必须记清楚写错一个就会在关键时刻掉链子。占位符含义%d失效节点的 ID0 或 1%H失效节点的主机名%P失效节点的端口%D失效节点的数据目录%m新主库的节点 ID%M新主库的数据目录%R新主库的主机名脚本本身很短但每一行都关乎能不能切过去。#!/bin/bash # /usr/local/pgpool/bin/failover.sh # $1%d 失效节点ID $2%H 失效主机名 $3%P 端口 $4%D 数据目录 # $7%R 新主库主机名 FAILED_ID$1 FAILED_HOST$2 NEW_MASTER_HOST$7 LOG/var/log/pgpool/failover.log echo $(date %F %T) failover start: failed$FAILED_ID($FAILED_HOST) new$NEW_MASTER_HOST $LOG # 只有主库节点0挂掉才需要提升备库 if [ $FAILED_ID 0 ]; then ssh -T -o BatchModeyes postgres$NEW_MASTER_HOST \ /usr/local/pgsql/bin/pg_ctl promote -D /data/pgsql/data $LOG 21 if [ $? -ne 0 ]; then echo $(date %F %T) promote failed on $NEW_MASTER_HOST $LOG exit 1 fi fi echo $(date %F %T) failover done $LOG exit 0pg_ctl promote在 12 上会删除standby.signal让实例转为主库10 上则把recovery.conf改名成recovery.done。退出码必须正确返回pgpool 靠它判断切换是否成功——脚本里exit 0写死不管结果的做法会让 pgpool 以为切换完成实际上根本没有主库能写。follow_master_command在原主库恢复回来之后执行负责把它重新拉成备库。这一步用pg_rewind比pg_basebackup快得多前提是原主库开启了wal_log_hints on或者data_checksums。4.4 watchdog 与虚拟 IP两个节点怎么避免脑裂pgpool 自身也不能是单点所以两个节点上都装 pgpool用 watchdog 互相监控对外提供同一个虚拟 IP。主 pgpool 持有 VIP备 pgpool 等着主的一挂 VIP 立刻漂过去。# 节点1 的 pgpool.conf节点2 改 wd_hostname 和心跳目标 use_watchdog on wd_hostname 192.168.100.11 wd_port 9000 wd_priority 1 delegate_IP 192.168.100.100 if_up_cmd /usr/local/pgpool/bin/if_up.sh $_IP_$ $_DELEGATE_$ if_down_cmd /usr/local/pgpool/bin/if_down.sh $_IP_$ arping_cmd arping -U $_IP_$ -w 1 -I eth0 # 心跳监控对端 pgpool 和 VIP 所在网络 heartbeat_destination0 192.168.100.12 heartbeat_destination_port0 9694 heartbeat_device0 eth0 # 集群成员 other_pgpool_hostname0 192.168.100.12 other_pgpool_port0 9999 other_pgpool_wd_port0 9000if_up_cmd里的$_IP_$和$_DELEGATE_$是 pgpool 的预定义变量会被替换成带子网掩码的地址和裸 IP脚本里两个都要用上。对应脚本#!/bin/bash # /usr/local/pgpool/bin/if_up.sh $1带掩码的IP $2裸IP /sbin/ip addr add $1 dev eth0 /usr/sbin/arping -U $2 -w 1 -I eth0 exit 0arping这一步不能省。VIP 漂过去之后交换机和业务服务器上的 ARP 缓存还指向旧机器会出现「VIP 在节点2 上但流量还是打到节点1」的玄学现象加一条免费 ARP 通告能让网络设备立刻更新表项。eth0要换成实际网卡名写错了ip addr add会成功但 arping 失败脚本照样返回 0问题被静默吞掉。4.5 认证三件套pcp.conf、pool_passwd、pool_hba.confpgpool 有三套独立的认证文件分别管管理命令、数据库用户和接入控制缺一个就会出现某个功能能用、另一个不能用的情况。pcp.conf管的是pcp_*系列管理命令用户名密码用 md5 存。# 生成 pcp 用户的 md5 串 echo pcpadmin:{MD5} /usr/local/pgpool/etc/pcp.conf # 实际用 pg_md5 生成 /usr/local/pgpool/bin/pg_md5 pcpadminpool_passwd存的是数据库用户的认证串。pgpool 以 md5 方式连后端时前端传来的密码要先在本地比对一次比对通过才放行到后端。# 方法一交互式输入密码 /usr/local/pgpool/bin/pg_md5 -m -u appuser # 方法二密码直接跟在后面 /usr/local/pgpool/bin/pg_md5 -m -u appuser App_2024#pg # 验证生成结果 grep appuser /usr/local/pgpool/etc/pool_passwdpool_hba.conf控制谁能连 pgpool 的 9999 端口格式和 PostgreSQL 的pg_hba.conf一样但经常被忽略。# /usr/local/pgpool/etc/pool_hba.conf host all all 192.168.100.0/24 md5 host all all 127.0.0.1/32 trust这里和pg_hba.conf是两个独立的门禁。pgpool 的pool_hba.conf放行了、后端的pg_hba.conf没放行报的是no pg_hba.conf entry for host但日志出现在后端库上很容易查错方向。最后是 pgpool 的元数据函数show pool_nodes和故障恢复都依赖它。# 在主库上安装 pgpool 相关函数 psql -U postgres -d postgres -f /usr/local/pgpool/share/postgresql/pgpool-regclass.sql psql -U postgres -d postgres -f /usr/local/pgpool/share/postgresql/pgpool-recovery.sql psql -U postgres -d postgres -c CREATE EXTENSION pgpool_recovery;启动之后用三条命令确认状态/usr/local/pgpool/bin/pgpool -n -D -f /usr/local/pgpool/etc/pgpool.conf psql -h 192.168.100.100 -p 9999 -U appuser -d postgres -c show pool_nodes; /usr/local/pgpool/bin/pcp_node_info -h 192.168.100.11 -U pcpadmin -n 1show pool_nodes里role列要显示primary/standbystatus列是up。如果role都显示unknow基本就是sr_check_user密码错或者权限不够。5. 避坑与排查五个把一个通宵搭进去的现场方案文档里最难写、也最有价值的就是这一节。下面五条都是搭建和运维过程中反复出现的问题按现象、原因、解决三个维度拆开讲。5.1 搭建阶段备库起不来、主库卡死现象一备库启动直接失败日志里只有FATAL: could not open file pg_wal/...或者pg_basebackup报目录非空。原因通常是$PGDATA里已有残留数据或者目录属主不是 postgres。pg_basebackup -D要求目标目录为空而且写入用户必须是运行 PostgreSQL 的用户。另一个高发原因是版本不一致——主库 12.2 而备库装的 10.3基础备份能拉下来启动时一定会崩。解决方式是重建目录再拉一次同时把版本核对写进流程# 备库清空并重设权限 rm -rf /data/pgsql/data/* chown -R postgres:postgres /data/pgsql chmod 700 /data/pgsql/data # 两个节点核对版本必须完全一致 /usr/local/pgsql/bin/postgres --version之后再执行pg_basebackup。养成习惯拉备库前先跑一遍版本比对比事后翻日志省半小时。现象二主库所有写操作挂起不返回应用侧大量超时pg_stat_activity里全是wait_event SyncRep。这是典型的同步复制等待。synchronous_standby_names里配的application_name和备库实际连上来的名字对不上主库一直在等一个永远不存在的备库确认。也可能是备库已经断开但主库还没超时判定。解决分两步先确认备库名字-- 主库 SELECT application_name, state, sync_state FROM pg_stat_replication; SHOW synchronous_standby_names;对不上就改备库primary_conninfo里的application_name改完重启备库。如果本来就是备库失联主库要等到wal_sender_timeout默认 60 秒才会把 walsender 断掉这期间所有写请求都在等。生产环境建议把这个值调到 10 到 30 秒之间损失一点容忍度换更快的自愈。5.2 pgpool 阶段连不上、识别错、认证绕圈现象三应用连 9999 端口报no pg_hba.conf entry for host 192.168.100.11但这个 IP 明明在pg_hba.conf里加过。要看来源 IP 到底是谁。pgpool 连后端用的是节点的物理 IP不是 VIP很多人加的是 VIP 那一行自然对不上。另外如果 pgpool 和数据库部署在同一台机器也可能走的是127.0.0.1而这条规则被注释掉了。解决是把所有可能的来源都放行并且把pool_hba.conf和pg_hba.conf同步维护# pg_hba.conf host all pgpool_check 192.168.100.11/32 md5 host all pgpool_check 192.168.100.12/32 md5 host all appuser 192.168.100.0/24 md5改完pg_ctl reload生效然后从 pgpool 节点上用psql -h 192.168.100.11 -U pgpool_check直接连一次后端库绕开 pgpool 单独验证这一层能立刻定位问题在前端还是后端。现象四show pool_nodes里两个节点的role都是unknown读写分离没生效所有查询都压在主库上。原因通常是sr_check_user权限不足。12 上这个用户需要pg_monitor角色才能查pg_stat_replication只给普通 LOGIN 权限是查不到的。另一个可能是backend_hostname写成了 VIPpgpool 连 VIP 再到后端来源 IP 变成节点自己容易绕进认证死循环。解决办法-- 主库 GRANT pg_monitor TO pgpool_check; -- 验证该用户能否查到复制状态 \c postgres pgpool_check SELECT count(*) FROM pg_stat_replication;同时把backend_hostname0/1全部改成节点的真实 IP不要用 VIP。VIP 只对外对内一律走物理地址这条规则能避免很多莫名其妙的连接问题。5.3 切换阶段VIP 漂了但服务没恢复现象五模拟主库宕机后pcp_watchdog_info显示 VIP 已经漂到节点2但应用连上之后报all backend nodes are down。这个报错说明 pgpool 连不上任何后端节点。常见原因有两个备库虽然拿到了 VIP但并没有被pg_ctl promote提升为主库仍然处于只读状态pgpool 检测到它不能写就标记成 down或者failover_command里的 SSH 因为BatchMode之外的交互比如首次连接的主机指纹确认卡住脚本超时被杀pgpool 认为切换失败。排查顺序是先在节点2 上确认角色su - postgres -c /usr/local/pgsql/bin/pg_ctl status -D /data/pgsql/data ls -l /data/pgsql/data/standby.signal # 提升成功后这个文件应该消失 psql -h 192.168.100.100 -p 9999 -U appuser -c SELECT pg_is_in_recovery();返回f表示已经是主库。返回t说明提升没执行去看/var/log/pgpool/failover.log里的具体报错。修复后把节点重新挂回集群/usr/local/pgpool/bin/pcp_attach_node -h 192.168.100.11 -U pcpadmin -n 0这里有个血泪教训切换前几天一定要在两个节点之间手工跑一遍ssh -o BatchModeyes node2 date确保免密和指纹都过了。免密这事儿配的时候能过重启之后~/.ssh/known_hosts一变就过不去切换时才发现就晚了。6. 用 pg_rman 补上离线备份再做一次真刀真枪的切换演练流复制防的是机器坏防不了误删表。DELETE忘了加WHERE、脚本跑错库这些操作会瞬间同步到备库主备一起没。所以在流复制之外还要有一条独立于主备链路的离线备份通道这就是 pg_rman 的位置。前提是归档已经打开。第 3 章配的archive_mode on和archive_command就是为这里准备的先确认归档真的在跑SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;failed_count一直涨说明archive_command有问题多半是归档目录权限或者磁盘满了。接着装 pg_rman版本必须和数据库主版本严格对齐——12.2 的库就装 pg_rman 1.3.12 以上版本错位会导致备份直接失败。# 初始化备份目录并做一次全量 export PGDATA/data/pgsql/data pg_rman init -B /backup/pgrman --with-serverlog pg_rman backup --backup-modefull -B /backup/pgrman -C -Z pg_rman validate -B /backup/pgrman pg_rman show -B /backup/pgrman-C是备份前做一次检查点能让备份集更整齐-Z压缩数据文件磁盘紧张的时候很有用。validate这一步千万别省没有验证过的备份等于没有备份pg_rman show里Status列显示OK才算数。日常用脚本跑增量每周一次全量保留策略按磁盘容量定我一般留两份全量加七天增量。备份配好之后最值得做的一件事是切换演练。流程很朴素在业务低峰停掉主库的 postgres 进程观察 pgpool 日志里 failover 是否触发、VIP 是否漂到节点2、备库是否被提升、应用连接是否在几十秒内恢复。演练完再把本文还有配套的精品资源点击获取