PolarDB:Flashback Table

Supported table types

The flashback table feature supports only ordinary (regular) tables. The following database objects cannot be restored:

• Indexes
• TOAST tables
• Materialized views
• Partitioned tables
• Partitions of a partitioned table (child tables)
• System tables
• External tables
• Tables that contain child TOAST tables

DDL restrictions

You cannot flash back a table if any of the following DDL operations were performed on it between the target timestamp and the current time:

• DROP TABLE
• ALTER TABLE SET WITH OIDS
• ALTER TABLE SET WITHOUT OIDS
• TRUNCATE TABLE
• Modifying a column type where the source and target types cannot be implicitly converted, unless a USING clause is specified for explicit type conversion.
• Setting the table to UNLOGGED or LOGGED.
• Adding an identity column.
• Adding a column with a restricted data type.
• Adding a column whose default value expression contains volatile functions.

Core parameters

These parameters control the fundamental behavior of the flashback table feature.

Flashback point parameters

These parameters control how frequently PolarDB creates flashback points in time, which determine the granularity of available restore targets.

Memory parameters

These parameters configure the shared memory allocation for flashback log structures.

Performance parameters

These parameters tune the parallelism and background process behavior for flashback operations.

Step 1: Create a table and insert data

Create a test table and populate it with 10,000 rows.

CREATE TABLE test(id int);
INSERT INTO test select * FROM generate_series(1, 10000);

Step 2: Verify the baseline data

Query the row count and sum to establish a baseline.

SELECT count(1) FROM test;

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

SELECT sum(id) FROM test;

   sum
----------
  50005000
(1 row)

Step 3: Simulate an accidental deletion

Wait 10 seconds and then delete all rows from the table.

SELECT pg_sleep(10);
DELETE FROM test;

Verify that the table is now empty.

SELECT * FROM test;

 id
----
(0 rows)

Step 4: Perform the flashback

Restore the table data to its state 10 seconds ago.

FLASHBACK TABLE test TO TIMESTAMP now() - interval'10s';

PolarDB returns a NOTICE message indicating the name of the new table:

NOTICE:  Flashback the relation test to new relation polar_flashback_65566, please check the data
FLASHBACK TABLE

Step 5: Verify the restored data

Query the new table to confirm that the data has been restored correctly.

SELECT count(1) FROM polar_flashback_65566;

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

SELECT sum(id) FROM polar_flashback_65566;

   sum
----------
  50005000
(1 row)

The row count and sum match the baseline values from Step 2, confirming that the data has been fully restored.

After the flashback operation completes, you can query the data of the new table and compare it with the original table based on the NOTICE information. The missing data can be inserted back into the original table.