PostgreSQL 运维实战系列，第四期：故障排查与深度诊断实战

0. 前言：问题总会来，诊断能力决定你能走多远

前三期我们覆盖了生产环境搭建、高可用架构和性能调优。但无论你的系统建得多稳固，问题总会来——慢查询、连接风暴、磁盘写满、备库延迟、死锁……区别在于：优秀的 DBA 在故障还在“症状期”就发现了它；平庸的 DBA 在“故障期”才开始排查；而糟糕的 DBA 在“灾难期”才被叫醒。

数据库专业人员在工作中常遇到诸如响应速度缓慢、死锁增多、复制Slot异常、大表膨胀甚至数据库崩溃等问题。诊断是 DBA 最核心的技能，它不来自于天赋，而来自系统的方法论和反复的演练。

本期聚焦 如何系统性地诊断 PostgreSQL 生产故障，涵盖：慢查询全链路诊断、锁阻塞与死锁分析、表膨胀判定与 VACUUM 急救、WAL 堆积根因定位、日志统一分析与巡检工具链——形成一套从“发现”到“定位”再到“修复”的完整方法论。

1. 慢查询诊断：从“慢”到“哪里慢”

1.1 发现慢查询：生产环境需要三层监控

第一层——日志自动捕捉：通过 auto_explain 模块自动记录所有慢查询的执行计划，无需人工干预。配置如下：

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000           # 记录超过 1 秒的查询
auto_explain.log_analyze = on                  # 输出真实执行时间（有性能开销）
auto_explain.log_buffers = on                  # 查看读了多少数据块
auto_explain.log_timing = on
auto_explain.log_nested_statements = on

auto_explain 尤其适用于大型应用中跟踪那些难以手动发现的非优化查询。将 log_min_duration 设为 0 会记录所有查询计划，有性能开销，生产环境建议设为 1000–5000ms。

第二层——实时统计聚合：pg_stat_statements 无需等慢查询发生，随时可查询过去一段时间的高负载模式：

-- 找到总耗时最高的查询（全局性能热点）
SELECT queryid, query, calls, 
       total_exec_time, mean_exec_time, 
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 10;

第三层——业务链路关联：在应用日志中记录请求 ID 并回填数据库日志，实现“一个慢页面 → 一个慢 SQL → 一个执行计划”的完整溯源链路。

1.2 EXPLAIN 进阶：BUFFERS 才是真相

标准 EXPLAIN ANALYZE 告诉你“有多慢”，但 EXPLAIN (ANALYZE, BUFFERS) 才能告诉你“慢在哪儿”。BUFFERS 揭示了每个执行节点读取了多少数据块，这是判断 I/O 瓶颈的关键线索。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders WHERE status = 'pending';

输出解读：

Seq Scan on orders (actual time=523.791..523.791 rows=0 loops=1)
  Buffers: shared read=24816

shared read=24816 意味着扫描触发了 24816 次磁盘 I/O——这是优化的主攻方向。通常读取的块数远多于实际返回的行数，说明索引条件未能有效缩小范围。

优化器估测偏差排查：EXPLAIN (ANALYZE, BUFFERS) 输出中的 actual rows 与计划估算的 rows 字段对比。如果偏差超过 10 倍（如估算返回 5 行，实际返回 5000 行），说明统计信息过时或因列间关联导致选择率计算错误，应及时执行 ANALYZE 或创建扩展统计。

1.3 auto_explain 实战：捕获“灵异慢查询”

某些间歇性慢查询难以手动复现——凌晨的批处理变慢。解决方案：启用 auto_explain.log_min_duration 生产级阈值设置，让数据库在查询运行时自动记录当时的执行计划。生产环境中建议配置 log_analyze，但需注意 auto_explain.log_timing 会因频繁读取系统时钟显著增加开销，可按需关闭。

现场回放的终极手段：将清理后的查询语句与优化后的执行计划固化后，利用 pg_hint_plan 恢复对数据库的控制权。

/*+ SeqScan(orders) */
SELECT * FROM orders WHERE status = 'pending' AND created_at > now() - interval '7 days';

2. 锁阻塞与死锁：生产环境的最大隐形杀手

锁问题在单节点开发时几乎看不出来，一到生产高并发环境就暴露。处理此类标准的流程遵循“排查定位 → 处理解锁 → 预防优化”的闭环。

2.1 阻塞源定位三步法

第一步——找出在等锁的进程：

SELECT pid, usename, application_name, state, 
       wait_event_type, wait_event,
       now() - query_start AS duration,
       substring(query, 1, 100) AS query_preview
FROM pg_stat_activity 
WHERE wait_event_type = 'Lock' AND state != 'idle';

如果某个进程处于 active 状态，但同时 wait_event 和 wait_event_type 字段不是 NULL，它正在等待锁释放。

第二步——追到阻塞的“根进程”：

SELECT blocked.pid AS blocked_pid, 
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, 
       blocking.query AS blocking_query
FROM pg_stat_activity blocked 
JOIN pg_locks l1 ON l1.pid = blocked.pid 
JOIN pg_locks l2 ON l2.locktype = l1.locktype 
                AND l2.relation IS NOT DISTINCT FROM l1.relation 
                AND l2.pid != l1.pid
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid
WHERE NOT l1.granted;

结果中 blocking_pid 即为阻塞源。

第三步——处理阻塞根据现场决策：

-- 终止阻塞进程（需超级用户权限）
SELECT pg_terminate_backend(<阻塞源的PID>);
-- 或优雅取消查询（让事务继续，更好）
SELECT pg_cancel_backend(<PID>);

终止阻塞进程会将整个事务回滚，这可能导致部分数据未提交，但也确保了系统的其他部分不再卡死。pg_cancel_backend 仅取消当前查询而不结束会话，需要根据业务情况做选择。

2.2 真正理解“锁升级”：全表扫描的危险

生产中最常见的锁事故是：UPDATE 语句因无索引而执行全表扫描，在扫描过程中逐行持有行锁。一旦扫描了几百万行，实质上已达到表级锁的效果，阻塞了所有并发的读写操作。

排查方法：检查 pg_stat_activity.wait_event='Lock' 且 state='active'，以及查询的扫描方式预估。

长期对策：确保 UPDATE 和 DELETE 的 WHERE 条件有合适的索引，避免表级锁过期。对超大的 UPDATE 或 DELETE 使用批量小事务削减锁持有时长。最危险的锁模式是 AccessExclusiveLock——由 ALTER TABLE、DROP TABLE 等 DDL 操作持有，它会阻塞所有其他操作。

2.3 死锁诊断与根因消除

当 PostgreSQL 检测到死锁时，会自动终止其中一个事务并在日志中记录详细信息：

ERROR: deadlock detected
DETAIL: Process X waits for ShareLock on transaction Y; blocked by process Z.
Process Z waits for ShareLock on transaction X; blocked by process Y.
HINT: See server log for query details.

死锁的本质：两个或多个进程各自持有一部分资源，同时又在等待对方释放资源，循环等待。

诊断查询：

-- 查看正在等待锁的进程详情
SELECT pid, usename, state, wait_event, wait_event_type,
       age(now(), query_start) AS waiting_time,
       query
FROM pg_stat_activity 
WHERE wait_event_type = 'Lock';

消除死锁最有效的方法：让应用在所有事务中按相同的顺序访问资源。例如，多个表被频繁交叉更新，固定访问顺序 orders → order_items → payments 可以在根本上消除死锁。

超时兜底：设置 deadlock_timeout = 1s（默认 1 秒），让死锁检测更及时。

3. 表膨胀与 VACUUM 急救

3.1 膨胀的本质：死元组积累与清理滞后

在 MVCC 机制下，UPDATE 或 DELETE 操作并不立即回收空间，而是在磁盘上留下死元组。autovacuum 本来负责清理，但在高频写入场景下，autovacuum 会落后于死元组生成的速度。这导致物理表远超逻辑数据量，拖慢全表扫描，污染操作系统缓存，危害性不低于磁盘写满。

3.2 膨胀诊断：精确测量每一张表

以下 SQL 可快速评估表膨胀风险：

-- 死元组占比最高的表（膨胀晴雨表）
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_dead_tup + n_live_tup, 0) * 100, 2) AS dead_ratio,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000 
ORDER BY n_dead_tup DESC;

-- 事务 ID 年龄检查（回卷风险的硬性指标）
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database 
ORDER BY xid_age DESC;

3.3 表级精细化 VACUUM 策略

与其使用全局统一的 autovacuum 设置生存，不如针对每一张活跃表手工调参：

-- 高频更新的大表：激进策略
ALTER TABLE order_items SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 5000,
    autovacuum_analyze_scale_factor = 0.01
);
-- 静态只追加表：保守策略
ALTER TABLE event_logs SET (
    autovacuum_vacuum_scale_factor = 0.2,
    autovacuum_analyze_scale_factor = 0.1
);

出现下列情况的任何一种时，考虑执行手动 VACUUM：

某表死元组占比超过 10% 且连续一周没有 autovacuum 记录；
age(datfrozenxid) 超过 15 亿（接近 20 亿上限）；
某张业务表的查询响应时间从几毫秒突然上升到几百毫秒。

手动 VACUUM 正确姿势：

-- 标准清理（不锁表）
VACUUM (VERBOSE, ANALYZE) table_name;
-- 紧急冻结（接近回卷时的救命稻草）
VACUUM (FREEZE, VERBOSE) database_name;

切勿滥用 VACUUM FULL：它会重写整个表文件，持有 ACCESS EXCLUSIVE 锁，阻塞所有读写，且需要额外的磁盘空间。绝大部分表膨胀通过标准 VACUUM 即可控制。

4. WAL 堆积：磁盘无声的吞噬者

pg_wal 目录填满磁盘时，PostgreSQL 会毫不犹豫地崩溃并停止所有连接。

4.1 三种被遗忘的 WAL 积压原因

归档失败：archive_command 返回非零退出码（权限错误、目标存储不可达），PostgreSQL 会无限重试直到归档成功，WAL 文件永不删除。
停滞的复制槽：最经典的隐患排查点。pg_replication_slots 中 active = false 的槽会持续保留 WAL，直到数据库某天突然崩溃。
检查点罕见：WAL 生成速率远大于检查点释放速率，可通过缩短 checkpoint_timeout 缓解。

4.2 紧急止血与长期防御

紧急止血（磁盘告警时立即执行）：

-- 1. 列出所有复制槽及积压量
SELECT slot_name, slot_type, active,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

-- 2. 清理确认无用的停滞槽
SELECT pg_drop_replication_slot('slot_name');

删除停滞复制槽后，WAL 会在下一次检查点被回收。若 WAL 仍不回收，执行 CHECKPOINT 主动触发回收。

长期防御配置（事前防崩）：

# postgresql.conf
max_wal_size = 20GB
min_wal_size = 4GB
max_slot_wal_keep_size = 10GB        # 单个槽最大积压上限

一旦单个复制槽积压的 WAL 超过此限制，PostgreSQL 会丢弃该槽的数据并标记其失效，宁可丢失复制功能，极力保护磁盘空间不被耗尽。

4.3 WAL 与数据目录分离部署的最佳实践

将 pg_wal 迁移至独立高性能磁盘（通常是 SSD 或 NVMe），能够大幅提升事务写入性能和稳定性。

5. 日志统一分析：从“查日志”到“看报表”

5.1 日志配置标准化

# 推荐的生产级日志配置
log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_min_duration_statement = 1000
log_lock_waits = on
log_checkpoints = on
log_autovacuum_min_duration = 500

5.2 pgBadger：日志界的体检报告

每周运行一次 pgBadger 生成 HTML 报告：pgbadger /var/log/postgresql/*.log -o /var/www/html/report.html。报告包含慢查询分布、错误频率、锁等待统计、Temp 文件使用量等 30+ 维度，是性能趋势研判的“体检报告”。

5.3 关键日志模式与告警策略

生产环境建议重点监控以下日志模式，配合日志采集系统（rsyslog/Vector）和正则匹配进行实时告警：

日志模式（正则）	典型含义	严重等级	自动化动作
`FATAL:`	致命错误，进程即将退出	P0（致命）	立即告警 + 自动拉起
`ERROR:`	执行错误，SQL 失败	P1（错误）	5 分钟内聚合告警
`could not open file`	存储或文件句柄耗尽	P0（致命）	立即告警
`archive command failed`	WAL 归档失败	P1（错误）	立即告警
`deadlock detected`	死锁	P1（错误）	按业务窗口告警
`connection received` 密集爆发	连接风暴	P1（错误）	触发连接数监控 + 限流
`remaining connection slots reserved`	连接池耗尽	P0（致命）	紧急巡检应用连接池配置
`is not accepting commands to avoid wraparound`	事务 ID 回卷（致命模式）	P0（致命）	数据库已进入只读状态，需紧急 VACUUM FREEZE，手工恢复

6. 诊断工具箱与巡检体系

光有方法论还不够，高效的诊断离不开趁手的工具链。以下工具覆盖从一键巡检到深度排查的全场景：

工具	定位	何时使用	适用场景
pg_gather	纯 SQL 诊断脚本，零部署	定期健康巡检，问题前收集现场数据	生成数据库健康分析 HTML 报告
postgres_dba	psql 交互式诊断套件，无扩展依赖	日常交互式排查，逐一探查锁树、膨胀、冗余索引	34 个诊断报告，含阻塞锁树、膨胀分析、corruption 检查
auto_explain	自动慢查询日志	全时开启	捕捉无法手动复现的间歇性慢查询
pg_stat_statements	实时 SQL 统计	全时开启	按总耗时、I/O 排序识别全局性能热点
pgBadger	日志分析报告	每天/每周生成	生产趋势分析和合规审计
pgstattuple	精确膨胀统计	深度排查时需要准确数据	获取表死亡元组比例、空闲空间率等微观指标
pg_repack	在线重组表（无需 Acquire 锁）	表膨胀严重但不会锁太久	生产环境 DDL 更新和收缩表，比 VACUUM FULL 安全
pg_hint_plan	强制执行计划	优化器方案持续选错	查询复杂且数据倾斜严重需临时保住性能

6.1 pg_gather：一键采集健康检查数据

pg_gather 是 Percona 开源维护的 PostgreSQL 健康巡检工具，零部署——只需要标准的 psql 命令行工具，无需在数据库主机上部署任何可执行文件，完全基于纯 SQL 实现。

# 收集数据（输出为制表符分隔文件）
psql -X -d yourdb -f gather.sql > /tmp/health.tsv

# 生成报告（在独立数据库实例中导入分析）
psql -f gather_schema.sql -f /tmp/health.tsv

数据收集与分析分离的设计，确保了分析查询不会对关键生产系统产生不利影响。生成的分析报告涵盖配置参数、锁竞争、索引使用率、膨胀估算等核心维度。建议配置为 cron 每周运行一次，形成历史基线，便于发现性能退化趋势。

6.2 postgres_dba：psql 里的全功能诊断套件

postgres_dba 7.0 是一个基于 psql 的交互式诊断工具包，无扩展依赖，支持 PostgreSQL 13–18。在 psql 中只需执行 i start.psql 即可开始探索。

覆盖的诊断模块包括：膨胀与 VACUUM 分析（v1, v2）、表和索引健康检查（i1–i3）识别未使用索引、冗余索引和非索引外键、查询分析（s1–s3）、锁树追踪（l1）精准定位阻塞源、4 级 amcheck 套件的损坏检测（c1–c4）。

6.3 pg-dash：CI/CD 集成的自动化健康检查

pg-dash 是一个零配置的健康检查工具，专注于将诊断融入开发流程。核心亮点是迁移安全检查器——每次 PR 提交迁移 SQL 文件时，pg-dash 自动检测 CREATE INDEX WITHOUT CONCURRENTLY、ADD COLUMN NOT NULL WITHOUT DEFAULT、无 WHERE 条件的 DELETE/UPDATE 等危险模式，并给出确切影响的行数和预估停机时间，CI 返回非零退出码直接阻断发布。

7. 故障排查速查表

症状	第一步查什么	紧急止血	根治方案
磁盘写满告警	`du -sh /pg_wal; SELECT * FROM pg_replication_slots;`	删除停滞复制槽；`CHECKPOINT`	设置`max_slot_wal_keep_size`；归档命令监控告警
突然所有查询变慢	`SELECT * FROM pg_stat_activity WHERE wait_event IS NOT NULL;`	`pg_terminate_backend(阻塞源PID)`	优化锁持有时间；确保所有 UPDATE/DDL 有索引支撑
某表查询越来越慢（扩容滞后）	`n_dead_tup/n_live_tup` 膨胀指标	对该表执行 VACUUM（非 FULL）	表级精细调参 autovacuum 策略
高并发时频繁死锁	检查应用层事务中表的访问顺序	已自动终止，无需人工干预	统一访问顺序；调低`deadlock_timeout`至 1s
间歇性的某个查询突然超时	`auto_explain` 捕获不稳定执行计划	记录当时的查询参数和计划	调整统计信息采样；必要时 hint 锁定
`WARNING: database is not accepting commands to avoid wraparound`	`SELECT age(datfrozenxid) FROM pg_database;`	立即执行 `VACUUM FREEZE`	监控自动预警，设置低于 15 亿阈值

写在最后：建立故障预防的文化

一个好的 DBA 做的绝大多数工作，不是为了处理下一场事故，而是为了确保事故不会发生。

周检：执行 pg_gather 收集健康快照，生成本周对比上周的膨胀/死锁/慢查询趋势；月检：用 postgres_dba 检查未使用索引和冗余索引并在业务低峰期清理；季度：完整恢复演练，检验备份文件可恢复性。

故障复盘五问：问题是什么时候开始的？根本原因在哪个层面（应用/数据库/基础设施）？为什么监控没有提前发现？RTO 和 RPO 是否满足？需要增加哪些自动化防御措施？用好这五个问题，每次故障都会成为团队能力的跃升。

下一期预告：容量规划与分区策略设计，涉及分区分表策略、数据生命周期管理、归档方案设计、容量预测模型，让数据库在三年内平稳扩容。

参考资料

auto_explain module documentation [9†L9-L13]

PostgreSQL 锁问题排查与处理—CentOS [10†L5-L29]

PostgreSQL Troubleshooting deadlocks—Rockdata [11†L10-L27]

Preventing the Silent Spiral of Table Bloat—Tiger Data [12†L28-L31]

Debian 下 PostgreSQL 日志分析技巧 [13†L4-L11]

WAL 日志堆积问题定位及处理方法—华为云 [14†L3-L20]

Dealing with WAL accumulation in pg_wal directory—Rockdata [15†L7-L24]

postgres_dba 7.0 announcement—PostgreSQL Announce [16†L16-L25]

pg_gather documentation—Rockdata [18†L2-L19]

Pg_Gather Tool for PostgreSQL Database Health Assessment—Datavail [19†L3-L6]

pg-dash: PostgreSQL health checker—DEV Community [20†L10-L25]