瀚高闪回查询功能
本文与大家分享一下瀚高数据库(HighGo DB)的专利功能:闪回查询。
1 介绍
闪回查询,英文简称:HG_FBQ,英文全称:Higho FlashBackQuery。
闪回查询基于MVCC机制,可用于查询或恢复数据。闪回功能主要用来恢复逻辑错误,无法恢复物理损坏造成的数据问题。
瀚高数据库各版本对闪回查询的支持情况:
- 企业版v5.6.x --> 支持
- 安全版v4.3.4.x --> 不支持
- 企业版v6.0.x --> 支持
- 安全版v4.5.x --> 支持
闪回查询总体架构图:
图1 闪回查询总体架构图
使用场景:
- 场景1:误操作频繁发生、数据容易丢失;
- 场景2:针对一些小错误的恢复,用常规备份来恢复,低效耗时,且需要停止数据库服务,中断了业务,增加恢复操作的复杂度。
使用闪回查询恢复迅速,不需要中断业务。可以查看不同版本数据的差异,从中选取所需的版本,恢复误操作的数据。
2 参数配置
闪回功能默认是关闭的。如果要使用该功能,需要配置以下3个参数。
① track_commit_timestamp:
默认值off。若要启用闪回查询功能,须将该参数设置为on,以记录事务提交的时间。该参数的变更,需要重启数据库服务才能生效。
说明:只有当这个参数是on,函数pg_xact_commit_timestamp(xid)、pg_last_committed_xact()才能获得已提交事务的时间值。
② hg_fbq_retention:
默认值0,单位秒(s)。闪回数据保存时间,建议最大值不要超过432000(秒,即5天)。
例如:设置为30,是指变更前的旧数据版本,可以在变更提交后30秒内通过闪回功能查询得到;但30s过后就无法通过闪回功能查到了。
也就是说,闪回数据保存时间 = 变更事务提交时间 + hg_fbq_retention。
③ hg_fbq_guarantee:
默认值off。只有当上一个参数(hg_fbq_retention)大于0时,才会生效。
1) 如果想不允许autovacuum机制清理“保存时间”内的数据,则保持默认值off不变。但手工vacuum操作仍然可以清理它们;
2) 如果设置为on,则任何vacuum操作均不允许清理“保存时间”内的数据。
闪回功能开启的条件:
track_commit_timestamp = on;
hg_fbq_retention > 0;
参数配置实例:
我们需要使用数据库管理员登录命令行工具psql执行以下命令:
ALTER SYSTEM SET track_commit_timestamp = on;
ALTER SYSTEM SET hg_fbq_retention = '43200s';
ALTER SYSTEM SET hg_fbq_guarantee = on;
注意:
- 数据库管理员在企业版中是highgo,安全版是sysdba;
- 这里的43200秒就是12小时,即:闪回数据可以保留12小时;
- 除了第一个参数需要重启数据库服务外,其他只需要执行select pg_reload_conf();重新加载配置即可;
- 查看当前参数配置,可以使用show命令,例如:
show track_commit_timestamp;
show hg_fbq_retention;
show hg_fbq_guarantee;
3 语法结构
根据架构图,我们得知有两类查询,皆遵循flashback语法。
第一类:闪回查询。
可以根据时间点、事务ID去查询某个点的所有数据。
根据mvcc原理,删除或者更新元组并没有立即删除旧数据,而只是标记为”已删除“,在vacuum清理之前,这些数据还存在page页中,根据元组可见性,闪回查询过去某个时间点或某个事务可见的数据。
- 根据时间点的查询语法如下:
SELECT column_name[ , … ] FROM table_name
[ FLASHBACK TIMESTAMP expression ]
[ WHERE condition ];
- 根据事务ID的查询语法如下:
SELECT column_name[ , … ] FROM table_name
[ FLASHBACK XID expression ]
[ WHERE condition ];
第二类:闪回版本查询。
根据时间段、事务ID段去查询某个范围的数据。原理与闪回查询一样,只不过闪回版本查询查到的是多个版本
- 指定时间段的查询语法:
SELECT column_name[ , … ] FROM table_name
[ FLASHBACK BETWEEN TIMESTAMP expression1 AND expression2 ]
[ WHERE condition ];
- 指定事务ID段的查询语法:
SELECT column_name[ , … ] FROM table_name
[ FLASHBACK BETWEEN XID expression1 AND expression2 ]
[ WHERE condition ];
接下来,我们用瀚高数据库企业版v5.6.5实践一下。
4 创建测试实例
首先,创建需要用到的:用户、数据库、模式,命令如下:
create user test password '666666';
create database testdb with owner = test;
\c testdb
create schema test authorization test;
\c testdb test
创建表并插入7条数据:
CREATE TABLE EMPLOYEES (
emp_no INT NOT NULL , /*明星编号*/
emp_name VARCHAR(60) NOT NULL , /*姓名*/
gender VARCHAR(10) NOT NULL , /*性别*/
likecolor VARCHAR(10) NOT NULL , /*喜欢的颜色*/
transtime timestamptz NOT NULL /*修改时间*/
);
INSERT INTO EMPLOYEES VALUES (10001,'刘亦菲','M','红色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10002,'房祖名','F','紫色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10003,'郭雪芙','M','白色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10004,'陈晓','M','黑色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10005,'黄子韬','M','黄色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10006,'肖战','F','红色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10007,'蔡徐坤','F','紫色',transaction_timestamp());
设置参数:
ALTER SYSTEM SET track_commit_timestamp = on;
ALTER SYSTEM SET hg_fbq_retention = '43200s';
ALTER SYSTEM SET hg_fbq_guarantee = off;
重启数据库,使参数生效:
[root@Location-01 ~]# systemctl restart hgdb-enterprise-5.6.5.service
做几个更新删除操作(4个事务):
BEGIN;
INSERT INTO EMPLOYEES VALUES (10008,'杨幂','M','绿色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10009,'陈伟霆','F','橙色',transaction_timestamp());
INSERT INTO EMPLOYEES VALUES (10010,'邓紫棋','F','紫色',transaction_timestamp());
DELETE FROM EMPLOYEES WHERE EMP_NO = 10001;
END;
select pg_last_committed_xact() "T1";
select pg_sleep(60);
BEGIN;
DELETE FROM EMPLOYEES WHERE EMP_NO = 10002;
UPDATE EMPLOYEES SET likecolor = '黑色', transtime = transaction_timestamp() WHERE EMP_NO = 10007;
UPDATE EMPLOYEES SET likecolor = '绿色', transtime = transaction_timestamp() WHERE EMP_NO = 10006;
END;
select pg_last_committed_xact() "T2";
select pg_sleep(60);
BEGIN;
UPDATE EMPLOYEES SET likecolor = '白色', transtime = transaction_timestamp() WHERE EMP_NO = 10007;
END;
select pg_last_committed_xact() "T3";
select pg_sleep(60);
BEGIN;
UPDATE EMPLOYEES SET likecolor = '蓝色', transtime = transaction_timestamp() WHERE EMP_NO = 10007;
END;
select pg_last_committed_xact() "T4";
四种语法结构测试对比:
select xmin,xmax,cmin,cmax,* from employees flashback timestamp '2021-02-07 14:16:49.03023';
select xmin,xmax,cmin,cmax,* from employees flashback xid 598;
select xmin,xmax,cmin,cmax,* from employees flashback between timestamp '2021-02-07 14:16:49.03023' and '2021-02-07 14:19:49.382556';
select xmin,xmax,cmin,cmax,* from employees flashback between xid 598 and 604;
5 最佳实践
我们要使用闪回查询数据,但我们未必知道精确的事务ID或对应的事务提交时间。
下面我们分析一下上一节实例最后的数据变更操作,将事务时间填在T后面:
事务/时间 | 操作 | 定义(简称) |
T1 - 598,"2021-02-07 14:16:49.03023+08" | 插入 | emp_no=10008、10009、10010 |
T1 | 删除 | emp_no=10001 |
T2 - 602,"2021-02-07 14:17:49.138024+08" | 删除 | emp_no=10002 |
T2 | 更新 | emp_no=10007,likecolor,之前:'紫色',现在:'黑色' |
T2 | 更新 | emp_no=10006,likecolor,之前:'青色',现在:'绿色' |
T3 - 603,"2021-02-07 14:18:49.254683+08" | 更新 | emp_no=10007,likecolor,之前:'黑色',现在:'白色' |
T4 - 604,"2021-02-07 14:19:49.382556+08" | 更新 | emp_no=10007,likecolor,之前:'白色',现在:'蓝色' |
四种语法结构测试对比:
select xmin,xmax,cmin,cmax,* from employees flashback timestamp '2021-02-06 16:16:28.256017';
select xmin,xmax,cmin,cmax,* from employees flashback xid 602;
select xmin,xmax,cmin,cmax,* from employees flashback between timestamp '2021-02-06 16:16:28.256017' and '2021-02-06 16:33:00';
select xmin,xmax,cmin,cmax,* from employees flashback between xid 602 and 607;
例1:我们发现emp_no为10001的数据误删了,现在要尝试恢复。我们可以将12小时内的数据版本,使用基于时间段的闪回版本查询语句,查询出来。若找到误删的数据,我们可以将数据重新插入表中即可。
首先,根据时间段执行闪回版本查询:
testdb=> select xmin,xmax,cmin,cmax,* from employees flashback between timestamp '2021-02-05 00:00:00' and '2021-02-05 09:16:00' where emp_no=10001;
xmin | xmax | cmin | cmax | emp_no | emp_name | gender | likecolor | transtime
------+------+------+------+--------+----------+--------+-----------+-------------------------------
594 | 604 | 3 | 3 | 10001 | 刘亦菲 | M | 红色 | 2021-02-05 08:59:52.894497+08
(1 row)
然后,将需要的数据插回原表即可,可以根据事务ID以及数据键值,插入语句如:
testdb=> insert into employees(select * from employees flashback between timestamp '2021-02-05 00:00:00' and '2021-02-05 09:16:00' where emp_no=10001);
INSERT 0 1
最后,验证一下,emp_no=10001的数据恢复了:
testdb=> select * from employees where emp_no=10001;
emp_no | emp_name | gender | likecolor | transtime
--------+----------+--------+-----------+-------------------------------
10001 | 刘亦菲 | M | 红色 | 2021-02-05 08:59:52.894497+08
(1 row)
例2:我们发现当前肖战喜欢的颜色是绿色,这个是不对的,有一个误操作。
首先,我们估算一下大概是哪个时间段做的这个误操作,使用闪回版本查询看一下,如:
testdb=> select xmin,xmax,cmin,cmax,* from employees flashback between timestamp '2021-02-05 08:00:00' and '2021-02-05 09:24:00';
xmin | xmax | cmin | cmax | emp_no | emp_name | gender | likecolor | transtime
------+------+------+------+--------+----------+--------+-----------+-------------------------------
594 | 604 | 3 | 3 | 10001 | 刘亦菲 | M | 红色 | 2021-02-05 08:59:52.894497+08
595 | 605 | 0 | 0 | 10002 | 房祖名 | F | 紫色 | 2021-02-05 08:59:52.910263+08
596 | 0 | 0 | 0 | 10003 | 郭雪芙 | M | 白色 | 2021-02-05 08:59:52.916203+08
597 | 0 | 0 | 0 | 10004 | 陈晓 | M | 黑色 | 2021-02-05 08:59:52.924312+08
598 | 0 | 0 | 0 | 10005 | 黄子韬 | M | 黄色 | 2021-02-05 08:59:52.933655+08
599 | 605 | 2 | 2 | 10006 | 肖战 | F | 红色 | 2021-02-05 08:59:52.939363+08
600 | 605 | 1 | 1 | 10007 | 蔡徐坤 | F | 紫色 | 2021-02-05 08:59:54.51202+08
604 | 0 | 0 | 0 | 10008 | 杨幂 | M | 绿色 | 2021-02-05 09:03:59.764112+08
604 | 0 | 1 | 1 | 10009 | 陈伟霆 | F | 橙色 | 2021-02-05 09:03:59.764112+08
604 | 0 | 2 | 2 | 10010 | 邓紫棋 | F | 紫色 | 2021-02-05 09:03:59.764112+08
605 | 606 | 0 | 0 | 10007 | 蔡徐坤 | F | 黑色 | 2021-02-05 09:04:59.839748+08
605 | 0 | 2 | 2 | 10006 | 肖战 | F | 绿色 | 2021-02-05 09:04:59.839748+08
606 | 607 | 0 | 0 | 10007 | 蔡徐坤 | F | 白色 | 2021-02-05 09:06:01.150718+08
607 | 0 | 0 | 0 | 10007 | 蔡徐坤 | F | 蓝色 | 2021-02-05 09:07:01.255006+08
608 | 0 | 0 | 0 | 10001 | 刘亦菲 | M | 红色 | 2021-02-05 08:59:52.894497+08
(15 rows)
我们也可以加上where条件,将结果集准确过滤一下。如:
testdb=> select xmin,xmax,cmin,cmax,* from employees flashback between timestamp '2021-02-05 08:00:00' and '2021-02-05 09:24:00' where emp_no=10006;
xmin | xmax | cmin | cmax | emp_no | emp_name | gender | likecolor | transtime
------+------+------+------+--------+----------+--------+-----------+-------------------------------
599 | 605 | 2 | 2 | 10006 | 肖战 | F | 红色 | 2021-02-05 08:59:52.939363+08
605 | 0 | 2 | 2 | 10006 | 肖战 | F | 绿色 | 2021-02-05 09:04:59.839748+08
(2 rows)
然后,我们根据事务ID(即xmax)准确定位到这一版本数据,将当时的数据更新到相应的人员信息中,如:
testdb=> update employees t1 set t1.likecolor = t2.likecolor from (select * from employees flashback between timestamp '2021-02-05 08:00:00' and '2021-02-05 09:24:00' where xmax=605 and emp_no=10006 ) t2 where t1.emp_no = t2.emp_no;
UPDATE 1
最后,验证一下数据是否还原:
testdb=> select * from employees where emp_no = 10006;
emp_no | emp_name | gender | likecolor | transtime
--------+----------+--------+-----------+-------------------------------
10006 | 肖战 | F | 红色 | 2021-02-05 09:04:59.839748+08
(1 row)
可以看到肖战喜欢的颜色(likecolor)已经恢复为”红色“了。
另外,如果我们不小心通过delete删除了整个表的数据,或者需要恢复的数据量比较大,我们还可以将闪回查询的结果集存入一个新建的表中。例如:
testdb=> create table employees_tmp as select * from employees flashback xid 605;
SELECT 8
testdb=> select * from employees_tmp ;
emp_no | emp_name | gender | likecolor | transtime
--------+----------+--------+-----------+-------------------------------
10003 | 郭雪芙 | M | 白色 | 2021-02-05 08:59:52.916203+08
10004 | 陈晓 | M | 黑色 | 2021-02-05 08:59:52.924312+08
10005 | 黄子韬 | M | 黄色 | 2021-02-05 08:59:52.933655+08
10008 | 杨幂 | M | 绿色 | 2021-02-05 09:03:59.764112+08
10009 | 陈伟霆 | F | 橙色 | 2021-02-05 09:03:59.764112+08
10010 | 邓紫棋 | F | 紫色 | 2021-02-05 09:03:59.764112+08
10007 | 蔡徐坤 | F | 黑色 | 2021-02-05 09:04:59.839748+08
10006 | 肖战 | F | 绿色 | 2021-02-05 09:04:59.839748+08
(8 rows)
这样的好处是将该版本的数据持久化,防止过期查不到。尤其是时间马上就要过期的情况下,这样操作是非常保险的。
6 注意事项
目前闪回查询功能只能对DML(insert、update 和delete)操作进行闪回查询,对于DDL 及truncate 对表的操作,目前闪回查询还不支持。除此之外,闪回查询也不支持表结构做出的修改操作。
该功能会多少影响一些数据库性能,尤其是大量的DML操作时,大量版本的未过期数据不能自动清理或者无法清理,所以有效期的时间值要设置合理。
时间关系,这里就不对DDL、truncate、修改表结构以及vacuum操作进行验证了
参考文档:
[1] 《瀚高数据库企业版V5-闪回查询功能使用手册》;
[2] 《瀚高数据库企业版V6-管理手册》,第4.5章节 闪回查询;
[3] 《瀚高安全版数据库系统V4.5-管理手册》,第9.6章节 闪回查询;
(这些手册,可以跟瀚高相关销售联系获得,或者拨打400-708-8006联系)