前言
今天有个业务的妹子问我“在吗？”
我说什么事？
给我发个截图，我一看！噢，原来是把数据删除了，想让我把数据找回来。
他说，大哥你能不能帮我。
我说可以！
很快啊，我就打开终端，一个指令就开始了。
我大E了，发现数据闪回不了。
然后十分钟后我告诉她，搞不定了。
然后她就投诉到领导哪里去了，说她丢的数据找不回来了，DBA也搞不定。
我找到这个妹子，说：“你不讲规矩，你不懂这个恢复的难度。”
她忙说对不起，我不懂规矩啊！
我说：“不讲规矩，来，告状，诬陷我20多年经验的dba连点数据都恢复不了。这好吗？这不好。我劝这位女同学，耗子尾汁。好好反思。
PostgreSQL如何开闪
说到闪回查询，PostgreSQL一开始是有这个功能的，它叫Time Travel(时间旅行)，这名字听上去比闪回查询要高大上，瞬间让我想起了好几部描述这类的电影，如《时间机器》、《时间旅行者的妻子》、《信条》。
我们拥有时间旅行，可以穿梭于任意的时空，然后来阻止一场灾难？大多数电影都是这类的桥段，而对于数据库来说，时间旅行也是我们拯救灾难的一种方法。
时间旅行Time Travel这项技术还要追溯到PostgreSQL 6时代，但是在之后就被打入了冷宫，变成可以使用但是并不推荐。官方文网也说有性能上的影响，推荐使用触发器替代，一直到PostgreSQL 12这个版本，才真正的把这项功能移除了。
那么怎么在PostgreSQL 12上实现呢？有很多种解决方案，其实原生的PG只是一个食材，大家可以根据这个食材，自由发挥做出各种各样美味的料理。你可以灵活选择适合你口味的料理（适配你的系统）。
pg_dirtyread
pg_dirtyread的工作原理比较简单，就是从Dead Tuples中读取数据。这得益于PostgreSQL的MVCC机制。在PostgreSQL的MVCC机制中，当更新或者删除任何一行记录的时候，将在内部创建新行并将旧行标记为Dead Tuples。而Pg_dirtyread就可以助我们从Dead Tuples读出数据。但是缺点也很明显，如果Dead Tuples被autovacuum进程清理了，也就没数据了。
接下来我们来测试一下，下载编译pg_dirtyread插件。
make && make install
create extension pg_dirtyread;
create table students
(
stuno int,
name varchar(50),
age varchar(50),
city varchar(50)
);
insert into students (stuno, name, age, city) values (1, 'abhiram', 22, 'allahabad');
insert into students (stuno, name, age, city) values (2, 'alka', 20, 'ghaziabad');
insert into students (stuno, name, age, city) values (3, 'disha', 21, 'varanasi');
insert into students (stuno, name, age, city) values (4, 'esha', 21, 'delhi');
insert into students (stuno, name, age, city) values (5, 'manmeet', 23, 'jalandhar');
postgres=# select * from students;
stuno | name | age | city
-------+---------+-----+-----------
1 | abhiram | 22 | allahabad
2 | alka | 20 | ghaziabad
3 | disha | 21 | varanasi
4 | esha | 21 | delhi
5 | manmeet | 23 | jalandhar
复制代码对上述表做多次更新。
update students set city='WuHan' where stuno=5;
update students set age=21 where stuno=5;
update students set city='Shanghai' where stuno=5;
复制代码通过pg_dirtyread读取更新前的数据。
postgres=# SELECT * FROM pg_dirtyread('students') students(stuno int, name varchar(50),age varchar(50),city varchar(50));
stuno | name | age | city
-------+---------+-----+-----------
1 | abhiram | 22 | allahabad
2 | alka | 20 | ghaziabad
3 | disha | 21 | varanasi
4 | esha | 21 | delhi
5 | manmeet | 23 | jalandhar
5 | manmeet | 23 | WuHan
5 | manmeet | 21 | WuHan
5 | manmeet | 21 | Shanghai
复制代码可以看到把历史的数据都找回来了，但是很乱，它读出了所有的历史数据，可能你只是想恢复到其中的某一个时间点。
postgres=# SELECT * FROM pg_dirtyread('students')
postgres-# AS students(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,stuno int, name varchar(50),age varchar(50),city varchar(50));
tableoid | ctid | xmin | xmax | cmin | cmax | dead | stuno | name | age | city
----------+-------+----------+----------+------+------+------+-------+---------+-----+-----------
20023788 | (0,1) | 26678735 | 0 | 0 | 0 | f | 1 | abhiram | 22 | allahabad
20023788 | (0,2) | 26678736 | 0 | 0 | 0 | f | 2 | alka | 20 | ghaziabad
20023788 | (0,3) | 26678737 | 0 | 0 | 0 | f | 3 | disha | 21 | varanasi
20023788 | (0,4) | 26678738 | 0 | 0 | 0 | f | 4 | esha | 21 | delhi
20023788 | (0,5) | 26678739 | 26678741 | 0 | 0 | t | 5 | manmeet | 23 | jalandhar
20023788 | (0,6) | 26678741 | 26678742 | 0 | 0 | t | 5 | manmeet | 23 | WuHan
20023788 | (0,7) | 26678742 | 26678743 | 0 | 0 | f | 5 | manmeet | 21 | WuHan
20023788 | (0,8) | 26678743 | 0 | 0 | 0 | f | 5 | manmeet | 21 | Shanghai
复制代码不过知道死元组的事务ID就可以通过pg_xact_commit_timestamp函数将xmin转换成时间。
使用
pg_xact_commit_timestamp函数，需要将参数track_commit_timestamp设置为on，修改该参数需要重启数据库。
postgres=# select (pg_xact_commit_timestamp(xmin)) from
postgres-# (SELECT * FROM pg_dirtyread('students') AS students(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean,stuno int, name varchar(50),age varchar(50),city varchar(50))) as b;
pg_xact_commit_timestamp
-------------------------------
2020-11-23 15:23:48.611553+08
2020-11-23 15:23:48.624343+08
2020-11-23 15:23:48.6367+08
2020-11-23 15:23:48.65629+08
2020-11-23 15:23:48.676773+08
2020-11-23 15:23:55.716173+08
2020-11-23 15:23:55.729868+08
2020-11-23 15:23:56.02215+08
复制代码这样就可以基于时间点恢复到你想要的位置了。
可以说pg_dirtyread很好的解决了误操作导致的数据修改删除问题。但是它最大的缺点就是受制于autovacuum进程，如果autovacuum进程清理掉了死元组，pg_dirtyread就没办法工作了。所以当出现误删数据之后，我们第一时间就要先关闭autovacuum，然后通过下面查询误操作的表是否已经发生了vacuum。
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;
复制代码最后说一点，这个方法虽好，但是不支持truncate和drop这类的ddl。
temporal_tables
temporal_tables也是一个不错的扩展，但是它的原理和pg_dirtyread完全不同。它的原理是将我们修改或者删除的旧行归档到一个历史表中，这样可以方便进行审计、对比。至于这个插件我觉得他们和IBM DB2的功能很接近，都需要使用一个叫时态表temporal tables技术。
我们来测试一下，下载编译temporal_tables插件。
make && make install
create extension temporal_tables;
复制代码接下来我们来创建表。
create table students
(
stuno int,
name varchar(50),
age varchar(50),
city varchar(50)
);
复制代码建完表后，我们要增加一个sys_period列。
ALTER TABLE students ADD COLUMN sys_period tstzrange NOT NULL;
复制代码然后我们需要创建一个历史表。
CREATE TABLE students_history (LIKE students);
复制代码最后我们要创建一个触发器，把我们的表和历史表关联起来。
CREATE TRIGGER students_hist_trigger BEFORE
INSERT OR UPDATE OR DELETE ON students FOR EACH ROW
EXECUTE PROCEDURE versioning('sys_period', 'students_history', true);
复制代码插入记录。
insert into students (stuno, name, age, city) values (1, 'abhiram', 22, 'allahabad');
insert into students (stuno, name, age, city) values (2, 'alka', 20, 'ghaziabad');
insert into students (stuno, name, age, city) values (3, 'disha', 21, 'varanasi');
insert into students (stuno, name, age, city) values (4, 'esha', 21, 'delhi');
insert into students (stuno, name, age, city) values (5, 'manmeet', 23, 'jalandhar');
复制代码此时通过查询，会发现sys_period列上会显示时间。["2020-11-23 17:10:58.702324+08",)。这个格式前面代表着有效期开始时间，后面则代表有效期结束时间，这里，后面是空的，代表了无穷大。
postgres=# select * from students;
stuno | name | age | city | sys_period
-------+---------+-----+-----------+------------------------------------
1 | abhiram | 22 | allahabad | ["2020-11-23 17:48:13.841549+08",)
2 | alka | 20 | ghaziabad | ["2020-11-23 17:48:13.862973+08",)
3 | disha | 21 | varanasi | ["2020-11-23 17:48:13.874852+08",)
4 | esha | 21 | delhi | ["2020-11-23 17:48:13.891388+08",)
5 | manmeet | 21 | Shanghai | ["2020-11-23 17:51:22.613059+08",)
复制代码我们对表进行更新操作，此时查询students_history就会显示我们的历史数据。
update students set city='WuHan' where stuno=5;
update students set age=21 where stuno=5;
update students set city='Shanghai' where stuno=5;
postgres=# select * from students_history;
stuno | name | age | city | sys_period
-------+---------+-----+-----------+-------------------------------------------------------------------
5 | manmeet | 23 | jalandhar | ["2020-11-23 17:48:13.916846+08","2020-11-23 17:48:31.964398+08")
5 | manmeet | 23 | WuHan | ["2020-11-23 17:48:31.964398+08","2020-11-23 17:50:30.014874+08")
5 | manmeet | 21 | WuHan | ["2020-11-23 17:50:30.014874+08","2020-11-23 17:51:22.613059+08")
复制代码虽然能显示数据，但是和我们想要的差距还是有点大。因为如上面所示，数据还是很紊乱的。想要查询到指定的时间点还是很困难的。不过根据历史视图，我们知道我们在下面三个时间点更新了数据。分别是17:48:13 17:50:30 17:51:22
我们可以在创建一个视图。
CREATE VIEW students_with_history AS
SELECT * FROM students
UNION ALL
SELECT * FROM students_history;
复制代码然后执行下面操作，我就想看看在17:49分应该是显示那条数据，这里要显示第一次做update的结果。
postgres=# SELECT * FROM students_with_history
postgres-# WHERE stuno = 5 AND sys_period @> '2020-11-23 17:49:00'::timestamptz;
stuno | name | age | city | sys_period
-------+---------+-----+-------+-------------------------------------------------------------------
5 | manmeet | 23 | WuHan | ["2020-11-23 17:48:31.964398+08","2020-11-23 17:50:30.014874+08")
(1 row)
复制代码可以看到结果完全正确。换成17点51分在看看，这里显示了第二次做update的结果。
postgres=# SELECT * FROM students_with_history
postgres-# WHERE stuno = 5 AND sys_period @> '2020-11-23 17:51:00'::timestamptz ;
stuno | name | age | city | sys_period
-------+---------+-----+-------+-------------------------------------------------------------------
5 | manmeet | 21 | WuHan | ["2020-11-23 17:50:30.014874+08","2020-11-23 17:51:22.613059+08")
(1 row)
复制代码以上的操作方法就像Oracle中的语法as of timestamp一样。而AS OF SYSTEM TIME语法是SQL 2011的标准。
而wiki上也有一篇文章详细的描述了PostgreSQL实现SQL 2011的方法和建议，实现的原理和temporal_tables差不多，但是它多出来了truncate的恢复。其实temporal_tables要实现truncate也很简单，自己做一个truncate的触发器就行了。
衍生版PG
当前现在市面上有一些衍生版的PG，比如CockroachDB。提供了SQL 2011中的AS OF SYSTEM TIME语法。
总结
通过验证可以看出PostgreSQL打开闪回功能还是比较复杂的，它不像其他数据库内置了这个功能。需要我们自己找第三方插件来实现。而大多数第三方插件都是基于触发器实现的。而触发器的往往会存在一些开销。同时还要在原表上增加一个时间区间的字段。所以还是推荐使用pg_dirtyread来拯救您误删除的数据啊喂。
京公网安备 11010502036488号