Flashback in GBase 8c for Data Consistency

Flashback technology in GBase 8c is a crucial method for ensuring global data consistency. It allows for the selective and efficient rollback of a committed transaction's effects, facilitating recovery from human errors. Prior to the introduction of flashback technology, recovery from committed database changes was only possible through methods such as backups and PITR, which could take minutes or even hours. With flashback technology, restoring data to its state before the committed changes takes only seconds, and this recovery time is independent of the database size.

Flashback supports two recovery modes:

• MVCC-based multi-version data recovery: This mode is suitable for recovering from mistakenly deleted, updated, or inserted data. Users can configure the retention time for old versions and execute the corresponding query or recovery command to restore the data to a specified point in time or CSN point.
• Recycle bin-based recovery similar to the Windows system recycle bin: This mode is used for recovering tables mistakenly dropped or truncated. Users can configure the recycle bin settings and execute the corresponding recovery command to restore the mistakenly dropped or truncated tables.

Usage

Configure the following parameters:

gs_guc set -N all -I all -c "undo_zone_count=16384"           ## Number of undo zones allocatable in memory, 0 means undo and Ustore tables are disabled, recommended value is max_connections*4
gs_guc set -N all -I all -c "enable_default_ustore_table=on"  ## Enable default support for the Ustore storage engine
gs_guc set -N all -I all -c "version_retention_age=10000"     ## Number of transactions for which old versions are retained, versions beyond this count will be cleaned up

Or, log in to the database to modify parameters:

ALTER SYSTEM SET undo_zone_count=16384;
ALTER SYSTEM SET enable_default_ustore_table=on;
ALTER SYSTEM SET version_retention_age=10000;

Recommended Recycle Bin Parameters

gs_guc set -N all -I all -c "enable_recyclebin=on"            ## Enable the recycle bin
gs_guc set -N all -I all -c "recyclebin_retention_time=15min" ## Set the retention time for recycle bin objects, objects beyond this time will be automatically cleaned up

Restart the database to apply the changes:

gs_om -t restart

Example

Example 1:

Create a sample table and insert data:

postgres=# drop table if exists t1;
postgres=# create table t1(a int,b int,c int,d int);
postgres=# insert into t1 values(1,2,3,4),(21,22,23,24),(31,32,33,34);

Query the data:

postgres=# select * from t1;

postgres=# select current_timestamp;
  pg_systimestamp
------------------------------
2021-10-12 10:03:08.272344+08

postgres=# update t1 set a=99;
postgres=# select * from t1;
a  | b  | c  | d
----+----+----+----
99 |  2 |  3 |  4
99 | 22 | 23 | 24
99 | 32 | 33 | 34

postgres=# select snptime,snpcsn from gs_txn_snapshot where snptime between '2022-04-21 13:39:36.007842+08' and '2022-04-21 13:39:39.007842+08';
postgres=# select * from t1 timecapsule timestamp to_timestamp('2022-04-21 13:39:38.540667+08','YYYY-MM-DD HH24:MI:SS.FF');
a  | b  | c  | d
----+----+----+----
 1 |  2 |  3 |  4
21 | 22 | 23 | 24
31 | 32 | 33 | 34

postgres=# select * from t1 timecapsule csn 417232;
a  | b  | c  | d
----+----+----+----
 1 |  2 |  3 |  4
21 | 22 | 23 | 24
31 | 32 | 33 | 34
postgres=# SELECT rcyname,rcyoriginname,rcytablespace FROM GS_RECYCLEBIN;
      rcyname           | rcyoriginname | rcytablespace
-----------------------------+---------------+---------------
BIN$3BFF4EB403B$4C71318==$0 | t2            |             0   -- Note: Only the t2 table stored in Astore is visible; the t1 table stored in Ustore is not visible.
(1 row)

postgres=# timecapsule table t1 to before drop rename to t1_bak;
# The return message "TimeCapsule Table" indicates successful execution.

postgres=# select * from t2_bak;
id |   name
----+----------
1 | t2_Tom
2 | t2_Jerry

Example 2:

postgres=# create table t_astore(id int,col1 varchar(8)) with (storage_type=astore);
postgres=# create table t_ustore(id int,col1 varchar(8));
postgres=# insert into t_ustore values(1,'u1'),(2,'u2');
postgres=# select now();
postgres=# update t_ustore set col1='uu' where id=2;
postgres=# select now();
postgres=# delete from t_ustore;
postgres=# select now();
postgres=# select * from t_ustore ;

postgres=# select * from t_ustore timecapsule timestamp to_timestamp('2022-03-20 23:33:41','YYYY-MM-DD HH24:MI:SS.FF');
id | col1
----+------
 1 | u1
 2 | u2
(2 rows)


postgres=# select * from t_ustore timecapsule timestamp to_timestamp('2022-03-20 23:34:04','YYYY-MM-DD HH24:MI:SS.FF');
id | col1
----+------
 1 | u1
 2 | uu
(2 rows)

postgres=# select * into t from t_ustore timecapsule timestamp to_timestamp('2022-03-20 23:34:04','YYYY-MM-DD HH24:MI:SS.FF');
postgres=# select * from t;
id | col1
----+------
 1 | u1
 2 | uu
(2 rows)