误删数据总是在所难免，一种是使用延时备库，在延迟间隔内可以从备库恢复数据，另一种使用开源插件pg_dirtyread，pg_dirtyread使用时需要关闭表的自动清理功能，不然可能也恢复不了。

1. 编译扩展pg_dirtyread

# su - postgres
$ wget https://codeload.github.com/df7cb/pg_dirtyread/tar.gz/2.2 
$ tar zxvf pg_dirtyread-2.2.tar.gz
$ make PG_CONFIG=/opt/pg12/bin/pg_config
$ make install PG_CONFIG=/opt/pg12/bin/pg_config

2. 创建扩展和测试表

$ psql -p5555 -Upostgres
psql (12.1)
Type "help" for help.

postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION

postgres=# create extension pageinspect;
CREATE EXTENSION

postgres=# create table t(id int,info text);
CREATE TABLE

postgres=# insert into t values(1,'aaa'),(2,'bbb'),(3,'ccc');
INSERT 0 3

postgres=# select * from t;
 id | info 
----+------
  1 | aaa
  2 | bbb
  3 | ccc
(3 rows)

postgres=# select ctid,* from t;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | aaa
 (0,2) |  2 | bbb
 (0,3) |  3 | ccc
(3 rows)

postgres=# select lp as tuple,t_xmin,t_xmax
postgres-#   from heap_page_items(get_raw_page('t',0));
 tuple | t_xmin | t_xmax 
-------+--------+--------
     1 |    506 |      0
     2 |    506 |      0
     3 |    506 |      0
(3 rows)
--前三行数据是一个事务语句插入的，所以xmin都是506

postgres=# insert into t values(4,'ddd');
INSERT 0 1
postgres=# select lp as tuple,t_xmin,t_xmax
  from heap_page_items(get_raw_page('t',0));
 tuple | t_xmin | t_xmax 
-------+--------+--------
     1 |    506 |      0
     2 |    506 |      0
     3 |    506 |      0
     4 |    507 |      0
(4 rows)
--新插入一行，xmin变为507

3. 删除行后再查看

postgres=# delete from t where id=2;
DELETE 1

postgres=# select ctid,xmin,xmax,* from t;
 ctid  | xmin | xmax | id | info 
-------+------+------+----+------
 (0,1) |  506 |    0 |  1 | aaa
 (0,3) |  506 |    0 |  3 | ccc
 (0,4) |  507 |    0 |  4 | ddd
(3 rows)
--删除id=2后的行已经看不见

postgres=# select lp as tuple,t_xmin,t_xmax
  from heap_page_items(get_raw_page('t',0));
 tuple | t_xmin | t_xmax 
-------+--------+--------
     1 |    506 |      0
     2 |    506 |    508
     3 |    506 |      0
     4 |    507 |      0
(4 rows)
--通过插件查看，实际行数据是存在的，只是不可见而已

4. 手工对表做一下清理

postgres=# vacuum t;
VACUUM
postgres=# select lp as tuple,t_xmin,t_xmax
  from heap_page_items(get_raw_page('t',0));
 tuple | t_xmin | t_xmax 
-------+--------+--------
     1 |    506 |      0
     2 |        |       
     3 |    506 |      0
     4 |    507 |      0
(4 rows)
--可以看到第2个块已经清理了

postgres=# vacuum FULL t;
VACUUM
postgres=# select lp as tuple,t_xmin,t_xmax
  from heap_page_items(get_raw_page('t',0));
 tuple | t_xmin | t_xmax 
-------+--------+--------
     1 |    506 |      0
     2 |    506 |      0
     3 |    507 |      0
(3 rows)
--做完碎片整理后再查看更清晰

postgres=# select ctid,* from t;
 ctid  | id | info 
-------+----+------
 (0,1) |  1 | aaa
 (0,2) |  3 | ccc
 (0,3) |  4 | ddd
(3 rows)

5. pg_dirtyread使用

2.1版本之后支持pg12
--先关闭表的自动清理
postgres=#   ALTER TABLE t SET (
postgres(#     autovacuum_enabled = false, 
postgres(#     toast.autovacuum_enabled = false
postgres(#   );
ALTER TABLE

--插入两条测试数据
postgres=#  insert into t values(5,'eee'),(6,'fff');
INSERT 0 2

--模拟误操作，删除刚才的两条数据
postgres=# delete from t where id>4;
DELETE 2

--此时查看发现上一步操作错了
postgres=# select * from t;
 id | info 
----+------
  1 | aaa
  3 | ccc
  4 | ddd
(3 rows)

-- 使用pg_dirtyread函数传入需要恢复的表名，as后面接自定义表结构(与原表结构一致)
postgres=# select * from pg_dirtyread('t') as recovery_t(id int,info text);
 id | info 
----+------
  1 | aaa
  3 | ccc
  4 | ddd
  5 | eee
  6 | fff
(5 rows)