PolarDB:Flashback drop

Applicability

This feature is available for PolarDB for PostgreSQL (Compatible with Oracle) with Oracle syntax compatible 2.0 and minor engine version 2.0.14.9.15.0 or later.

Precautions

• Enable the flashback drop feature only when you delete important tables. Use this feature with caution for routine operations.

• The flashback drop feature moves a dropped table to the recycle bin instead of permanently deleting it. The disk space that the table occupies is not released. This can consume a large amount of disk space over time. You must clear the recycle bin regularly.

• When the flashback drop feature is enabled, attempts to delete the dependencies of a dropped table may fail because the table is not permanently deleted and still relies on these dependencies.

• Dropped tables are moved to the recyclebin schema. Do not create a schema named recyclebin. Tables in a schema named recyclebin are cleared by the purge recyclebin statement.

• After you enable the flashback drop feature, the following operations are not supported:

  • Flashback drop is not supported for partitioned tables and temporary tables. These tables are permanently deleted and cannot be recovered.

  • The sql_drop event trigger is not supported.

Parameters

Syntax

The flashback drop feature supports the following SQL syntax:

• Deletion policy

  drop table table_name;         #Moves the table to the recycle bin.
  drop table table_name purge;   #Permanently deletes the table. The table cannot be recovered.

• Restore a dropped table

  flashback table table_name to before drop;                        #Restores the dropped table. If tables with the same name exist, the latest one is restored.
  flashback table table_name to before drop rename to table_name_1; #Restores and renames the dropped table.

• Permanently delete a table from the recycle bin

  purge table table_name;       

  Note

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

• Empty the recycle bin

  purge recyclebin;   

  Note

  Emptying the recycle bin requires the polar_super_user permission.

• View recycle bin information

  show recyclebin;      

Examples

• Prepare test data:

  Create the test1 and test2 tables and insert data into 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

  The output is as follows:

           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

    The following shows the result:

    Did not find any relations.

  • View the recycle bin.

    show recyclebin;

    The following shows the result:

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

• Flashback the dropped tables:

  • Restore the dropped tables.

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

    The result is as follows:

       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 recovered data.

    View the data in the test1 table.

    SELECT count(*) FROM test1;

    The following output is returned:

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

    View the data in the test3 table.

    SELECT count(*) FROM test3;

    The result is as follows:

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