磐维数据库闪回功能测试

一、闪回查询

1、闪回测试

目前生产环境主要以astore表为主，次数只测试astore表

mydb=> create table t1 as select * from pg_tables;
INSERT 0 143

mydb=> SELECT * FROM t1 FLASHBACK(5);
错误:  flashback关闭

提示需要开启闪回功能

mydb=> show max_flashback_time ;
 max_flashback_time 
--------------------
 0
(1 行记录)

mydb=> alter system set max_flashback_time=7200;
ALTER SYSTEM SET
mydb=> show max_flashback_time ;
 max_flashback_time 
--------------------
 7200
(1 行记录)

配置max_flashback_time参数后继续测试

mydb=> update t1 set tablename='t2';
UPDATE 286

mydb=> SELECT * FROM t1 FLASHBACK(10);
     schemaname     | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time         
--------------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
 pg_catalog         | t1        | omm        |            | t          | f        | f           |              |                               | 2025-04-06 03:55:48.497566+08
 cy                 | t1        | cy         |            | f          | f        | f           | cy           | 2025-04-06 10:50:46.993532+08 | 2025-04-06 10:50:46.993532+0
 
 mydb=> delete  from t1; 
DELETE 286
mydb=> select * from t1;
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time 
------------+-----------+------------+------------+------------+----------+-------------+--------------+---------+---------------
(0 行记录)

mydb=> SELECT * FROM t1 FLASHBACK(10);   
     schemaname     | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator |            created            |         last_ddl_time         
--------------------+-----------+------------+------------+------------+----------+-------------+--------------+-------------------------------+-------------------------------
 pg_catalog         | t2        | omm        |            | t          | f        | f           |              |                               | 2025-04-06 03:55:48.497566+08
 cy                 | t2        | cy         |            | f          | f        | f           | cy           | 2025-04-06 10:50:46.993532+08 | 2025-04-06 10:50:46.993532+08
 pg_catalog         | t2        | omm        |            | t          | f        | f           |              |                               | 2025-04-06 03:55:25.69895+08
 pg_catalog         | t2        | omm        |            | f          | f        | f           | omm          | 2025-04-06 03:55:25.69895+08  | 2025-04-06 03:55:25.69895+08
 pg_catalog         | t2        | omm        |            | f          | f        | f           | omm          | 2025-04-06 03:55:25.69895+08  | 2025-04-06 03:55:25.69895+08
 pg_catalog         | t2        | omm        |            | t          | f        | f           |              |                               | 2025-04-06 03:55:48.497566+08

2、参数解释

max_flashback_time 控制闪回功能支持的最大时间范围，单位为秒，直接配置即可。数据库通过保存数据的多个版本，允许查询某一时间点或事务号（CSN）的历史状态。

二、闪回drop

1、闪回测试

开启回收站

postgres=# alter system set enable_recyclebin='on';
ALTER SYSTEM SET
postgres=# show enable_recyclebin ;                
 enable_recyclebin 
-------------------
 on
(1 行记录)

postgres=# show recyclebin_retention_time ;
 recyclebin_retention_time 
---------------------------
 15min
(1 行记录)

mydb=> drop table t1;
DROP TABLE
mydb=> timecapsule table t1 to before drop;
TimeCapsule Table
mydb=> select count(*) from t1;
 count 
-------
   143
(1 行记录)

成功闪回

mydb=> drop table t1;
DROP TABLE
mydb=> TIMECAPSULE TABLE t1 TO BEFORE drop RENAME TO t2;         
TimeCapsule Table
mydb=> select count(*) from t1;
错误:  关系 "t1" 不存在在 dn_6001
第1行select count(*) from t1;
                          ^
mydb=> select count(*) from t2;
 count 
-------
   143
(1 行记录) 

闪回并rename为t2

2、参数解释

enable_recyclebin启用回收站功能，默认为off。需设置为on，被DROP的表会进入回收站而非直接删除。

recyclebin_retention_time 设置回收站中对象保留时间（单位：秒）。超时后自动清理。

启用enable_recyclebin后，执行DROP操作时，表元数据及数据文件被移动到回收站（类似操作系统的回收站），并不立即删除。恢复时从中提取对象重建。

三、闪回truncate

1、测试

mydb=> truncate table t1;
TRUNCATE TABLE
mydb=> SELECT * FROM t1 FLASHBACK(10);
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | tablecreator | created | last_ddl_time 
------------+-----------+------------+------------+------------+----------+-------------+--------------+---------+---------------
(0 行记录)

truncate之后无法使用flashback函数进行闪回。

mydb=> timecapsule table t1 to before truncate;
TimeCapsule Table
mydb=> select count(*) from t1;                
 count 
-------
   143
(1 行记录)

闪回truncate需要使用timecapsule闪回。