Flashback table

Syntax

FLASHBACK TABLE
    [ schema. ]table
    TO TIMESTAMP expr;

Example: recover accidentally deleted data

The following steps show how to recover a table whose rows were accidentally deleted.

Step 1: Record the initial state.

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

SELECT count(1) FROM test;

Output:

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

SELECT sum(id) FROM test;

Output:

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

Step 2: Simulate accidental deletion.

SELECT pg_sleep(10);
DELETE FROM test;

SELECT * FROM test;

Output:

 id
----
(0 rows)

Step 3: Flash back the table to 10 seconds ago.

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

Output:

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

The recovered data is in a new table named polar_flashback_65566. Only the table data is restored to the specified point in time—indexes are not included.

Step 4: Verify the recovered data.

SELECT count(1) FROM polar_flashback_65566;

Output:

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

SELECT sum(id) FROM polar_flashback_65566;

Output:

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

After verifying the data, copy the missing rows back to the original table as needed.

Supported table types

The flashback table feature supports only regular (heap) tables. It cannot restore the following:

• Indexes

• TOAST tables

• Materialized views

• Partitioned tables and child partitioned tables

• System tables

• Foreign tables

• Tables that contain child TOAST tables

DDL restrictions

A table cannot be flashed back if any of the following DDL statements were executed between the target timestamp and the current time:

• DROP TABLE

• TRUNCATE TABLE

• ALTER TABLE SET WITH OIDS

• ALTER TABLE SET WITHOUT OIDS

• Modifying a column's data type where the old and new types are not implicitly convertible and no USING clause with a secure forced conversion is provided

• Changing the table to UNLOGGED or LOGGED

• Adding a column as IDENTITY

• Adding a column where only limited data types can be selected

• Adding a column where the default value expression contains volatile functions

To recover a table dropped with DROP TABLE, use the flashback delete feature of PolarDB for PostgreSQL (Compatible with Oracle).

Memory

Enabling flashback logging increases shared memory by:

• polar_flashback_log_buffers x 8 KB

• polar_flashback_logindex_mem_size MB

• polar_flashback_logindex_queue_buffers MB

Enabling the fast recovery area increases shared memory by approximately 32 KB.

Disk

Flashback logs, WAL logs, and their LogIndex files must all be retained for the target time window. The polar_fast_recovery_area_rotation parameter controls how long transaction information is kept: for example, setting it to 300 retains 5 hours of history.

As a rough estimate, flashback logs consume about 1/20 of the WAL log volume (a 1:20 ratio). For example, if 20 GB of WAL logs are generated in five hours, approximately 1 GB of flashback logs are generated. This ratio increases with write-heavy workloads and decreases with larger values of polar_flashback_point_segments and polar_flashback_point_timeout.

For flashback point configuration, follow these guidelines:

• Set polar_flashback_point_segments to a multiple of max_wal_size.

• Set polar_flashback_point_timeout to a multiple of checkpoint_timeout.

Performance

The flashback logging feature adds two background processes (bgwriter and bginserter) that consume additional CPU. To reduce CPU overhead, increase the working intervals using polar_flashback_log_bgwrite_delay and polar_flashback_log_insert_list_delay—though this may reduce throughput. The default values work well for most workloads.

Because flashback log flushing occurs before page flushing, you may observe a performance overhead of less than 5% in most scenarios. During a flashback operation, the table's pages are swapped in and out of the shared memory pool, which may cause brief access jitter on other databases.