【GaussDB】闪回DROP

1.背景信息

•闪回DROP：可以恢复意外删除的表，从回收站（recyclebin）中恢复被删除的表及其附属结构如索引、表约束等。
闪回drop是基于回收站机制，通过还原回收站中记录的表的物理文件，实现已drop表的恢复。
•闪回TRUNCATE：可以恢复误操作或意外被进行truncate的表，从回收站中恢复被truncate的表及索引的物理数据。
闪回truncate基于回收站机制，通过还原回收站中记录的表的物理文件，实现已truncate表的恢复。

2.开启闪回功能 

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" 

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

3.DROP 表。

mydb1=> select snptime,snpcsn from gs_txn_snapshot where snptime>='2024-04-09 11:40' order by snptime desc;
           snptime            | snpcsn 
-------------------------------+--------
 2024-04-09 11:40:31.55595+08  |  26719
 2024-04-09 11:40:28.526085+08 |  26716
 2024-04-09 11:40:25.489872+08 |  26711
 2024-04-09 11:40:22.45913+08  |  26707
 2024-04-09 11:40:19.428504+08 |  26704
 2024-04-09 11:40:16.397608+08 |  26701
 2024-04-09 11:40:13.36746+08  |  26698
 2024-04-09 11:40:10.334387+08 |  26695
 2024-04-09 11:40:07.3036+08   |  26692
 2024-04-09 11:40:04.270007+08 |  26687
 2024-04-09 11:40:01.242144+08 |  26683
(11 rows)


我们取当前的CSN:26719

4.删除表。

mydb1=> \d
                                         List of relations
 Schema | Name  | Type  | Owner |                             Storage                              
--------+-------+-------+-------+------------------------------------------------------------------
 public | test2 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
 public | test3 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
(2 rows)

mydb1=> select * from test2;
 id 
----
  1
  2
(2 rows)

mydb1=> drop table test2;
DROP TABLE
mydb1=> \d
                                                     List of relations
 Schema |             Name              | Type  | Owner |                             Storage                              
--------+-------------------------------+-------+-------+------------------------------------------------------------------
 public | BIN$600023386004$18181C88==$0 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
 public | test3                         | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
(2 rows)

5.闪回DROP

mydb1=> \d
                                                     List of relations
 Schema |             Name              | Type  | Owner |                             Storage                              
--------+-------------------------------+-------+-------+------------------------------------------------------------------
 public | BIN$600023386004$18181C88==$0 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
 public | test3                         | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
(2 rows)

mydb1=> TIMECAPSULE TABLE "BIN$600023386004$18181C88==$0" to before drop;
TimeCapsule Table
mydb1=> \d
                                         List of relations
 Schema | Name  | Type  | Owner |                             Storage                              
--------+-------+-------+-------+------------------------------------------------------------------
 public | test2 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
 public | test3 | table | root  | {orientation=row,compression=no,storage_type=USTORE,segment=off}
(2 rows)

6.查询。 

mydb1=> select * from test2; 
 id 
----
  1
  2
(2 rows)

--果然，闪回DROP 完成。