PostgreSQL插件——pg_dirtyread

pg_dirtyread是一个脏读插件，对于误操作删除的数据或者列，在未对表执行vacuum之前可以读取出来并进行恢复，但是对truncate操作是没有效果的，使用此插件需要让vacuum相关参数不敏感，或者调整关键表的autovacuum/vacuum存储参数设置，另外使用此参数，也要考虑对性能的影响和维护成本。

PostgreSQL 版本：PG14.6
pg_dirtyread 版本：2.4

一、安装部署

1.获取地址

https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.4.tar.gz

2.解压

 tar -xvf pg_dirtyread-2.4.tar.gz 
 mv pg_dirtyread-2.4 /pgsoft/postgresql-14.6/contrib/
 

3.编译

[pg14@node1 pg_dirtyread-2.4]$ make PG_CONFIG=/pgsoft/pg14/bin/pg_config
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -fPIC -I. -I./ -I/pgsoft/pg14/include/postgresql/server -I/pgsoft/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o pg_dirtyread.o pg_dirtyread.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -fPIC -I. -I./ -I/pgsoft/pg14/include/postgresql/server -I/pgsoft/pg14/include/postgresql/internal  -D_GNU_SOURCE   -c -o dirtyread_tupconvert.o dirtyread_tupconvert.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -gdwarf-2 -ggdb -g -fPIC -shared -o pg_dirtyread.so pg_dirtyread.o dirtyread_tupconvert.o -L/pgsoft/pg14/lib    -Wl,--as-needed -Wl,-rpath,'/pgsoft/pg14/lib',--enable-new-dtags  
[pg14@node1 pg_dirtyread-2.4]$ make install PG_CONFIG=/pgsoft/pg14/bin/pg_config
/bin/mkdir -p '/pgsoft/pg14/lib/postgresql'
/bin/mkdir -p '/pgsoft/pg14/share/postgresql/extension'
/bin/mkdir -p '/pgsoft/pg14/share/postgresql/extension'
/bin/install -c -m 755  pg_dirtyread.so '/pgsoft/pg14/lib/postgresql/pg_dirtyread.so'
/bin/install -c -m 644 .//pg_dirtyread.control '/pgsoft/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql  '/pgsoft/pg14/share/postgresql/extension/'

4.安装

[pg14@node1 pg_dirtyread-2.4]$ psql -Utest2 -d test2
psql (14.6)
Type "help" for help.

test2=# \dx
                                       List of installed extensions
    Name     | Version |   Schema   |                             Description                             
-------------+---------+------------+---------------------------------------------------------------------
 pageinspect | 1.9     | public     | inspect the contents of database pages at a low level
 pg_recovery | 1.0     | public     | recovery table data of update/delete/rollback rows and drop columns
 plpgsql     | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

test2=# select * from pg_available_extensions() where name='pg_dirtyread';
     name     | default_version |                 comment                  
--------------+-----------------+------------------------------------------
 pg_dirtyread | 2               | Read dead but unvacuumed rows from table
(1 row)

test2=# create extension pg_dirtyread;
CREATE EXTENSION
test2=# select * from pg_available_extensions() where name='pg_dirtyread';
     name     | default_version |                 comment                  
--------------+-----------------+------------------------------------------
 pg_dirtyread | 2               | Read dead but unvacuumed rows from table
(1 row)

test2=# \dx
                                       List of installed extensions
     Name     | Version |   Schema   |                             Description                             
--------------+---------+------------+---------------------------------------------------------------------
 pageinspect  | 1.9     | public     | inspect the contents of database pages at a low level
 pg_dirtyread | 2       | public     | Read dead but unvacuumed rows from table
 pg_recovery  | 1.0     | public     | recovery table data of update/delete/rollback rows and drop columns
 plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

二、使用测试

1.创建测试数据

test2=# create table fb_dirty(id int,name varchar(10)) with (autovacuum_enabled = false,toast.autovacuum_enabled=false);
CREATE TABLE
test2=# \d+ fb_dirty;
                                                Table "public.fb_dirty"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id     | integer               |           |          |         | plain    |             |              | 
 name   | character varying(10) |           |          |         | extended |             |              | 
Access method: heap
Options: autovacuum_enabled=false

test2=# insert into  fb_dirty values(1,'aaa'),(2,'bbb');
INSERT 0 2

2.查看数据库

test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid  | id | name 
------+------+-------+----+------
  766 |    0 | (0,1) |  1 | aaa
  766 |    0 | (0,2) |  2 | bbb
(2 rows)

test2=# select * from pg_dirtyread('fb_dirty') as foo (tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,dead boolean,id int,name varchar(10));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    16438 | (0,1) |  766 |    0 |    0 |    0 | f    |  1 | aaa
    16438 | (0,2) |  766 |    0 |    0 |    0 | f    |  2 | bbb
(2 rows)

test2=# 

3.删除数据

test2=# delete from fb_dirty  where id=2;
DELETE 1
test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid  | id | name 
------+------+-------+----+------
  766 |    0 | (0,1) |  1 | aaa
(1 row)

test2=# select * from pg_dirtyread('fb_dirty') as foo (tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,dead boolean,id int,name varchar(10));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    16438 | (0,1) |  766 |    0 |    0 |    0 | f    |  1 | aaa
    16438 | (0,2) |  766 |  767 |    0 |    0 | t    |  2 | bbb
(2 rows)

当我们通过delete操作将id=2的这条数据删除后，在表里我们已经看不到这条记录，但是在脏读中可以看到这条数据，但是dead那列标示这条数据已经是t，说明这条数据被删除。

4.更新数据

test2=# update  fb_dirty set name='AAA'  where id=1;
UPDATE 1
test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid  | id | name 
------+------+-------+----+------
  768 |    0 | (0,3) |  1 | AAA
(1 row)

test2=# select * from pg_dirtyread('fb_dirty') as foo (tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,dead boolean,id int,name varchar(10));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | name 
----------+-------+------+------+------+------+------+----+------
    16438 | (0,1) |  766 |  768 |    0 |    0 | t    |  1 | aaa
    16438 | (0,2) |  766 |  767 |    0 |    0 | t    |  2 | bbb
    16438 | (0,3) |  768 |    0 |    0 |    0 | f    |  1 | AAA
(3 rows)

当我们更新id=1这条数据之后，在脏读中我们可以看到id=1的事务766已经被事务768覆盖，事务766的两个操作都已经dead，事务768中的col1这列的值’aaa’替换成了’AAA’，同时我们也能看到这个表中的历史操作流水。

5.删除列

test2=# alter  table fb_dirty  drop column name;
ALTER TABLE
test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid  | id 
------+------+-------+----
  768 |    0 | (0,3) |  1
(1 row)

test2=# select * from pg_dirtyread('fb_dirty') as foo (tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,dead boolean,id int,dropped_2 varchar(10));
 tableoid | ctid  | xmin | xmax | cmin | cmax | dead | id | dropped_2 
----------+-------+------+------+------+------+------+----+-----------
    16438 | (0,1) |  766 |  768 |    0 |    0 | t    |  1 | aaa
    16438 | (0,2) |  766 |  767 |    0 |    0 | t    |  2 | bbb
    16438 | (0,3) |  768 |    0 |    0 |    0 | f    |  1 | AAA
(3 rows)

test2=# 

我们删除列name后，在脏读中依然能看到之前的数据，但是这里要注意一个变更，就是name已经没有了，需要用dropped_n来替代，n代表表中的第几列。

6.truncate 测试

test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid  | id 
------+------+-------+----
  768 |    0 | (0,3) |  1
(1 row)

test2=# truncate fb_dirty ;
TRUNCATE TABLE
test2=# select xmin,xmax,ctid,* from fb_dirty;
 xmin | xmax | ctid | id 
------+------+------+----
(0 rows)

test2=# select * from pg_dirtyread('fb_dirty') as foo (tableoid oid,ctid tid,xmin xid,xmax xid,cmin cid,cmax cid,dead boolean,id int,dropped_2 varchar(10));
 tableoid | ctid | xmin | xmax | cmin | cmax | dead | id | dropped_2 
----------+------+------+------+------+------+------+----+-----------
(0 rows)

truncate 这个操作很危险，它不但将表中的数据清除，同时也将脏读的历史流水也同步清理。

参考：https://www.modb.pro/db/376858