PostgreSQL / 系统课时

恢复误删或误更新的数据

恢复误删或误更新的数据

误删或误更新发生后,最安全的做法通常不是直接覆盖生产库,而是先根据事故时间和业务证据确定恢复目标,在隔离实例执行 PITR,再按影响范围选择局部数据修复或整库回切,从而尽量保留事故后的合法交易。

学习目标

  • 根据日志、审计和业务证据确定可验证的 PITR 目标点。
  • 在隔离数据目录恢复到错误事务之前,并确认恢复确实暂停在目标边界。
  • 在局部数据修复与整库回切之间做选择,避免丢失事故后的正常数据。

先确定事故边界

先保全当前生产库、WAL 归档和相关日志,不要为了“尽快恢复”删除现有数据目录或清理归档。目标时间应尽量依据错误事务的提交时间,而不是只依据用户点击时间或应用请求开始时间;如果已经设置过命名恢复点,应优先使用能明确定位的恢复点。

若只有时间证据,要统一时区,并把应用日志、数据库日志、审计记录和业务异常首次出现时间放在同一时间轴上。对错误提交时间仍有不确定性时,应先恢复到更早的候选点验证,而不是冒险让错误事务被重放。

误删误更新事故从证据定位到隔离 PITR 再到修复或回切的流程

在隔离实例执行 PITR

以下以 PostgreSQL 14 及以上版本为例,恢复触发方式使用 PostgreSQL 12 起提供的 recovery.signal。准备一份结束时间早于目标点的物理基础备份,将其恢复到独立的 <PATH>/pitr-incident,并确认 WAL 归档覆盖目标时间。不要让业务流量连接这个实例。

把 <PATH>、<TIME> 和 <PORT> 替换成真实值后执行。这里将 <TIME> 设为已经确认的错误事务提交时间,并用 recovery_target_inclusive = false 让恢复停在该提交之前。

bash
PGDATA='<PATH>/pitr-incident'

cat >> "$PGDATA/postgresql.auto.conf" <<'EOF'
restore_command = 'cp <PATH>/wal-archive/%f %p'
recovery_target_time = '<TIME>'
recovery_target_inclusive = false
recovery_target_action = 'pause'
hot_standby = on
port = <PORT>
EOF

touch "$PGDATA/recovery.signal"
pg_ctl -D "$PGDATA" start

预期输出:

text
waiting for server to start.... done
server started

如果 <TIME> 只是确认安全的较早点,而不是错误事务本身的提交时间,recovery_target_inclusive 不能弥补证据不精确;仍应通过业务数据验证边界。若 WAL 在目标点之前就耗尽,恢复不算成功,应先补齐归档链路。

验证暂停点并提取正确数据

恢复到目标后,应确认 WAL 回放确实处于暂停状态,并记录最后重放事务时间。连接参数按隔离实例实际环境填写。

bash
psql "host=127.0.0.1 port=<PORT> dbname=postgres user=<USER>" -c "SELECT pg_is_in_recovery() AS in_recovery, pg_get_wal_replay_pause_state() AS pause_state, pg_last_xact_replay_timestamp() AS replay_time;"

预期输出:

text
 in_recovery | pause_state |      replay_time
-------------+-------------+------------------------
 t           | paused      | <REPLAY_TIME>
(1 row)

<REPLAY_TIME> 是最后一个已重放事务的提交时间;当错误事务被 recovery_target_inclusive = false 排除时,它应早于该错误事务的提交时间。PostgreSQL 12~13 可用 pg_is_wal_replay_paused() 做兼容性检查。

对于只影响少量行或少数表的事故,优先从隔离实例导出事故前的正确记录,再在生产库通过 staging 表对比后合并。下面假设事故对象为 public.orders,实际使用时替换 <DB>、<USER>、<ID_LIST> 和输出路径。

bash
psql "host=127.0.0.1 port=<PORT> dbname=<DB> user=<USER>" -c "\copy (SELECT id, status, updated_at FROM public.orders WHERE id IN (<ID_LIST>) ORDER BY id) TO '<PATH>/orders-recovered.csv' CSV HEADER"

预期输出:

text
COPY <N>

<N> 是实际导出的行数。把文件送入生产前,应先导入独立 staging 表,对比当前值与恢复值,只更新已确认受事故影响的主键;不要用整表覆盖修复少量记录。

选择逻辑修复还是整库回切

情况 更合适的动作 主要代价
少量行、少数表被误删或误更新 从 PITR 实例提取正确数据,事务化合并到当前生产库 需要逐条确认业务键和关联关系
大范围跨表污染、批量 DDL 或状态难以逐项修补 评估后执行整库回切 目标点之后的合法写入需要另行补偿
事故边界仍不确定 继续在隔离实例尝试更早或更晚的恢复目标 增加恢复验证次数,但不破坏现网

整库回切前必须冻结或排空生产写入,保留当前生产库作为可回退证据,并明确目标点之后有哪些合法交易会消失。若决定采用已经暂停且验证通过的 PITR 实例作为新主库,应在受控切换窗口结束恢复,再重新接入归档、监控和业务流量。

局部数据修复的核心是只取回错误操作破坏的那部分历史;整库回切的核心是接受整个集群回到旧时间点。把两种动作区分开,才能避免修复一次误操作时再制造一次更大的数据丢失。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

恢复误删或误更新的数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通