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变为5075.删除行后再查看
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)