使用插件pg_dirtyread闪回查询PG数据库

一 pg_dirtyread简介
误删除数据恢复
使用开源插件pg_dirtyread可以读取未被vacuum的dead数据

版本支持
支持PostgreSQL数据库12和13版本

语法
SELECT * FROM pg_dirtyread('tablename') AS t(col1 type1, col2 type2, ...)

二 pg_dirtyread部署
安装插件pg_dirtyread
wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.3.zip

unzip 2.3.zip

cd pg_dirtyread-2.3/

make PG_CONFIG=/var/postgresql/soft/pg12.8/bin/pg_config

make install PG_CONFIG=/var/postgresql/soft/pg12.8/bin/pg_config

扩展插件pg_dirtyread
postgres=# CREATE EXTENSION pg_dirtyread;

扩展插件pageinspect
postgres=# CREATE EXTENSION pageinspect;

三 pg_dirtyread示例
恢复误删除的表记录
postgres=CREATE TABLE t1 (a bigint, b text);

关闭掉t1表自动vacuum

ALTER TABLE t1 SET (

autovacuum_enabled = false, toast.autovacuum_enabled = false

);

kingdee=# DELETE FROM t1 WHERE a = 1;

DELETE 1

kingdee=# select * from t1 ;

a | b

---+----------

2 | New Test

kingdee=# SELECT * FROM pg_dirtyread('t1') as t(a bigint, b text);

a | b

---+----------

1 | Test

2 | New Test

(2 rows)

可以看到被删除的记录(1, 'Test')已经可以查询到

恢复drop掉的列
kingdee=# SELECT * FROM pg_dirtyread('ab') ab(a text, dropped_2 text);

可以看到虽然b列被drop掉了，但是仍然可以读取到数据（这里使用dropped_N来访问第N列，从1开始计数）。

注意：由于PostgreSQL数据库删除了原始列的元数据信息，因此需要在表列名中指定正确的类型进行完整性检查后按值传递。