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