跳转至

集群单表闪回

集群单表闪回

集群单表闪回允许将集群单个用户表中数据恢复到某个指定时间的状态。能够在短时间内恢复对表的误操作(DML或部分DDL)，为数据库的数据恢复提供更加便捷的途径。

配置参数

  • shared_preload_libraries 设置shared_preload_libraries = 'sdtrashcan'，加载此功能需要用的库。

  • flashback_mode 设置flashback_mode = 'mod'，来保证不涉及删除表文件的事务可以被闪回，不必重启服务，但需要重新加载配置参数。 设置flashback_mode = 'all'，来保证所有事务可以被闪回，不必重启服务，但需要重新加载配置参数。

  • autovacuum 控制是否使用autovacuum功能，设置autovacuum = 'off'，关闭该功能。

说明 第一个参数需要重启服务才能生效。

集群单表闪回

功能描述:

集群单表闪回允许将表中数据恢复到某一指定时间戳时的状态。

工作原理：

在SeaboxMPP中，根据mvcc原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在vacuum清理之前，这些数据还存在page页中，根据元组可见性，通过闪回查询过去某个时间点或某个事务可见的数据，然后恢复到表中。

使用注意事项：

  • 闪回SQL中的时间戳或集群事务号，通过调用函数sd_record_timestamp_gxid存储到系统表sd_timestamp_gxid中
  • 用户可以手动执行或者启用定时job来调用函数sd_record_timestamp_gxid 
    seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-06 14:34:26+08 | 214016 | 13841
(1 row)

说明

  • gxid表示当前时刻（time）下一个开始的集群事务的事务号，即该集群事务号（gxid）之前的事务号已经被使用

  • 从闪回结果来看，gxid所对应的事务是不可见的

  • 指定时间点集群单表闪回

功能描述：

使用FLASHBACK语句将表闪回至某个过去时间点的状态。

集群单表闪回语法：

FLASHBACK TABLE table_name TIMESTAMP expression
TIMESTAMP 用于指定要闪回到的时间点。时间为 timestamp 类型，时间格式为“YYYY-MM-DD HH24:MI:SS”。

示例：

  • 闪回DML操作
seaboxsql=# CREATE TABLE test(a int, b text) distributed by (a);
CREATE TABLE
seaboxsql=# create index idx on test(a);
CREATE INDEX
seaboxsql=# insert into test select g, g||'abcd' from generate_series(1, 20)g;
INSERT 0 20
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-06 14:34:26+08 | 214016 | 13841
(1 row)

seaboxsql=# insert into test select g, g||'abcd' from generate_series(21, 40)g;
INSERT 0 20
seaboxsql=# select * from sd_timestamp_gxid ;
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-06 14:34:26+08 | 214016 | 13841
(1 row)

seaboxsql=# flashback table test timestamp '2023-06-06 14:34:26';
FLASHBACK TABLE
seaboxsql=# select count(*) from test;
 count
-------
    20
(1 row)

  • 闪回DML与带时间戳的VACUUM

  • vacuum timestamp阻止了删除的数据被vacuum

seaboxsql=# create table t1(a int, b varchar(20)) distributed by(a);
CREATE TABLE
seaboxsql=# insert into t1 select i, 'abcd' from generate_series(1, 10)i;
INSERT 0 10
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
2023-11-09 10:57:59+08 | 160034 | 14761
(1 row)

seaboxsql=# delete from t1 where a < 6;
DELETE 5
seaboxsql=# vacuum before '2023-11-09 10:57:59' t1;
VACUUM
seaboxsql=# flashback table t1 timestamp '2023-11-09 10:57:59';
FLASHBACK TABLE
seaboxsql=# select * from t1;
a  |  b   
----+------
  1 | abcd
  6 | abcd
  9 | abcd
10 | abcd
  5 | abcd
  7 | abcd
  8 | abcd
  2 | abcd
  3 | abcd
  4 | abcd
(10 rows)
  • vacuum timestamp 使delete的数据被vacuum
seaboxsql=# create table t1(a int, b varchar(20)) distributed by(a);
CREATE TABLE
seaboxsql=# insert into t1 select i, 'abcd' from generate_series(1, 10)i;
INSERT 0 10
seaboxsql=# delete from t1 where a < 6;
DELETE 5
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
2023-11-09 11:01:40+08 | 160041 | 14761
(1 row)

seaboxsql=# vacuum before '2023-11-09 11:01:40' t1;
VACUUM
seaboxsql=# flashback table t1 timestamp '2023-11-09 11:01:40';
FLASHBACK TABLE
seaboxsql=# select * from t1;
a  |  b   
----+------
  6 | abcd
  9 | abcd
10 | abcd
  7 | abcd
  8 | abcd
(5 rows)
  • 闪回DDL操作
seaboxsql=# CREATE TABLE test(a int, b text) distributed by (a);
CREATE TABLE
seaboxsql=# create index idx on test(a);
CREATE INDEX
seaboxsql=# insert into test select g, g||'abcd' from generate_series(1, 20)g;
INSERT 0 20
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-06 14:57:10+08 | 214016 | 13841
(1 row)

seaboxsql=# insert into test select g, g||'abcd' from generate_series(21, 40)g;
INSERT 0 20
seaboxsql=# drop table test;
DROP TABLE
seaboxsql=# select * from sd_timestamp_gxid ;
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-06 14:57:10+08 | 214016 | 13841
(1 row)

seaboxsql=# flashback table test timestamp '2023-06-06 14:57:10';
FLASHBACK TABLE
seaboxsql=# select count(*) from test;
 count
-------
    20
(1 row)
集群单表查询闪回

闪回查询允许查询过去时间点数据，或过去时间段数据变化。能够在短时间内恢复错误操作等造成的数据错误问题，恢复错误的DML操作，为数据库的数据恢复提供更加便捷的途径。

功能描述:

闪回查询允许查询过去某个时间点的所有数据。

工作原理：

在SeaboxSQL中，根据mvcc原理，删除或者更新元组并没有立即删除旧数据，而只是标记为已删除，在vacuum清理之前，这些数据还存在page页中，根据元组可见性，闪回查询过去某个时间点或某个事务可见的数据。 使用系统表sd_timestamp_gxid记录时间戳及其集群事务号。

  1. 指定时间点闪回查询

功能描述：

在SELECT中使用FLASHBACK子句指定过去时间点查询。

闪回语法：

SELECT column_name[, …]
FROM table_name
[FLASHBACK TIMESTAMP expression]
[WHERE condition]

其中， FLASHBACK用于指定闪回查询时查询的时间点。时间为timestamp类型，时间格式为“YYYY-MM-DD HH24:MI:SS”。

示例1：

  • 事务操作 

    seaboxsql=# CREATE TABLE test(a int, b text) distributed by (a);
CREATE TABLE
seaboxsql=# insert into test select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 08:48:43+08 | 214016 | 13841
(1 row)

seaboxsql=# update test set a=a*10;
UPDATE 10
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 08:48:52+08 | 214016 | 13841
(1 row)

seaboxsql=# update test set a=a*10;
UPDATE 10
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 08:48:58+08 | 214016 | 13841
(1 row)

seaboxsql=# update test set a=a*10;
UPDATE 10
seaboxsql=# select * from test;
   a   |   b
-------+--------
  7000 | 7abcd
  8000 | 8abcd
  6000 | 6abcd
  2000 | 2abcd
  1000 | 1abcd
  5000 | 5abcd
  9000 | 9abcd
  3000 | 3abcd
  4000 | 4abcd
 10000 | 10abcd
(10 rows)

  • 指定时间戳闪回查询 

    seaboxsql=# select * from test flashback timestamp '2023-06-07 08:48:52';
  a  |   b
-----+--------
  10 | 1abcd
 100 | 10abcd
  70 | 7abcd
  80 | 8abcd
  60 | 6abcd
  90 | 9abcd
  20 | 2abcd
  30 | 3abcd
  40 | 4abcd
  50 | 5abcd
(10 rows)

seaboxsql=# select * from test flashback timestamp '2023-06-07 08:48:58';
  a   |   b
------+--------
  600 | 6abcd
  200 | 2abcd
  100 | 1abcd
  700 | 7abcd
  800 | 8abcd
  500 | 5abcd
 1000 | 10abcd
  900 | 9abcd
  300 | 3abcd
  400 | 4abcd
(10 rows)

  • 指定事务号闪回查询

功能描述：

在SELECT中使用FLASHBACK子句指定事务号查询。

闪回语法：

SELECT column_name[, …]
FROM table_name
[FLASHBACK XID expression]
[WHERE condition]
其中， FLASHBACK 用于指定闪回查询时查询的事务号。

示例2：

  • 指定事务号闪回查询 
    seaboxsql=# select * from test flashback xid 114011;
  a  |   b
-----+--------
  10 | 1abcd
 100 | 10abcd
  70 | 7abcd
  80 | 8abcd
  60 | 6abcd
  90 | 9abcd
  20 | 2abcd
  30 | 3abcd
  40 | 4abcd
  50 | 5abcd
(10 rows)

seaboxsql=# select * from test flashback xid 114012;
  a   |   b
------+--------
  700 | 7abcd
  800 | 8abcd
  500 | 5abcd
 1000 | 10abcd
  900 | 9abcd
  300 | 3abcd
  400 | 4abcd
  600 | 6abcd
  200 | 2abcd
  100 | 1abcd
(10 rows)
闪回版本查询

功能描述：

闪回版本查询返回在指定时间间隔或事务号间隔内的所有版本。

工作原理：

与闪回查询同理，返回一个时间段内所有的版本。

  1. 指定时间点闪回版本查询

功能描述：

在 SELECT 中使用 FLASHBACK BETWEEN 子句指定时间点查询。

闪回语法：

SELECT column_name[, …]
FROM table_name
[FLASHBACK BETWEEN
TIMESTAMP expression AND expression]
WHERE condition
FLASHBACK BETWEEN 用于指定闪回版本查询时查询的时间段。

seaboxsql=# select * from test flashback between timestamp '2023-06-07 08:48:52' and '2023-06-07 08:48:58';
  a   |   b
------+--------
   10 | 1abcd
  100 | 10abcd
  600 | 6abcd
  200 | 2abcd
  100 | 1abcd
   70 | 7abcd
   80 | 8abcd
   60 | 6abcd
   90 | 9abcd
  700 | 7abcd
  800 | 8abcd
  500 | 5abcd
 1000 | 10abcd
   20 | 2abcd
   30 | 3abcd
   40 | 4abcd
   50 | 5abcd
  900 | 9abcd
  300 | 3abcd
  400 | 4abcd
(20 rows)
  1. 指定事务号闪回版本查询

功能描述：

在SELECT中使用FLASHBACK BETWEEN子句指定事务号查询。

闪回语法：

SELECT column_name[, …]
FROM table_name
[FLASHBACK BETWEEN
XID expression AND expression]
WHERE condition

  • FLASHBACK BETWEEN 用于指定闪回版本查询时查询的事务号段。

  • 示例 1

seaboxsql=# select * from test flashback between xid 114011 and 114012;
  a   |   b
------+--------
   20 | 2abcd
   30 | 3abcd
   40 | 4abcd
   50 | 5abcd
  900 | 9abcd
  300 | 3abcd
  400 | 4abcd
   10 | 1abcd
  100 | 10abcd
  600 | 6abcd
  200 | 2abcd
  100 | 1abcd
   70 | 7abcd
   80 | 8abcd
   60 | 6abcd
   90 | 9abcd
  700 | 7abcd
  800 | 8abcd
  500 | 5abcd
 1000 | 10abcd
(20 rows)
  • 示例 2

在同一个事务中对一行更新多次，只显示最后一次提交的版本。

seaboxsql=# CREATE TABLE test(a int, b text) distributed by (a);
CREATE TABLE
seaboxsql=# insert into test select g, g||'abcd' from generate_series(1, 10)g;
INSERT 0 10
seaboxsql=#
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 09:13:34+08| 214016 | 13841
(1 row)

seaboxsql=#
seaboxsql=# begin;
BEGIN
seaboxsql=# update test set a=a*10;
UPDATE 10
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 09:13:35+08 | 214016 | 13841
(1 row)

seaboxsql=# update test set a=a*10;
UPDATE 10
seaboxsql=# commit;
COMMIT
seaboxsql=# select * from sd_record_timestamp_gxid();
          time          |  gxid  | dboid 
------------------------+--------+-------
 2023-06-07 09:13:46+08 | 214016 | 13841
(1 row)

seaboxsql=# select * from test flashback between xid 114021 and 114022;
  a   |   b
------+--------
    2 | 2abcd
    3 | 3abcd
    4 | 4abcd
    7 | 7abcd
    8 | 8abcd
  700 | 7abcd
  800 | 8abcd
  500 | 5abcd
 1000 | 10abcd
    5 | 5abcd
    6 | 6abcd
    9 | 9abcd
   10 | 10abcd
  600 | 6abcd
  200 | 2abcd
  100 | 1abcd
    1 | 1abcd
  900 | 9abcd
  300 | 3abcd
  400 | 4abcd
(20 rows)
注意事项

目前闪回查询功能只能对DML（insert、 update 和 delete）操作进行闪回查询，对于DDL truncate对表的操作，目前闪回查询还不支持。除此之外，闪回查询也不支持表结构做出的修改操作。