写点什么

数据库:我没有带闪,不讲武德

用户头像
比伯
关注
发布于: 2020 年 11 月 24 日
数据库:我没有带闪,不讲武德

前言

今天有个业务的妹子问我“在吗?”

我说什么事?

给我发个截图,我一看!噢,原来是把数据删除了,想让我把数据找回来。

他说,大哥你能不能帮我。

我说可以!

很快啊,我就打开终端,一个指令就开始了。

我大 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 installcreate 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 installcreate 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 ROWEXECUTE 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 ASSELECT * FROM studentsUNION ALLSELECT * FROM students_history;复制代码
复制代码

然后执行下面操作,我就想看看在 17:49 分应该是显示那条数据,这里要显示第一次做 update 的结果。

postgres=# SELECT * FROM students_with_historypostgres-# 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_historypostgres-#   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 语法。


有兴趣的同学可以看看蟑螂数据库是怎么实现 SQL:2011 标准的。

总结

通过验证可以看出 PostgreSQL 打开闪回功能还是比较复杂的,它不像其他数据库内置了这个功能。需要我们自己找第三方插件来实现。而大多数第三方插件都是基于触发器实现的。而触发器的往往会存在一些开销。同时还要在原表上增加一个时间区间的字段。所以还是推荐使用pg_dirtyread来拯救您误删除的数据啊喂。


最后我还为大家整理了一份面试题库,有想要领取的只需要添加小编的 vx:mxzFAFAFA 即可!!!



用户头像

比伯

关注

还未添加个人签名 2020.11.09 加入

还未添加个人简介

评论

发布
暂无评论
数据库:我没有带闪,不讲武德