在我们学习完PG的MVCC机制之后，对于DML操作，被操作的行其实并未被删除，只能手工vacuum或自动vacuum触发才会清理掉这些无效数据，也就是死元组。

基于这种机制，在发生死元组清理动作之前，只需要将其中不可见的行中的数据解析出来，或者发生脏读，就可以获取到误删除的数据。虽然PG不支持脏读，但今天介绍的pg_dirtyread插件，可以实现脏读。

一、安装pg_dirtyread

下载地址：GitHub - df7cb/pg_dirtyread: Read dead but unvacuumed tuples from a PostgreSQL relation

这个网页上有详细的安装说明

编译安装

unzip pg_dirtyread-master.zipcd pg_dirtyread-master/makemake install

二、使用示例

2.1、在对应库创建EXTENSION

create extension pg_dirtyread ;

2.2、创建表并禁用autovacuum

testdb=# CREATE TABLE t1 (id int, name text);CREATE TABLE

2.3、插入并通过heap_page_items查看数据

testdb=# INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'),(3,'ccc');INSERT 0 3testdb=#testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0)); tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data-------+--------+--------+-------+--------+----------------------------     1 |   1104 |      0 |     0 | (0,1)  | \x010000000000000009616161     2 |   1104 |      0 |     0 | (0,2)  | \x020000000000000009626262     3 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363(3 rows)

2.4、删除数据

testdb=# DELETE FROM t1 WHERE id = 1;DELETE 1testdb=# DELETE FROM t1 WHERE id = 2;DELETE 1testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0)); tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data-------+--------+--------+-------+--------+----------------------------     1 |   1104 |   1105 |     0 | (0,1)  | \x010000000000000009616161     2 |   1104 |   1106 |     0 | (0,2)  | \x020000000000000009626262     3 |   1104 |      0 |     0 | (0,3)  | \x030000000000000009636363(3 rows)

这里发现被删除的数据还在块中

2.5、 发现数据被误删除后第一时间关掉表上的vacuum

这一步很关键！！！

这一步很关键！！！

这一步很关键！！！

ALTER TABLE t1 SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

2.6、查看表vacuum情况

testdb=# \xExpanded display is on.testdb=# select * from pg_stat_all_tables where relname='t1';-[ RECORD 1 ]-------+-------relid               | 49546schemaname          | publicrelname             | t1seq_scan            | 3seq_tup_read        | 6idx_scan            |idx_tup_fetch       |n_tup_ins           | 3n_tup_upd           | 0n_tup_del           | 2n_tup_hot_upd       | 0n_live_tup          | 1n_dead_tup          | 2n_mod_since_analyze | 5n_ins_since_vacuum  | 3last_vacuum         |last_autovacuum     |last_analyze        |last_autoanalyze    |vacuum_count        | 0autovacuum_count    | 0analyze_count       | 0autoanalyze_count   | 0

last_vacuum和last_autovacuum都是空的，表示还未被vacuum过。

2.7、使用pg_dirtyread查看表，dead为t表示数据已经删除

testdb=# SELECT * FROM pg_dirtyread('t1')AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boole an,id int, name text); tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name----------+-------+------+------+------+------+------+----+------    49546 | (0,1) | 1104 | 1105 |    0 |    0 | t    |  1 | aaa    49546 | (0,2) | 1104 | 1106 |    0 |    0 | t    |  2 | bbb    49546 | (0,3) | 1104 |    0 |    0 |    0 | f    |  3 | ccc(3 rows)

删除数据后，需要查询一下，pg_dirtyread中的dead列才会更新。也就是pg_dirtyread需要扫描一次表中的page才知道该行是不是被修改过。

testdb=# delete from t1;DELETE 3testdb=# SELECT * FROM pg_dirtyread('t1')AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name----------+-------+------+------+------+------+------+-----+-----49541 | (0,1) | 1102 | 1108 | 0 | 0 | f | 1 | aaa49541 | (0,2) | 1102 | 1108 | 0 | 0 | f | 2 | bbb49541 | (0,3) | 1102 | 1108 | 0 | 0 | f | 3 | ccc(3 rows)testdb=# select * from t1;id | name-----+-----(0 rows)testdb=# SELECT * FROM pg_dirtyread('t1')AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text);tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | name----------+-------+------+------+------+------+------+-----+-----49541 | (0,1) | 1102 | 1108 | 0 | 0 | t | 1 | aaa49541 | (0,2) | 1102 | 1108 | 0 | 0 | t | 2 | bbb49541 | (0,3) | 1102 | 1108 | 0 | 0 | t | 3 | ccc(3 rows)

2.8 恢复到某个时间

如果做不完全恢复，即恢复数据到某个时刻，需要使用函数pg_xact_commit_timestamp将事务ID进行转换。

testdb=# alter system set track_commit_timestamp=on;ALTER SYSTEM#删除一条数据testdb=# select * from t1;id | name----+------5 | EEE(1 row)testdb=# delete from t1;DELETE 1#查看删除时间testdb=# SELECT pg_xact_commit_timestamp(xmin) xmin_time,pg_xact_commit_timestamp(xmax) xmax_time,*FROM pg_dirtyread('t1')AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text)where xmax<>0;-[ RECORD 1 ]----------------------------xmin_time | 2023-12-03 16:27:03.830358+08xmax_time | 2023-12-06 10:10:29.115887+08tableoid | 49776ctid | (0,2)xmin | 7207xmax | 7235cmin | 0cmax | 0dead | fid | 5name | EEE

xmax_time 就是数据具体删除时间

2.9、pg_dirtyread还支持被删除的列

testdb=# select * from t1;id | name----+------3 | ccc(1 row)testdb=#testdb=# ALTER TABLE t1 DROP COLUMN name;ALTER TABLEtestdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);id | dropped_2----+-----------1 | aaa2 | bbb3 | ccc3 | ccc(4 rows)testdb=# select * from t1;id----3(1 row)

三、如果表上已经发生了vacuum

3.1、对表进行vacuum回收死元组

postgres=# vacuum t1;VACUUM

3.2、查看块中的数据被清理

testdb=# SELECT lp as tuple, t_xmin, t_xmax, t_field3 as t_cid, t_ctid,t_data FROM heap_page_items(get_raw_page('t1', 0));tuple | t_xmin | t_xmax | t_cid | t_ctid | t_data-------+--------+--------+-------+--------+----------------------------1 | | | | |2 | | | | |3 | | | | |4 | 1110 | 0 | 0 | (0,4) | \x030000000000000009636363(4 rows)

3.3、再次用pg_dirtyread查看死元组的数据已经被清理了

testdb=# \xExpanded display is on.testdb=# select * from pg_stat_all_tables where relname='t1';-[ RECORD 1 ]-------+------------------------------relid | 49546schemaname | publicrelname | t1seq_scan | 8seq_tup_read | 33idx_scan |idx_tup_fetch |n_tup_ins | 4n_tup_upd | 0n_tup_del | 3n_tup_hot_upd | 0n_live_tup | 1n_dead_tup | 0n_mod_since_analyze | 7n_ins_since_vacuum | 0last_vacuum | 2023-12-01 14:55:44.099392+0821 last_autovacuum |last_analyze |last_autoanalyze |vacuum_count | 1autovacuum_count | 0analyze_count | 0autoanalyze_count | 0testdb=# SELECT * FROM pg_dirtyread('t1') t1(id int, dropped_2 text);id | dropped_2----+-----------3 | ccc(1 row)

这种场景下，就无法通过脏块或解析死元组中的数据信息来恢复数据库，下一篇介绍WALMINER恢复误删除的数据。

总结

如果不小心误删除了数据，可以通过特殊手段来恢复数据的，具体恢复步骤如下：

1. 对表执行禁用vacuum（特别强调，这一步非常重要）

ALTER TABLE t1 SET (autovacuum_enabled = false, toast.autovacuum_enabled = false);

2. 使用pg_dirtyread插件查询被删除的数据，同时将数据抽取到中间表

create table t1_bak select id ,name from ((SELECT * FROM pg_dirtyread('t1')AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,id int, name text))) as foo;

另外，如何要找的数据己被vacuum，还可以通过分析数据具体被删除的时间，然后通过WalMiner解析wal日志，找到对应的时间点，生成undo sql(如果执行的delete，undo sql就是insert语句)。