数据库的下载安装
[root@localhost ~]# cat /etc/os-release
NAME="CentOS Linux"
VERSION="8 (Core)"
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 ~]lltotal4137600drwxr−xr−x2kingbasekingbase6Jun2109:48Desktopdrwxr−xr−x2kingbasekingbase6Jun2109:48Documentsdrwxr−xr−x2kingbasekingbase6Jun2109:48Downloads−rw−rw−r−−1kingbasekingbase2668978176Jun2109:50KingbaseESV008R006C007B0024Lin64install.isodrwxr−xr−x2kingbasekingbase6Jun2109:48Musicdrwxr−xr−x2kingbasekingbase6Jun2109:48Picturesdrwxr−xr−x2kingbasekingbase6Jun2109:48Publicdrwxr−xr−x2kingbasekingbase6Jun2109:48Templatesdrwxr−xr−x2kingbasekingbase6Jun2109:48Videos[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 ~]lltotal4137600drwxr−xr−x2kingbasekingbase6Jun2109:48Desktopdrwxr−xr−x2kingbasekingbase6Jun2109:48Documentsdrwxr−xr−x2kingbasekingbase6Jun2109:48Downloads−rw−rw−r−−1kingbasekingbase2668978176Jun2109:50KingbaseESV008R006C007B0024Lin64install.isodrwxr−xr−x2kingbasekingbase6Jun2109:48Musicdrwxr−xr−x2kingbasekingbase6Jun2109:48Picturesdrwxr−xr−x2kingbasekingbase6Jun2109:48Publicdrwxr−xr−x2kingbasekingbase6Jun2109:48Templatesdrwxr−xr−x2kingbasekingbase6Jun2109:48Videos[kingbase@localhost ]
[kingbase@localhost ~]mkdirsoftwareLoc[kingbase@localhost ] mkdir softwareLoc
[kingbase@localhost ~]mkdirsoftwareLoc[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=#