PostgreSQL误删数据的救命稻草

一、初识pg_resetwal

pg_resetwal类似于Oracle的flashback工具，当生产环境发生误删除、修改数据的时候，能够尽可能恢复原有数据，通过pg_resetwal来重新回滚到wal日志的指定位置。pg_resetwal会清除预写日志，并可选地重置pg_control文件中的一些其他控制信息。当 WAL 文件或 pg_control 控制文件损坏时，导致数据库无法启动时，该操作将作为数据库修复的最后手段使用，通常也称pg_resetwal为postgresql数据库最后的救命稻草。

语法如下：

二、故障恢复流程

1、准备测试数据

2、模拟误删除数据，此处删除id=2的记录

3、查看当前lsn及wal文件

select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());

4、切换wal日志再进行waldump挖掘日志，并找到如上 0/1000c038之前的delete记录，确定事务ID为 652

postgres=# select pg_switch_wal();     --切换wal文件，否则会报错
[linux-postgresql-13]$ pg_waldump -b -p $wal路径 $wal文件名 > 目标.log    --挖掘wal日志，获取事务ID

5、关闭数据库，根据wal日志回滚到目标事务ID

[linux-postgresql-13]$ pg_ctl -D $PG_DATA stop   --关闭数据库
[linux-postgresql-13]$ pg_resetwal -x 事务ID -D $PG_DATA   --回滚到指定事务ID

6、查看表记录恢复成功

三、注意事项

①数据库正在运行时，无法使用此命令

②pg_resetwal只能恢复dml动作，无法恢复truncate等操作

③pg_resetwal会对整个实例进行回滚到指定的事务ID，且WAL日志整体被清理

④生产环境慎用，可以将数据拷贝到测试环境进行恢复后再还原至生产