前事不忘,后事之师,不忘国耻!

 找回密码
 用户注册
搜索
查看: 19|回复: 0

[开发应用] PostgreSQL WAL 归档与 PITR 恢复实战:从误删表无从回退到归档链监控与恢复窗口双线收敛

[复制链接]
发表于 昨天 05:49 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?用户注册

×
PostgreSQL WAL 归档与 PITR 恢复实战:从误删表无从回退到归档链监控与恢复窗口双线收敛


一、具体的问题

周五晚上快下班,业务侧反馈订单明细表查不到了——一查操作日志,是有人在生产库直接跑了 TRUNCATE orders_detail,没有 WHERE、没有事务包裹,一秒内清空。DBA 手里只有每周日凌晨跑一次的 pg_dump 逻辑备份,重放只能回到一周前,中间六天的增量数据没有任何手段补回;就算手里有物理基础备份,WAL 段全堆在 pg_wal 里早被 checkpoint 回收复用,想重放也无处取日志。这类事故在 PostgreSQL 环境里反复出现,根子是三个具体缺口:一是归档没开或者形同虚设,archive_command 一直在报错,failed_count 涨了三个月没人看;二是真出事时不知道该恢复到哪个点,服务器时钟和业务时钟对不齐、时区写错、操作发生时间比发现时间早四十分钟,全靠拍脑袋;三是恢复过程一知半解,recovery_target_action 没设好,重放冲过了误删点才发现,而目标点一旦越过就没有回头路。

二、核心原理

1. PITR 的本源是"基础备份 + WAL 重放"。 WAL 记录每一次数据变更,只要基础备份之后的所有 WAL 段连续可取,就能从备份完成点重放到任意停止点——按 LSN、按时间戳、按事务号、按命名还原点都行。三个要素缺一不可:一份完整的物理基础备份(pg_basebackup 产出)、一条不断档的 WAL 归档链、一个明确的恢复目标。少任何一件,PITR 就只存在于文档里。

2. 归档链的契约比配置本身更重要。 archive_command 只对已写满的 WAL 段执行,契约有三条:成功必须返回退出码 0,返回非 0 时 PostgreSQL 会保留该段并周期性重试,pg_wal 持续堆积,塞满盘直接 PANIC 停机(已提交事务不丢,但库下线直到人工清场);命令必须拒绝覆盖归档目录里已存在的同名文件——两套库误写同一归档目录、或崩溃重启后重复归档同一段,都靠这条守住归档完整性;归档目录必须放在独立磁盘或异地,和数据盘放一起等于没备份。一个常见陷阱:GNU cp -i 在目标已存在时返回 0,看似安全实际允许覆盖,必须用 test ! -f 前置判断。

3. 恢复侧的机制决定容错空间。 PG12 起用空文件 recovery.signal 替代 recovery.conf;restore_command 负责从归档取段,找不到文件必须返回非 0,重放遇到缺口会停下而不是跳过——归档链有洞就等于不可恢复。恢复目标五选一(recovery_target / _lsn / _name / _xid / _time),配置里最后一个生效;到点后的默认动作是 pause:只要 hot_standby 开着,就能以只读方式连上去验证数据是不是恢复对了,确认后再 pg_wal_replay_resume() 或 pg_promote() 放行。关键约束有两条:恢复目标只能落在基础备份完成点之后;目标只能后移不能前移,冲过头的唯一退路是重新解基础备份从头重放——这正是默认 pause 的价值。

三、实例参考(动手步骤)

步骤 0:摸清现状基线(五分钟,先别改任何配置)。
  1. SHOW archive_mode;  SHOW archive_command;  SHOW archive_timeout;
  2. SELECT archived_count, last_archived_wal, failed_count,
  3.        last_failed_wal, last_failed_time FROM pg_stat_archiver;
复制代码
再在操作系统层看 du -sh $PGDATA/pg_wal。如果 archive_mode=off 或 failed_count>0,先记下数字,这是治理前后对比的起点。

步骤 1:开归档并实测归档链通不通。
  1. # postgresql.conf
  2. wal_level = replica
  3. archive_mode = on
  4. archive_command = 'test ! -f /archive/wal/%f && cp %p /archive/wal/%f'
  5. archive_timeout = 300
复制代码
归档目录 /archive/wal 放独立磁盘。archive_mode 和 wal_level 改完必须重启(pg_ctl restart),archive_command 和 archive_timeout 支持 reload。重启后强制切一段验证:
  1. SELECT pg_switch_wal();
  2. -- 等 2 秒再查
  3. SELECT last_archived_wal, last_archived_time, failed_count FROM pg_stat_archiver;
复制代码
last_archived_wal 前进且归档目录出现新文件、failed_count=0,归档链才算真正打通。

步骤 2:定期物理全量备份。
  1. CREATE ROLE backupusr WITH REPLICATION LOGIN PASSWORD '***';
复制代码
pg_hba.conf 放行后:
  1. pg_basebackup -U backupusr -D /backups/base/20261120 -Ft -z -X stream -c fast -P
复制代码
-X stream 把备份期间产生的 WAL 一并打入,备份自包含;记录备份完成时刻——PITR 只能恢复到这个时刻之后。

步骤 3:危险操作前打命名还原点。
  1. SELECT pg_create_restore_point('pre-truncate-orders-20261120');
复制代码
同时把 log_statement = 'ddl' 布置上,让下一次 DROP/TRUNCATE 在日志里留下带时间戳的痕迹。

步骤 4:演练一次完整 PITR(误删表场景,建议先在测试库走通)。
  1. pg_ctl stop -D $PGDATA -m fast
  2. mv $PGDATA ${PGDATA}.broken          # 保留坏库,里面有未归档的 WAL
  3. mkdir -m 700 $PGDATA
  4. tar -xzf /backups/base/20261120/base.tar.gz -C $PGDATA
  5. rm -rf ${PGDATA:?}/pg_wal/*          # 重放须从归档取段
  6. cat >> $PGDATA/postgresql.conf <<'EOF'
  7. restore_command = 'cp /archive/wal/%f %p'
  8. recovery_target_name = 'pre-truncate-orders-20261120'
  9. recovery_target_action = pause
  10. EOF
  11. touch $PGDATA/recovery.signal
  12. pg_ctl start -D $PGDATA
复制代码
等重放到目标点后用只读连接验证:被删的表在不在、行数对不对。确认无误执行 SELECT pg_wal_replay_resume();(或 SELECT pg_promote();),实例开启新 timeline 接受写入,再用 pg_dump 把关键表导回原库。

步骤 5:把归档链纳入监控。 三条告警线:failed_count 一旦增长立即告警;last_archived_time 滞后超过 archive_timeout 的两倍告警(低写入库归档停摆的信号);pg_wal 目录容量超 80% 预警(归档卡死时它会先撑满磁盘)。

治理前后对比

指标治理前治理后
数据丢失窗口一周(逻辑备份周期)≤5 分钟(archive_timeout=300)
可恢复的粒度只能整库回到备份日任意 LSN / 时间 / 事务 / 命名还原点
归档故障发现3 个月后翻统计才见failed_count 告警,5 分钟内
恢复点定位拍脑袋猜时间戳pg_waldump + 命名还原点,精确到 LSN
恢复验证方式promote 完才发现冲过头pause 只读验证后再放行
恢复演练从未做过每季度一次,全程 25 分钟
pg_wal 突满PANIC 停机才知道容量 80% 预警,留出处置时间


四、实操检查清单


  • SHOW archive_mode 确认 on,改配置后确实重启过。
  • archive_command 含 test ! -f 前置判断,手工执行一遍退出码为 0。
  • 归档目录在独立磁盘或异地,与数据盘、pg_wal 不同盘。
  • archive_timeout=300,低写入量的库必设,不能留默认 0。
  • pg_switch_wal() 实测过:last_archived_wal 前进、目录里出现新文件。
  • failed_count 接入告警,持续增长即人工介入。
  • last_archived_time 滞后告警阈值设为 archive_timeout×2。
  • pg_wal 目录容量监控,80% 预警。
  • pg_basebackup 每周至少一次,-X stream,完成时刻有记录。
  • 一切危险变更(TRUNCATE/DROP/批量 UPDATE)前先 pg_create_restore_point。
  • log_statement='ddl' 长期开着,事故后有据可查。
  • 每季度做一次完整恢复演练:pause 验证 → 确认 → 再 promote。


几个容易踩的坑


  • GNU cp -i 当防覆盖手段:目标已存在时它返回 0,等于放行覆盖,归档链可能被同名段毁掉。用 test ! -f && cp。
  • 改了 archive_mode 不重启:配置看着都对,归档一动不动。archive_mode 和 wal_level 必须重启生效,archive_command 才是 reload 级。
  • archive_timeout 留默认 0:低写入库 WAL 段几小时切不了一次,出事时最新变更根本没进归档。
  • 恢复目标设在基础备份完成点之前:官方明确不可能,重放起点就是备份结束点,更早的只能换更早的备份。
  • 冲过恢复目标想回头:目标只能后移不能前移,唯一退路是重解基础备份重头重放。所以永远用 pause,验证后再放行。
  • recovery_target_time 依赖时钟:服务器时钟偏差、时区缩写歧义都能让停止点偏掉。时间戳写全时区偏移量,更稳的是 pg_waldump 从归档段里找出坏事务的 xid/LSN,用 recovery_target_lsn 或命名还原点。
  • 配了多个 recovery_target*:配置文件里最后一个生效,前面全是摆设,别堆参数。
  • 忘了清掉基础备份自带的 pg_wal 内容:重放优先用旧段,可能停在错误位置。解完备份先 rm -rf pg_wal/*。
  • restore_command 找不到文件返回 0:重放会"成功"结束在错误的位置。契约是成功才 0、找不到必须非 0。
  • 只备份不演练:归档链断了三年没人知道,第一次真恢复就是事故现场。归档监控比备份任务日志可靠,恢复演练是唯一能把"纸面可用"变成"真可用"的手段。
免责申明1、欢迎访问本站,本文内容及相关资源来源于网络,版权归版权方所有!本站原创内容版权归本站所有,请勿转载!
2、本文内容仅代表作者观点,不代表本站立场,作者自负,本站资源仅供学习研究,请勿非法使用,否则后果自负!请下载后24小时内删除!
3、本文内容,包括但不限于源码、文字、图片等,仅供参考。本站不对其安全性,正确性等作出保证。但本站会尽量审核会员发表的内容。
4、如本帖侵犯到任何版权问题,请立即告知本站 ,本站将及时删除并致以最深的歉意!客服邮箱:admin@dbabbs.com
您需要登录后才可以回帖 登录 | 用户注册

本版积分规则

Archiver|小黑屋|DBA论坛中国 ( 鲁ICP备20017503号-2 )

GMT+8, 2026-11-23 05:01 , Processed in 0.020650 second(s), 9 queries , MemCached On.

Powered by Discuz! X5.0

© 2001-2026 Discuz! Team.

快速回复 返回顶部 返回列表