闪回表

闪回表基于MVCC多版本机制，通过删除指定时间点和该时间点之后的增量数据，并找回指定时间点和当前时间点删除的数据，实现表级数据还原。

方案一：基于TIMECAPSULE语法的闪回表

功能描述

使用TIMECAPSULE语法进行闪回表，可以将表恢复至特定时间点或者CSN。

注意事项

• 使用闪回表功能需要配置GUC参数undo_retention_time，用于设置undo旧版本的保留时间。

  该参数属于SIGHUP类型参数，请参考重设参数表1中对应设置方法进行设置。

• 此功能暂不支持ASTORE引擎。因此需要在建表时指定存储方式为USTORE，或者设置GUC参数enable_default_ustore_table为on，此时创建的所有表默认使用USTORE存储引擎。

• 不支持闪回表的对象类型：系统表、列存表、内存表、DFS表、全局临时表、本地临时表、unlogged表、序列表、hashbucket表。

• 闪回点和当前点之间，如果执行过修改表结构或影响物理存储的语句（DDL、DCL、VACUUM FULL），将导致闪回失败。

语法格式

TIMECAPSULE TABLE table_name TO { TIMESTAMP | CSN } expression

闪回表的用法例如：

• 闪回表至指定的时间戳：

  TIMECAPSULE TABLE t1 TO TIMESTAMP to_timestamp ('2020-02-11 10:13:22.724718', 'YYYY-MM-DD HH24:MI:SS.FF');
  

• 闪回表至指定的CSN号：

  TIMECAPSULE TABLE t1 TO CSN 9617;
  

参数说明

• TIMECAPSULE

  表示使用闪回功能。

• table_name

  需要使用闪回功能的表名。

• TIMESTAMP

  指要闪回到某个表在TIMESTAMP这个时间点上的数据，TIMESTAMP指一个具体的历史时间。

• CSN

  CSN指一个具体逻辑提交时间点，数据库中的CSN为写一致性点，每个CSN代表整个数据库的一个一致性点。

示例

1、设置undo_retention_time为600秒。

alter system set undo_retention_time to 600;

2、查看修改是否生效。

show undo_retention_time;

如下返回结果表示设置生效：

 undo_retention_time
---------------------
 600s
(1 row)

3、创建USTORE测试表并插入两条数据。

create table table1(name int) with(STORAGE_TYPE=USTORE);
insert into table1 values(1);
insert into table1 values(2);

4、记录当前时间戳。

select current_timestamp;

返回结果为：

        pg_systimestamp
-------------------------------
 2023-04-04 16:33:41.183125+08
(1 row)

5、再次插入一条数据。

insert into table1 values(3);

6、再次查看当前时间戳。

select current_timestamp;

返回结果为：

        pg_systimestamp
-------------------------------
 2023-04-04 16:33:48.603477+08
(1 row)

7、查询步骤4和步骤6查看到的两个timestamp之间的全部CSN。

select snptime,snpcsn from gs_txn_snapshot where snptime between '2023-04-04 16:33:41.183125+08' and '2023-04-04 16:33:48.603477+08';

返回结果如下:

            snptime            | snpcsn
-------------------------------+--------
 2023-04-04 16:33:43.953524+08 |   6123
 2023-04-04 16:33:46.976891+08 |   6126
(2 rows)

8、查看当前测试表的数据：

select * from table1;

返回结果如下，共有3条数据：

 name
------
    1
    2
    3
(3 rows)

9、根据步骤7查看到的CSN号，选择其中较早的一个CSN并恢复到其指定的表版本。

TIMECAPSULE TABLE table1 to CSN 6123;

10、恢复后，查看当前表的数据：

select * from table1;

返回结果如下，表示此表已成功恢复至步骤5时插入第三条数据之前的版本。

 name
------
    1
    2
(2 rows)

方案二：基于pg_flashback函数的闪回表

功能描述

使用基于pg_flashback函数的闪回表，可以将表闪回到不超过max_flashback_time时长以内的旧版本。

注意事项

• max_flashback_time用于控制闪回功能支持的最大时长，单位为秒（s）。该参数属于SIGHUP类型参数，请参考重设参数表1中对应设置方法进行设置。

• 不支持连续闪回。

• 使用闪回查询功能时建议关闭autovacuum功能，确保闪回时间点的数据没有被autovacuum清理。

• 此方案下数据表truncate后无法闪回。

语法格式

SELECT PG_FLASHBACK('table_name',n);

参数说明

• table_name

  指定需要进行闪回表功能的表名。

• n

  闪回表的时长，单位为s（秒）。

  例如PG_FLASHBACK('table1',15)表示将表table1闪回至15秒之前的状态。

示例

1、设置max_flashback_time为180秒。

alter system set max_flashback_time to 180;

2、查看修改是否生效：

show max_flashback_time;

如下返回结果表示设置成功：

 max_flashback_time
--------------------
 180
(1 row)

3、检查是否开启了事务自动提交。

\echo :AUTOCOMMIT

返回结果为on，表示启用了自动提交，此为默认值。若未启用自动提交，可使用\set AUTOCOMMIT on命令开启。

4、创建测试表并插入一条数据。

create table t_a (col1 int, col2 char(20),col3 char(20));
insert into t_a values(1,'aa','a1');

5、休眠等待10秒。

select pg_sleep(10);

6、为表格插入第二条数据，随后闪回至插入这条数据之前的状态。

执行以下两条SQL时需要注意连贯性，避免因间隔时间过长导致闪回表的结果与示例不符。

insert into t_a values(2,'bb','b1');
select pg_flashback('t_a',8);

7、查看闪回结果。

select * from t_a;

返回结果如下，表示表 t_a 已闪回为未插入第二条数据之前的旧版本。

 col1 |         col2         |         col3         
------+----------------------+----------------------
    1 | aa                   | a1                                  
(1 row)