一 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数据库删除了原始列的元数据信息，因此需要在表列名中指定正确的类型进行完整性检查后按值传递。