📚 课程目录第4章应急工具箱

4. 应急工具箱

当生产库出现响应变慢、连接堆积、复制延迟或写入抖动时，DBA 的第一反应不应该是重启实例，而是用一组系统视图在几十秒内完成“定位问题域”。PostgreSQL 提供了一组轻量、无需额外安装的诊断视图，它们直接读取共享内存中的统计信息，开销极低，可以安全地在故障现场反复查询。本节聚焦五个最常用的视图：pg_stat_activity、pg_locks、pg_stat_replication、pg_stat_database 和 pg_stat_bgwriter，讲清它们各自回答什么问题、关键字段怎么读、以及如何组合成实战查询。

五个视图的分工定位

应急诊断的核心是“先分层，再下钻”。这五个视图恰好覆盖了从会话层到实例层的不同切面：pg_stat_activity 回答“谁在连、谁在跑、谁在等”；pg_locks 回答“谁锁住了谁”；pg_stat_replication 回答“备库跟上了没有”；pg_stat_database 回答“哪个库压力最大、命中率如何”；pg_stat_bgwriter 回答“检查点与后台写是否成为瓶颈”。理解它们的分工，才能避免在错误的视图上浪费时间。

pg_stat_activity：会话现场的第一手信息

这个视图的每一行代表一个后端进程。应急时最需要关注四个字段：state（active、idle、idle in transaction、idle in transaction (aborted)）、wait_event_type 与 wait_event（当前在等什么）、query_start（当前语句开始时间）、以及 backend_start（连接建立时间）。其中 idle in transaction 是最危险的信号之一——它持有事务快照和可能的锁，却什么都不做，会阻塞 vacuum 并放大膨胀。

下面这条查询用于找出运行超过 5 分钟的活动语句，并按等待类型排序，快速区分“真在算”和“在等锁/等 IO”：

SELECT pid,
       now() - query_start AS duration,
       state,
       wait_event_type,
       wait_event,
       left(query, 80) AS query_snippet
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

如果 wait_event_type 为 Lock，说明它在等锁，应立刻转向 pg_locks；如果为 IO，则可能是磁盘或检查点压力；如果为 Client，往往是客户端读取慢或网络问题。另一条常用查询是统计各状态的连接数，用于判断是否连接池打满：

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

pg_locks：定位阻塞链的钥匙

pg_locks 本身不包含 SQL 文本，必须与 pg_stat_activity 关联。关键字段是 locktype、relation、mode、granted 和 pid。granted = false 的行表示该进程正在等待锁。实战中常用“阻塞树”查询：找出谁在等、谁在挡。

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 bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.locktype = bl.locktype
                AND kl.database IS NOT DISTINCT FROM bl.database
                AND kl.relation IS NOT DISTINCT FROM bl.relation
                AND kl.page IS NOT DISTINCT FROM bl.page
                AND kl.tuple IS NOT DISTINCT FROM bl.tuple
                AND kl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE blocked.pid <> blocking.pid;

拿到 blocking_pid 后，先确认它是否处于 idle in transaction。如果是，通常意味着应用忘记提交或回滚，此时应联系应用侧处理，而不是直接 pg_terminate_backend。只有在确认阻塞源已失控且业务可容忍时，才考虑终止。

pg_stat_replication：复制延迟的量化

该视图在主库上查询，每个 walsender 对应一个备库。核心字段包括 state（streaming、catchup 等）、sync_state（async、sync、quorum）、sent_lsn、write_lsn、flush_lsn、replay_lsn，以及 write_lag、flush_lag、replay_lag。判断延迟时，replay_lag 最贴近“备库可见数据落后多久”。

SELECT application_name,
       state,
       sync_state,
       replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_bytes
FROM pg_stat_replication
ORDER BY replay_lag DESC NULLS LAST;

若 replay_lag 持续增长且 replay_bytes 扩大，说明备库回放跟不上；若 state 不是 streaming，则可能是网络中断或备库进程异常。注意 replay_lag 为 NULL 表示尚未产生可测量的延迟样本，不能简单当作“零延迟”。

pg_stat_database 与 pg_stat_bgwriter：实例级压力画像

pg_stat_database 按数据库汇总事务与块访问。缓存命中率可用 blks_hit / (blks_hit + blks_read) 估算，OLTP 库通常应高于 99%。若某库 blks_read 激增而命中率下降，说明工作集超出共享缓冲区，或有大范围扫描。同时关注 xact_rollback 是否异常升高，它可能指向应用错误或锁超时。

SELECT datname,
       xact_commit,
       xact_rollback,
       blks_hit,
       blks_read,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY blks_read DESC;

pg_stat_bgwriter 则反映检查点与后台写行为。重点看 checkpoints_timed 与 checkpoints_req 的比例：如果 checkpoints_req 远高于 checkpoints_timed，说明检查点频繁被 WAL 量触发，可能引起周期性 IO 抖动。同时 buffers_backend 表示后端进程自己写脏页的次数，该值偏高说明共享缓冲区不足或检查点过于稀疏，后端被迫参与刷脏。

应急查询建议加上 LIMIT 或时间过滤，避免在高峰期对 pg_stat_activity 做全量排序；同时用 pg_stat_activity 的 pid 与 pg_locks 关联时，注意 pg_locks 中 pid 可能为 NULL（如预备事务），需用 IS NOT DISTINCT FROM 处理。

组合诊断的实战顺序

面对“系统变慢”的告警，推荐按以下顺序执行：先用 pg_stat_activity 按 state 分组，确认是连接堆积还是长事务；若发现 Lock 等待，立即用阻塞树查询定位阻塞源；若写入延迟明显，查 pg_stat_replication 的 replay_lag；若整体 IO 压力大，再看 pg_stat_database 的命中率与 pg_stat_bgwriter 的检查点比例。整个过程不需要重启，也不需要额外工具，全部通过 SQL 完成。把这五个视图的查询保存为应急脚本，可以在故障现场把定位时间从十几分钟压缩到一两分钟。