企业官网建设流程全解析

凌晨两点收到告警，数据库连接数飙到上限，业务侧大量请求超时，登录服务器一看 CPU 才 20%，磁盘 IO 也不高。这种场景对管过 PostgreSQL 生产环境的人来说应该不陌生——明明数据库还活着，业务却像死了一样。这类问题是最典型的“假死”，根源往往不在数据库本身，而在连接层、锁机制、统计信息这些平时不太注意的角落。

这篇就是我从多次生产事故里总结出来的排查手册，覆盖 PostgreSQL 最常见的高频故障：连接耗尽、慢 SQL 与 CPU 飙升、锁等待、磁盘与膨胀、主从复制中断。每个故障我都会讲清楚现象、定位方法、处置动作和背后的原理，最后附上我日常巡检和复盘的习惯。适合一线 DBA、后端开发、运维同学，也适合正准备把 PostgreSQL 深度用起来的团队参考。

1. 别急着查 SQL：先建立一套自己的故障分类和排查顺序

生产环境出故障时，最怕的不是问题复杂，而是被现象牵着走。明明磁盘满了，你却在花半小时分析慢 SQL；明明是锁等待，你却在反复重启应用。所以第一件事，是给故障快速分个类。

1.1 生产环境故障到底分几类

我习惯把 PostgreSQL 生产故障分成六类，每类都有比较明显的信号特征：

这张表不是绝对的，但可以让你在收到告警时先问自己一句：这更像哪一层？分类准了，后续定位时间能省一大半。

1.2 三份必须会读的“现场证据”

分类之后，就要看现场。PostgreSQL 生产故障里，我优先级最高的三个信息来源是：

• pg_stat_activity：当前所有会话的状态、SQL、wait_event。这是第一现场，几乎每次排查都要先查它。
• pg_locks+pg_blocking_pids()：锁等待的现场，能直接看到谁在等谁。
• 数据库日志：log_duration、log_min_duration_statement如果开了，慢 SQL 和错误都会记在里面。

很多人忽略wait_event，其实它特别关键。比如ClientRead表示会话在等应用发数据，IO表示等磁盘，Lock表示等锁，LWLock可能跟内存/缓冲池争用有关。先看懂等待事件，就不会把网络问题当成 CPU 问题。

1.3 我个人的标准排查顺序

我自己的固定顺序是：告警内容 → 系统负载（top、iostat、free）→ 数据库活跃会话（pg_stat_activity）→ 等待事件 → 具体 SQL / 锁 / 日志。

顺序不能反。曾经有一次应用报“数据库性能差”，我先去查慢 SQL，半天没找到。后来发现其实是磁盘 IO 高达 95%，所有磁盘读写都卡，再快的 SQL 也跑不动。如果第一步先看系统负载，当场就能判断是存储的问题。

有个特别好的比喻：数据库像一家餐厅。连接数是门口排队的号，锁是座位上的人不走，慢 SQL 是后厨做菜慢，磁盘满则是厨房没食材了。你得先看是门口塞了、座位卡了，还是后厨菜做不出来。顺序对了，排查才高效。

2. 连接数被打满：数据库没宕，但业务已经“假死”

2.1 故障表现

PostgreSQL 的连接数是固定上限（max_connections），超了之后新的连接直接被拒绝，报错通常是：

FATAL: sorry, too many clients already

但更常见的情况是：还没报这个错，应用侧已经大面积超时。因为应用连接池建连要时间，数据库 accept 队列积压，请求排队。此时top看 CPU 不高，数据库的active会话也不多，给你的感觉就是“奇怪，怎么连不上”。

2.2 一分钟定位连接问题

登录到数据库后，我第一个 SQL 永远是：

select state, count(*) from pg_stat_activity group by state;

state常见三种：active（正在跑）、idle（空闲）、idle in transaction（事务内空闲）。问题往往就藏在最后一种。

接着看系统层面的连接数和队列：

ss -lnt | grep 5432 | awk '{print $NF}' | sort | uniq -c

如果Recv-Q很大，说明内核 accept 队列塞满了，是应用连不上，不是数据库执行慢。

2.3 最常见的三个根因

• max_connections配置偏小，且应用直接连库没用连接池。这种情况在促销、爬虫、突发流量时特别明显。
• 应用层连接池设置不合理，比如最小连接数设得很大、连接泄漏，或者连接池探活有问题。
• idle in transaction会话长期占着连接。我见过最多的情况是代码里开了事务，中途抛异常没有 rollback，也没 close，事务一直挂着，连接就永远不释放。

尤其第三种最容易忽略，因为数据库 CPU、内存都正常，但连接就这样被空耗掉了。

2.4 我是怎么处理的（案例）

某次早高峰，某业务突然大面积超时。我查了pg_stat_activity，发现 500 个连接里有 300 多个idle in transaction，而且都在同一个应用账号下。再查pg_stat_activity里的backend_start，发现这些会话大多已经存活几小时，明显是泄漏。

当时的处置流程是：

1. 先定位这些会话的执行时间，确认都是空闲事务。
2. 和业务确认是否可以强制清理，然后执行：

   select pg_terminate_backend(pid) from pg_stat_activity where state = 'idle in transaction' and backend_start < now() - interval '5 minutes';

3. 临时调大max_connections，触发pg_ctl reload让配置生效，先恢复业务。
4. 让开发查代码，在finally块里统一加 rollback 和连接归还逻辑，修复根因。

这里有个关键点：pg_terminate_backend会中断事务，未提交的数据会回滚，所以一定要和业务侧确认。如果其中有正在跑写事务的会话，强杀可能造成应用层报错，甚至部分业务需要手动补偿。我习惯只处理空闲超过一定时间、且确实确认无异常的会话，绝不无差别乱杀。

3. 慢SQL与CPU飙升：索引不是万能的，统计信息才是

3.1 现象辨识

慢查询增多，top里多个 PostgreSQL 进程 CPU 占满，数据库整体吞吐下降。这种问题大家最喜欢甩锅给“业务 SQL 写得烂”，但在生产环境里，很多时候 SQL 本身没变，变的是数据分布、统计信息或者内存参数。

3.2 让证据说话的三个工具

• pg_stat_statements：这个扩展一定要装。它按语句聚合统计，能直接按总耗时排序，快速找出“真正的黑天鹅”。

  select query, calls, total_exec_time, mean_exec_time, rows from pg_stat_statements order by total_exec_time desc limit 10;

• auto_explain：生产环境开一下很值得。它可以把超过阈值的慢 SQL 执行计划自动记录到日志，不用等现场复现。

  shared_preload_libraries = 'pg_stat_statements,auto_explain' auto_explain.log_min_duration = '500ms' auto_explain.log_analyze = on

• 手动EXPLAIN：对候选慢 SQL 执行EXPLAIN (ANALYZE, BUFFERS)，看实际行数、排序、嵌套循环、临时文件。

3.3 深入根因：为什么加了索引还慢

最常见的误区是：慢 SQL 就加索引。实际上我见过不少加了索引也没用的案例，根因往往是这些：

1. 统计信息不新。当数据量大幅变化、或者高频更新后，优化器还按旧统计信息选计划。解决办法是ANALYZE表，或调整autovacuum的触发阈值。
2. 字段类型不一致导致隐式转换。比如表里是text类型，SQL 里用varchar参数关联，或者反过来，PostgreSQL 可能放弃索引，全表扫描。检查EXPLAIN里有没有::text这种转换。
3. 随机 IO 成本参数不合适。random_page_cost默认是 4，如果跑在 SSD 或云盘上，这个值明显偏高，优化器会偏向顺序扫描而不是索引扫描。我会把它调低到 1.1~1.5。
4. 内存排序溢出。work_mem太小，排序或哈希操作会写临时文件，表现为慢查询间歇性发生、磁盘 IO 有写入。调大work_mem能立竿见影，但要注意它是每个连接都可能有独立内存，不能无限调大，否则内存直接被吃光。

3.4 CPU飙升时的紧急处理

如果已经 CPU 100%、业务告警，我没有时间慢慢做计划分析，顺序是这样的：

1. 从pg_stat_activity抓出所有active会话，按query和耗时排序，找出最可疑的大 SQL。
2. 如果是历史执行计划选错，且我们判断索引慢在“扫描行数巨大”，可以先临时禁用某种连接方式让优化器换条路：

   set enable_hashjoin = off; set enable_nestloop = off;

   注意，这种设置只对当前会话有效，适合应急验证。
3. 确认是计划问题后，再回源头：更新统计信息、修正参数、改写 SQL。

我之前遇到过一张千万级表，等值查询条件列有索引，但因为该列大量重复值，优化器选择全表扫描，导致 CPU 跑满。当时先临时调enable_seqscan=off让业务先恢复，随后通过分析数据分布、改造成复合索引才彻底解决。临时关闭顺序扫描不值得提倡，但作为止血动作，理解它的代价是可控的。

4. 锁等待与死锁：数据库卡住的幕后推手

4.1 锁的种类和冲突矩阵

PostgreSQL 锁的种类很多，生产环境里我最关心的是两类：表级锁和行级锁。行锁冲突是最容易引发业务卡死的。当一个事务更新了一行没提交，另一个事务再去更新同一行时，就会等待。这个等待在业务上表现出来就是“应用一直转圈”，而且数据库 CPU 不高。

很多人对锁的印象停留在LOCK TABLE，其实绝大多数事故都是普通行锁，根本原因是长事务 + 更新相同数据范围。

4.2 快速定位阻塞者

遇到锁等待，我用的查询非常直接：

select a.pid, a.state, a.wait_event_type, a.wait_event, a.query, b.pid as blocking_pid, pg_blocking_pids(a.pid) from pg_stat_activity a left join pg_stat_activity b on b.pid = any(pg_blocking_pids(a.pid)) where a.state = 'active' and a.wait_event_type = 'Lock';

pg_blocking_pids(a.pid)是个特别好用的函数，直接给出哪些 PID 堵着当前会话。拿到阻塞者 PID 后，再单独看它的状态和 SQL：

select pid, state, query, backend_start, xact_start from pg_stat_activity where pid = <阻塞PID>;

4.3 死锁的发现与善后

死锁时，PostgreSQL 日志里会出现deadlock detected提示，并且会自动回滚其中一个事务。它会选择终止代价较小的一方，被终止的一方应用层会收到类似：

ERROR: deadlock detected DETAIL: Process <pid> waits for ShareLock...

处理死锁不是等数据库检测，核心策略是消除“互相等待”的条件。常见做法：

• 业务侧统一按主键顺序更新多行，避免两个事务以不同顺序更新同一批数据。
• 控制长事务，避免一个事务里做太多事，减少锁持有时间。
• 应用层增加重试机制，对死锁错误做一次重试，zui简单也最有效。

4.4 一个曾让我头疼的案例

某系统的定时任务会并发跑几十个 worker，每个 worker 处理一批订单，它们都会更新同一张订单状态表。上线后周期性出现“订单处理变慢”，从数据库看就是大量进程在等同一个 PID。定位后发现，worker 处理的数据范围有重叠，A worker 锁了订单 1 在等订单 2，B worker 锁了订单 2 在等订单 1，双方互相卡成死锁。

最后改法很简单：每个 worker 在开始前先把要处理的所有主键排序，再按顺序逐条更新，锁冲突概率大幅下降，死锁日志几乎消失了。这个经验也一直沿用到现在。

5. 磁盘与存储：空间满、表膨胀、checkpoint 抖动

5.1 磁盘满时数据库的行为

磁盘空间耗尽的表现很有迷惑性：数据库不会立刻宕机，但会逐步变得“诡异”。写事务开始报错，日志目录写不进新内容，临时文件建不出来，连VACUUM都跑不动。最典型报错是：

ERROR: could not write to file "pg_wal/...": No space left on device

快速处置建议：

• 先确认是不是 WAL 堆积：du -sh pg_wal，如果特别大且备库正常，可能是归档失败或复制中断导致 WAL 无法清理。
• 再查大文件：du -sh /var/lib/postgresql/*，按大小排一排。
• 临时清理时，千万不要直接删 PostgreSQL 的数据文件或pg_wal下的 WAL 文件，那会把实例弄坏。安全做法是先清理数据库里的临时文件、归档日志、无关备份，或者快速扩容磁盘。

5.2 表膨胀与 VACUUM

PostgreSQL 的 MVCC 机制是：更新/删除数据时，旧版本并不会立刻从磁盘消失，而是标记为 dead tuple，等 VACUUM 来回收。如果 autovacuum 跟不上更新频率，表就会持续膨胀。表膨胀的坏处不只是磁盘占用，索引和扫描效率也会急剧下降。

查膨胀率我常用这篇脚本：

select schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup,0) * 100, 1) as dead_pct from pg_stat_user_tables where n_dead_tup > 10000 order by n_dead_tup desc;

一旦发现某个表n_dead_tup持续巨大，就要查 autovacuum 为什么没跑。常见原因：表太大，默认阈值来不及；长事务阻塞了 vacuum；autovacuum_max_workers太少。

紧急处理：

vacuum (verbose, analyze) your_table;

如果表特别大又需要立刻回收磁盘空间，VACUUM FULL或pg_repack可以用，但它们会锁表或产生额外代价，必须放在低峰期执行。我通常不把VACUUM FULL当默认动作，它需要重建表，期间会占用大量 IO。

5.3 checkpoint 与 IO 尖刺

还有一个容易忽略的故障模式：业务正常，但数据库每隔一段时间磁盘 IO 飙到 100%，应用出现周期性抖动的延迟。这往往和checkpoint有关。

PostgreSQL 写数据会先写 WAL，再在后台把脏页刷到数据文件。checkpointer定期做一次脏页刷新，频率受max_wal_size和checkpoint_timeout控制。如果max_wal_size设置太小，checkpoint 会特别频繁，每次刷大量脏页，造成 IO 尖刺。

排查时看pg_stat_bgwriter：

select checkpoints_req, checkpoints_timed, buffers_backend, buffers_checkpoint from pg_stat_bgwriter;

如果checkpoints_req比checkpoints_timed还大，说明很多 checkpoint 是因为 WAL 量达到上限被触发的，而不是时间到了。此时适当调大max_wal_size（比如 16GB 到 64GB），让 checkpoint 间隔拉长、单次刷盘更平滑，能明显改善抖动。SSD 和云盘上这个参数很值得优化。

6. 主从延迟与复制中断：数据一致性不能只看能连通

6.1 主从复制的同步状态

PostgreSQL 流复制本身比较稳定，但生产环境出问题最多的反而是配置和理解。备库连上不代表复制正常，就像两个对讲机信号通，不代表对方听到的每句话都是完整的。

检查主从状态：

select client_addr, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) as lag from pg_stat_replication;

state是streaming才算正常，sync_state表示同步方式。lag大于几十 MB 时需要注意。

延迟常见原因：

• 主库写入量极大，备库单线程回放跟不上。
• 备库在跑大查询，占用了 IO 或 CPU。
• 备库磁盘慢，WAL 接收和回放速度跟不上。
• 网络带宽限制，跨机房尤其明显。

6.2 复制中断的常见场景与处理

复制中断最隐蔽的表现是：主库的pg_wal目录持续变大。因为备库追不上，主库会保留更多 WAL 以备备库请求。如果中断太久，WAL 可能被清理，备库再也追不回来，只能重建备库。

定位先看主库：

select * from pg_stat_replication;

如果这里没有记录，说明主库看不到备库，大概率网络断了或者备库进程挂了。再看备库日志，搜hot standby、wal receiver之类的关键词。

恢复手段通常是：

• 先修网络。
• 如果只是临时断开，通常备库重连后会自动追 WAL。
• 如果 WAL 已经被回收、备库无法追平，那就从主库重新pg_basebackup重建备库，别想着硬凑。

6.3 主从切换的坑

主从切换时的经典悲剧是“脑裂”或者“主库提交阻塞”。这里必须提一个容易踩的坑：synchronous_standby_names配置了一个具体的备库名字，备库挂了之后，主库会因为等不到同步确认，所有写事务卡住。

我曾经遇到某系统把同步复制配成：

synchronous_standby_names = 'standby1'

结果 standby1 所在机器宕机后，主库所有commit都被阻塞，业务完全不可写。当时还以为是数据库性能问题，查了半天才发现同步模式的问题。

处置方式是临时把该参数清空或改成ANY 1，让主库不再强制等待那个不可达的备库：

synchronous_standby_names = 'ANY 1 (standby1, standby2)'

然后再重建备库。生产环境如果要求数据可靠，用ANY 1比固定单节点更稳，至少不会因为某一台备库故障把主库写入拖死。

7. 把排查能力沉淀成团队的日常动作

7.1 环境基线与巡检

排障快的人不是天生会排障，而是平时对系统的“正常状态”很熟悉。我建议至少每周采样一次数据库的关键视图，保留历史数据：

• pg_stat_database：事务、读写块、死锁数、临时文件。
• pg_stat_user_tables：死元组、顺序扫描、索引扫描次数。
• pg_settings：关键参数快照。
• pg_stat_replication：主从延迟和状态。

有了基线，再出问题时你能立刻判断“这个数字是异常的”还是“其实一直这样”。很多隐性故障，比如死元组悄悄上涨、复制延迟缓慢变大，都是在基线对比里提前暴露的。

7.2 监控告警项建议

告警不在于多，在于准。我整理的必告警清单供参考：

7.3 故障复盘模板

每次故障结束后，我都会写一份复盘，格式固定为：发生时间、影响范围、根因分类、排除过程、止血动作、改进事项。复盘不是追责，而是把这次踩过的坑变成下一次的排查手册。持续积累三个月，你会发现自己再遇到类似问题，完全不用翻资料，条件反射就知道先看哪里。

如果条件允许，我还会在测试环境做一次“故障演练”：人为把备库停掉、把表锁住、把磁盘填到 90%，让大家在低压力状态下熟悉排查命令。别等到真实故障那天，再来搜“PostgreSQL 锁等待怎么查”。

再多说一句我个人的体会：PostgreSQL 生产环境的绝大多数故障，都不是突然发生的，而是平时缺少观察和基线。把今天这篇里提到的几个查询保存下来，下一次大促前跑一遍，很多事故还没发生就已经被拦下来了。