PostgreSQL 故障排查与最佳实践

排障的基本顺序

数据库出问题，最忌讳上来就改参数。先按「现象 → 证据 → 根因 → 处置」走：

1. 确认现象：应用报什么错？是连不上、连上但慢、还是数据不对？
2. 看现场：pg_stat_activity 看连接和活跃查询，pg_locks 看锁，系统层看 CPU/IO/磁盘。
3. 找根因：慢 SQL、锁等待、膨胀、复制延迟，通常是这四类之一。
4. 处置并留证据：改完记录参数、SQL 和影响范围，避免下次重复踩坑。

本篇按四类高频故障展开，最后给一份可以直接对照的最佳实践清单。

连接数打满

现象

应用日志里出现：

FATAL:  sorry, too many clients already
FATAL:  remaining connection slots are reserved for non-replication superuser connections

第一条是普通连接超过 max_connections；第二条是超级用户保留槽位也被占满（superuser_reserved_connections 默认 3 个）。

定位

-- 当前连接总数与上限
SELECT count(*) AS used,
       current_setting('max_connections')::int AS max_conn,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity;

-- 按应用 + 客户端地址分组，找出谁占的
SELECT application_name,
       client_addr,
       state,
       count(*) AS conns
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3
ORDER BY conns DESC;

-- 空闲连接占比（大量 idle 说明连接池没配好或应用没释放）
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

处置

• 短期：清理异常连接（pg_terminate_backend），必要时临时调大 max_connections（需要重启），但要同步评估内存和 CPU 是否够分摊到更多 backend。
• 长期：上连接池。PgBouncer 的 transaction 模式能把几百个应用连接复用成几十个数据库连接，是 PostgreSQL 生态最常用的方案。配置核心就两项：max_client_conn（应用侧上限）和 default_pool_size（到数据库的真实连接数）。
• 排查泄漏：如果连接数只涨不跌，查应用是否忘了关连接、连接池 maxIdle 是否失效、是否有长事务占着连接不放。

别把 max_connections 当解药

每个 backend 是独立进程，max_connections 每加 100，内存和上下文切换开销都实打实增加。调到 1000 以上而不配连接池，通常只是把「连接被拒绝」换成「数据库整体变慢」。经验值：4C8G 的机器 200 左右，16C64G 可以到 500，再往上必须靠 PgBouncer。

锁等待与死锁

锁等待怎么发生的

PostgreSQL 的行锁是「写写冲突」：两个事务改同一行，后到的等前一个提交。等太久就是锁等待；如果两个事务互相等对方持有的锁，就是死锁，数据库会检测到并杀掉其中一个（默认 deadlock_timeout = 1s 后检测）。

最常见的死锁模式是加锁顺序不一致：

-- 两个会话按下面的顺序交替执行
-- 会话 A：锁住 id=1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 会话 B：锁住 id=2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;

-- 会话 A：等 B 释放 id=2
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 会话 B：等 A 释放 id=1，环形成，死锁
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

避免方法很简单：所有事务按相同的顺序访问资源（比如按主键升序），或者把大事务拆小、缩短持锁时间。

排查查询

-- 1. 谁在等锁，谁持有锁（pg_blocking_pids 是 9.6+ 的利器）
SELECT
    blocked.pid          AS blocked_pid,
    blocked.usename      AS blocked_user,
    blocked.state        AS blocked_state,
    now() - blocked.query_start AS wait_time,
    left(blocked.query, 100)    AS blocked_query,
    blocking.pid         AS blocking_pid,
    blocking.state       AS blocking_state,
    left(blocking.query, 100)   AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY wait_time DESC;

-- 2. 锁明细：哪个对象、什么锁模式、是否已授予
SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    l.relation::regclass AS relation,
    a.state,
    left(a.query, 80) AS query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted
   OR l.mode IN ('ExclusiveLock', 'AccessExclusiveLock')
ORDER BY l.granted, l.pid;

-- 3. 死锁次数（累计值）
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

读锁明细时的几个要点：

• granted = false 的行就是正在等待的锁。
• AccessExclusiveLock 最危险，ALTER TABLE、VACUUM FULL、DROP TABLE 都会拿它，会阻塞所有读写。
• relation 为空的可能是事务 ID 锁或虚拟事务锁，通常和长事务、prepared transaction 有关。
• 一个 SELECT ... FOR UPDATE 拿的是 RowShareLock 加上行级锁，别看到锁就慌，先看模式和持有时长。

处置

-- 取消等待中的查询（推荐，影响面小）
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE pid = ANY (SELECT pid FROM pg_locks WHERE NOT granted);

-- 确认是死锁/僵尸事务后再 terminate
SELECT pg_terminate_backend(pid);

死锁本身不用人工干预，数据库会自动处理。要做的是从日志里找到死锁现场：

# postgresql.conf
log_lock_waits = on
deadlock_timeout = 1s
log_min_messages = warning

日志里会打印 deadlock detected 和两个事务的完整 SQL，拿去定位代码里的加锁顺序问题。

DDL 也会被锁住

ALTER TABLE 要拿 AccessExclusiveLock，前面只要有一个长事务在读写这张表，DDL 就会一直排队，而排队的 DDL 又会挡住后面所有新查询，形成「雪崩」。生产上执行 DDL 前先设短 lock_timeout：

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN remark text;

超时失败就重试，别让它无限等。大表加列在 PG 11+ 是轻量操作（不重写表），但拿锁这一步仍然要小心。

VACUUM 膨胀与 autovacuum 调优

膨胀是怎么来的

第 1 篇讲过，UPDATE 是「插新版本 + 标记旧版本」，DELETE 是「标记删除」，旧版本要等 VACUUM 回收。如果：

• 长事务一直开着，xmin 降不下去，VACUUM 不敢清；
• autovacuum 跟不上写入速度；
• 表上频繁全表 UPDATE；

死元组就会越积越多，表和索引物理膨胀，查询扫描的页数变多，越来越慢。

-- 1. 看哪些表死元组多
SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 2. 看表的物理大小和膨胀（需要 pgstattuple 扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
-- dead_tuple_percent 高就是膨胀严重

autovacuum 调优

默认参数对小表够用，对大表往往太保守：

# postgresql.conf
autovacuum = on
autovacuum_max_workers = 6              # 默认 3，大实例可以加
autovacuum_naptime = 30s                # 默认 1min，缩短提高响应速度
autovacuum_vacuum_scale_factor = 0.05   # 默认 0.2，表越大越该调小
autovacuum_vacuum_threshold = 50
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms      # 默认 2ms（PG12+），别设太大
autovacuum_vacuum_cost_limit = 2000     # 默认 200，调高让单次清理更激进

单表可以覆盖全局设置，给大表更激进的策略：

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_scale_factor = 0.005
);

已经膨胀了怎么办

• VACUUM 表名：回收死元组，不锁表，但不会把空间还给操作系统（留在表文件里复用）。
• VACUUM FULL 表名：重写表，空间真正释放，但拿 AccessExclusiveLock，大表在生产上等于停服。
• pg_repack：在线重写，不阻塞读写，大表首选，需要装扩展。
• REINDEX ... CONCURRENTLY：索引膨胀时在线重建，不锁写。

事务 ID 回卷

PostgreSQL 的事务 ID 是 32 位的，约 21 亿一轮。如果 VACUUM 长期跟不上，age(datfrozenxid) 会逼近上限，数据库会强制停写甚至进入只读保护。监控这个值：

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

超过 10 亿就要当紧急事件处理：查长事务、查被阻塞的 autovacuum、必要时手工 VACUUM FREEZE。这不是「性能问题」，是「数据库要挂」的问题。

复制延迟与 WAL 堆积

判断延迟

主库上看：

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

备库上看：

SELECT status,
       receive_start_lsn,
       received_tli,
       last_msg_send_time,
       last_msg_receipt_time,
       latest_end_lsn,
       slot_name
FROM pg_stat_wal_receiver;

-- 备库回放落后多少
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

lag_bytes 是「主库已发送 vs 备库已回放」的字节差，replay_lag 是时间差。时间差比字节差更直观，监控告警一般用 pg_replication_lag > 30。

常见原因

原因	表现	处理
网络带宽不足	延迟持续增长，备库 last_msg_receipt_time 落后	查链路、压缩 WAL、升级带宽
备库 IO 跟不上	备库磁盘 util 100%，回放慢	换 SSD、减少备库上的查询
备库长查询与回放冲突	回放要取消查询，超过 max_standby_streaming_delay 会强制中断	取消备库长查询；hot_standby_feedback = on 能减少取消但会让主库膨胀
主库大事务	一次产生几 GB WAL，备库追不上	拆大事务、错峰执行
同步复制等待	主库提交变慢，sync_state 异常	检查同步备库健康、增加候选
复制槽堆积	pg_wal 目录暴涨	删无用槽、设 max_slot_wal_keep_size

WAL 堆积

pg_wal 目录持续变大是危险信号，磁盘写满后数据库会直接停摆。三个常见来源：

-- 1. 复制槽不消费（最常见，在主库执行）
SELECT slot_name,
       slot_type,
       active,
       restart_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_bytes
FROM pg_replication_slots
ORDER BY retained_bytes DESC;

-- 2. 归档失败，WAL 堆在 pg_wal 里等重试
SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

-- 3. WAL 总量
SELECT pg_size_pretty(sum(size)) AS wal_total FROM pg_ls_waldir();

处置：

• inactive 的复制槽：确认备库不再使用后 SELECT pg_drop_replication_slot('slot_name');，这是释放空间最快的手段。
• 归档失败：查 archive_command 的返回值、目标目录权限、磁盘空间。归档没恢复之前，WAL 会一直堆。
• 兜底参数：max_slot_wal_keep_size = 10GB，槽拖住的 WAL 超过这个量就让备库失效重建，宁可备库重搭也不能把主库磁盘写满。
• 扩容：临时扩磁盘能续命，但要同时找根因，不然只是推迟爆炸时间。

复制槽的两面性

复制槽保证 WAL 不被提前删除，是备库稳定复制的基石；但它也是「WAL 堆积」的头号嫌疑人。用完就删、设上限、监控保留字节数，三件事缺一不可。pg_replication_slots 里 active = false 且 retained_bytes 很大的槽，基本都是历史遗留。

最佳实践清单

部署与配置

• shared_buffers 给物理内存的 25% 左右，别照搬 MySQL 的 70%。
• work_mem 从小往大调，盯着 log_temp_files，别让并发把内存吃爆。
• wal_level = replica，full_page_writes 保持开启，max_wal_size 按写入量给。
• 连接串里配 application_name，排障时能一眼看出是哪个服务。
• 生产必须配连接池（PgBouncer），max_connections 控制在几百以内。

日常运维

• 监控 age(datfrozenxid)，超过 10 亿当紧急事件。
• 监控 pg_replication_slots 的 retained_bytes 和 pg_wal 目录大小。
• 长事务阈值告警（5 分钟），idle in transaction 超过 30 分钟清理。
• 大表单独设置 autovacuum 参数，别只靠全局默认值。
• 定期检查 pg_stat_archiver.failed_count，归档失败等于备份不可靠。

变更与故障处理

• DDL 前设 lock_timeout，大表加索引用 CREATE INDEX CONCURRENTLY。
• 批量更新/删除分批做（每批几千行），避免大事务和锁等待。
• 改参数前记录原值，改完观察 pg_stat_statements 和监控曲线。
• 任何 terminate/cancel 操作前先保存 PID 和 SQL，事后能复盘。
• 定期演练 PITR 恢复，备份没验证过就等于没有备份。

SQL 与开发

• 事务里不要包含网络调用、文件操作等慢动作，持锁时间越短越好。
• 并发更新用原子 SQL（UPDATE ... WHERE 条件）或 SELECT ... FOR UPDATE，别「先查再改」。
• JSONB 查询用 @> 配合 GIN 索引，别在 ->> 结果上做无索引比较。
• 分页大偏移用游标（WHERE id > 上一页最后一条），别用 OFFSET 100000。

小结

• 连接数满：先按应用/地址定位来源，短期清理、长期上 PgBouncer；max_connections 不是解药。
• 锁等待：用 pg_blocking_pids() 找阻塞链，统一加锁顺序避免死锁；DDL 设 lock_timeout 防雪崩。
• 膨胀：死元组靠 autovacuum 清，长事务是最大障碍；大表单独调参，已膨胀用 pg_repack 或 VACUUM FULL（慎用）。
• 事务 ID 回卷比性能问题更致命，age(datfrozenxid) 必须监控。
• 复制延迟先分网络、IO、长查询、大事务四类；WAL 堆积优先查复制槽和归档失败，设 max_slot_wal_keep_size 兜底。
• 最佳实践的核心就一句：缩短事务、控制连接、监控复制与膨胀、变更前留退路。

系列正文到此结束。附录里整理了 psql 元命令、诊断 SQL 和备份恢复命令，排障时可以随手查。