Flashback Table

Scope of application

Flashback Table is supported in the following editions of PolarDB for PostgreSQL: PostgreSQL 11 with a minor engine version of 2.0.11.9.22.0 or later.

Overview

Flashback Table

The Flashback Table feature periodically retains data page snapshots in flashback logs and transaction information in the fast recovery area. This allows you to restore table data as of a specific point in time to a new table.

Syntax

FLASHBACK TABLE
    [ schema. ]table
    TO TIMESTAMP expr;

Parameters:

Examples

1. Prepare test data.

   1. Create a table named test and insert data.

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

   2. Query the total number of rows in the test table.

      SELECT count(1) FROM test;

      The following result is returned:

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

   3. Calculate the sum of the id values.

      SELECT sum(id) FROM test;

      The following result is returned:

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

2. Wait for 10 seconds and delete data from the test table.

   SELECT pg_sleep(10);
   DELETE FROM test;

   Query the test table after deletion.

   SELECT * FROM test;

   The following result is returned:

    id
   ----
   (0 rows)

3. Flash back the test table to 10 seconds ago.

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

   The following result is returned:

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

4. Query data in the flashed-back table.

   Query the total number of rows in the flashed-back table.

   SELECT count(1) FROM polar_flashback_65566;

   The following result is returned:

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

   Calculate the sum of the id values in the flashed-back table.

   SELECT sum(id) FROM polar_flashback_65566;

   The following result is returned:

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

Usage guidelines

The Flashback Table feature depends on flashback logs and the fast recovery area. You must set the polar_enable_flashback_log and polar_enable_fast_recovery_area parameters and then restart the cluster. Other related parameters should also be adjusted as needed. We recommend that you modify all relevant parameters at once and restart the cluster during off-peak hours. Enabling Flashback Table increases memory and disk usage and causes some performance overhead. Evaluate the impact carefully before enabling this feature.

Memory usage

Enabling flashback logs requires additional shared memory equal to the sum of the following three items:

• polar_flashback_log_buffers × 8 KB

• polar_flashback_logindex_mem_size MB

• polar_flashback_logindex_queue_buffers MB

Enabling the fast recovery area requires approximately 32 KB of additional shared memory. Evaluate the current cluster status before adjusting these parameters.

Disk usage

To flash back to a specific point in time, flashback logs, WAL logs, and their LogIndex files within that period must be retained. This increases disk usage. In general, the larger the value of polar_fast_recovery_area_rotation, the more disk space is consumed. For example, if polar_fast_recovery_area_rotation is set to 300, historical data of the last 5 hours is retained.

After flashback logs are enabled, flashback points are created periodically. A flashback point is a special type of checkpoint. When a checkpoint is triggered, the polar_flashback_point_segments and polar_flashback_point_timeout parameters are evaluated to determine whether the current checkpoint is a flashback point. We recommend that you configure these parameters as follows:

• Set polar_flashback_point_segments to a multiple of max_wal_size.

• Set polar_flashback_point_timeout to a multiple of checkpoint_timeout.

For example, if 20 GB of WAL logs are generated in 5 hours and the ratio of flashback logs to WAL logs is approximately 1:20, approximately 1 GB of flashback logs are generated. The ratio of flashback logs to WAL logs depends on the following two factors:

• Workload pattern: the more write-intensive the workload, the more flashback logs are generated.

• The larger the values of polar_flashback_point_segments and polar_flashback_point_timeout, the fewer flashback logs are generated.

Performance impact

The flashback log feature introduces two background processes that consume flashback logs, which increases CPU overhead. You can adjust the polar_flashback_log_bgwrite_delay and polar_flashback_log_insert_list_delay parameters to extend the interval between these background processes and reduce CPU consumption. However, this may cause some performance degradation. We recommend that you use the default values.

The flashback log feature must flush the corresponding flashback logs before dirty pages are flushed to disk to prevent flashback log loss. This may cause some performance degradation. In most scenarios, the performance degradation does not exceed 5%.

During a table flashback, the pages involved in the target table are swapped in and out of the shared memory pool. This may cause performance fluctuations in other database operations.

Usage limits

The Flashback Table feature restores the data of a target table to a new table named polar_flashback_<target table OID>. After you execute the FLASHBACK TABLE statement, the following NOTICE message is displayed:

• flashback table test to timestamp now() - interval '1h';
  NOTICE:  Flashback the relation test to new relation polar_flashback_54986, please check the data
  FLASHBACK TABLE

  In this example, polar_flashback_54986 is a temporary table generated by the flashback operation. Only the table data as of the target point in time is restored.

• Flashback Table supports only regular tables. The following database objects cannot be flashed back:

  • Indexes

  • TOAST tables

  • Materialized views

  • Partitioned tables

  • Table partitions

  • System tables

  • Foreign tables

  • Tables with TOAST child tables

• A table cannot be flashed back if any of the following DDL operations have been performed on the table between the target point in time and the current time:

  • DROP TABLE

  • ALTER TABLE SET WITH OIDS

  • ALTER TABLE SET WITHOUT OIDS

  • TRUNCATE TABLE

  • Column type modifications where the original and new types cannot be implicitly converted and no safe explicit cast is provided by using the USING clause.

  • Changing the table to UNLOGGED or LOGGED.

  • Adding an IDENTITY column.

  • Adding a column whose type has constraints.

  • Adding a column whose default value expression contains volatile functions.

    Note

    If DROP TABLE has been executed, you can use the Flashback Drop feature of PolarDB for PostgreSQL to restore the table.

Usage suggestions

If data is accidentally modified, we recommend that you use audit logs to locate the time when the accidental operation occurred and then flash back the target table to a point in time before that operation. During the table flashback, an exclusive lock is held on the target table, so only query operations can be performed on the target table. In addition, during the table flashback, the pages involved in the target table are swapped in and out of the shared memory pool, which may cause performance fluctuations in other database operations. Therefore, we recommend that you perform flashback operations during off-peak hours.

The speed of a flashback depends on the table size. For large tables, you can increase the value of polar_workers_per_flashback_table to increase the number of parallel flashback workers and reduce the flashback time.

After the table flashback is complete, you can query the data in the flashed-back table based on the NOTICE message and compare it with the data in the original table. The flashed-back table does not have any indexes. You can create indexes as needed. After the data comparison is complete, you can restore the missing data to the original table.

Parameters