PolarDB:Flashback table

How it works

Flashback table works by periodically saving page snapshots to flashback logs and retaining transaction information in the fast recovery area. When you trigger a flashback, PolarDB reads those logs to reconstruct the table as it existed at the target timestamp and writes the result to a new table.

The original table is not modified. The restored table is named polar_flashback_<OID>, where <OID> is the object identifier of the original table.

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster running PostgreSQL 11, revision version 1.1.22 or later. To check the revision version, run:

  SHOW polar_version;

• Flashback table enabled. See Enable flashback table.

Enable flashback table

Enabling flashback table requires setting two parameters and restarting the cluster. Review the resource impact before enabling in production.

1. Set the two mandatory parameters:

   ALTER SYSTEM SET polar_enable_flashback_log = on;
   ALTER SYSTEM SET polar_enable_fast_recovery_area = on;

2. Restart the cluster for the changes to take effect.

3. (Optional) Tune additional parameters based on your retention and performance requirements. See Parameters.

polar_enable_flashback_log and polar_enable_fast_recovery_area take effect after SIGHUP, but enabling these features requires a full restart to apply the associated memory changes.

Syntax

FLASHBACK TABLE [ schema. ]table TO TIMESTAMP expr;

A flashback creates a new table named polar_flashback_<OID>; it does not modify the original. The operation acquires an exclusive lock on the source table, so only read queries on that table are allowed while the flashback is running.

Timestamp examples

Relative offset (5 minutes ago):

FLASHBACK TABLE orders TO TIMESTAMP now() - interval '5 min';

Absolute timestamp:

FLASHBACK TABLE orders TO TIMESTAMP '2026-03-28 10:00:00';

Flash back a table

The following example shows how to flash back a table after an accidental delete.

Step 1: Prepare test data

Create the test table and insert 10,000 rows:

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

Verify the initial state:

SELECT count(1) FROM test;
-- count: 10000

SELECT sum(id) FROM test;
-- sum: 50005000

Step 2: Delete data and confirm the loss

SELECT pg_sleep(10);
DELETE FROM test;

SELECT * FROM test;
-- id
-- ----
-- (0 rows)

Step 3: Flash back the table

Restore the table to the state it was in 10 seconds ago:

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

Expected output:

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

The NOTICE message shows the name of the restored table (polar_flashback_65566).

Step 4: Verify the restored data

SELECT count(1) FROM polar_flashback_65566;
-- count: 10000

SELECT sum(id) FROM polar_flashback_65566;
-- sum: 50005000

Step 5: Merge missing rows back

After verifying that the restored data is correct, insert the missing rows back into the original table:

INSERT INTO test SELECT * FROM polar_flashback_65566
WHERE id NOT IN (SELECT id FROM test);

No indexes are created on the flashback table. Create indexes manually if your queries require them.

Limitations

Resource impact

Enabling flashback table increases memory and disk usage and may degrade performance. Evaluate the following before enabling in production.

Best practices

• Recover from accidental deletes: Check the audit log to identify the exact time of the unintended operation, then flash back the table to a point just before that time.

• Large tables: Increase polar_workers_per_flashback_table to enable parallel flashback and reduce recovery time.

• Post-recovery workflow: After flashback completes, compare the restored table against the original using the table name from the NOTICE output. Confirm the data is correct, then insert the missing rows back into the original table.

• Parameter changes: Modify parameters one at a time and schedule cluster restarts during off-peak hours.

Parameters

What's next

• Flashback delete: Recover tables dropped with DROP TABLE.