PolarDB:Drop flashback

Supported versions

Drop flashback is supported on the following engine versions:

• PostgreSQL 11 (revision version 1.1.30 or later)

• PostgreSQL 14 (revision version 14.9.15.0 or later)

To check the revision version of your cluster, run the appropriate statement:

• PostgreSQL 11

  SHOW polar_version;

• PostgreSQL 14

  SELECT version();

How it works

Set polar_enable_flashback_drop to ON to enable the feature:

When polar_enable_flashback_drop is ON, a DROP TABLE statement moves the table into a space which runs in the recyclebin mode. The table's data remains intact and continues to occupy disk space. Recycle bin contents are retained until you manually purge them.

To permanently delete a table and bypass the recycle bin, append PURGE to the drop statement:

DROP TABLE table_name PURGE;

To restore a table, use FLASHBACK TABLE ... TO BEFORE DROP. If multiple versions of the same table exist in the recycle bin (dropped at different times), the most recent version is restored.

SQL syntax

-- Move the table to the recycle bin (recoverable)
DROP TABLE table_name;

-- Permanently delete the table, bypassing the recycle bin
DROP TABLE table_name PURGE;

-- Restore the table. If multiple versions exist, the most recently dropped version is restored.
FLASHBACK TABLE table_name TO BEFORE DROP;

-- Restore and rename the table in one step
FLASHBACK TABLE table_name TO BEFORE DROP RENAME TO new_table_name;

-- View all tables in the recycle bin
SHOW RECYCLEBIN;

-- Permanently delete a specific table from the recycle bin.
-- If multiple versions with the same name exist, the oldest version is deleted.
PURGE TABLE table_name;

-- Clear the entire recycle bin (requires polar_super_user)
PURGE RECYCLEBIN;

Example: drop and restore tables

This example creates two tables, drops them, inspects the recycle bin, and then restores them.

1. Create tables and insert data

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

Expected output:

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

2. Drop the tables and view the recycle bin

DROP TABLE test1;
DROP TABLE test2;

\dt

Expected output:

Did not find any relations.

SHOW RECYCLEBIN;

Expected output:

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

Both tables are in the recycle bin, identified by their internal names.

3. Restore the tables

-- Restore test1 with its original name
FLASHBACK TABLE test1 TO BEFORE DROP;

-- Restore test2 under a new name
FLASHBACK TABLE test2 TO BEFORE DROP RENAME TO test3;

\dt

Expected output:

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

Note: test2 was restored as test3.

4. Verify the restored data

SELECT count(*) FROM test1;

Expected output:

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

SELECT count(*) FROM test3;

Expected output:

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

All 10,000 rows are intact in both tables.

Limitations

• Partitioned tables and temporary tables: DROP TABLE on partitioned tables and temporary tables bypasses the recycle bin and permanently deletes the table. These tables cannot be restored with FLASHBACK TABLE.

• `sql_drop` event trigger: The sql_drop event trigger is not supported when drop flashback is enabled.

• `recyclebin` mode name: Do not create a mode with the name recyclebin. Tables stored in that mode can be cleared by PURGE RECYCLEBIN.

• Dependencies: Because dropped tables are not immediately deleted, attempts to remove their dependencies may fail while the tables remain in the recycle bin.

Usage notes

• Enable drop flashback selectively for tables where accidental deletion would be costly to recover from. Leaving it on globally increases disk usage.

• Tables in the recycle bin continue to occupy disk space. Clear the recycle bin periodically with PURGE RECYCLEBIN (requires polar_super_user) or remove individual entries with PURGE TABLE table_name.