HGDB数据库闪回查询举例详解(企业版)
发布于 2021-09-25 14:05
目录
文档用途
详细信息
文档用途
1、采用闪回技术，可以针对行级和事务级发生过变化的数据进行恢复，减少了数据恢复的时间，而且操作简单，通过 SQL 语句就可以实现数据的恢复，大大提高了数据库恢复的效率。
2、闪回查询允许查询过去时间点数据，或过去时间段数据变化。能够在短时间内恢复错误操作等造成的数据错误问题，恢复错误的 DML 操作，为数据库的数据恢复提供更加便捷的途径。
详细信息
一、前期准备
1.1、配置参数
1、track_commit_timestamp 默认值是off，要想使用闪回查询，必须设置track_commit_timestamp=on，开启事务提交时间跟踪，确保可以从事务号得到事务结束的时间。
2、hg_fbq_retention 闪回查询数据保存时间，单位为秒（s），默认值是 0，建议最大值不超过 432000。
3、hg_fbq_guarantee 开启闪回查询后，是否保证hg_fbq_retention参数时间设置内的数据可以闪回查询到。默认为 off，即不允许autovacuum 清理该参数时间范围内的数据，但不限制手工vacuum操作。
4、参数生效方式：pg_ctl reload 或 select pg_reload_conf()使其生效。
1.2、参数说明
1、hg_fbq_retention 为系统当前时间之前保留的时间值，如 hg_fbq_retention=30，即保留当前时间之前 30s 的数据，此参数设置后，该时间范围内的数据不允许进行autovacuum 操作，但允许手工vacuum清理。该参数值默认为 0，即默认没有闪回查询功能。
2、hg_fbq_quarantee，该参数表示，开启闪回查询后，是否保证hg_fbq_retention参数时间设置内的数据可以闪回查询到，默认值为 off。
1)当hg_fbq_retention 大于 0（即为已经配置）时，hg_fbq_quarantee参数对应功能才可生效。
2)当hg_fbq_retention 大于 0（即为已经配置），并且 hg_fbq_quarantee设为off时，若系统进行 autovacuum 操作，则不允许清理该参数时间范围内的数据，但不限制手工 vacuum操作。
3)当hg_fbq_retention 大于 0（即为已经配置），并且hg_fbq_quarantee设为on时，所有的vacuum操作都不允许清理 hg_fbq_retention配置时间范围内的数据。
4)当hg_fbq_retention等于0（即为不配置）时，此参数功能不生效，不影响原有系统功能。
注意：当事物号 XID 快用完时，数据库会强制启动 vacuum 对事务 ID 进行回收，此时以上参数不进行控制。Vacuum 之后闪回数据会丢失，此时进行闪回查询时返回结果为空。
二、功能描述及原理
2.1、功能描述
闪回查询允许查询过去某个时间点的所有数据。
2.2、工作原理
在 PostgreSQL中，根据 mvcc 原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在 vacuum 清理之前，这些数据还存在 page 页中，根据元组可见性，闪回查询过去某个时间点或某个事务可见的数据。
三、闪回查询举例
3.1、指定时间点闪回查询
功能描述：在 SELECT 中使用 FLASHBACK 子句指定过去时间点查询。
3.1.1、闪回语法
SELECT column_name[, …]FROM table_name[FLASHBACK TIMESTAMP expression][WHERE condition]
其中，FLASHBACK 用于指定闪回查询时查询的时间点。
时间为 timestamp 类型，时间格式为“YYYY-MM-DD HH24:MI:SS”。
3.1.2、事务操作
示例：
highgo=# create table test(id int,city varchar(200),time timestamp);CREATE TABLEhighgo=# insert into test values(1,'jinan',now());INSERT 0 1highgo=# select xmin,xmax,* from test;xmin | xmax | id | city | time------+------+----+-------+----------------------------2513 | 0 | 1 | jinan | 2020-09-27 09:21:32.072763(1 行记录)highgo=# update test set city='hangzhou',time=now() where id=1;UPDATE 1highgo=# select xmin,xmax,* from test;xmin | xmax | id | city | time------+------+----+----------+----------------------------2514 | 0 | 1 | hangzhou | 2020-09-27 09:21:46.411916(1 行记录)highgo=# update test set city='beijing',time=now() where id=1;UPDATE 1highgo=# select xmin,xmax,* from test;xmin | xmax | id | city | time------+------+----+---------+----------------------------2515 | 0 | 1 | beijing | 2020-09-27 09:22:04.351423(1 行记录)
3.1.3、闪回查询
highgo=# select * from test flashback timestamp '2020-09-27 09:21:32';id | city | time----+------+------(0 行记录)highgo=# select * from test flashback timestamp '2020-09-27 09:21:33';id | city | time----+-------+----------------------------1 | jinan | 2020-09-27 09:21:32.072763(1 行记录)highgo=# select * from test flashback timestamp '2020-09-27 09:21:55';id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:21:46.411916(1 行记录)
3.2、指定事务号查询
功能描述：在SELECT中使用FLASHBACK子句指定事务号查询。
3.2.1、闪回语法
SELECT column_name[, …]FROM table_name[FLASHBACK XID expression][WHERE condition]
其中，FLASHBACK 用于指定闪回查询时查询的事务号。
示例：
highgo=# select * from test flashback xid 2513;id | city | time----+-------+----------------------------1 | jinan | 2020-09-27 09:21:32.072763(1 行记录)highgo=# select * from test flashback xid 2514;id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:21:46.411916(1 行记录
3.3、闪回版本查询
功能描述：闪回版本查询返回在指定时间间隔或事务号间隔内的所有版本。
工作原理：与闪回查询同理，返回一个时间段内所有的版本。
3.3.1、指定时间点闪回版本查询
功能描述：在 SELECT 中使用 FLASHBACK BETWEEN 子句指定时间点查询。
3.3.2、闪回语法：
SELECT column_name[, …]FROM table_name[FLASHBACK BETWEENTIMESTAMP expression AND expression]WHERE condition
FLASHBACK BETWEEN：用于指定闪回版本查询时查询的时间段。
示例：
highgo=# select * from test flashback between timestamp '2020-09-27 09:21:32' and '2020-09-27 09:23:32' where id = '1';id | city | time----+----------+----------------------------1 | jinan | 2020-09-27 09:21:32.0727631 | hangzhou | 2020-09-27 09:21:46.4119161 | beijing | 2020-09-27 09:22:04.351423行记录)highgo=# select * from test flashback between timestamp '2020-09-27 09:21:32' and '2020-09-27 09:21:55' where id = '1';id | city | time----+----------+----------------------------1 | jinan | 2020-09-27 09:21:32.0727631 | hangzhou | 2020-09-27 09:21:46.411916行记录)
3.4、指定事务号闪回版本查询
功能描述：在 SELECT 中使用 FLASHBACK BETWEEN 子句指定事务号查询。
3.4.1、闪回语法：
SELECT column_name[, …]FROM table_name[FLASHBACK BETWEENXID expression AND expression]WHERE condition
FLASHBACK BETWEEN：用于指定闪回版本查询时查询的事务号段。
示例1：
highgo=# select * from test flashback between xid 2513 and 2515 where id = '1';id | city | time----+----------+----------------------------1 | jinan | 2020-09-27 09:21:32.0727631 | hangzhou | 2020-09-27 09:21:46.4119161 | beijing | 2020-09-27 09:22:04.351423行记录)highgo=# select * from test flashback between xid 2513 and 2514 where id = '1';id | city | time----+----------+----------------------------1 | jinan | 2020-09-27 09:21:32.0727631 | hangzhou | 2020-09-27 09:21:46.411916行记录
示例2：
在同一个事务中对一行更新多次，只显示最后一次提交的版本。
highgo=# begin;BEGINhighgo=# update test set city='jinan' where id=1;UPDATE 1highgo=# update test set city='hangzhou' where id=1;UPDATE 1highgo=# commit;COMMIThighgo=# select xmin,xmax,* from test;xmin | xmax | id | city | time------+------+----+----------+----------------------------2516 | 0 | 1 | hangzhou | 2020-09-27 09:22:04.351423(1 行记录)highgo=# select * from test flashback between timestamp '2020-09-27 09:21:32' and '2020-09-27 09:51:32' where id = '1';id | city | time----+----------+----------------------------1 | jinan | 2020-09-27 09:21:32.0727631 | hangzhou | 2020-09-27 09:21:46.4119161 | beijing | 2020-09-27 09:22:04.3514231 | hangzhou | 2020-09-27 09:22:04.351423(4 行记录)
Jinan并不是一个版本，因为没有提交。
3.5、恢复操作
3.5.1、通过闪回查询进行数据恢复
闪回查询可以支持数据恢复，将数据恢复到过去某个时刻的状态。
在更新时，未指定between条件，导致全表更新，闪回查询过去时间点的数据放到临时表，从临时表恢复原数据或者重命名表。
示例1：
highgo=# select * from test;id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:22:04.3514232 | jinan | 2020-09-27 10:03:37.1338153 | beijing | 2020-09-27 10:03:38.703543行记录)highgo=# select * from test;id | city | time----+----------+----------------------------1 | shanghai | 2020-09-27 09:22:04.3514232 | shanghai | 2020-09-27 10:03:37.1338153 | shanghai | 2020-09-27 10:03:38.703543行记录)highgo=# select * from test flashback timestamp '2020-09-27 10:03:39';id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:22:04.3514232 | jinan | 2020-09-27 10:03:37.1338153 | beijing | 2020-09-27 10:03:38.703543行记录)highgo=# create table test_tmp as select * from test flashback timestamp '2020-09-27 10:03:39';SELECT 3highgo=# select * from test_tmp;id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:22:04.3514232 | jinan | 2020-09-27 10:03:37.1338153 | beijing | 2020-09-27 10:03:38.703543行记录)highgo=# update test a set a.city=(select b.city from test_tmp b where a.id=b.id);UPDATE 3highgo=# select * from test;id | city | time----+----------+----------------------------1 | hangzhou | 2020-09-27 09:22:04.3514232 | jinan | 2020-09-27 10:03:37.1338153 | beijing | 2020-09-27 10:03:38.703543行记录)##或者直接将临时表命名为原表名highgo=# alter table test_tmp rename to test;ALTER TABLE
示例2：
highgo=# update test set city = (select city from test flashback timestamp '2020-09-27 09:22:04' where id=1) where id = 1;UPDATE 1highgo=# update test set city = (select city from test flashback timestamp '2020-09-27 10:03:37' where id=2) where id = 2;UPDATE 0highgo=# update test set city = (select city from test flashback timestamp '2020-09-27 10:03:38' where id=2) where id = 3;UPDATE 0highgo=# select * from test;id | city | time----+----------+----------------------------2 | jinan | 2020-09-27 10:03:37.1338153 | beijing | 2020-09-27 10:03:38.7035431 | hangzhou | 2020-09-27 09:22:04.351423(3 行记录)
3.6、注意事项
目前闪回查询功能只能对 DML（insert、update 和 delete）操作进行闪回查询，对于 DDL 及 truncate 对表的操作，目前闪回查询还不支持。除此之外，闪回查询也不支持表结构做出的修改操作。
本文来自网络或网友投稿，如有侵犯您的权益，请发邮件至：aisoutu@outlook.com 我们将第一时间删除。
相关素材