PolarDB:Drop flashback

Precautions

• We recommend that you enable the drop flashback feature when you delete important tables. Use caution when you perform 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 deleted in the true sense, so the space occupied by the table is not released. Therefore, large disk space may be occupied when a lot of data is accumulated. 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 actually deleted and the table still use these dependencies.
• Because the deleted table is moved to the space which runs in recyclebin mode. Try to avoid creating a mode with the name recyclebin, because the table in recyclebin mode will 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 actually 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 policy

  drop table table_name;         # Move the table to the space which runs in recycle bin mode. 
  drop table table_name purge;   # Delete the table, which cannot be restored. 

• Restore a deleted table

  flashback table table_name to before drop;                        # Restore the deleted table (the latest table is restored if the table has the same name as other tables). 
  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 The oldest table is deleted if the table has the same name as other tables.
• 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;      

Examples

• 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

  A similar output is displayed:

           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

    A similar output is displayed:

    Did not find any relations.

  • View the recycle bin.

    show recyclebin;

    A similar output is displayed:

     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

    A similar output is displayed:

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

    Note The test2 table is renamed test3.
  • View the restored data.
    View the data in the test1 table.

    SELECT count(*) FROM test1;

    A similar output is displayed:

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

    View the data in the test3 table.

    SELECT count(*) FROM test3;

    A similar output is displayed:

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