Updated on 2025-09-22 GMT+08:00

Flashback

Overview

Flashback is part of data restoration, allowing you to selectively reverse the impact of a committed transaction and restore data from incorrect operations. It uses the database recycle bin to restore deleted tables, or uses the MVCC mechanism to restore data to a specified time point or CSN. Flashback is used in the following scenarios:

  • Data restoration based on the MVCC mechanism: restores tables from incorrect DML operations.
  • Restoration based on the database recycle bin: restores tables from incorrect DROP and TRUNCATE operations.
  • Astore supports only the flashback DROP/TRUNCATE function.
  • Standby nodes do not support flashback.
  • You can enable flashback as required, but enabling it will cause performance deterioration.

Flashback Query

  • Overview

    Flashback query is used to query a snapshot of a table at a certain time point in the past. This feature can be used to view and logically rebuild damaged data that is accidentally deleted or modified. Flashback query is based on the MVCC mechanism, making it possible to retrieve data from earlier versions.

  • Prerequisites

    You have set the GUC parameter undo_retention_time, which specifies the retention period of undo logs in earlier versions.

  • Example
    -- Create a table and insert data.
    gaussdb=# CREATE TABLE tb_t1 (c1 int, c2 int) WITH(storage_type = ustore);
    gaussdb=# INSERT INTO tb_t1 values (1,1),(2,2);
    
    -- Query the current time and CSN.
    gaussdb=# SELECT now(), int8in(xidout(next_csn)) FROM gs_get_next_xid_csn() limit 1;
                  now              | int8in  
    -------------------------------+---------
     2025-04-22 15:22:47.574995+08 | 2020912
    (1 row)
    
    -- Simulate a data misoperation.
    gaussdb=# UPDATE tb_t1 SET C1=222,C2=222 WHERE C1 = 2;
    gaussdb=# SELECT * FROM tb_t1;
     c1  | c2  
    -----+-----
       1 |   1
     222 | 222
    (2 rows)
    
    -- Query data before the table is modified based on the specific time.
    gaussdb=# SELECT * FROM tb_t1 TIMECAPSULE TIMESTAMP '2025-04-22 15:22:47.574995';
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- Query data before the table is modified based on the CSN.
    gaussdb=# SELECT * FROM tb_t1 TIMECAPSULE CSN '2020912';
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- Drop.
    gaussdb=# DROP TABLE tb_t1;

Table Flashback

  • Overview

    Table flashback can restore a table to a specific time point. If the logical damage is limited to one or a group of tables instead of the entire database, this feature can restore tables to a specified time point or CSN based on the MVCC mechanism, implementing table-level data restoration.

  • Prerequisites

    You have set the GUC parameter undo_retention_time, which specifies the retention period of undo logs in earlier versions.

  • Example
    -- Create a table and insert data.
    gaussdb=# CREATE TABLE tb_t2 (c1 int, c2 int) WITH(storage_type = ustore);
    gaussdb=# INSERT INTO tb_t2 VALUES (1,1),(2,2);
    
    -- Query the current time and CSN.
    gaussdb=# SELECT now(), int8in(xidout(next_csn)) FROM gs_get_next_xid_csn() limit 1;
                 now              | int8in  
    ------------------------------+---------
     2025-04-22 15:32:22.99132+08 | 2021175
    (1 row)
    
    
    -- Simulate a data misoperation.
    gaussdb=# UPDATE tb_t2 SET c1 = 222, c2 = 222 WHERE C1 = 2;
    gaussdb=# SELECT * FROM tb_t2;
     c1  | c2  
    -----+-----
       1 |   1
     222 | 222
    (2 rows)
    
    -- Flash back the table data to the time point before the misoperation.
    gaussdb=# TIMECAPSULE table tb_t2 TO TIMESTAMP to_timestamp('2025-04-22 15:32:22.99132','YYYY-MM-DD HH24:MI:SS.FF');
    gaussdb=# SELECT * FROM tb_t2;
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- You can also run the following SQL statements to flash back the table based on the CSN:
    gaussdb=# UPDATE tb_t2 SET c1 = 333, c2 = 333 where c1 = 2;
    gaussdb=# TIMECAPSULE table tb_t2 TO CSN 2021175;
    gaussdb=# SELECT * FROM tb_t2;
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- Drop.
    gaussdb=# DROP TABLE tb_t2;

Flashback DROP/TRUNCATE

  • Overview
    • Flashback DROP restores tables that are accidentally dropped, along with their structures, such as indexes and table constraints, from the recycle bin. Flashback DROP is based on the recycle bin mechanism. You can restore physical table files from the recycle bin to restore the dropped tables.
    • Flashback TRUNCATE restores tables that are mistakenly or accidentally truncated, along with their indexes, from the recycle bin. Flashback TRUNCATE is based on the recycle bin mechanism. You can restore physical table files from the recycle bin to restore the truncated tables.
  • Prerequisites
    • You have set the GUC parameter enable_recyclebin to on to enable the recycle bin.
    • You have set the GUC parameter recyclebin_retention_time to specify how long files will be kept in the recycle bin. Files will be automatically deleted from the recycle bin after this length of time.
  • Example
    -- Create a table and insert data.
    gaussdb=# CREATE TABLE tb_t3 (c1 int,c2 int) WITH(storage_type = ustore);
    gaussdb=# INSERT INTO tb_t3 VALUES (1,1), (2,2);
    
    -- Drop the table.
    gaussdb=# DROP TABLE tb_t3;
    
    -- Flash back to the time before the table is dropped.
    gaussdb=# TIMECAPSULE TABLE tb_t3 TO BEFORE DROP;
    gaussdb=# SELECT * FROM tb_t3;
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- Use TRUNCATE to empty the table.
    gaussdb=# TRUNCATE TABLE tb_t3;
    
    -- Flash back to the time before the table is emptied.
    gaussdb=# TIMECAPSULE TABLE tb_t3 TO BEFORE TRUNCATE;
    gaussdb=# SELECT * FROM tb_t3;
     c1 | c2 
    ----+----
      1 |  1
      2 |  2
    (2 rows)
    
    -- Drop.
    DROP TABLE tb_t3;