人大金仓（kes）闪回表及闪回回收站

常用场景：dml数据条数及drop表丢失时间短，存在历史数据。

优点：便捷省事，还原场景小，还原速度快。

缺点：

3.1.1.闪回查询和闪回表技术依赖于历史的数据，如果历史数据因为vacuum、truncate、rewrite 等操作被回收掉，那么会导致无法闪回到这些操作之前的时刻。因此推荐用户在期望使用闪回查询的时候对vacuum相关参数做一定的调整（关闭表级的autovacuum，推荐调大vacuum_defer_cleanup_age的值以降低历史数据被回收的机会）

3.1.2.目前闪回查询和闪回表技术在vacuum、truncate、和部分ddl 之后将不允许进行闪回到这些操作之前

3.1.3.闪回查询应用于视图或者物化视图里面应该尽量避免对于常量时间戳和CSN 的使用，可能会引发dump 和 restore的失败

3.1.4.闪回回收站需要手动清理，定期运维

3.1.4.1操作步骤

1.前期准备：

vim /data/dbdata/data/kingbase.conf

shared_preload_libraries = 'kdb_flashback'

注：修改完之后需要重启数据库

2.创建扩展--所在库

test=# \c db1

您现在已经连接到数据库 "db1",用户 "system".

db1=# create extension kdb_flashback;

CREATE EXTENSION

db1=# \dx kdb_flashback

                       已安装扩展列表

     名称      | 版本 |  架构模式  |          描述

---------------+------+------------+-------------------------

 kdb_flashback | 1.0  | pg_catalog | kdb_flashback extension

(1 行记录)

3.检查参数

test=# show kdb_flashback.enable_flashback_query; ---闪回查询，scn号

 kdb_flashback.enable_flashback_query

--------------------------------------

 on

(1 行记录)

db1=# show track_commit_timestamp ; --闪回查询，时间戳，如果没开，需要开启并重启服务器

 track_commit_timestamp

------------------------

 on

(1 行记录)

test=# show kdb_flashback.db_recyclebin;  --闪回回收站，drop

 kdb_flashback.db_recyclebin

-----------------------------

 on

(1 行记录)

4.闪回查询--可用vacuum full清空快照

db1=# create table fb_example(id int, name varchar(100));

CREATE TABLE

db1=# insert into fb_example values(1, 'name1');

INSERT 0 1

db1=# insert into fb_example values(2, 'name1');

INSERT 0 1

db1=# insert into fb_example values(3, 'name1');

INSERT 0 1

db1=# select * from fb_example;

 id | name

----+-------

  1 | name1

  2 | name1

  3 | name1

(3 行记录)

db1=# select now();

              now

-------------------------------

 2023-10-16 14:56:10.278140+08

(1 行记录)

4.1进行update操作

db1=# update fb_example set name = null where id=1;

UPDATE 1

db1=# select * from fb_example;

 id | name

----+-------

  2 | name1

  3 | name1

  1 |

(3 行记录)

db1=# select now();

              now

-------------------------------

 2023-10-16 14:57:16.054610+08

(1 行记录)

4.2进行delete操作

db1=# delete from fb_example where id=2;

DELETE 1

db1=# select * from fb_example;

 id | name

----+-------

  3 | name1

  1 |

(2 行记录)

db1=# select now();

              now

-------------------------------

 2023-10-16 14:58:46.585519+08

(1 行记录)

4.3进行insert操作

db1=# insert into fb_example values(4, 'name1');

INSERT 0 1

db1=# select * from fb_example;

 id | name

----+-------

  3 | name1

  1 |

  4 | name1

(3 行记录)

db1=# select now();

              now

-------------------------------

 2023-10-16 14:59:27.333974+08

(1 行记录)

4.4查询scn号

db1=# select versions_startscn, versions_endcsn, * from fb_example versions between csn minvalue and maxvalue;

 versions_startscn | versions_endcsn | id | name

-------------------+-----------------+----+-------

       65536000001 |     65536000004 |  1 | name1

       65536000002 |     65536000005 |  2 | name1

       65536000003 |                 |  3 | name1

       65536000004 |                 |  1 |

       65536000006 |                 |  4 | name1

(5 行记录)

可查询各scn号的快照具体内容

db1=# select * from fb_example as of csn  65536000003;

 id | name

----+-------

  1 | name1

  2 | name1

  3 | name1

(3 行记录)

db1=# select * from fb_example as of csn  65536000004;

 id | name

----+-------

  2 | name1

  3 | name1

  1 |

(3 行记录)

db1=# select * from fb_example as of csn  65536000005;

 id | name

----+-------

  3 | name1

  1 |

(2 行记录)

db1=# select * from fb_example as of csn  65536000006;

 id | name

----+-------

  3 | name1

  1 |

  4 | name1

(3 行记录)

注：我们可以看到分别对应update，delete，insert后查询内容

4.5根据scn号恢复到update之前

db1=# flashback table fb_example to csn 65536000003;

FLASHBACK TABLE

db1=# select * from fb_example ;

 id | name

----+-------

  3 | name1

  1 | name1

  2 | name1

(3 行记录)

注：此时数据恢复回来

4.6根据时间戳恢复到update之前

db1=# update fb_example set name = null;

UPDATE 3

db1=# select * from fb_example ;

 id | name

----+------

  3 |

  1 |

  2 |

(3 行记录)

db1=# select * from fb_example as of timestamp '2023-10-16 14:56:10.278140+08';

 id | name

----+-------

  1 | name1

  2 | name1

  3 | name1

(3 行记录)

注：可以根据时间戳闪回查询数据内容

5.闪回回收站

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname | object_name | original_name | type | droptime

---------+--------+---------+-------------+---------------+------+----------

(0 行记录)

db1=# select * from  recyclebin;

 oid | original_name | droptime | type

-----+---------------+----------+------

(0 行记录)

db1=# select * from fb_example ;

 id | name

----+------

  3 |

  1 |

  2 |

(3 行记录)

5.1进行drop该表

db1=# drop table fb_example ;

DROP TABLE

db1=# select * from fb_example ;

ERROR:  relation "fb_example" does not exist

第1行select * from fb_example ;

                   ^

5.2闪回回收站进行drop表恢复

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname |                object_name                | original_name | type |           droptime

---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------

    1259 |  21733 | public  | bin$$21733$$2023-10-16 15:21:18.071489+08 | fb_example    | r    | 2023-10-16 15:21:18.071489+08

(1 行记录)

db1=# select * from  recyclebin;

  oid  | original_name |           droptime            | type

-------+---------------+-------------------------------+-------

 21733 | fb_example    | 2023-10-16 15:21:18.071489+08 | TABLE

(1 行记录)

db1=# flashback table fb_example to before drop;

FLASHBACK TABLE

db1=# select * from fb_example ;

 id | name

----+------

  3 |

  1 |

  2 |

(3 行记录)

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname | object_name | original_name | type | droptime

---------+--------+---------+-------------+---------------+------+----------

(0 行记录)

db1=# select * from  recyclebin;

 oid | original_name | droptime | type

-----+---------------+----------+------

(0 行记录)

注：恢复之后回收站该表清理

5.3闪回回收站清理

注：闪回回收站只能手动清理，需要定期维护

db1=# create table fb_example1 as select * from fb_example;

SELECT 3

db1=# select * from fb_example ;

 id | name

----+------

  3 |

  1 |

  2 |

(3 行记录)

db1=# select * from fb_example1 ;

 id | name

----+------

  3 |

  1 |

  2 |

(3 行记录)

db1=# drop table fb_example ;

DROP TABLE

db1=# drop table fb_example1 ;

DROP TABLE

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname |                object_name                | original_name | type |           droptime

---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------

    1259 |  21733 | public  | bin$$21733$$2023-10-16 15:26:11.793302+08 | fb_example    | r    | 2023-10-16 15:26:11.793302+08

    1259 |  21736 | public  | bin$$21736$$2023-10-16 15:26:13.895265+08 | fb_example1   | r    | 2023-10-16 15:26:13.895265+08

(2 行记录)

db1=# select * from  recyclebin;

  oid  | original_name |           droptime            | type

-------+---------------+-------------------------------+-------

 21733 | fb_example    | 2023-10-16 15:26:11.793302+08 | TABLE

 21736 | fb_example1   | 2023-10-16 15:26:13.895265+08 | TABLE

(2 行记录)

5.3.1清空闪回回收站指定表

db1=# purge table fb_example;

PURGE

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname |                object_name                | original_name | type |           droptime

---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------

    1259 |  21736 | public  | bin$$21736$$2023-10-16 15:26:13.895265+08 | fb_example1   | r    | 2023-10-16 15:26:13.895265+08

(1 行记录)

db1=# select * from  recyclebin;

  oid  | original_name |           droptime            | type

-------+---------------+-------------------------------+-------

 21736 | fb_example1   | 2023-10-16 15:26:13.895265+08 | TABLE

(1 行记录)

5.3.2闪回回收站全部清空

db1=# purge recyclebin;

PURGE

db1=# select * from  recyclebin;

 oid | original_name | droptime | type

-----+---------------+----------+------

(0 行记录)

db1=# select * from sys_recyclebin ;

 classid | reloid | nspname | object_name | original_name | type | droptime

---------+--------+---------+-------------+---------------+------+----------

(0 行记录)

注：闪回表及闪回回收站，便于快速处理，但性能损耗略大，磁盘占用大，过久的历史数据恢复不了。