PostgreSQL恢复监控实战：SQL洞察WAL回放与主备延迟

如果你管过 PostgreSQL 的主从架构，大概率经历过这种时刻：备库恢复突然变慢、主备延迟飙高、或者备库干脆卡住不动，而日志里只剩下一条孤零零的restartpoint complete。真正让人头疼的是——你没法从日志里看清"现在到底恢复到哪里了、还剩多少、速度是快是慢"。这时候，用 SQL 直接查询恢复状态，就成了最顺手也最可靠的监控手段。

这篇内容不是理论科普，是我把日常运维里用过的 PostgreSQL recovery 监控查询整理成的一份实战笔记。读完后你会知道：恢复监控到底在看哪些指标、每条 SQL 背后的含义是什么、如何组合成一套实时洞察工具，以及哪些常见的坑会让你的监控判断完全跑偏。适合正在维护 PostgreSQL 主备环境的 DBA、运维工程师，也适合打算自己搭一套轻量监控的同学。

1. 先说清楚：恢复监控到底在盯什么

1.1 从主库到备库，数据是怎么"追"过去的

要理解恢复监控，先得理解 PostgreSQL 在备库上做的事。主库持续产生 WAL（Write-Ahead Logging）日志，记录了所有数据页的变更；备库通过流复制接收这些 WAL，然后不断重放（replay），把数据页恢复到与主库一致的状态。

这里有个容易混淆的点：备库的"恢复"实际上分两层。第一层是 WAL 的接收和落盘，由walreceiver进程负责，它从主库拉取 WAL 并写入备库的pg_wal目录；第二层是 WAL 的回放，由startup进程负责，它把已经落盘的 WAL 解析出来，真正应用到数据文件上。这两个进程的快慢会直接决定备库与主库之间的延迟。

所以，监控恢复状态时，至少要盯住两个位置：一个是"已经接收了多少"，另一个是"已经回放了多少"。这两个值之间的差距，往往藏着问题的线索。

1.2 恢复监控三件套：状态、位点、延迟

PostgreSQL 提供了一系列内置函数和视图，专门供我们查询这些信息。我把它们归成三类，你可以把它们当恢复监控的三件套：

• 状态类：pg_is_in_recovery()返回true表示当前实例正处于恢复模式（也就是备库或恢复中的实例），pg_is_wal_replay_paused()可以用来判断回放是否被人为暂停。
• 位点类：pg_last_wal_received_lsn()返回备库 WAL 接收进程最近接收并落盘到的日志序列号（LSN），pg_last_wal_replay_lsn()返回已经回放到的 LSN。这两个 LSN 是恢复监控的核心坐标。
• 延迟类：pg_wal_lsn_diff()可以计算两个 LSN 之间的差值，单位是字节。用"主库当前写入位点"减去"备库回放位点"，就是通常所说的主备延迟。

代码上的样子大致是这样：

SELECT pg_is_in_recovery() AS in_recovery, pg_last_wal_received_lsn() AS received_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) AS receive_replay_gap_bytes;

这条 SQL 就能返回备库当前的核心状态，receive_replay_gap_bytes表示接收位点和回放位点之间的差距。如果这个值持续增大，说明 WAL 接收速度远快于回放速度，备库正在"积压"恢复任务。

1.3 为什么不能只看日志

最常见的误区是：恢复出问题就看 PostgreSQL 日志。日志确实能告诉我们recovery是否开始、是否有checkpoint、是否有错误信息，但它有两个致命缺陷。

第一，日志是事件驱动的，不是状态快照。你不会看到"现在回放到了 LSN 4A/XXXXXXX，剩余 3GB"，只会在某些节点看到一条记录。想要判断趋势，完全不够。

第二，日志是滞后且不完整的。某些卡顿不会产生任何日志，比如回放进程因为等待锁而停滞，日志可能安安静静，但实际恢复早就停了。你用 SQL 直接查询pg_last_wal_replay_lsn()，连续取几次快照就会发现 LSN 根本不动，这才是最真实的信号。

用 SQL 做监控还有一个额外好处：它可以被远程执行、被监控系统采集、可以算历史趋势，而日志很难直接做这类分析。所以我的建议是：日志用来定位问题根因，SQL 用来做实时监控和趋势判断，两者配合而不是互相替代。

2. 手写一套基础监控 SQL：从单条命令到组合查询

2.1 判断当前是不是恢复中

我们第一步先确认实例状态。pg_is_in_recovery()是个布尔函数，实际上它不仅对物理备库返回true，在崩溃恢复或者从归档恢复的过程中也返回true。所以它可以作为一个总开关，决定后续监控逻辑是否生效。

比如，如果这条查询返回false，说明实例是主库，那么pg_last_wal_received_lsn()这类备库相关函数的值就没有参考意义（在主库上它返回的可能是当前写入位点），监控逻辑就需要跳过。

一条实用的状态检查 SQL 可以长这样：

SELECT pg_is_in_recovery() AS in_recovery, pg_is_wal_replay_paused() AS replay_paused, (SELECT status FROM pg_stat_wal_receiver) AS wal_receiver_status;

pg_stat_wal_receiver是备库上非常有用的视图，status字段通常显示streaming，表示流复制正常；如果显示stopped或者视图为空，则说明接收进程不在工作。

2.2 发送与回放位点：两个 LSN 是关键

把 LSN 理解成"WAL 日志的字节坐标"就好。PostgreSQL 每次写日志都会推进一个 LSN，格式类似4A/D9A123B8。通过比较不同坐标，就能算出进度。

监控恢复时最常用的三个坐标是：

• pg_last_wal_receive_lsn()：备库从主库接收到的最新 WAL 位置（已经落盘到 wal 文件）。
• pg_last_wal_replay_lsn()：备库 startup 进程已经完成回放的位置。
• 主库侧的pg_current_wal_lsn()：主库当前正在写入的位置，通常用作对照基准。

由于备库上无法直接获取主库的实时 LSN（除非用pg_stat_replication的sent_lsn字段），我们实际中更常比较"接收位点与回放位点"。这两个值都在备库本地就能取到，不需要跨库通信。

来看一段能直接执行的位点查询：

SELECT pg_last_wal_received_lsn() AS received, pg_last_wal_replay_lsn() AS replay, pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) AS gap_bytes, pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn())) AS gap_human;

gap_bytes如果长期大于 0，说明备库接收 WAL 的速度快于回放速度，恢复追赶在进行中；如果等于 0，说明所有已接收的 WAL 都已回放，备库处于"追平"状态。

2.3 经典组合查询与结果解读

单看一个 LSN 没太大用，组合起来才有洞察力。我维护环境时最常用的是下面这条组合查询，它把状态、位点、延迟和接收器信息放在一起输出：

SELECT now() AS sample_time, pg_is_in_recovery() AS in_recovery, pg_last_wal_received_lsn() AS received_lsn, pg_last_wal_replay_lsn() AS replay_lsn, pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) AS gap_bytes, CASE WHEN pg_last_wal_replay_lsn() IS NULL THEN NULL WHEN pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) = 0 THEN 0 ELSE pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) / 1024.0 / 1024.0 END AS gap_mb, p.wait_event_type, p.wait_event FROM pg_stat_activity p WHERE p.pid = pg_backend_pid();

这里为什么带pg_stat_activity？因为我想顺便确认当前查询自身的等待状态，顺带看看数据库是否存在全局性阻塞。正常情况wait_event应该是ClientRead或者空，如果出现WALReceive、WALWrite这类等待，说明后端正在等 WAL 相关操作，对分析恢复性能也有帮助。

运行结果里几个关键信息要这样读：

• gap_bytes为 0，说明备库已追平。
• gap_bytes缓慢增长，说明主库写入比备库回放快，备库可能暂时跟不上，但不一定异常，得看持续时长。
• gap_bytes持续线性增长，说明备库回放能力存在瓶颈，需要继续排查 CPU、磁盘或锁等待。
• replay_lsn长时间不变，说明回放可能完全卡住了，这是比延迟增长更严重的信号，要立刻排查startup进程状态和锁等待。

3. 实时洞察进阶：把恢复监控做成一张仪表盘

3.1 采集层：定时执行 SQL 的注意事项

基础 SQL 能查一次，但"实时洞察"需要连续采集。我的做法是用 cron 或者监控系统的采集器，每隔 10 到 30 秒执行一次上面的查询，把结果写入一张历史表。

采集时有几个细节值得注意：

• 不要使用事务包裹查询，直接SELECT即可，避免引入不必要的开销。
• 采集查询本身要轻量，尽量避免在备库上做大范围扫描；上面这些函数都是内存级读取，开销极低。
• 建议在结果里带上sample_time时间戳，方便后续计算速率和增量。
• 如果监控系统支持，可以直接把gap_bytes暴露为指标，不用自己历史表；但自己有表会更灵活，后续想回看历史也更方便。

一个简单的历史表结构可以这样建：

CREATE TABLE recovery_monitor_history ( sample_time timestamptz PRIMARY KEY, received_lsn pg_lsn, replay_lsn pg_lsn, gap_bytes numeric, in_recovery boolean );

每隔一段时间插入一行快照。积累一天后，你就能画出恢复延迟的趋势曲线，这比任何临时查询都有说服力。

3.2 展示层：按时间窗口计算回放速率

有了连续快照后，最有价值的计算是"回放速率"。回放速率 = 一段时间内回放位点推进的字节数 / 时间。它可以直观回答"备库每秒能回放多少 MB"这个问题。

比如这样一条 SQL：

SELECT sample_time, replay_lsn, pg_wal_lsn_diff(replay_lsn, lag(replay_lsn) OVER (ORDER BY sample_time)) AS replay_bytes_delta, EXTRACT(EPOCH FROM (sample_time - lag(sample_time) OVER (ORDER BY sample_time))) AS seconds_delta FROM recovery_monitor_history WHERE in_recovery ORDER BY sample_time DESC LIMIT 20;

用窗口函数lag()取上一行位点，算出两次采样之间的位点增量和时间增量。两者相除，就是该时间段的平均回放速率。注意如果两次之间发生了重启或者备库提升为新主库，位点增量可能会出现负数或跳变，要根据in_recovery字段过滤。

回放速率是恢复监控里最容易被忽视的指标。很多人只看延迟，但延迟高不一定代表恢复有问题——如果备库追平方才刚开始，延迟高是正常的；真正衡量的应该是：在主库持续写入的背景下，备库回放速率是否能跟上写入速率。如果回放速率长期低于接收速率，延迟迟早持续拉大。

3.3 用窗口函数计算 ETA，预测恢复完成时间

如果备库正在进行一次大的恢复追赶（比如刚重新搭好的备库、或者从备份恢复），我们需要知道"大约还要多久才能追平"。此时可以基于最近一段时间的平均回放速率，估算剩余 WAL 量需要多久回放完。

剩余量可以用pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn())得到。假设最近 5 分钟的平均回放速率为rate_bytes_per_sec，那么秒级别的 ETA 就是gap_bytes / rate_bytes_per_sec。

一段简单的估算 SQL 长这样：

WITH recent AS ( SELECT replay_lsn, sample_time, row_number() OVER (ORDER BY sample_time DESC) AS rn FROM recovery_monitor_history WHERE in_recovery LIMIT 10 ), first_last AS ( SELECT min(sample_time) AS start_time, max(sample_time) AS end_time, (SELECT replay_lsn FROM recent WHERE rn = 1) AS latest_replay, (SELECT replay_lsn FROM recent WHERE rn = 10) AS earliest_replay FROM recent ) SELECT pg_wal_lsn_diff(latest_replay, earliest_replay) AS replayed_bytes, EXTRACT(EPOCH FROM (end_time - start_time)) AS elapsed_seconds, pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) AS remaining_bytes, CASE WHEN EXTRACT(EPOCH FROM (end_time - start_time)) > 0 AND pg_wal_lsn_diff(latest_replay, earliest_replay) > 0 THEN pg_wal_lsn_diff(pg_last_wal_received_lsn(), pg_last_wal_replay_lsn()) / (pg_wal_lsn_diff(latest_replay, earliest_replay) / EXTRACT(EPOCH FROM (end_time - start_time))) ELSE NULL END AS eta_seconds FROM first_last;

ETA 只能当参考，因为回放速率会随着 WAL 内容类型变化：大量 DDL 操作、大批量更新、vacuum 产生的 WAL 回放速度差异很大。实践中要把采样窗口拉长一些，避免被短期抖动带偏。

4. 实战验证：用一个模拟场景测试监控 SQL

4.1 搭建隔离环境：主备实例配置要点

纸上谈兵没意思，我实际搭过一套最小化环境来验证这些 SQL。为了避免影响生产，用的是两台隔离主机，一台做主库，一台做备库，版本选的是 PostgreSQL 15，配置也不算复杂：

主库postgresql.conf里关键项：

wal_level = replica max_wal_senders = 10 archive_mode = on

备库则是在主库基础备份上启动的，配置：

hot_standby = on

然后建立流复制连接。这套环境里，备库启动后会自动进入持续恢复状态，正好适合我们做监控验证。

4.2 制造恢复追赶场景并采集数据

环境搭好后，我先在主库上进行了一轮批量写入，生成大量 WAL：插入多张表、每批提交若干事务，并在写入过程中间暂停几秒。这样做的目的是模拟"主库短时间产生大量 WAL，备库接收后回放需要一个追赶过程"的场景。

在写入的同时，我每隔 10 秒在备库执行一次采样 SQL，记录received_lsn、replay_lsn和gap_bytes。原始数据片段大概是这样：

能明显看到gap_bytes逐步缩小，说明备库回放速率高于接收速率，正在追赶主库。

4.3 结果分析：从数据里读到的事实

这批数据最有价值的地方在于，它能帮我们区分两种恢复状态：

• 追赶状态：gap_bytes为正且在缩小，说明备库回放速度快于主库写入速度，系统健康，等待追平即可。
• 落后状态：gap_bytes持续为正且在扩大，说明备库回放速度跟不上主库写入，属于性能瓶颈场景，需要进一步排查磁盘 IO、CPU 或者是否存在长时间锁等待。

在上面模拟中，我还发现一个有意思的现场：当我在主库执行了大量单条 UPDATE 时，备库回放速率的波动会比批量 INSERT 更大。原因在于 UPDATE 会产生大量 WAL 记录，每条记录的回放开销也更高，如果涉及索引更新，回放成本会进一步上升。这个结论直接影响阈值设计——不能用一个固定速率去衡量所有场景。

另外，我也测过pg_is_wal_replay_paused()的用途：在备库上执行SELECT pg_wal_replay_pause();暂停回放，然后连续采样，发现replay_lsn完全不动，但received_lsn还在增长，gap_bytes持续上升。这就是典型的"接收正常、回放卡住"状态，只有日志的话根本看不出来。恢复监控 SQL 的价值在这一刻体现得淋漓尽致。

5. 恢复监控的常见误判陷阱与阈值设计

5.1 备库空闲时的假阳性延迟

很多团队刚上恢复监控时，设置"延迟超过 100MB 就告警"，结果备库在凌晨突然疯狂告警。排查半天发现：主库业务低峰期没有写入，备库也追平了，为什么 gap 会突然变大？

原因通常出在 WAL 接收和回放的粒度上。主库长时间不产生 WAL 时，备库的received_lsn和replay_lsn都会保持不动；一旦主库产生一小批 WAL，备库接收进程会先把它拉到本地，此时received_lsn前进而replay_lsn还没跟上，瞬时 gap 就会变大。如果这段时间很短，点采样恰好落在间隙里，就会产生一个虚假的高延迟告警。

解决办法一是调大采样间隔，用连续多次采样判断趋势，而不是单点阈值；二是把"持续超过 N 秒/持续超过 N 个采样周期"作为告警条件，而不是瞬时值。

5.2 归档模式下的 replay 延迟波动

另一个容易被忽略的情况是：备库回放不仅依赖流复制接收，还依赖已经落盘的 WAL 文件。如果主库开启了archive_mode = on并且备库同时配置了restore_command（比如从归档拉取 WAL），那么恢复的节奏就不完全由流复制决定，归档拉取的速度、归档文件的可用性都会影响回放。

这种情况下，received_lsn代表的和restore_command拉取的进度可能不一致。你可能会看到流复制显示已经接收了某一段 WAL，但回放进程却在等归档文件。此时单纯比较两个 LSN 会产生误导。

我在实践中遇到过一次：备库received_lsn在正常增长，但replay_lsn停在一个 LSN 好久不动，后来发现是归档目录里的一个 WAL 段损坏，restore_command反复重试。这种情况如果只看received_lsn，甚至会觉得一切正常；必须同时关注replay_lsn和备库日志，才能发现回放阻塞。

5.3 告警阈值该按什么定：稳定基线优先

阈值设计没有万能公式，但有一条经验：先跑出稳定基线，再定阈值。我建议至少积累 3 到 5 天的恢复监控历史数据，观察正常业务周期里gap_bytes的分布，然后在这个分布基础上取 2 到 3 倍作为告警线。

更合理的指标其实不是绝对 gap 大小，而是"gap 的增速"。如果每次采样都在变大，哪怕当前绝对值不大，也要提前关注；如果 gap 绝对值大但每个周期都在缩小，说明恢复正在追平，反而不用急着处理。所以我把监控重点放在两个维度：

这套设计比单一阈值靠谱得多，因为它考虑到了时间因素和趋势因素，而不是拿一个静态数字卡所有场景。

5.4 与物理复制监控的配合

最后提醒一点：恢复监控 SQL 最好和物理复制监控一起用，不要孤立看待。pg_stat_replication视图在主库上能查到每个备库的write_lsn、flush_lsn、replay_lsn，这些反映了主库视角的复制进度；而备库本地查询则提供备库视角的恢复状态。

两者结合能排查一类非常经典的故障：备库明明显示replay_lsn一直在涨，但主库的pg_stat_replication显示某备库replay_lsn落后很多。这时问题可能出在备库上报状态的网络延迟、或者备库存在多个apply进程竞争。只有从主备两个方向同时观察，才能精确定位是接收问题、回放问题还是上报问题。

我还习惯把恢复监控 SQL 的采集周期和pg_stat_replication的采集周期保持一致，放到同一张监控表里。这样一旦出问题，可以直接做时间轴对齐，一眼看出是主库侧先异常，还是备库侧先异常。

6. 恢复监控之外：一点实操补充

如果你已经把这些 SQL 跑起来了，我再分享两个实际使用中的小经验。

第一个是历史数据保留策略。recovery_monitor_history表如果每 10 秒插一条，一天就是 8640 条，一个月大约 26 万条，在备库上长期占用磁盘虽然不算大，但查询趋势时会变慢。建议做两层保留：明细数据保留 7 天，聚合数据（每分钟一条）保留 60 天。聚合时把gap_bytes做平均，把replay_lsn做最大值，趋势判断完全够用。

第二个是不要忽略备库的wait_event变化。我在一次排查中看到备库回放卡住，replay_lsn几个采样周期都不动，但gap_bytes还在微幅波动。后来查pg_stat_activity里startup进程的wait_event，发现出现BufferIO等待，磁盘 IO 饱和导致回放跟不上。这个线索在日志里完全看不到，但 SQL 查询一次就暴露了瓶颈。

恢复监控本质上不是一两条炫技 SQL 的事，而是把 PostgreSQL 暴露给我们的这些 LSN 坐标和状态变量持续采集、组合、解读的过程。我的建议是：从最简单的一条状态查询开始，跑起来后再逐步叠加速率、趋势和历史表，最后形成一套适合自己环境的监控方案。你不需要一开始就做成一个大系统，但一定要动手跑一次，亲眼看看备库在恢复时gap_bytes是怎样变化的——这个过程比看十篇文档都有用。