【GaussDB】基于CSN/TIMESTAMP闪回表到历史时间。

1.开启闪回功能 

gs_guc set -N all -I all -c "enable_default_ustore_table=on"  (默认打开)
--## 开启默认支持Ustore存储引擎
gs_guc set -N all -I all -c "version_retention_age=10000"    (默认0) 
--## 旧版本保留的事务数，超过该事务数的旧版本将被回收清理
gs_guc set -N all -I all -c "enable_recyclebin=on"           (默认关闭)  
--## 打开回收站
gs_guc set -N all -I all -c "recyclebin_retention_time=15min" (默认15分钟)
--## 置回收站对象保留时间，超过该时间的回收站对象将被自动清理
gs_guc set -N all -I all -c "undo_retention_time=86400"       (默认0)

gs_guc set -N all -I all -c "enable_default_ustore_table=on" 
gs_guc set -N all -I all -c "version_retention_age=10000"
gs_guc set -N all -I all -c "enable_recyclebin=on"  
gs_guc set -N all -I all -c "recyclebin_retention_time=15min" 
gs_guc set -N all -I all -c "undo_retention_time=86400" 

如果已经开启就不用重复开了。

2.操作数据并记录时间 

select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 12:00' order by snptime desc;
            snptime            | snpcsn 
-------------------------------+--------
 2024-04-09 11:58:40.135915+08 |  28126
 2024-04-09 11:58:37.10279+08  |  28123
 2024-04-09 11:58:34.072052+08 |  28120
 2024-04-09 11:58:31.040367+08 |  28117
 2024-04-09 11:58:28.009364+08 |  28112
 2024-04-09 11:58:24.973027+08 |  28108
 2024-04-09 11:58:21.945881+08 |  28105
 2024-04-09 11:58:18.909798+08 |  28102
 2024-04-09 11:58:15.879746+08 |  28099
 2024-04-09 11:58:12.84645+08  |  28096
 2024-04-09 11:58:09.815601+08 |  28093
 2024-04-09 11:58:06.782726+08 |  28090
 2024-04-09 11:58:03.74962+08  |  28084
 2024-04-09 11:58:00.722211+08 |  28081
(14 rows)

(1)插入前时间:28126
插入数据:
mydb1=> insert into test2 values(3),(4),(5),(6);
INSERT 0 4
mydb1=> select * from test3; 
 id |  name   
----+---------
  1 | 雪霜去
  2 | 薛双奇2
(2 rows)

mydb1=> select * from test2; 
 id 
----
  1
  2
  3
  4
  5
  6
(6 rows)

(2)第二次插入:28275
            snptime            | snpcsn 
-------------------------------+--------
 2024-04-09 12:00:41.424479+08 |  28275
 2024-04-09 12:00:38.394161+08 |  28272
 2024-04-09 12:00:35.366127+08 |  28269
 2024-04-09 12:00:32.333742+08 |  28266
 2024-04-09 12:00:29.301718+08 |  28263
 2024-04-09 12:00:26.266639+08 |  28258
 2024-04-09 12:00:23.236084+08 |  28254
 2024-04-09 12:00:20.205659+08 |  28251
 2024-04-09 12:00:17.175269+08 |  28248
 2024-04-09 12:00:14.14343+08  |  28245
 2024-04-09 12:00:11.110262+08 |  28242
 2024-04-09 12:00:08.078396+08 |  28239
 2024-04-09 12:00:05.047515+08 |  28234
 2024-04-09 12:00:02.009359+08 |  28230
(14 rows)

--当前时间:2024-04-09 12:00:41
insert into test2 values(7),(8);
insert into test3 values(3,'薛双奇2'),(3,'薛双奇3');
mydb1=> select * from test2; 
 id 
----
  1
  2
  3
  4
  5
  6
  7
  8
(8 rows)

mydb1=> select * from test3; 
 id |  name   
----+---------
  1 | 雪霜去
  2 | 薛双奇2
  3 | 薛双奇2
  3 | 薛双奇3
(4 rows)

3.闪回TEST2 到:28126

mydb1=> TIMECAPSULE TABLE test2 TO   CSN 28126;
TimeCapsule Table
mydb1=> select * from test2; 
 id 
----
  1
  2
(2 rows)

mydb1=> select * from test3;
 id |  name   
----+---------
  1 | 雪霜去
  2 | 薛双奇2
  3 | 薛双奇2
  3 | 薛双奇3
(4 rows)

由此可见表TEST2已经闪回到插入前的状态，但TEST3却没有任何变化。即闪回表不会对其他表
产生影响。

4.基于TIMESTAMP 闪回test3; 

mydb1=> select * from test3; 
 id |  name   
----+---------
  1 | 雪霜去
  2 | 薛双奇2
  3 | 薛双奇2
  3 | 薛双奇3
(4 rows)

mydb1=> TIMECAPSULE TABLE test3 TO   TIMESTAMP  to_timestamp('2024-04-09 12:00:41','YYYY-MM-DD HH24:MI:SS');
TimeCapsule Table
mydb1=> select * from test3; 
 id |  name   
----+---------
  1 | 雪霜去
  2 | 薛双奇2
(2 rows)

--果然基于TIMESTAMP 闪回到之前的时间点了。