SRE文档

深浅模式

1.介绍 ​

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

闪回 ​

DML闪回和DDL闪回。

DML闪回指对INSET, UPDATE, DELETE操作的闪回。

DDL闪回指DROP, TRUNCATE操作的闪回

闪回的实现分两种： ​

1、物理回退，相当于使用物理备份和归档进行时间点恢复，全库恢复到误操作前的状态。

（可以新建一个库用于恢复，恢复到目标时间点，恢复后，将误操作前的数据导出来，再导入线上数据库。）

2、在当前库回退，在当前库，将误操作影响的数据找出来。

闪回的手段： ​

1、物理回退，PG内核已支持时间点恢复，只要有误操作前的全量备份和所有归档即可。

2、当前库回退，使用HOOK，可以实现DROP和TRUNCATE操作的回收站功能。

3、使用延迟垃圾回收、脏读、行头事务号、事务提交日志，可以实现DML操作的闪回

2.支持的版本 ​

10和11已经支持，2.0以后的版本已经支持12和13

3.编译安装扩展 ​

#下载
# wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.2.tar.gz
# tar -zxvf ./2.2.tar.gz
# cd pg_dirtyread-2.2
# make
# make install PG_CONFIG=/opt/pg12/bin/pg_config（根据路径修改）
/usr/bin/mkdir -p '/data/apps/pgsql/12/lib'
/usr/bin/mkdir -p '/data/apps/pgsql/12/share/extension'
/usr/bin/mkdir -p '/data/apps/pgsql/12/share/extension'
/usr/bin/install -c -m 755  pg_dirtyread.so '/data/apps/pgsql/12/lib/pg_dirtyread.so'
/usr/bin/install -c -m 644 .//pg_dirtyread.control '/data/apps/pgsql/12/share/extension/'
/usr/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql  '/data/apps/pgsql/12/share/extension/'
#下载
# wget https://github.com/df7cb/pg_dirtyread/archive/refs/tags/2.2.tar.gz
# tar -zxvf ./2.2.tar.gz
# cd pg_dirtyread-2.2
# make
# make install PG_CONFIG=/opt/pg12/bin/pg_config（根据路径修改）
/usr/bin/mkdir -p '/data/apps/pgsql/12/lib'
/usr/bin/mkdir -p '/data/apps/pgsql/12/share/extension'
/usr/bin/mkdir -p '/data/apps/pgsql/12/share/extension'
/usr/bin/install -c -m 755  pg_dirtyread.so '/data/apps/pgsql/12/lib/pg_dirtyread.so'
/usr/bin/install -c -m 644 .//pg_dirtyread.control '/data/apps/pgsql/12/share/extension/'
/usr/bin/install -c -m 644 .//pg_dirtyread--1.0.sql .//pg_dirtyread--1.0--2.sql .//pg_dirtyread--2.sql  '/data/apps/pgsql/12/share/extension/'

4.创建扩展和测试表 ​

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

postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION

postgres=# create extension pageinspect;
CREATE EXTENSION

#查看
postgres=# \dx+ pg_dirtyread
Objects in extension "pg_dirtyread"
       Object description        
---------------------------------
 function pg_dirtyread(regclass)
(1 row)


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
$ psql -p5555 -Upostgres
psql (12.1)
Type "help" for help.

postgres=# CREATE EXTENSION pg_dirtyread;
CREATE EXTENSION

postgres=# create extension pageinspect;
CREATE EXTENSION

#查看
postgres=# \dx+ pg_dirtyread
Objects in extension "pg_dirtyread"
       Object description        
---------------------------------
 function pg_dirtyread(regclass)
(1 row)


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

5.删除行后再查看 ​

sql
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)
--通过插件查看，实际行数据是存在的，只是不可见而已
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)
--通过插件查看，实际行数据是存在的，只是不可见而已

6.手工对表做一下清理 ​

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)
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)

7.pg_dirtyread使用 ​

sql
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)
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)

参考：https://developer.aliyun.com/article/617340