PostgreSQL 误删数据怎么找回：PITR 恢复到误删前实测，recovery_target_inclusive 默认值让我第一次恢复了个寂寞
在 PostgreSQL 17.11 测试实例（orders 200 万行自造数据，开 WAL 归档）上实测按时间点恢复（PITR）找回一条漏写条件的 DELETE 删掉的 599100 行：基础备份 209MB、恢复到误删前约 0.6–0.9 秒，再只把被删的行导回原库约 2.6–2.8 秒，误删之后写入的数据不丢。踩坑三个：recovery_target_xid 默认 inclusive=true 会把误删也重放；最后一个 WAL 段没归档时恢复直接 FATAL；停在 DROP TABLE 提交前时查这张表会被恢复进程的锁卡住。附只读就绪度检查 SQL 和可复跑脚本。
PostgreSQL 17.11 实测（测试实例、自造数据）：orders 200 万行，一条漏了条件的 DELETE 删掉 599100 行。用误删前的基础备份（209MB）+ WAL 归档在另一个目录拉起恢复实例，recovery_target_time 设成误删前记下的时间，约 0.6–0.7 秒停在「recovery stopping before commit of transaction 741」，orders 回到 200 万行；再用 \copy 把 599100 行导出、导回原库，约 2.6–2.8 秒，误删后写入的 500 条 payments 不受影响。同一个 xid 用 recovery_target_xid 但不设 recovery_target_inclusive=false，会停在提交之后，orders 还是 1400900 行。
适用对象
- PITR 不要直接在原库上做：在另一个目录用「误删前的基础备份 + WAL 归档」拉一个恢复实例，确认数据对了，再只把被删的行搬回原库。本次恢复约 0.6–0.9 秒，搬回 599100 行约 2.6–2.8 秒，误删后的新写入不丢。
- 按事务号恢复时，recovery_target_xid 默认 recovery_target_inclusive=true，会把这个事务本身也重放掉；找回误删要显式设 recovery_target_inclusive = false。
- 恢复只能用到已经归档的 WAL。误删写的 WAL 很少（比如 DROP TABLE）时，它所在的段可能还没归档，恢复会报 FATAL: recovery ended before configured recovery target was reached；原库还活着就先执行 pg_switch_wal()。
- 恢复默认 recovery_target_action = pause，实例只读；停在 DROP TABLE 提交前时，恢复进程持有这张表的 AccessExclusiveLock，查询会一直等，要 promote 之后才能查。
按时间恢复到误删前：在旁边拉一个恢复实例，约 0.6 秒停在误删提交前
步骤：① 把误删前做的 pg_basebackup（-Fp -X stream -c fast，209MB，528–719ms）整目录复制到新目录；② 在新目录的 postgresql.conf 里改端口、关掉 archive_mode（免得恢复实例往同一个归档目录写），加上 restore_command = 'cp /归档目录/%f %p' 和 recovery_target_time = '误删前的时间'；③ touch recovery.signal；④ pg_ctl start。
日志依次是 starting point-in-time recovery to 2026-10-22 10:30:52.770016+08、recovery stopping before commit of transaction 741、pausing at the end of recovery。连上去查：orders 2000000 行，refunded 599100 行，pg_is_in_recovery() = t，pg_get_wal_replay_pause_state() = paused；试着插一行，报 ERROR: cannot execute INSERT in a read-only transaction。这是默认的 recovery_target_action = pause：停在目标点、只读，留给你确认数据。
recovery_target_time 的含义是「停在第一个提交时间晚于它的事务之前」。本次误删前那一刻到误删提交之间没有别的事务提交，所以正好停在 741 之前；生产库上这几秒里还有别的事务时，它们也会被挡在外面，这正是下一步不整库回滚、只搬数据的原因。
| 场景 | 恢复目标 | 日志里的停止点 | 结果 | 第 1 轮 | 第 2 轮 | 脚本复跑 |
|---|---|---|---|---|---|---|
| R2 按时间 | recovery_target_time = 误删前时间 | before commit of transaction 741 | orders 2000000；paused | 约 0.63 s | 约 0.73 s | 约 0.63 s |
| R3 按 xid | recovery_target_xid = 741，inclusive = false | before commit of transaction 741 | orders 2000000 | 约 0.73 s | 约 0.83 s | 约 0.83 s |
| R4 按 xid（默认） | recovery_target_xid = 741 | after commit of transaction 741 | orders 1400900，refunded 0 | 约 0.83 s | 约 0.83 s | 约 0.83 s |
只把被删的行搬回原库：2.6–2.8 秒，误删之后的新数据不丢
如果直接让恢复实例 promote 顶替原库，误删之后原库上新写的数据都会丢。本次误删后原库又写了 500 条 payments（共 2500 条），恢复实例里只有 2000 条。
所以我只搬数据：在恢复实例上 \copy (SELECT * FROM orders WHERE status = 'refunded') TO 'refunded.csv'，599100 行导出 240–276ms；在原库上开一个事务，建临时表 (LIKE orders)，\copy 进去，再 INSERT INTO orders SELECT * FROM 临时表 ON CONFLICT (id) DO NOTHING，2586–2840ms。完成后原库 orders 2000000 行、refunded 599100 行、payments 2500 行。
两点我会在生产上多做：一是导回前先确认这批 id 在原库里确实不存在（ON CONFLICT DO NOTHING 只是兜底）；二是检查序列，本次 max(id) = 2000000、orders_id_seq 的 last_value 也是 2000000，没有冲突；如果误删后又插过新行，要确认新行没有复用被删的 id。
recovery_target_xid 默认会把误删本身也重放：恢复完 orders 还是 1400900 行
按时间恢复要靠应用侧有人记下误删前的时间，多数时候没有。更准的办法是找到误删事务的 xid。本次用 pg_waldump 从基础备份起点（START WAL LOCATION 0/15000028）扫归档，-x 741 只看这个事务：599100 条 Heap DELETE 记录 + 1 条 COMMIT（lsn 0/1F908C40，COMMIT 2026-10-22 10:41:23.579097 CST），从 …15 扫到 …1F 共 11 个段，约 5.6 秒。真实排查时还不知道 xid，可以去掉 -x，在误删时间附近找「大量 DELETE + 一个 COMMIT」的事务。
拿到 xid 后，我第一次只写了 recovery_target_xid = '741'，恢复日志是 recovery stopping after commit of transaction 741，orders 1400900 行、refunded 0 行，等于白恢复。原因是 recovery_target_inclusive 默认 on：停在目标事务「之后」。加上 recovery_target_inclusive = false 再来，日志变成 recovery stopping before commit of transaction 741，orders 2000000 行。
按时间恢复也受这个参数影响：目标时间恰好等于某个事务的提交时间时，默认会包含这个事务。所以我现在的习惯是：找回误删一律显式写 recovery_target_inclusive = false。
最后一个 WAL 段没归档：恢复直接 FATAL，pg_switch_wal() 之后才行
第二场我模拟了一个写 WAL 很少的误操作：DROP TABLE payments。执行后原库正在写的是 000000010000000000000029，最后归档的是 …28；DROP 的提交记录还在 29 里，没归档。
这时按 DROP 之前的时间恢复，日志是 starting point-in-time recovery、redo done at 0/28FFFFB8，接着 FATAL: recovery ended before configured recovery target was reached，实例退出。恢复把能拿到的 WAL 都重放完了，也没看到一个提交时间晚于目标的事务，于是不知道该停在哪；PostgreSQL 17 宁可报错退出，也不会把「重放到哪算哪」的结果交给你。
原库还活着时，先在原库执行 SELECT pg_switch_wal()，当前段立刻归档（本次 29 进了归档目录），再重新恢复就正常了，停在 before commit of transaction 745。原库已经坏了的话，要从原库的 pg_wal 目录里把还没归档的段手工拷出来，这一步本次没有实测。
本节判断
- 平时把 archive_timeout 设成大于 0 可以缩小「没归档」的窗口：本次设成 10s 后，有写入时大约每 10 秒切一次段并归档。代价是每个归档文件都是完整的 16MB，切得越勤归档越占空间；具体取值按可接受的丢失窗口和归档存储来定（经验判断）。
- 只读 SQL 第 3 段会同时列出当前 WAL 段和最后归档的段，以及当前段已经写了多少字节。
恢复停在 DROP TABLE 提交前，查这张表一直卡住：锁在恢复进程手里
切完 WAL 重新恢复后，实例停在 before commit of transaction 745（就是那个 DROP TABLE），我照例连上去 SELECT count(*) FROM payments 核对，结果命令一直不返回。第一轮我等了一阵手工杀掉了；第二轮加了 5 秒的 statement_timeout，报 ERROR: canceling statement due to statement timeout。
查 pg_locks：payments 上有一把已授予的 AccessExclusiveLock。DROP TABLE 在 WAL 里先记录了排他锁，提交记录还没重放，恢复进程就替这个「还没结束的事务」一直拿着锁。对比实验一的 DELETE：它只拿行级锁和 RowExclusiveLock，暂停时查 orders 没有任何阻塞。
解决办法是确认停止点没问题后 promote：SELECT pg_promote(true, 60)，约 1.1 秒返回，恢复结束，没提交的 DROP 被当成中止，新 WAL 文件名变成 000000020000000000000029（时间线 2），payments 查出 2500 行。promote 之后这个实例就是一个独立的新库，不能再继续往前重放，所以我会先在暂停状态下把能查的都查完，查不了的表再 promote。
pg_ctl start 返回了，不代表已经恢复到目标点
我第一版脚本在 pg_ctl start -w 返回后马上查数据，结果数字对不上。日志显示 pg_ctl 约 0.2 秒就返回了，而恢复到目标点要 0.6–0.9 秒：恢复实例开着 hot_standby，到达一致性点就开始接受只读连接，之后才继续重放到目标。中间这段时间查到的是「重放到一半」的数据。
同一轮里还有一个低级错误：上一个恢复实例没停，还占着同一个端口，新实例起不来，我查到的其实是旧实例。现在脚本里每个恢复实例用不同端口，并且一直等到日志出现 pausing at the end of recovery 或 FATAL 才开始查。
我找回误删数据的顺序
- 01
1
先止血
停掉继续写这张表的任务；原库还活着就执行 pg_switch_wal()，确认 pg_stat_archiver 里最新段已归档。
- 02
2
定停止点
有可靠的误删前时间用 recovery_target_time；没有就用 pg_waldump 找 xid，配 recovery_target_inclusive = false。
- 03
3
旁路恢复
基础备份复制到新目录、换端口、关 archive_mode，等日志出现 pausing 再核对行数。
- 04
4
只搬数据
导出被删的行，在原库事务里导回，核对主键冲突和序列；不要让恢复实例直接顶替原库，除非接受丢掉误删后的写入。
出事之前先确认能不能 PITR：只读就绪度检查
只读 SQL 5 段，开头 SET statement_timeout = '30s'，只查 pg_settings、pg_stat_archiver 和系统函数，不切 WAL、不改参数。第 1 段列 wal_level、archive_mode、archive_command / archive_library、archive_timeout 和恢复相关参数；第 2 段看归档成功 / 失败的最近文件和时间；第 3 段对比当前 WAL 段和最后归档的段；第 4 段看最近检查点；第 5 段每项给一个 true / false。
测试实例上的输出：前 5 项都是 t，最后一项「archive_timeout 已设置」是 f，因为本次用的是默认 0；第 3 段当前段 …2B、最后归档 …2A、当前段已写 33 kB，也就是说这 33 kB 的改动此刻还恢复不到。
这份检查只能说明「归档在跑」，证明不了「能恢复」。能不能恢复，只有真的拉一个恢复实例才知道，这正是下载的演示脚本做的事；生产库建议至少每月在测试机上按同样步骤演练一次（经验判断）。
觉得有用？转给正在做这件事的同事或朋友
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
PostgreSQL 误删数据了还能恢复吗？
开了 WAL 归档、并且有误删前的基础备份，就可以用 PITR 恢复到误删前。本次 PostgreSQL 17.11 测试实例上，200 万行的表误删 599100 行，恢复实例约 0.6–0.9 秒停在误删提交前。
PITR 恢复会不会把误删之后的新数据也丢掉？
直接用恢复实例顶替原库会丢。建议在旁边拉恢复实例，只把被删的行导出再导回原库；本次导回 599100 行约 2.6–2.8 秒，误删后写入的数据不丢。
recovery_target_xid 恢复后数据还是没了，为什么？
recovery_target_inclusive 默认是 on，会停在目标事务提交之后。找回误删要设 recovery_target_inclusive = false，本次设置后日志从 after commit 变成 before commit，行数恢复正常。
恢复时报 recovery ended before configured recovery target was reached 怎么办？
说明需要的 WAL 还没归档，恢复读完所有归档也没到目标点。原库还在就先执行 SELECT pg_switch_wal() 让当前段归档，再重新恢复；本次切完后恢复成功。
恢复实例里查某张表一直卡住是什么原因？
如果停止点在一个 DROP TABLE、TRUNCATE 之类的事务提交前，恢复进程会一直持有这张表的 AccessExclusiveLock。确认停止点后执行 pg_promote()，本次 promote 约 1.1 秒，之后就能查。
下一步
推荐动作
你提供这份只读就绪度检查的结果和备份 / 归档配置（可脱敏），我们判断现在出了误删能不能恢复、能恢复到哪一刻，并给出在测试机上演练 PITR 的步骤；生产变更和恢复操作由你方授权执行。