人大金仓 Kingbase数据库闪回功能及插件kdb_flashback的使用

数据猿 2023-08-18

数据库的下载安装

[root@localhost ~]# cat /etc/os-release 
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

[root@localhost ~]# uname -a
Linux localhost.localdomain 4.18.0-80.el8.x86_64 #1 SMP Tue Jun 4 09:19:46 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost ~]#
复制

[root@localhost ~]# useradd kingbase
[root@localhost ~]# 
[root@localhost ~]# passwd kingbase 
Changing password for user kingbase.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@localhost ~]# 
[root@localhost ~]# vim /etc/sudoers
[root@localhost ~]#
复制

软件程序下载，点击前往

[kingbase@localhost ~]$ ll
total 4137600
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Desktop
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Documents
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Downloads
-rw-rw-r-- 1 kingbase kingbase 2668978176 Jun 21 09:50 KingbaseES_V008R006C007B0024_Lin64_install.iso
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Music
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Pictures
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Public
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Templates
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Videos
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ mkdir softwareLoc
[kingbase@localhost ~]$ 
复制

[kingbase@localhost ~]$ su
Password: 
[root@localhost kingbase]# cd
[root@localhost ~]# 
[root@localhost ~]# mkdir -p /mnt/cdrom
[root@localhost ~]# 
[root@localhost ~]# mount /home/kingbase/KingbaseES_V008R006C007B0024_Lin64_install.iso /mnt/cdrom
mount: /mnt/cdrom: WARNING: device write-protected, mounted read-only.
[root@localhost ~]# 
[root@localhost ~]# ll /mnt/cdrom/
total 6
dr-xr-xr-x 2 root root 2048 Mar 23 03:50 setup
-r-xr-xr-x 1 root root 3829 Mar 23 03:50 setup.sh
[root@localhost ~]# ll /mnt/cdrom/setup
total 2606065
-r-xr-xr-x 1 root root 2668607786 Mar 23 03:50 install.bin
-r-xr-xr-x 1 root root         46 Mar 23 03:50 MD5
-r--r--r-- 1 root root       1418 Mar 23 03:50 silent.cfg
[root@localhost ~]# 
[root@localhost ~]# cp -r /mnt/cdrom/* /home/kingbase/softwareLoc
[root@localhost ~]# exit
exit
[kingbase@localhost ~]$ ll softwareLoc/
total 4
dr-xr-xr-x 2 root root   54 Jun 21 09:57 setup
-r-xr-xr-x 1 root root 3829 Jun 21 09:57 setup.sh
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo chown kingbase:kingbase softwareLoc/*

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for kingbase: 
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo chown kingbase:kingbase softwareLoc/setup/*
[kingbase@localhost ~]$
[kingbase@localhost ~]$ ll softwareLoc/
total 4
dr-xr-xr-x 2 kingbase kingbase   54 Jun 21 09:57 setup
-r-xr-xr-x 1 kingbase kingbase 3829 Jun 21 09:57 setup.sh
[kingbase@localhost ~]$ ll softwareLoc/setup
total 2606072
-r-xr-xr-x 1 kingbase kingbase 2668607786 Jun 21 09:57 install.bin
-r-xr-xr-x 1 kingbase kingbase         46 Jun 21 09:57 MD5
-r--r--r-- 1 kingbase kingbase       1418 Jun 21 09:57 silent.cfg
[kingbase@localhost ~]$ 
复制

先行下载一个授权文件，如下：

[kingbase@localhost ~]$ ll
total 2606428
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Desktop
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Documents
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Downloads
-rw-rw-r-- 1 kingbase kingbase 2668978176 Jun 21 09:50 KingbaseES_V008R006C007B0024_Lin64_install.iso
-rwxrw-rw- 1 kingbase kingbase       2735 Jun 21 10:07 license_企业版.zip
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Music
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Pictures
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Public
drwxrwxr-x 3 kingbase kingbase         35 Jun 21 09:57 softwareLoc
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Templates
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Videos
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ ll license_企业版.zip 
-rwxrw-rw- 1 kingbase kingbase 2735 Jun 21 10:07 license_企业版.zip
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ unzip license_企业版.zip 
Archive:  license_企业版.zip
   creating: license_29296/
  inflating: license_29296/license_29296_0.dat  
[kingbase@localhost ~]$
[kingbase@localhost ~]$ cd softwareLoc/setup/
[kingbase@localhost setup]$ chmod 755 ./* 
[kingbase@localhost setup]$ ll
total 2606072
-rwxr-xr-x 1 kingbase kingbase 2668607786 Jun 21 09:57 install.bin
-rwxr-xr-x 1 kingbase kingbase         46 Jun 21 09:57 MD5
-rwxr-xr-x 1 kingbase kingbase       1418 Jun 21 09:57 silent.cfg
[kingbase@localhost setup]$ 
复制

先行安装中文（zh_CN）语言包，如下：

[kingbase@localhost ~]$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
[kingbase@localhost ~]$ locale -a |grep CN
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo yum install -y langpacks-zh_CN
...
复制

安装开始，如下：

在这里插入图片描述

选择license，如下：

在这里插入图片描述

安装完成，如下：

[kingbase@localhost release]$ pwd
/home/kingbase/release
[kingbase@localhost release]$ 
[kingbase@localhost release]$ ll
total 4
lrwxrwxrwx  1 kingbase kingbase   64 Jun 21 10:19 ClientTools -> /home/kingbase/release/KESRealPro/V008R006C007B0024/ClientTools/
drwx------ 23 kingbase kingbase 4096 Jun 21 10:30 data
lrwxrwxrwx  1 kingbase kingbase   56 Jun 21 10:19 doc -> /home/kingbase/release/KESRealPro/V008R006C007B0024/doc/
lrwxrwxrwx  1 kingbase kingbase   60 Jun 21 10:19 install -> /home/kingbase/release/KESRealPro/V008R006C007B0024/install/
lrwxrwxrwx  1 kingbase kingbase   62 Jun 21 10:19 Interface -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Interface/
drwxrwxr-x  3 kingbase kingbase   31 Jun 21 10:19 KESRealPro
lrwxrwxrwx  1 kingbase kingbase   63 Jun 21 10:19 KingbaseHA -> /home/kingbase/release/KESRealPro/V008R006C007B0024/KingbaseHA/
lrwxrwxrwx  1 kingbase kingbase   63 Jun 21 10:19 license.dat -> /home/kingbase/release/KESRealPro/V008R006C007B0024/license.dat
lrwxrwxrwx  1 kingbase kingbase   59 Jun 21 10:19 Server -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Server/
lrwxrwxrwx  1 kingbase kingbase   61 Jun 21 10:19 SupTools -> /home/kingbase/release/KESRealPro/V008R006C007B0024/SupTools/
lrwxrwxrwx  1 kingbase kingbase   62 Jun 21 10:19 Uninstall -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Uninstall/
[kingbase@localhost release]$
复制

我这里删除掉安装时初始化的集群，重新初始化如下：

在这里插入图片描述

[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# select version();
                                                       version                                                        
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

kingbase=# select commercial_version();
                                           commercial_version                                            
---------------------------------------------------------------------------------------------------------
 KingbaseES V8.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

kingbase=#
复制

kdb_flashback插件

安装插件

插件kdb_flashback是KingbaseES的一个扩展插件。主要功能是提供错误数据的快速恢复能力，目前提供的闪回技术包括闪回回收站、闪回查询、闪回版本查询和闪回到任意时间点。

插件名为 kdb_flashback
插件版本 V1.0

将其添加到 kingbase.conf 文件的 shared_preload_libraries 中，重启数据库时自动加载，如下：

[kingbase@localhost bin]$ vim test/kingbase.conf 

shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr,sys_squeeze, src_restrict,kdb_flashback'
复制

[kingbase@localhost bin]$ ./sys_ctl start -D test/
waiting for server to start....2023-06-21 11:03:04.044 PDT [12936] LOG:  sepapower extension initialized
2023-06-21 11:03:04.051 PDT [12936] LOG:  starting KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2023-06-21 11:03:04.052 PDT [12936] LOG:  listening on IPv4 address "0.0.0.0", port 54321
2023-06-21 11:03:04.052 PDT [12936] LOG:  listening on IPv6 address "::", port 54321
2023-06-21 11:03:04.054 PDT [12936] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54321"
2023-06-21 11:03:04.128 PDT [12936] LOG:  redirecting log output to logging collector process
2023-06-21 11:03:04.128 PDT [12936] HINT:  Future log output will appear in directory "sys_log".
 done
server started
[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# create extension kdb_flashback;
CREATE EXTENSION
kingbase=# \dx kdb_flashback 
                  List of installed extensions
     Name      | Version |   Schema   |       Description       
---------------+---------+------------+-------------------------
 kdb_flashback | 1.0     | pg_catalog | kdb_flashback extension
(1 row)

kingbase=#
复制

插件kdb_flashback的参数配置，如下：

-- 闪回查询的开启参数，默认开启
kingbase=# show kdb_flashback.enable_flashback_query;
 kdb_flashback.enable_flashback_query 
--------------------------------------
 on
(1 row)

-- 闪回回收站开启参数，参数为bool类型，参数级别为PGC_SIGHUP，默认关闭
kingbase=# show kdb_flashback.db_recyclebin;
 kdb_flashback.db_recyclebin 
-----------------------------
 off
(1 row)

kingbase=#
复制

插件使用

插件kdb_flashback的使用方法，闪回查询相关的技术使用方法：

整个闪回技术(闪回查询和闪回表到指定时间点) 可以通过时间戳和CSN(commit sequence number) 两种方式进行
两种方式目前都需要依赖提交的时间戳，因此需要开启track_commit_timestamp 这个参数。另外闪回查询还需要开启kdb_flashback.enable_flashback_query 的参数
这里解释下时间戳和CSN两个快照指定的方式，时间戳即timestamp：用户可以在闪回查询的快照时间表达式里指定任意一个有效的时间戳

如果允许闪回查询，那么将返回该时刻能够可见的历史快照的数据。CSN 是提交顺序号，第一个有效的CSN为65536000000，CSN 随着提交线性增加。因此如果用户指定一个有效的CSN，比如65536000160，那么闪回查询将基于这个CSN 提交号构建历史快照

闪回查询技术有一定的限制，在数据进行深度清理后和部分schema change后将拒绝闪回查询（具体参考闪回查询须知）

下面具体介绍这几种闪回技术，在使用任何闪回查询相关的技术前，保证在配置文件中开启track_commit_timestamp参数，并重启服务。

闪回查询

闪回查询能返回用户指定历史时刻的快照数据，其中历史快照时刻指定方式可以通过timestamp和csn 两种方式指定(具体语法参考 SELECT )。

[kingbase@bogon bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# show track_commit_timestamp ;
 track_commit_timestamp 
------------------------
 on
(1 row)

kingbase=# show kdb_flashback.enable_flashback_query;
 kdb_flashback.enable_flashback_query 
--------------------------------------
 on
(1 row)

kingbase=#
复制

下面来看一下闪回查询的使用示例，如下：

-- AS OF 闪回查询（timestamp） 语句

kingbase=# create table fb_example(id int, name varchar(100));
CREATE TABLE
kingbase=# insert into fb_example values(1, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(2, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(3, 'name1');
INSERT 0 1
kingbase=# table fb_example;
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select now();
              now              
-------------------------------
 2023-06-24 22:08:06.091204-07
(1 row)

kingbase=# update fb_example set name = null;
UPDATE 3
kingbase=# table fb_example;                 
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=#
复制

如上，update 语句误将 name 更改为null，此时可以通过闪回查询查询到update前的数据。

通常CSN 方式进行闪回查询的时候，需要借助闪回版本查询确定合适的CSN 号，比如上面的误操作(update)，那么通过版本查询可以确定该update 事务的CSN为65536000004，可以通过如下的闪回语句查询相关数据。

-- AS OF 闪回查询（CSN） 语句

kingbase=# 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 |     65536000004 |  2 | name1
       65536000003 |     65536000004 |  3 | name1
       65536000004 |                 |  1 | 
       65536000004 |                 |  2 | 
       65536000004 |                 |  3 | 
(6 rows)

kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';                               
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select * from fb_example as of csn 65536000004;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of csn 65536000003;
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select * from fb_example as of csn 65536000002;
 id | name  
----+-------
  1 | name1
  2 | name1
(2 rows)

kingbase=#
复制

-- 下面执行相关的清理

kingbase=# vacuum full;
VACUUM
kingbase=# table fb_example;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of csn 65536000003;
ERROR:  flashback query "fb_example" failed: can not flashback to csn before table definition changed.
kingbase=# 
kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';
ERROR:  flashback query "fb_example" failed: can not flashback to timestamp before table definition changed.
kingbase=#
复制

闪回版本查询

闪回版本查询的作用主要有以下几个方面：

进行历史数据的分析
查看数据的变更历史
确定闪回表的精确时间点

闪回版本查询拥有和Oracle 相同的参考伪列，伪列和功能参考下表：

伪列名	描述
VERSIONS_STARTTIME	元组被创建的时间戳
VERSIONS_ENDTIIME	元组被删除的时间戳
VERSIONS_STARTCSN	元组被创建的commit seqno
VERSIONS_ENDCSN	元组被删除的commit seqno
VERSIONS_OPERATION	对应的操作 D表示该元组被删除，I表示该元组被插入
VEERSIONS_XID	元组被创建的事务ID

闪回版本查询的start和end的快照表达式既可以指定一个有效的快照表达式，也可以通过minvalue和maxvalue两个关键字进行查询。当指定时，将返回该表能够闪回查询的所有的版本信息。

-- 通过minvalue 和maxvalue 进行闪回版本查询（CSN） 语句

kingbase=# create table fb_example(id int, name varchar(100));
CREATE TABLE
kingbase=# insert into fb_example values(1, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(2, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(3, 'name1');
INSERT 0 1
kingbase=# select now();
              now              
-------------------------------
 2023-06-24 22:23:24.509776-07
(1 row)

kingbase=# update fb_example set name = 'name2';
UPDATE 3
kingbase=# table fb_example;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |                 | I                  |  1 | name2
       65536000117 |                 | I                  |  2 | name2
       65536000117 |                 | I                  |  3 | name2
(6 rows)

kingbase=# delete from fb_example ;
DELETE 3
kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |     65536000118 | D                  |  1 | name2
       65536000117 |     65536000118 | D                  |  2 | name2
       65536000117 |     65536000118 | D                  |  3 | name2
(6 rows)

kingbase=# table fb_example;                                                                                                           
 id | name 
----+------
(0 rows)

kingbase=#
复制

通过上面的例子，我们可以追溯fb_example的版本变更的历史，65536000117这个CSN的事务做了一次update的操作，65536000118这个CSN的事务做了一次delete的操作。因此如果我们想查询被删除前的数据可以尝试65536000117这个CSN快照，如下：

kingbase=# table fb_example;                              
 id | name 
----+------
(0 rows)

kingbase=# select * from fb_example as of csn 65536000117;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=#
复制

闪回表到指定时间点

闪回表到指定的时间点，实际上为用户提供了还原数据的一种手段，如果说闪回查询能够帮助用户查询历史的数据，那么闪回表实际上是帮助用户彻底的找回数据，具体语法参考 FLASHBACK。

闪回表的通常步骤：

通过闪回版本查询确定待闪回的版本范围，查询startxxx，endxxx和operation确定边界
通过AS OF 闪回查询结合确定的边界锁定闪回查询的精确时间点，并观察数据时候符合预期
通过FLASHBACK TABLE TO [ Timestamp | CSN ] asof_item，完成表的闪回

如上的的例子，比如我们推理出错误delete的事务是65536000118这个CSN；那么我们可以指定65536000117进行闪回查询；再通过flashback闪回到误更前的时刻，如下：

kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |     65536000118 | D                  |  1 | name2
       65536000117 |     65536000118 | D                  |  2 | name2
       65536000117 |     65536000118 | D                  |  3 | name2
(6 rows)

kingbase=# table fb_example;                                                                                                           
 id | name 
----+------
(0 rows)

kingbase=# select * from fb_example as of csn 65536000117;                                                                             
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# flashback table fb_example to csn 65536000117;
FLASHBACK TABLE
kingbase=# 
kingbase=# table fb_example;                              
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=#
复制

闪回查询和闪回表技术的使用须知：

闪回查询和闪回表技术依赖于历史的数据，如果历史数据因为vacuum、truncate、rewrite 等操作被回收掉，那么会导致无法闪回到这些操作之前的时刻。因此推荐用户在期望使用闪回查询的时候对vacuum相关参数做一定的调整（关闭表级的autovacuum，推荐调大vacuum_defer_cleanup_age的值以降低历史数据被回收的机会）
目前闪回查询和闪回表技术在vacuum、truncate、和部分ddl 之后将不允许进行闪回到这些操作之前
闪回查询应用于视图或者物化视图里面应该尽量避免对于常量时间戳和CSN 的使用，可能会引发dump 和 restore的失败

闪回回收站

闪回回收站功能为用户提供一种误删表后还原表的一种手段，闪回回收站分为将回收站中的表闪回到删除之前的状态和清空回收站。

[kingbase@bogon bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# show kdb_flashback.db_recyclebin;
 kdb_flashback.db_recyclebin 
-----------------------------
 on
(1 row)

kingbase=# 
复制

kingbase=# table recyclebin;
 oid | original_name | droptime | type 
-----+---------------+----------+------
(0 rows)

kingbase=# \d+ recyclebin
                                    View "pg_catalog.recyclebin"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Description 
---------------+--------------------------+-----------+----------+---------+----------+-------------
 oid           | oid                      |           |          |         | plain    | 
 original_name | name                     |           |          |         | plain    | 
 droptime      | timestamp with time zone |           |          |         | plain    | 
 type          | text                     |           |          |         | extended | 
View definition:
 SELECT rel.oid,
    sys.original_name,
    sys.droptime,
    'TABLE'::text AS type
   FROM sys_recyclebin sys
     LEFT JOIN pg_class rel ON rel.oid = sys.reloid
  WHERE rel.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
UNION ALL
 SELECT sys.reloid AS oid,
    sys.original_name,
    sys.droptime,
    'INDEX'::text AS type
   FROM sys_recyclebin sys
     LEFT JOIN (pg_class rel
     JOIN pg_index idx ON rel.oid = idx.indexrelid) ON rel.oid = sys.reloid
  WHERE sys.type = ANY (ARRAY['i'::"char", 'I'::"char", 'g'::"char"])
UNION ALL
 SELECT con.oid,
    sys.original_name,
    sys.droptime,
    'CONSTRAINT'::text AS type
   FROM sys_recyclebin sys
     JOIN pg_constraint con ON con.conindid = sys.reloid
  WHERE sys.type = ANY (ARRAY['i'::"char", 'I'::"char", 'g'::"char"])
UNION ALL
 SELECT con.oid,
    con.conname AS original_name,
    sys.droptime,
    'CONSTRAINT'::text AS type
   FROM sys_recyclebin sys
     JOIN pg_constraint con ON con.conrelid = sys.reloid
  WHERE con.conindid = 0::oid
UNION ALL
 SELECT tg.oid,
    tg.tgname AS original_name,
    sys.droptime,
    'TRIGGER'::text AS type
   FROM pg_trigger tg,
    sys_recyclebin sys
  WHERE tg.tgrelid = sys.reloid
UNION ALL
 SELECT r.oid,
    r.rulename AS original_name,
    sys.droptime,
    'RULE'::text AS type
   FROM pg_rewrite r,
    sys_recyclebin sys
  WHERE (sys.type = ANY (ARRAY['r'::"char", 'p'::"char"])) AND r.oid = sys.reloid
UNION ALL
 SELECT sys.reloid AS oid,
    sys.original_name,
    sys.droptime,
    'SEQUENCE'::text AS type
   FROM sys_recyclebin sys
  WHERE sys.type = 'S'::"char";

kingbase=#
复制

kingbase=# \d+ sys_recyclebin 
                                        Table "pg_catalog.sys_recyclebin"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 classid       | oid                      |           | not null |         | plain   |              | 
 reloid        | oid                      |           | not null |         | plain   |              | 
 nspname       | name                     |           | not null |         | plain   |              | 
 object_name   | name                     |           | not null |         | plain   |              | 
 original_name | name                     |           | not null |         | plain   |              | 
 type          | "char"                   |           | not null |         | plain   |              | 
 droptime      | timestamp with time zone |           | not null |         | plain   |              | 
Indexes:
    "sys_recyclebin_reloid_index" UNIQUE, btree (reloid)
Access method: heap

kingbase=# table sys_recyclebin ;
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=#
复制

下面开始误操作，删除表：

kingbase=# \d      
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(3 rows)

kingbase=# table fb_example;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# drop table fb_example ;
DROP TABLE
kingbase=# \d
                 List of relations
 Schema |          Name           | Type |  Owner   
--------+-------------------------+------+----------
 public | sys_stat_statements     | view | kingbase
 public | sys_stat_statements_all | view | kingbase
(2 rows)

kingbase=#
复制

下面查看回收站的状态，如下：

kingbase=# table sys_recyclebin ;
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16983 | public  | bin$$16983$$2023-06-24 22:45:21.131386-07 | fb_example    | r    | 2023-06-24 22:45:21.131386-07
(1 row)

kingbase=# table recyclebin;     
  oid  | original_name |           droptime            | type  
-------+---------------+-------------------------------+-------
 16983 | fb_example    | 2023-06-24 22:45:21.131386-07 | TABLE
(1 row)

kingbase=#
复制

下面开始闪回表，如下：

kingbase=# \d                                                
                 List of relations
 Schema |          Name           | Type |  Owner   
--------+-------------------------+------+----------
 public | sys_stat_statements     | view | kingbase
 public | sys_stat_statements_all | view | kingbase
(2 rows)

kingbase=# flashback table fb_example to before drop;        
FLASHBACK TABLE
kingbase=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(3 rows)

kingbase=# table fb_example;     
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# table sys_recyclebin ;
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=# table recyclebin;     
 oid | original_name | droptime | type 
-----+---------------+----------+------
(0 rows)

kingbase=#
复制

回收站需用户定期维护，避免回收站膨胀，用户可以通过PURGE操作对回收站进行维护，PURGE操作分为删除回收站中一个指定表和清空回收站。清理回收站时，回收站视图recyclebin和系统表sys_recyclebin中相关对象将被清除。如下：

  
    
      










    
  
  
    
      数据库的下载安装
[root@localhost ~]# cat /etc/os-release 
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

[root@localhost ~]# uname -a
Linux localhost.localdomain 4.18.0-80.el8.x86_64 #1 SMP Tue Jun 4 09:19:46 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@localhost ~]#

[root@localhost ~]# useradd kingbase
[root@localhost ~]# 
[root@localhost ~]# passwd kingbase 
Changing password for user kingbase.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@localhost ~]# 
[root@localhost ~]# vim /etc/sudoers
[root@localhost ~]#


软件程序下载，点击前往

[kingbase@localhost ~]$ ll
total 4137600
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Desktop
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Documents
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Downloads
-rw-rw-r-- 1 kingbase kingbase 2668978176 Jun 21 09:50 KingbaseES_V008R006C007B0024_Lin64_install.iso
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Music
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Pictures
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Public
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Templates
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Videos
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ mkdir softwareLoc
[kingbase@localhost ~]$ 

[kingbase@localhost ~]$ su
Password: 
[root@localhost kingbase]# cd
[root@localhost ~]# 
[root@localhost ~]# mkdir -p /mnt/cdrom
[root@localhost ~]# 
[root@localhost ~]# mount /home/kingbase/KingbaseES_V008R006C007B0024_Lin64_install.iso /mnt/cdrom
mount: /mnt/cdrom: WARNING: device write-protected, mounted read-only.
[root@localhost ~]# 
[root@localhost ~]# ll /mnt/cdrom/
total 6
dr-xr-xr-x 2 root root 2048 Mar 23 03:50 setup
-r-xr-xr-x 1 root root 3829 Mar 23 03:50 setup.sh
[root@localhost ~]# ll /mnt/cdrom/setup
total 2606065
-r-xr-xr-x 1 root root 2668607786 Mar 23 03:50 install.bin
-r-xr-xr-x 1 root root         46 Mar 23 03:50 MD5
-r--r--r-- 1 root root       1418 Mar 23 03:50 silent.cfg
[root@localhost ~]# 
[root@localhost ~]# cp -r /mnt/cdrom/* /home/kingbase/softwareLoc
[root@localhost ~]# exit
exit
[kingbase@localhost ~]$ ll softwareLoc/
total 4
dr-xr-xr-x 2 root root   54 Jun 21 09:57 setup
-r-xr-xr-x 1 root root 3829 Jun 21 09:57 setup.sh
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo chown kingbase:kingbase softwareLoc/*

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for kingbase: 
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo chown kingbase:kingbase softwareLoc/setup/*
[kingbase@localhost ~]$
[kingbase@localhost ~]$ ll softwareLoc/
total 4
dr-xr-xr-x 2 kingbase kingbase   54 Jun 21 09:57 setup
-r-xr-xr-x 1 kingbase kingbase 3829 Jun 21 09:57 setup.sh
[kingbase@localhost ~]$ ll softwareLoc/setup
total 2606072
-r-xr-xr-x 1 kingbase kingbase 2668607786 Jun 21 09:57 install.bin
-r-xr-xr-x 1 kingbase kingbase         46 Jun 21 09:57 MD5
-r--r--r-- 1 kingbase kingbase       1418 Jun 21 09:57 silent.cfg
[kingbase@localhost ~]$ 


先行下载一个授权文件，如下：
[kingbase@localhost ~]$ ll
total 2606428
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Desktop
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Documents
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Downloads
-rw-rw-r-- 1 kingbase kingbase 2668978176 Jun 21 09:50 KingbaseES_V008R006C007B0024_Lin64_install.iso
-rwxrw-rw- 1 kingbase kingbase       2735 Jun 21 10:07 license_企业版.zip
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Music
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Pictures
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Public
drwxrwxr-x 3 kingbase kingbase         35 Jun 21 09:57 softwareLoc
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Templates
drwxr-xr-x 2 kingbase kingbase          6 Jun 21 09:48 Videos
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ ll license_企业版.zip 
-rwxrw-rw- 1 kingbase kingbase 2735 Jun 21 10:07 license_企业版.zip
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ unzip license_企业版.zip 
Archive:  license_企业版.zip
   creating: license_29296/
  inflating: license_29296/license_29296_0.dat  
[kingbase@localhost ~]$
[kingbase@localhost ~]$ cd softwareLoc/setup/
[kingbase@localhost setup]$ chmod 755 ./* 
[kingbase@localhost setup]$ ll
total 2606072
-rwxr-xr-x 1 kingbase kingbase 2668607786 Jun 21 09:57 install.bin
-rwxr-xr-x 1 kingbase kingbase         46 Jun 21 09:57 MD5
-rwxr-xr-x 1 kingbase kingbase       1418 Jun 21 09:57 silent.cfg
[kingbase@localhost setup]$ 


先行安装中文（zh_CN）语言包，如下：
[kingbase@localhost ~]$ locale
LANG=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=
[kingbase@localhost ~]$ locale -a |grep CN
[kingbase@localhost ~]$ 
[kingbase@localhost ~]$ sudo yum install -y langpacks-zh_CN
...

安装开始，如下：

选择license，如下：

安装完成，如下：
[kingbase@localhost release]$ pwd
/home/kingbase/release
[kingbase@localhost release]$ 
[kingbase@localhost release]$ ll
total 4
lrwxrwxrwx  1 kingbase kingbase   64 Jun 21 10:19 ClientTools -> /home/kingbase/release/KESRealPro/V008R006C007B0024/ClientTools/
drwx------ 23 kingbase kingbase 4096 Jun 21 10:30 data
lrwxrwxrwx  1 kingbase kingbase   56 Jun 21 10:19 doc -> /home/kingbase/release/KESRealPro/V008R006C007B0024/doc/
lrwxrwxrwx  1 kingbase kingbase   60 Jun 21 10:19 install -> /home/kingbase/release/KESRealPro/V008R006C007B0024/install/
lrwxrwxrwx  1 kingbase kingbase   62 Jun 21 10:19 Interface -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Interface/
drwxrwxr-x  3 kingbase kingbase   31 Jun 21 10:19 KESRealPro
lrwxrwxrwx  1 kingbase kingbase   63 Jun 21 10:19 KingbaseHA -> /home/kingbase/release/KESRealPro/V008R006C007B0024/KingbaseHA/
lrwxrwxrwx  1 kingbase kingbase   63 Jun 21 10:19 license.dat -> /home/kingbase/release/KESRealPro/V008R006C007B0024/license.dat
lrwxrwxrwx  1 kingbase kingbase   59 Jun 21 10:19 Server -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Server/
lrwxrwxrwx  1 kingbase kingbase   61 Jun 21 10:19 SupTools -> /home/kingbase/release/KESRealPro/V008R006C007B0024/SupTools/
lrwxrwxrwx  1 kingbase kingbase   62 Jun 21 10:19 Uninstall -> /home/kingbase/release/KESRealPro/V008R006C007B0024/Uninstall/
[kingbase@localhost release]$


我这里删除掉安装时初始化的集群，重新初始化如下：

[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# select version();
                                                       version                                                        
----------------------------------------------------------------------------------------------------------------------
 KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

kingbase=# select commercial_version();
                                           commercial_version                                            
---------------------------------------------------------------------------------------------------------
 KingbaseES V8.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

kingbase=#


kdb_flashback插件
安装插件
插件kdb_flashback是KingbaseES的一个扩展插件。主要功能是提供错误数据的快速恢复能力，目前提供的闪回技术包括闪回回收站、闪回查询、闪回版本查询和闪回到任意时间点。

插件名为 kdb_flashback
插件版本 V1.0

将其添加到 kingbase.conf 文件的 shared_preload_libraries 中，重启数据库时自动加载，如下：
[kingbase@localhost bin]$ vim test/kingbase.conf 

shared_preload_libraries = 'liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr,sys_squeeze, src_restrict,kdb_flashback'

[kingbase@localhost bin]$ ./sys_ctl start -D test/
waiting for server to start....2023-06-21 11:03:04.044 PDT [12936] LOG:  sepapower extension initialized
2023-06-21 11:03:04.051 PDT [12936] LOG:  starting KingbaseES V008R006C007B0024 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
2023-06-21 11:03:04.052 PDT [12936] LOG:  listening on IPv4 address "0.0.0.0", port 54321
2023-06-21 11:03:04.052 PDT [12936] LOG:  listening on IPv6 address "::", port 54321
2023-06-21 11:03:04.054 PDT [12936] LOG:  listening on Unix socket "/tmp/.s.KINGBASE.54321"
2023-06-21 11:03:04.128 PDT [12936] LOG:  redirecting log output to logging collector process
2023-06-21 11:03:04.128 PDT [12936] HINT:  Future log output will appear in directory "sys_log".
 done
server started
[kingbase@localhost bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# create extension kdb_flashback;
CREATE EXTENSION
kingbase=# \dx kdb_flashback 
                  List of installed extensions
     Name      | Version |   Schema   |       Description       
---------------+---------+------------+-------------------------
 kdb_flashback | 1.0     | pg_catalog | kdb_flashback extension
(1 row)

kingbase=#

插件kdb_flashback的参数配置，如下：
-- 闪回查询的开启参数，默认开启
kingbase=# show kdb_flashback.enable_flashback_query;
 kdb_flashback.enable_flashback_query 
--------------------------------------
 on
(1 row)

-- 闪回回收站开启参数，参数为bool类型，参数级别为PGC_SIGHUP，默认关闭
kingbase=# show kdb_flashback.db_recyclebin;
 kdb_flashback.db_recyclebin 
-----------------------------
 off
(1 row)

kingbase=#

插件使用
插件kdb_flashback的使用方法，闪回查询相关的技术使用方法：

整个闪回技术(闪回查询和闪回表到指定时间点) 可以通过时间戳和CSN(commit sequence number) 两种方式进行
两种方式目前都需要依赖提交的时间戳，因此需要开启track_commit_timestamp 这个参数。另外闪回查询还需要开启kdb_flashback.enable_flashback_query 的参数
这里解释下时间戳和CSN两个快照指定的方式，时间戳即timestamp：用户可以在闪回查询的快照时间表达式里指定任意一个有效的时间戳


如果允许闪回查询，那么将返回该时刻能够可见的历史快照的数据。CSN 是提交顺序号，第一个有效的CSN为65536000000，CSN 
随着提交线性增加。因此如果用户指定一个有效的CSN，比如65536000160，那么闪回查询将基于这个CSN 提交号构建历史快照


闪回查询技术有一定的限制，在数据进行深度清理后和部分schema change后将拒绝闪回查询（具体参考闪回查询须知）


下面具体介绍这几种闪回技术，在使用任何闪回查询相关的技术前，保证在配置文件中开启track_commit_timestamp参数，并重启服务。
闪回查询
闪回查询能返回用户指定历史时刻的快照数据，其中历史快照时刻指定方式可以通过timestamp和csn 两种方式指定(具体语法参考 SELECT )。
[kingbase@bogon bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# show track_commit_timestamp ;
 track_commit_timestamp 
------------------------
 on
(1 row)

kingbase=# show kdb_flashback.enable_flashback_query;
 kdb_flashback.enable_flashback_query 
--------------------------------------
 on
(1 row)

kingbase=#

下面来看一下闪回查询的使用示例，如下：
-- AS OF 闪回查询（timestamp） 语句

kingbase=# create table fb_example(id int, name varchar(100));
CREATE TABLE
kingbase=# insert into fb_example values(1, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(2, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(3, 'name1');
INSERT 0 1
kingbase=# table fb_example;
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select now();
              now              
-------------------------------
 2023-06-24 22:08:06.091204-07
(1 row)

kingbase=# update fb_example set name = null;
UPDATE 3
kingbase=# table fb_example;                 
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=#

如上，update 语句误将 name 更改为null，此时可以通过闪回查询查询到update前的数据。

通常CSN 方式进行闪回查询的时候，需要借助闪回版本查询确定合适的CSN 号，比如上面的误操作(update)， 那么通过版本查询可以确定该update 事务的CSN为65536000004，可以通过如下的闪回语句查询相关数据。
-- AS OF 闪回查询（CSN） 语句

kingbase=# 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 |     65536000004 |  2 | name1
       65536000003 |     65536000004 |  3 | name1
       65536000004 |                 |  1 | 
       65536000004 |                 |  2 | 
       65536000004 |                 |  3 | 
(6 rows)

kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';                               
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select * from fb_example as of csn 65536000004;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of csn 65536000003;
 id | name  
----+-------
  1 | name1
  2 | name1
  3 | name1
(3 rows)

kingbase=# select * from fb_example as of csn 65536000002;
 id | name  
----+-------
  1 | name1
  2 | name1
(2 rows)

kingbase=#


-- 下面执行相关的清理

kingbase=# vacuum full;
VACUUM
kingbase=# table fb_example;
 id | name 
----+------
  1 | 
  2 | 
  3 | 
(3 rows)

kingbase=# select * from fb_example as of csn 65536000003;
ERROR:  flashback query "fb_example" failed: can not flashback to csn before table definition changed.
kingbase=# 
kingbase=# select * from fb_example as of timestamp '2023-06-24 22:08:06.091204-07';
ERROR:  flashback query "fb_example" failed: can not flashback to timestamp before table definition changed.
kingbase=#


闪回版本查询
闪回版本查询的作用主要有以下几个方面：

进行历史数据的分析
查看数据的变更历史
确定闪回表的精确时间点

闪回版本查询拥有和Oracle 相同的参考伪列，伪列和功能参考下表：




伪列名
描述




VERSIONS_STARTTIME
元组被创建的时间戳


VERSIONS_ENDTIIME
元组被删除的时间戳


VERSIONS_STARTCSN
元组被创建的commit seqno


VERSIONS_ENDCSN
元组被删除的commit seqno


VERSIONS_OPERATION
对应的操作 D表示该元组被删除，I表示该元组被插入


VEERSIONS_XID
元组被创建的事务ID


闪回版本查询的start和end的快照表达式既可以指定一个有效的快照表达式，也可以通过minvalue和maxvalue两个关键字进行查询。当指定时，将返回该表能够闪回查询的所有的版本信息。
-- 通过minvalue 和maxvalue 进行闪回版本查询（CSN） 语句

kingbase=# create table fb_example(id int, name varchar(100));
CREATE TABLE
kingbase=# insert into fb_example values(1, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(2, 'name1');
INSERT 0 1
kingbase=# insert into fb_example values(3, 'name1');
INSERT 0 1
kingbase=# select now();
              now              
-------------------------------
 2023-06-24 22:23:24.509776-07
(1 row)

kingbase=# update fb_example set name = 'name2';
UPDATE 3
kingbase=# table fb_example;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |                 | I                  |  1 | name2
       65536000117 |                 | I                  |  2 | name2
       65536000117 |                 | I                  |  3 | name2
(6 rows)

kingbase=# delete from fb_example ;
DELETE 3
kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |     65536000118 | D                  |  1 | name2
       65536000117 |     65536000118 | D                  |  2 | name2
       65536000117 |     65536000118 | D                  |  3 | name2
(6 rows)

kingbase=# table fb_example;                                                                                                           
 id | name 
----+------
(0 rows)

kingbase=#

通过上面的例子，我们可以追溯fb_example的版本变更的历史，65536000117这个CSN的事务做了一次update的操作，65536000118这个CSN的事务做了一次delete的操作。因此如果我们想查询被删除前的数据可以尝试65536000117这个CSN快照，如下：
kingbase=# table fb_example;                              
 id | name 
----+------
(0 rows)

kingbase=# select * from fb_example as of csn 65536000117;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=#


闪回表到指定时间点
闪回表到指定的时间点，实际上为用户提供了还原数据的一种手段，如果说闪回查询能够帮助用户查询历史的数据，那么闪回表实际上是帮助用户彻底的找回数据，具体语法参考 FLASHBACK。
闪回表的通常步骤：

通过闪回版本查询确定待闪回的版本范围，查询startxxx，endxxx和operation确定边界
通过AS OF 闪回查询结合确定的边界锁定闪回查询的精确时间点，并观察数据时候符合预期
通过FLASHBACK TABLE TO [ Timestamp | CSN ] asof_item，完成表的闪回

如上的的例子，比如我们推理出错误delete的事务是65536000118这个CSN；那么我们可以指定65536000117进行闪回查询；再通过flashback闪回到误更前的时刻，如下：
kingbase=# select versions_startcsn, versions_endcsn, versions_operation, * from fb_example versions between csn minvalue and maxvalue;
 versions_startcsn | versions_endcsn | versions_operation | id | name  
-------------------+-----------------+--------------------+----+-------
       65536000114 |     65536000117 | D                  |  1 | name1
       65536000115 |     65536000117 | D                  |  2 | name1
       65536000116 |     65536000117 | D                  |  3 | name1
       65536000117 |     65536000118 | D                  |  1 | name2
       65536000117 |     65536000118 | D                  |  2 | name2
       65536000117 |     65536000118 | D                  |  3 | name2
(6 rows)

kingbase=# table fb_example;                                                                                                           
 id | name 
----+------
(0 rows)

kingbase=# select * from fb_example as of csn 65536000117;                                                                             
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# flashback table fb_example to csn 65536000117;
FLASHBACK TABLE
kingbase=# 
kingbase=# table fb_example;                              
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=#

闪回查询和闪回表技术的使用须知：

闪回查询和闪回表技术依赖于历史的数据，如果历史数据因为vacuum、truncate、rewrite 
等操作被回收掉，那么会导致无法闪回到这些操作之前的时刻。因此推荐用户在期望使用闪回查询的时候对vacuum相关参数做一定的调整（关闭表级的autovacuum，推荐调大vacuum_defer_cleanup_age的值以降低历史数据被回收的机会）
目前闪回查询和闪回表技术在vacuum、truncate、和部分ddl 之后将不允许进行闪回到这些操作之前
闪回查询应用于视图或者物化视图里面应该尽量避免对于常量时间戳和CSN 的使用，可能会引发dump 和 restore的失败


闪回回收站
闪回回收站功能为用户提供一种误删表后还原表的一种手段，闪回回收站分为将回收站中的表闪回到删除之前的状态和清空回收站。
[kingbase@bogon bin]$ ./ksql -p 54321
ksql (V8.0)
Type "help" for help.

kingbase=# show kdb_flashback.db_recyclebin;
 kdb_flashback.db_recyclebin 
-----------------------------
 on
(1 row)

kingbase=# 


kingbase=# table recyclebin;
 oid | original_name | droptime | type 
-----+---------------+----------+------
(0 rows)

kingbase=# \d+ recyclebin
                                    View "pg_catalog.recyclebin"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Description 
---------------+--------------------------+-----------+----------+---------+----------+-------------
 oid           | oid                      |           |          |         | plain    | 
 original_name | name                     |           |          |         | plain    | 
 droptime      | timestamp with time zone |           |          |         | plain    | 
 type          | text                     |           |          |         | extended | 
View definition:
 SELECT rel.oid,
    sys.original_name,
    sys.droptime,
    'TABLE'::text AS type
   FROM sys_recyclebin sys
     LEFT JOIN pg_class rel ON rel.oid = sys.reloid
  WHERE rel.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
UNION ALL
 SELECT sys.reloid AS oid,
    sys.original_name,
    sys.droptime,
    'INDEX'::text AS type
   FROM sys_recyclebin sys
     LEFT JOIN (pg_class rel
     JOIN pg_index idx ON rel.oid = idx.indexrelid) ON rel.oid = sys.reloid
  WHERE sys.type = ANY (ARRAY['i'::"char", 'I'::"char", 'g'::"char"])
UNION ALL
 SELECT con.oid,
    sys.original_name,
    sys.droptime,
    'CONSTRAINT'::text AS type
   FROM sys_recyclebin sys
     JOIN pg_constraint con ON con.conindid = sys.reloid
  WHERE sys.type = ANY (ARRAY['i'::"char", 'I'::"char", 'g'::"char"])
UNION ALL
 SELECT con.oid,
    con.conname AS original_name,
    sys.droptime,
    'CONSTRAINT'::text AS type
   FROM sys_recyclebin sys
     JOIN pg_constraint con ON con.conrelid = sys.reloid
  WHERE con.conindid = 0::oid
UNION ALL
 SELECT tg.oid,
    tg.tgname AS original_name,
    sys.droptime,
    'TRIGGER'::text AS type
   FROM pg_trigger tg,
    sys_recyclebin sys
  WHERE tg.tgrelid = sys.reloid
UNION ALL
 SELECT r.oid,
    r.rulename AS original_name,
    sys.droptime,
    'RULE'::text AS type
   FROM pg_rewrite r,
    sys_recyclebin sys
  WHERE (sys.type = ANY (ARRAY['r'::"char", 'p'::"char"])) AND r.oid = sys.reloid
UNION ALL
 SELECT sys.reloid AS oid,
    sys.original_name,
    sys.droptime,
    'SEQUENCE'::text AS type
   FROM sys_recyclebin sys
  WHERE sys.type = 'S'::"char";

kingbase=#

kingbase=# \d+ sys_recyclebin 
                                        Table "pg_catalog.sys_recyclebin"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 classid       | oid                      |           | not null |         | plain   |              | 
 reloid        | oid                      |           | not null |         | plain   |              | 
 nspname       | name                     |           | not null |         | plain   |              | 
 object_name   | name                     |           | not null |         | plain   |              | 
 original_name | name                     |           | not null |         | plain   |              | 
 type          | "char"                   |           | not null |         | plain   |              | 
 droptime      | timestamp with time zone |           | not null |         | plain   |              | 
Indexes:
    "sys_recyclebin_reloid_index" UNIQUE, btree (reloid)
Access method: heap

kingbase=# table sys_recyclebin ;
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=#

下面开始误操作，删除表：
kingbase=# \d      
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(3 rows)

kingbase=# table fb_example;
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# drop table fb_example ;
DROP TABLE
kingbase=# \d
                 List of relations
 Schema |          Name           | Type |  Owner   
--------+-------------------------+------+----------
 public | sys_stat_statements     | view | kingbase
 public | sys_stat_statements_all | view | kingbase
(2 rows)

kingbase=#

下面查看回收站的状态，如下：
kingbase=# table sys_recyclebin ;
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16983 | public  | bin$$16983$$2023-06-24 22:45:21.131386-07 | fb_example    | r    | 2023-06-24 22:45:21.131386-07
(1 row)

kingbase=# table recyclebin;     
  oid  | original_name |           droptime            | type  
-------+---------------+-------------------------------+-------
 16983 | fb_example    | 2023-06-24 22:45:21.131386-07 | TABLE
(1 row)

kingbase=#


下面开始闪回表，如下：
kingbase=# \d                                                
                 List of relations
 Schema |          Name           | Type |  Owner   
--------+-------------------------+------+----------
 public | sys_stat_statements     | view | kingbase
 public | sys_stat_statements_all | view | kingbase
(2 rows)

kingbase=# flashback table fb_example to before drop;        
FLASHBACK TABLE
kingbase=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(3 rows)

kingbase=# table fb_example;     
 id | name  
----+-------
  1 | name2
  2 | name2
  3 | name2
(3 rows)

kingbase=# table sys_recyclebin ;
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=# table recyclebin;     
 oid | original_name | droptime | type 
-----+---------------+----------+------
(0 rows)

kingbase=#


回收站需用户定期维护，避免回收站膨胀，用户可以通过PURGE操作对回收站进行维护，PURGE操作分为删除回收站中一个指定表和清空回收站。清理回收站时，回收站视图recyclebin和系统表sys_recyclebin中相关对象将被清除。如下：
kingbase=# create table fb_example1 as select * from fb_example;
SELECT 3
kingbase=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | fb_example1             | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(4 rows)

kingbase=# drop table fb_example ;
DROP TABLE
kingbase=# drop table fb_example1 ;
DROP TABLE
kingbase=# table sys_recyclebin ;
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16983 | public  | bin$$16983$$2023-06-24 22:49:47.860997-07 | fb_example    | r    | 2023-06-24 22:49:47.860997-07
    1259 |  16986 | public  | bin$$16986$$2023-06-24 22:49:49.657475-07 | fb_example1   | r    | 2023-06-24 22:49:49.657475-07
(2 rows)

kingbase=# purge table fb_example;
PURGE
kingbase=# table sys_recyclebin ;  
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16986 | public  | bin$$16986$$2023-06-24 22:49:49.657475-07 | fb_example1   | r    | 2023-06-24 22:49:49.657475-07
(1 row)

kingbase=# flashback table fb_example to before drop;           
ERROR:  table "fb_example" doesn't in recyclebin.
kingbase=# 
kingbase=# table recyclebin;                         
  oid  | original_name |           droptime            | type  
-------+---------------+-------------------------------+-------
 16986 | fb_example1   | 2023-06-24 22:49:49.657475-07 | TABLE
(1 row)

kingbase=# purge recyclebin;
PURGE
kingbase=# table sys_recyclebin ;                    
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=#


    
  





kingbase=# create table fb_example1 as select * from fb_example;
SELECT 3
kingbase=# \d
                  List of relations
 Schema |          Name           | Type  |  Owner   
--------+-------------------------+-------+----------
 public | fb_example              | table | kingbase
 public | fb_example1             | table | kingbase
 public | sys_stat_statements     | view  | kingbase
 public | sys_stat_statements_all | view  | kingbase
(4 rows)

kingbase=# drop table fb_example ;
DROP TABLE
kingbase=# drop table fb_example1 ;
DROP TABLE
kingbase=# table sys_recyclebin ;
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16983 | public  | bin$$16983$$2023-06-24 22:49:47.860997-07 | fb_example    | r    | 2023-06-24 22:49:47.860997-07
    1259 |  16986 | public  | bin$$16986$$2023-06-24 22:49:49.657475-07 | fb_example1   | r    | 2023-06-24 22:49:49.657475-07
(2 rows)

kingbase=# purge table fb_example;
PURGE
kingbase=# table sys_recyclebin ;  
 classid | reloid | nspname |                object_name                | original_name | type |           droptime            
---------+--------+---------+-------------------------------------------+---------------+------+-------------------------------
    1259 |  16986 | public  | bin$$16986$$2023-06-24 22:49:49.657475-07 | fb_example1   | r    | 2023-06-24 22:49:49.657475-07
(1 row)

kingbase=# flashback table fb_example to before drop;           
ERROR:  table "fb_example" doesn't in recyclebin.
kingbase=# 
kingbase=# table recyclebin;                         
  oid  | original_name |           droptime            | type  
-------+---------------+-------------------------------+-------
 16986 | fb_example1   | 2023-06-24 22:49:49.657475-07 | TABLE
(1 row)

kingbase=# purge recyclebin;
PURGE
kingbase=# table sys_recyclebin ;                    
 classid | reloid | nspname | object_name | original_name | type | droptime 
---------+--------+---------+-------------+---------------+------+----------
(0 rows)

kingbase=#
复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

人大金仓 Kingbase数据库闪回功能及插件kdb_flashback的使用

数据库的下载安装

kdb_flashback插件

安装插件

插件使用

闪回查询

闪回版本查询

闪回表到指定时间点

闪回回收站

数据库的下载安装

kdb_flashback插件

安装插件

插件使用

闪回查询

闪回版本查询

闪回表到指定时间点

闪回回收站

评论

相关阅读