如果你管过 PostgreSQL 的主从架构大概率经历过这种时刻备库恢复突然变慢、主备延迟飙高、或者备库干脆卡住不动而日志里只剩下一条孤零零的restartpoint complete。真正让人头疼的是——你没法从日志里看清现在到底恢复到哪里了、还剩多少、速度是快是慢。这时候用 SQL 直接查询恢复状态就成了最顺手也最可靠的监控手段。这篇内容不是理论科普是我把日常运维里用过的 PostgreSQL recovery 监控查询整理成的一份实战笔记。读完后你会知道恢复监控到底在看哪些指标、每条 SQL 背后的含义是什么、如何组合成一套实时洞察工具以及哪些常见的坑会让你的监控判断完全跑偏。适合正在维护 PostgreSQL 主备环境的 DBA、运维工程师也适合打算自己搭一套轻量监控的同学。1. 先说清楚恢复监控到底在盯什么1.1 从主库到备库数据是怎么追过去的要理解恢复监控先得理解 PostgreSQL 在备库上做的事。主库持续产生 WALWrite-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 接收进程最近接收并落盘到的日志序列号LSNpg_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这个问题。比如这样一条 SQLSELECT 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. 实战验证用一个模拟场景测试监控 SQL4.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。原始数据片段大概是这样sample_timereceived_lsnreplay_lsngap_bytes2025-01-15 10:00:004A/D90000004A/D8000000167772162025-01-15 10:00:104A/DA0000004A/D8200000146800642025-01-15 10:00:204A/DB0000004A/D850000010485760能明显看到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 绝对值大但每个周期都在缩小说明恢复正在追平反而不用急着处理。所以我把监控重点放在两个维度维度指标告警判断状态replay_lsn是否长时间不变连续 3 个采样周期不变触发告警趋势gap_bytes的时间差分连续 3 个采样周期递增触发告警能力回放速率 vs 接收速率回放速率持续低于接收速率触发告警健康pg_stat_wal_receiver.status非streaming触发告警这套设计比单一阈值靠谱得多因为它考虑到了时间因素和趋势因素而不是拿一个静态数字卡所有场景。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是怎样变化的——这个过程比看十篇文档都有用。
