PostgreSQL 高可用、备份恢复与性能排障实战

PostgreSQL 生产运维的难点不在安装，而在持续保证事务安全、复制可控、备份可恢复、连接不过载、表不膨胀，以及出现锁等待和慢 SQL 时能够快速定位。本篇建立一套从架构、备份、监控到故障处置的完整方法。

数据库变更必须先验证备份。不要在生产直接执行来源不明的清理 SQL、重建索引、主从切换或参数模板。

一、先定义可靠性目标

上线前明确：

RPO：最多允许丢失多少数据；
RTO：故障后多久必须恢复；
峰值 TPS、连接数和数据增长；
单机、同城双机还是跨地域灾备；
计划维护和故障切换是否允许短暂只读；
哪些表必须强一致，哪些任务可异步补偿。

高可用不能替代备份。主库误删除会通过 WAL 复制到所有副本，只有独立备份和时间点恢复才能回到误操作之前。

二、理解核心数据路径

事务提交时，关键路径包括共享缓冲区、WAL 缓冲、WAL 落盘、数据页后台刷写和检查点。默认情况下，提交成功通常意味着相关 WAL 已按配置持久化，而不是所有数据页都已写回数据文件。

需要理解的对象：

WAL：重做日志，支撑崩溃恢复、复制和 PITR；
Checkpoint：建立恢复起点，但过于频繁会带来写入抖动；
Autovacuum：清理不可见版本并冻结事务 ID；
Statistics：优化器估算数据分布的基础；
Replication Slot：防止主库过早回收副本或订阅者尚未消费的 WAL。

三、高可用架构

1. 流复制

物理流复制传输 WAL。异步复制延迟小但可能丢失最新事务；同步复制可降低 RPO，但副本或网络异常会增加提交延迟甚至阻塞写入。

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

2. Patroni 与一致性存储

Patroni 常配合 etcd、Consul 或 Kubernetes 实现选主。必须明确：

DCS 多数派是否可用；
最大复制延迟是否允许节点参选；
watchdog 是否启用；
旧主隔离和 STONITH 如何实现；
应用通过 VIP、代理还是服务发现连接新主；
切换后如何确认时间线和副本重建。

3. 连接入口

应用可通过 HAProxy、PgBouncer、DNS 或服务发现访问数据库。DNS TTL 并不能保证所有连接池立即刷新。生产要验证连接池断开、重连、事务重试和只读副本路由。

四、备份与PITR

合格的 PostgreSQL 备份至少包含基础备份和连续 WAL 归档。仅复制数据目录或依赖虚拟机快照，无法保证在线数据库的一致恢复。

常见工具包括 pg_basebackup、pgBackRest、Barman。生产推荐使用支持校验、增量、保留策略、并行和恢复编排的工具。

pg_basebackup -h <primary> -U <replication_user> \
  -D /backup/base -Fp -Xs -P

恢复能力要通过演练证明：

在隔离主机恢复基础备份；
回放 WAL 到指定时间、事务或恢复点；
启动数据库并检查日志；
校验关键表行数、时间范围和业务抽样；
记录实际 RTO、读取速度和所需人员；
防止恢复实例意外对外提供写服务。

备份监控不能只看“任务成功”，还要检查备份大小是否异常、WAL 归档是否连续、校验是否通过、保留周期是否执行以及对象存储是否可读。

五、连接管理与PgBouncer

PostgreSQL 每个连接对应后端进程，连接过多会增加内存、上下文切换和锁管理压力。应用应使用有限连接池，多个应用的连接上限总和必须小于数据库可承受范围。

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT datname, usename, application_name, client_addr, state,
       now() - state_change AS state_age, wait_event_type, wait_event
FROM pg_stat_activity
ORDER BY state_age DESC;

PgBouncer 的事务池模式节省连接最多，但会影响依赖会话状态、临时表、会话级预处理语句或 advisory lock 的应用。选择池模式前必须做兼容性测试。

六、慢SQL诊断

开启 pg_stat_statements，从总耗时、平均耗时、调用次数和共享块读写等多个维度分析，而不是只找单次最慢 SQL。

SELECT queryid, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read, temp_blks_written,
       left(query, 160) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

执行计划使用：

EXPLAIN (ANALYZE, BUFFERS, WAL, VERBOSE)
SELECT ...;

ANALYZE 会真正执行语句，对写操作和大查询有风险。先在副本或测试环境验证，并使用事务和只读保护。

常见原因：

缺少合适索引或索引顺序不合理；
统计信息失真；
返回行数过大；
隐式类型转换；
排序、Hash 或聚合溢出到临时文件；
参数敏感计划；
表膨胀导致更多页面访问；
锁等待被误认为执行慢。

七、锁等待与死锁

SELECT pid, usename, application_name, state,
       wait_event_type, wait_event,
       now() - query_start AS query_age,
       pg_blocking_pids(pid) AS blockers,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
   OR wait_event_type = 'Lock'
ORDER BY query_age DESC;

处置顺序：

找阻塞链的根事务；
确认事务正在处理、空闲还是客户端断连；
联系业务判断提交或终止；
仅在必要时使用 pg_cancel_backend；
pg_terminate_backend 会断开连接，应作为更高风险措施；
复盘事务边界、访问顺序和超时配置。

应用应设置合理的 statement_timeout、lock_timeout 和 idle_in_transaction_session_timeout，但不能用极短超时掩盖性能问题。

八、Autovacuum与表膨胀

MVCC 会保留旧版本，Vacuum 负责回收可重用空间并防止事务 ID 回卷。关闭 Autovacuum 或长期让事务不结束，可能导致表膨胀、查询变慢甚至停库保护。

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

治理重点：

大表按表设置 vacuum 阈值和 scale factor；
检查长事务、闲置事务和复制槽；
调整 worker、cost limit 和维护窗口；
监控冻结年龄；
VACUUM FULL 会获得强锁并重写表，不应作为日常手段；
需要在线重整时评估 pg_repack，仍需提前验证空间和锁行为。

九、索引治理

索引提高读取速度，也增加写入、WAL、缓存和维护成本。不要只依据“未使用索引”快照直接删除，统计会因重启清零，月末或故障场景的低频索引可能非常重要。

检查方向：

重复与包含关系索引；
低选择性索引；
膨胀索引；
无效索引；
外键列是否需要索引；
JSONB、全文、地理数据是否选对 GIN/GiST；
在线创建是否使用 CREATE INDEX CONCURRENTLY。

并发创建失败可能留下 invalid 索引，需要检测并清理。

十、复制延迟与槽位风险

复制延迟要区分发送、写入、刷盘和回放阶段。副本上的长查询可能阻止清理，或因恢复冲突被取消。

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

失活复制槽会让主库永久保留 WAL，最终占满磁盘。应设置告警、明确槽位负责人，并在删除前确认订阅者和恢复要求。

十一、参数调优原则

不要整包复制所谓“高性能配置”。参数需基于内存、CPU、存储延迟、连接数和查询类型确定。

重点包括：

shared_buffers；
work_mem：是每个操作节点可能使用，不是每个连接只用一份；
maintenance_work_mem；
effective_cache_size；
checkpoint_timeout、max_wal_size；
random_page_cost；
max_connections；
并行查询参数；
WAL、归档与复制参数。

每次只调整少量参数，记录假设、基线、观察窗口和回滚值。

十二、常见事故

磁盘即将写满

先区分数据、WAL、日志、临时文件、复制槽和归档失败。不要直接删除 pg_wal 下文件。优先处理归档故障、失活槽位、异常日志或扩容，并保持数据库有安全余量。

主库宕机

确认是否真正不可恢复、最新副本延迟、DCS 状态和旧主隔离；切换后检查时间线、应用写入、序列和后台任务；旧主必须重新跟随，不能直接以主角色上线。

CPU升高

按 queryid、调用量、计划变化、并发、锁、自 Vacuum、检查点和系统 steal 排查。CPU 高可能是业务流量增加，也可能是索引失效造成的结果。

误删除

立即记录时间、事务和业务范围，停止扩大影响；保留 WAL；在隔离环境做 PITR，导出受影响数据并经过业务核对后回补，避免直接覆盖现有主库。

十三、监控清单

实例和连接可用性；
TPS、提交/回滚、P95/P99 延迟；
活跃、空闲、等待和闲置事务连接；
锁等待、死锁、慢 SQL；
缓存命中、临时文件、检查点和 WAL 速率；
表/索引膨胀与 Autovacuum；
复制延迟、槽位保留 WAL；
数据盘、WAL 盘、备份仓库空间和延迟；
备份、归档和恢复演练结果。

十四、上线检查清单

[ ] 明确 RPO/RTO 和切换权限；
[ ] 备份、WAL 归档和恢复演练已通过；
[ ] 高可用组件跨故障域部署；
[ ] 防止旧主回归形成脑裂；
[ ] 连接池总量受控并验证切换重连；
[ ] pg_stat_statements、慢查询和锁监控启用；
[ ] 复制槽、长事务和磁盘水位有告警；
[ ] Autovacuum 未被全局关闭，大表单独调优；
[ ] 变更、DDL 和索引操作有维护方案；
[ ] 账号最小权限、TLS、审计和密钥轮换已落实。

十五、结语

PostgreSQL 的可靠性来自可恢复的备份、可验证的复制、受控的连接和事务，以及对 WAL、锁、Vacuum 和存储行为的理解。遇到问题先保护证据和数据，再恢复服务，最后治理根因；切忌把“切到副本”或“重启数据库”当作故障闭环。

参考资料

PostgreSQL Documentation：https://www.postgresql.org/docs/current/
pgBackRest User Guide：https://pgbackrest.org/user-guide.html
Patroni Documentation：https://patroni.readthedocs.io/

PostgreSQL高可用、备份恢复与性能排障实战