使用 pg_recovery 恢复数据

介绍

pg_recovery 是一款 PostgreSQL 数据找回工具。可以恢复 COMMIT / DELETE / UPDATE / ROLLBACK / DROP COLUMN 操作后导致的数据变化，并以表的形式返回。

安装

wget https://codeload.github.com/radondb/pg_recovery/zip/refs/heads/master -O /tmp/pg_recovery.zip

unzip pg_recovery.zip

cd /tmp/pg_recovery-master

make PG_CONFIG=/usr/local/pgsql/bin/pg_config

make install PG_CONFIG=/usr/local/pgsql/bin/pg_config

create extension pg_recovery

准备表和数据

update

delete

rollback

drop column

select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname=‘hero’ and attname ~ ‘dropped’;

select * from public.pg_recovery(‘hero’) as (id int, dropped_attnum_2 varchar(20));

显示该表历史上所有写入过的数据。

select * from public.pg_recovery(‘hero’, recoveryrow => false) as (id int, recoveryrow bool);

注意事项

目前 pg_revovery工具已支持 PostgreSQL 12/13/14 ，pg_recovery 通过读取 PostgreSQL dead 元组来恢复不可见的表数据。如果元组被 vacuum 清除掉，那么 pg_recovery 便不能恢复数据。建议每个表加一个时间列方便恢复。