PolarDB:Drop flashback

Prerequisites

The feature is supported on the PolarDB for PostgreSQL clusters that run the following engines:

• PostgreSQL 11 (revision version 1.1.30 or later)

• PostgreSQL 14 (revision version 14.9.15.0 or later)

Precautions

• We recommend that you enable the drop flashback feature when you delete important tables. Use caution when you use the drop flashback feature for routine operations.

• When the drop flashback feature is enabled, the deleted table is moved to the space which runs in recycle bin mode. The table is not really deleted, so the space occupied by the table is not released. Therefore, a large amount of disk space may be occupied when a lot of data is accumulated in the recycle bin. We recommend that you clear the recycle bin on a regular basis.

• In this case, your attempt to remove the dependencies of the deleted table may fail, because the table is not really deleted and the table still use these dependencies.

• The deleted table is moved to the space which runs in the recyclebin mode. Do not create a mode with the name recyclebin, because the table in the recyclebin mode can be cleared by the purge recyclebin statement.

• When the drop flashback feature is enabled, the following operations are not supported:

  • The deleted partitioned table and temporary tables cannot be flashed back. Partitioned table and temporary tables are really deleted and cannot be restored.

  • The sql_drop event trigger is not supported.

Parameters

Syntax

The drop flashback feature supports the following SQL statements:

• Delete a table

  drop table table_name;         # Move the table to the space which runs in the recyclebin mode. 
  drop table table_name purge;   # Delete the table. The table cannot be restored.

• Restore a deleted table

  flashback table table_name to before drop;                        # Restore the deleted table. If tables with the same name exist, the latest table is restored. 
  flashback table table_name to before drop rename to table_name_1; # Restore and rename the deleted table.

• Permanently delete files from the recycle bin

  purge table table_name;       

  Note

  If tables with the same name exist, the oldest table is deleted.

• Clear the recycle bin

  purge recyclebin;   

  Note

  The polar_super_user permissions are required when you clear the recycle bin.

• View recycle bin information

  show recyclebin;      

Performance test

• Prepare data for the test.

  Create the test1 and table test2 tables and insert data to them.

  CREATE TABLE test1(id int primary key, name text);
  
  CREATE TABLE test2(id int primary key, name text);
  
  INSERT INTO test1 select t,repeat('test1',1024) from generate_series(1, 10000) as t;
  
  INSERT INTO test2 select t,repeat('test2',1024) from generate_series(1, 10000) as t;
  \dt

  Sample result:

           List of relations
   Schema | Name  | Type  |  Owner
  --------+-------+-------+----------
   public | test1 | table | postgres
   public | test2 | table | postgres
  (2 rows)

• Delete the tables and view the recycle bin.

  • Delete the test1 and test2 tables.

    DROP TABLE test1;
    
    DROP TABLE test2;
    
    \dt

    Sample result:

    Did not find any relations.

  • View the recycle bin.

    show recyclebin;

    Sample result:

     table_catalog |         table_name          | table_type
    ---------------+-----------------------------+------------
     postgres      | public$test1$69461331094004 | BASE TABLE
     postgres      | public$test2$69461332967609 | BASE TABLE
    (2 rows)

• Flash back the deleted tables.

  • Restore the deleted tables.

    FLASHBACK TABLE test1 TO BEFORE DROP;
    
    FLASHBACK TABLE test2 TO BEFORE DROP RENAME TO test3;
    
    \dt

    Sample result:

       List of relations
     Schema | Name  | Type  |  Owner
    --------+-------+-------+----------
     public | test1 | table | postgres
     public | test3 | table | postgres
    (2 rows)

    Note

    The test2 table is renamed to test3.

  • View the restored data.

    View the data in the test1 table.

    SELECT count(*) FROM test1;

    Sample result:

     count
    -------
     10000
    (1 row)

    View the data in the test3 table.

    SELECT count(*) FROM test3;

    Sample result:

     count
    -------
     10000
    (1 row)