How do I use the drop flashback feature to quickly restore a deleted table - PolarDB

PolarDB for PostgreSQL does not provide undo logs. Your accidental operations may cause data loss. For example, data may be lost after you execute the DROP TABLE statement. The drop flashback feature on PolarDB for PostgreSQL allows you to quickly restore deleted tables and view and clear the recycle bin, which enables you to recover data.

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)

Note

You can execute one of the following statements to query the revision version of your PolarDB for PostgreSQL cluster:

PostgreSQL 11
```
show polar_version;
```
PostgreSQL 14
```
select version();
```

Precautions

We recommend that you enable the drop flashback feature when you delete important tables. Use caution when you perform these 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, a large amount of 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.
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 are 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

Parameter

Data type

Description

polar_enable_flashback_drop

BOOL

Specifies whether to enable the drop flashback feature. Default value: OFF. Valid values:

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 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;      
```

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 to 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)
```