首页 » PostgreSQL/GaussDB » 体验一下GaussDB集中式(本地部署)的Flashback/TIMECAPSULE 闪回功能
体验一下GaussDB集中式(本地部署)的Flashback/TIMECAPSULE 闪回功能
Oracle 提供了丰富的闪回功能，帮助用户在数据丢失或错误操作后快速恢复数据。最近，我们注意到 GaussDB 也支持部分闪回功能。在这里，我将记录一下在 GaussDB 中使用 drop、truncate 的闪回功能，以及闪回表和闪回查询的体验。或错误操作后快速恢复数据。
创建表
gaussdb=# create database anbob;
CREATE DATABASE
gaussdb=# \c anbob
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "anbob" as user "gauss".
anbob=# create table test(id int,name varchar2(4000));
CREATE TABLE
anbob=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Storage | Description
--------+------+-------+-------+---------+------------------------------------------------------------------+-------------
public | test | table | gauss | 0 bytes | {orientation=row,compression=no,storage_type=USTORE,segment=off} |
(1 row)
anbob=# select version();
version
-------------------------------------------------------------------------------------------------------------------
gaussdb (GaussDB Kernel 505.2.0 build 82d715e8) compiled at 2024-09-20 00:15:22 commit 9967 last mr 19883 release
(1 row)
anbob=# CREATE TABLE astore (a INT PRIMARY KEY, b CHAR (20)) WITH (STORAGE_TYPE=astore);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "astore_pkey" for table "astore"
CREATE TABLE
anbob=# \dt
List of relations
Schema | Name | Type | Owner | Storage
--------+--------+-------+-------+------------------------------------------------------------------
public | astore | table | gauss | {orientation=row,storage_type=astore,compression=no}
public | test | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off}
(2 rows)
Note:
默认GAUSSDB已使用USTORE, 不同于opengauss或postgresql的Astore, 当然目前也支持使用astore的创建。
1. 闪回查询
闪回查询功能允许用户查询某个时间点之前的数据状态。
anbob=# create table t(id int, name varchar2(4000));
CREATE TABLE
anbob=# insert into t select rownum,'anbob'||lpad('x',level,'x') from dual connect by rownum<=3000;
ERROR: Current Start With...Connect by has exceeded max iteration times 200
DETAIL: The condition in CONNECT BY clause has caused a deep recursion,
but its iteration time still should be less than max_recursive_times
HINT: Consider incrementing max_recursive_times or using NOCYCLE to finish CONNECT BY operation.
anbob=# show max_recursive_times;
max_recursive_times
---------------------
200
(1 row)
anbob=# set max_recursive_times=4000;
SET
anbob=# insert into t select rownum,'anbob'||lpad('x',level,'x') from dual connect by rownum<=3000;
INSERT 0 3000
anbob=# select count(*) from t;
count
-------
3000
anbob=# select * from test;
id | name
----+------
(0 rows)
anbob=# insert into test values(1,'anbob');
INSERT 0 1
anbob=# select * from test;
id | name
----+-------
1 | anbob
(1 row)
anbob=# select int8in(xidout(next_csn)) from gs_get_next_xid_csn();
int8in
--------
62237
(1 row)
anbob=# select sysdate from dual;
sysdate
---------------------
2024-10-25 20:22:10
(1 row)
anbob=# update test set id=2 where id=1;
UPDATE 1
anbob=# select sysdate from dual;
sysdate
---------------------
2024-10-25 20:22:30
(1 row)
anbob=# select * from test;
id | name
----+-------
2 | anbob
(1 row)
anbob=# SELECT * FROM test TIMECAPSULE CSN 62237;
ERROR: timecapsule feature is disabled.
anbob=# show undo_retention_time;
undo_retention_time
---------------------
0
(1 row)
Note:
闪回查询功能依赖参数undo_retention_time，类似UNDO 保留多版本。
启动闪回查询
-- 修改数据库配置文件
[gauss@anbob_com data]$ vi gaussdb.conf
-- append for flashback query, unit is second。
undo_retention_time=600
-- 重启数据库实例生效
[gauss@anbob_com data]$ gs_ctl stop
[gauss@anbob_com data]$ gs_ctl start
anbob=# show undo_retention_time;
undo_retention_time
---------------------
10min
(1 row)
anbob=# select * from test;
id | name
----+-------
1 | anbob
(1 row)
anbob=# select sysdate from dual;
sysdate
---------------------
2024-10-25 20:27:47
(1 row)
anbob=# select int8in(xidout(next_csn)) from gs_get_next_xid_csn();
int8in
--------
62420
(1 row)
anbob=# update test set id=2 where id=1;
UPDATE 1
anbob=# select * from test;
id | name
----+-------
2 | anbob
(1 row)
anbob=# select int8in(xidout(next_csn)) from gs_get_next_xid_csn(); -- like SCN
int8in
--------
62437
(1 row)
anbob=# select sysdate from dual;
sysdate
---------------------
2024-10-25 20:28:23
(1 row)
anbob=# select * from test TIMECAPSULE CSN 62420;
id | name
----+-------
1 | anbob
(1 row)
anbob=# select * from test TIMECAPSULE timestamp '2024-10-25 20:27:47';
id | name
----+-------
1 | anbob
(1 row)
anbob=# select * from test;
id | name
----+-------
2 | anbob
(1 row)
anbob=# select * from test TIMECAPSULE timestamp '2024-10-25 20:10:47';
ERROR: cannot find the restore point, timecapsule time is too old, please check and use correct time
Note:
可以基于CSN或时间点的闪回查询历史时间点，关键字是TIMECAPSULE.如果超过了undo retention时间提示类似ora-1555，too old报错。
2. 闪回表
闪回表功能允许用户恢复到某个时间点之前的表状态。这对于误操作或数据损坏的情况非常有用。
anbob=# TIMECAPSULE TABLE test TO CSN 62420; TimeCapsule Table anbob=# select * from test; id | name ----+------- 1 | anbob (1 row) anbob=# TIMECAPSULE TABLE test TO CSN 62437; TimeCapsule Table anbob=# select * from test; id | name ----+------- 2 | anbob (1 row) anbob=# TIMECAPSULE TABLE test TO CSN 62420; TimeCapsule Table anbob=# select * from test; id | name ----+------- 1 | anbob (1 row) anbob=# TIMECAPSULE TABLE test TO timestamp '2024-10-25 20:28:23'; TimeCapsule Table anbob=# select * from test; id | name ----+------- 2 | anbob (1 row)
Note:
使用TIMECAPSULE TABLE也可以来回的闪回表.
检查是否有undo tablespace
anbob=# select * from dict;
table_name | comments
-----------------------------------+----------
my_tablespaces |
dictionary |
my_recyclebin |
db_tables |
adm_tablespaces |
adm_data_files |
...
anbob=# select * from adm_data_files;
tablespace_name | bytes
-----------------+-----------
pg_default | 394928164
pg_global | 545194228
(2 rows)
anbob=# select tablespace_name from adm_tablespaces;
tablespace_name |
-----------------+
pg_default |
pg_global |
(2 rows)
3. 闪回 Drop 操作
在 GaussDB 中，如果误删了一个表，可以使用闪回功能来恢复该表及其数据。具体步骤如下：
打开回收站
anbob=# show enable_recyclebin;
enable_recyclebin
-------------------
off
(1 row)
anbob=# show recyclebin_retention_time;
recyclebin_retention_time
---------------------------
15min
(1 row)
[gauss@anbob_com data]$ vi gaussdb.conf
-- append for recyclebin
enable_recyclebin=on
[gauss@anbob_com data]$ gs_ctl stop
[gauss@anbob_com data]$ gs_ctl start
Note:
gaussdb 回收站依赖参数enable_recyclebin开关， 和recyclebin_retention_time保留时间。
测试回收站
anbob=# create table t1(id int); CREATE TABLE anbob=# insert into t1 values(1); INSERT 0 1 anbob=# select * from t1; id ---- 1 (1 row) anbob=# show enable_recyclebin; enable_recyclebin ------------------- on (1 row) anbob=# drop table t1; DROP TABLE anbob=# create table t2 as select * from t; INSERT 0 3000 anbob=# drop table t2; DROP TABLE anbob=# select * from gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyown er | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+-------------------------------+----------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+------- ---+---------------+----------------+---------------+-------------+--------------+----------------+----------- 18675 | 18654 | 18672 | BIN$48DE233848F0$340B39C0==$0 | t1 | d | 0 | 62984 | 2024-10-25 20:38:08.108734+08 | 62959 | 62959 | 2200 | 10 | 0 | 18672 | t | t | 121792 | 121792 | 18681 | 18654 | 18676 | BIN$48DE233848F4$342CA918==$0 | t2 | d | 0 | 63014 | 2024-10-25 20:38:48.212253+08 | 63007 | 63007 | 2200 | 10 | 0 | 18676 | t | t | 121840 | 121840 | 18681 | 18654 | 18679 | BIN$48DE233848F7$342CB500==$0 | pg_toast_18676 | d | 2 | 63014 | 2024-10-25 20:38:48.212517+08 | 0 | 0 | 99 | 10 | 0 | 18679 | f | f | 121840 | 121840 | (3 rows) anbob=# \dt List of relations Schema | Name | Type | Owner | Storage --------+-------------------------------+-------+-------+------------------------------------------------------------------ public | BIN$48DE233848F0$340B39C0==$0 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | BIN$48DE233848F4$342CA918==$0 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | astore | table | gauss | {orientation=row,storage_type=astore,compression=no} public | t | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} (4 rows) anbob=# timecapsule table t1 to before drop; TimeCapsule Table anbob=# timecapsule table t2 to before drop; TimeCapsule Table anbob=# select * from gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+- --------------+-------------+--------------+----------------+----------- (0 rows) anbob=# \dt List of relations Schema | Name | Type | Owner | Storage --------+--------+-------+-------+------------------------------------------------------------------ public | astore | table | gauss | {orientation=row,storage_type=astore,compression=no} public | t | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | t1 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | t2 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} (4 rows) anbob=# drop table t1; DROP TABLE anbob=# \dt List of relations Schema | Name | Type | Owner | Storage --------+-------------------------------+-------+-------+------------------------------------------------------------------ public | BIN$48DE233848F0$342E10E8==$0 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | astore | table | gauss | {orientation=row,storage_type=astore,compression=no} public | t | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} public | t2 | table | gauss | {orientation=row,compression=no,storage_type=USTORE,segment=off} (4 rows) anbob=# select * from "BIN$48DE233848F0$342E10E8==$0"; ERROR: can not access recycle object. anbob=# timecapsule table "BIN$48DE233848F0$342E10E8==$0" to before drop; TimeCapsule Table anbob=# select * from gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+---------+---------------+--------------+---------+---------------+----------------+--------------+--------------+--------------+----------+---------------+----------------+- --------------+-------------+--------------+----------------+----------- (0 rows) anbob=# select * from t1; id ---- 1
note:
闪回回收站表对象可以自动的带toast对象，可以使用对象名，或回收站改后的BIN$开头的名， 同一个表BIN$名并不会复用，并且不允许查询，drop 操作的表，使用\dt 依旧可以显示改名后BIN$开头的对象. 关键字同样是timecapsule 。有记录操作时间，和rcyoperation 值为D。
4. 闪回 Truncate 操作
如果误用了 TRUNCATE 命令清空了表中的数据，同样可以使用闪回功能恢复数据。
anbob=# select * from t1; id ---- 1 (1 row) anbob=# truncate table t1; TRUNCATE TABLE anbob=# select * from t1; id ---- (0 rows) anbob=# select * from gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowne r | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+-------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+-------- --+---------------+----------------+---------------+-------------+--------------+----------------+----------- 18684 | 18654 | 18672 | BIN$48DE233848F0$34309A00==$0 | t1 | t | 0 | 63204 | 2024-10-25 20:43:13.966921+08 | 62959 | 62959 | 2200 | 1 0 | 0 | 18672 | t | t | 121792 | 121792 | (1 row) anbob=# timecapsule table t1 to before truncate; TimeCapsule Table anbob=# select * from t1; id ---- 1 (1 row) anbob=# select * from gs_recyclebin; rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowner | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket -----------+---------+----------+------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+--------- -+---------------+----------------+---------------+-------------+--------------+----------------+----------- 18684 | 18654 | 18672 | BIN$48DE4EB48F0$3430F1D8==$0 | t1 | t | 0 | 63229 | 2024-10-25 20:43:48.561798+08 | 62959 | 62959 | 2200 | 10 | 0 | 18685 | t | t | 0 | 122037 | (1 row)
Note:
闪加truncate操作也是依赖于回收站，与drop不同的是truncate 对应的回收站记录rcyoperation 是”T[runcate]”，而drop是”D[rop]”; 同时truncate后因为原表结构还在，所以\dt 不会显示增加的bin$对应的前segment 数据名称。 并且在做truncate 闪回后，回收站中的记录gs_recyclebin依旧存在。
测试一下，闪回truncate 会不会覆盖truncate后的记录
anbob=# insert into t1 values(2);
INSERT 0 1
anbob=# select * from t1;
id
----
1
2
(2 rows)
anbob=# truncate table t1;
TRUNCATE TABLE
anbob=# select * from t1;
id
----
(0 rows)
anbob=# select * from gs_recyclebin;
rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowne
r | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket
-----------+---------+----------+-------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+--------
--+---------------+----------------+---------------+-------------+--------------+----------------+-----------
18684 | 18654 | 18672 | BIN$48DE4EB48F0$3430F1D8==$0 | t1 | t | 0 | 63229 | 2024-10-25 20:43:48.561798+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18685 | t | t | 0 | 122037 |
18686 | 18654 | 18672 | BIN$48DE233848F0$34315B98==$0 | t1 | t | 0 | 63268 | 2024-10-25 20:44:45.253044+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18672 | t | t | 121792 | 121792 |
(2 rows)
anbob=# timecapsule table "BIN$48DE4EB48F0$3430F1D8==$0" to before truncate;
TimeCapsule Table
anbob=# select * from gs_recyclebin;
rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowne
r | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket
-----------+---------+----------+-------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+--------
--+---------------+----------------+---------------+-------------+--------------+----------------+-----------
18686 | 18654 | 18672 | BIN$48DE233848F0$34315B98==$0 | t1 | t | 0 | 63268 | 2024-10-25 20:44:45.253044+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18672 | t | t | 121792 | 121792 |
18684 | 18654 | 18672 | BIN$48DE4EB48F0$3431AE20==$0 | t1 | t | 0 | 63290 | 2024-10-25 20:45:15.789343+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18687 | t | t | 0 | 122101 |
(2 rows)
anbob=# select * from t1;
id
----
(0 rows)
anbob=# timecapsule table "BIN$48DE4EB48F0$3431AE20==$0" to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
(0 rows)
anbob=# timecapsule table t1 to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
(0 rows)
anbob=# select * from t1 TIMECAPSULE timestamp '2024-10-25 20:43:05';
ERROR: Snapshot too old, ScanRelation, the info: snapxmax is 122031, snapxmin is 122031, csn is 63197, relfrozenxid64 is 122037, globalRecycleXid is 121802.
Note:
这个测试有点摸不到头脑,闪回truncate后回收站记录还在，似乎没有闪回truncate，关注 rcyrecyclecsn 列，因为中间使用了”BIN$”的名. 再来
anbob=# insert into t1 values(2);
INSERT 0 1
anbob=# select * from t1;
id
----
2
(1 row)
anbob=# truncate table t1;
TRUNCATE TABLE
anbob=# select sysdate from dual;
sysdate
---------------------
2024-10-25 20:48:39
(1 row)
anbob=# select * from gs_recyclebin;
rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowne
r | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket
-----------+---------+----------+-------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+--------
--+---------------+----------------+---------------+-------------+--------------+----------------+-----------
18686 | 18654 | 18672 | BIN$48DE233848F0$34315B98==$0 | t1 | t | 0 | 63268 | 2024-10-25 20:44:45.253044+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18672 | t | t | 121792 | 121792 |
18684 | 18654 | 18672 | BIN$48DE4EB48F0$343240E8==$0 | t1 | t | 0 | 63339 | 2024-10-25 20:46:23.563245+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18687 | t | t | 0 | 122101 |
18716 | 18654 | 18672 | BIN$48DE233848F0$343FBC40==$0 | t1 | t | 0 | 63444 | 2024-10-25 20:48:36.097789+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18685 | t | t | 0 | 122037 |
(3 rows)
anbob=# timecapsule table t1 to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
2
(1 row)
anbob=# timecapsule table t1 to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
(0 rows)
anbob=# timecapsule table t1 to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
2
(1 row)
anbob=# timecapsule table t1 to before truncate;
TimeCapsule Table
anbob=# select * from t1;
id
----
(0 rows)
Note:
闪回truncate可以来回的切换。闪回是会覆盖当前表里的记录，注意造成二次丢失。
测试表rename或DDL后会不会还能闪回
anbob=# select * from t1;
id
----
2
(1 row)
anbob=# truncate table t1;
TRUNCATE TABLE
anbob=# select * from t1;
id
----
(0 rows)
anbob=# select * from gs_recyclebin;
rcybaseid | rcydbid | rcyrelid | rcyname | rcyoriginname | rcyoperation | rcytype | rcyrecyclecsn | rcyrecycletime | rcycreatecsn | rcychangecsn | rcynamespace | rcyowne
r | rcytablespace | rcyrelfilenode | rcycanrestore | rcycanpurge | rcyfrozenxid | rcyfrozenxid64 | rcybucket
-----------+---------+----------+-------------------------------+---------------+--------------+---------+---------------+-------------------------------+--------------+--------------+--------------+--------
--+---------------+----------------+---------------+-------------+--------------+----------------+-----------
18718 | 18654 | 18672 | BIN$48DE233848F0$3443B590==$0 | t1 | t | 0 | 63651 | 2024-10-25 20:53:10.403167+08 | 62959 | 62959 | 2200 | 1
0 | 0 | 18685 | t | t | 0 | 122037 |
(1 row)
anbob=# alter table t1 rename to t10;
ALTER TABLE
anbob=# create table t1 as select * from t10 where 1=0;
INSERT 0 0
anbob=# timecapsule table t1 to before truncate;
ERROR: The table definition of "t10" has been changed.
anbob=# drop table t1;
DROP TABLE
anbob=# alter table t10 rename to t1;
ALTER TABLE
anbob=# timecapsule table t1 to before truncate;
ERROR: The table definition of "t1" has been changed.
Note:
表在做DDL后，truncate不允许再闪回。
清空回收站
anbob=# purge table t1; PURGE TABLE -- 一次清理1个同名对象 anbob=# purge recyclebin; PURGE RECYCLEBIN
总结
通过体验 GaussDB 的闪回功能，我们可以看出其在数据恢复方面的强大能力。无论是误删表、清空表中的数据，还是需要恢复到某个时间点之前的表状态，GaussDB 的闪回功能都能提供有效的解决方案。这些功能不仅提高了数据的安全性，也为数据库管理员提供了极大的便利。通过合理的配置和管理，GaussDB 的闪回功能将成为我们日常运维中的有力工具。
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)