PolarDB:Drop flashback

How it works

When you run DROP TABLE with the drop flashback feature enabled, the table is moved to the recycle bin rather than being physically deleted. The disk space the table occupies is not released until you explicitly purge it.

To recover a dropped table, run FLASHBACK TABLE ... TO BEFORE DROP. If multiple versions of the same table exist in the recycle bin, the most recent one is restored by default.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster

• The polar_enable_flashback_drop parameter set to ON

• polar_super_user permissions if you need to clear the entire recycle bin with PURGE RECYCLEBIN

Parameters

Limitations

• Partitioned tables and temporary tables are not supported. When the drop flashback feature is enabled, dropping a partitioned table or temporary table permanently deletes it — it cannot be restored.

• sql_drop event trigger is not supported.

• Schema name conflict: Avoid naming a schema recyclebin. Tables stored in the recycle bin are placed in the recyclebin schema, and running PURGE RECYCLEBIN clears everything in that schema.

• Disk space: Dropped tables remain on disk until purged. Clear the recycle bin regularly to avoid excessive disk usage, especially when large tables are frequently dropped.

• Dependency removal: Because dropped tables remain in the recycle bin, attempts to remove their dependencies may fail while the table still exists there.

SQL syntax

All drop flashback operations use the following statements.

Drop a table

DROP TABLE table_name;         -- Moves the table to the recycle bin
DROP TABLE table_name PURGE;   -- Permanently deletes the table (cannot be restored)

Restore a dropped table

-- Restore the most recently dropped table with the given name
FLASHBACK TABLE table_name TO BEFORE DROP;

-- Restore and rename the table at the same time
FLASHBACK TABLE table_name TO BEFORE DROP RENAME TO new_table_name;

If multiple versions of table_name exist in the recycle bin, FLASHBACK TABLE restores the most recent one.

Purge a specific table from the recycle bin

PURGE TABLE table_name;

If multiple versions of table_name exist in the recycle bin, PURGE TABLE removes the oldest one.

Clear the entire recycle bin

PURGE RECYCLEBIN;

PURGE RECYCLEBIN requires polar_super_user permissions.

View recycle bin contents

SHOW RECYCLEBIN;

The output includes the system-generated recycle bin name (table_name) for each dropped table. Use this name to identify which version to restore or purge when multiple versions of the same table exist.

Examples

The following example walks through a typical drop-and-restore workflow.

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

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

DROP TABLE test1;
DROP TABLE test2;

\dt

Did not find any relations.

SHOW RECYCLEBIN;

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

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

\dt

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

SELECT count(*) FROM test1;

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

SELECT count(*) FROM test3;

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