PolarDB:Flashback table

Prerequisites

The feature is supported in PolarDB for PostgreSQL clusters that run the following versions:

PostgreSQL 11 (revision version 1.1.22 or later)

show polar_version;

Overview

Flashback table

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

Syntax

FLASHBACK TABLE
    [ schema. ]table
    TO TIMESTAMP expr;

Parameters

Examples

1. Prepare data for the test.

   1. Create the test table 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;

      Sample result:

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

   3. Sum the id values.

      SELECT sum(id) FROM test;

      Sample result:

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

2. Wait 10 seconds and then delete the data in the test table.

   SELECT pg_sleep(10);
   DELETE FROM test;

   Query the deleted test table.

   SELECT * FROM test;

   Sample result:

    id
   ----
   (0 rows)

3. Flash back the data in the test table to that of 10 seconds ago.

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

   Sample result:

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

4. Query the table data after flashback.

   Query the total number of rows in the table after flashback.

   SELECT count(1) FROM polar_flashback_65566;

   Sample result:

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

   Sum the id values after flashback.

   SELECT sum(id) FROM polar_flashback_65566;

   Sample result:

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

Usage

Before you enable the flashback table feature, you need to enable the flashback logging feature by setting the polar_enable_flashback_log parameter to ON, enable the fast recovery area feature by setting the polar_enable_fast_recovery_area parameter to ON, and then restart the cluster. You also need to modify other parameters based on your business requirements. We recommend that you modify the parameters at a time and restart the cluster during off-peak hours. Enabling the flashback table feature leads to higher memory and disk usage, which can deteriorate performance. We recommend that you thoroughly evaluate the impacts of the feature on your business before using the feature.

Memory usage

After the flashback logging feature is enabled, increased shared memory includes:

• polar_flashback_log_buffers* 8 KB

• polar_flashback_logindex_mem_size MB

• polar_flashback_logindex_queue_buffers MB

After the fast recovery area is enabled, the shared memory size increases by about 32 KB. Check the current cluster status before you modify the parameters.

Disk usage

To flash back table data to a specified point in time, it is necessary to retain the flashback logs and WAL logs, as well as the LogIndex files that correspond to these logs, which consequently demands additional disk space. Theoretically, the greater the polar_fast_recovery_area_rotation value is, the more disk space is occupied. For example, if you set polar_fast_recovery_area_rotation to 300, 5 hours of historical data is saved.

After the flashback logging feature is enabled, flashback points are periodically created. A flashback point is a type of checkpoint. When a checkpoint is triggered, the polar_flashback_point_segments and polar_flashback_point_timeout parameters are checked to determine whether the current checkpoint is a flashback point. We recommend that you set the following parameters this way:

• Set the polar_flashback_point_segments value to a multiple of the max_wal_size value.

• Set the polar_flashback_point_timeout value to a multiple of the checkpoint_timeout value.

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

• In the business model, the number of write operations is proportionate to the number of flashback logs.

• The larger the polar_flashback_point_segments and polar_flashback_point_timeout parameter values are, the less flashback logs are generated.

Performance degradation

The flashback logging feature requires two more background processes to consume flashback logs. This inevitably increases CPU overheads. To reduce CPU overheads, you can adjust the polar_flashback_log_bgwrite_delay and polar_flashback_log_insert_list_delay parameters to set a longer latency between activity rounds for the two background processes. However, this may degrade the performance. We recommend that you use the default values of the two parameters.

After the flashback logging feature is enabled, flashback log flushing comes before page flushing to prevent loss of flashback logs. This may cause performance degradation. The performance degradation in most scenarios is less than 5%.

When a table is flashed back, the table-related pages are swapped in and out of the shared memory pool. This may cause jitters in the access performance of other databases.

Limits

The flashback table feature restores the data of an existing table to a new table named polar_flashback_Existing table OID. The following NOTICE is displayed after the FLASHBACK TABLE statement is executed:

• 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

  The polar_flashback_54986 table is a temporary table that is created by the flashback. Only the table data is restored to the specified time point.

• The flashback table feature can restore only common tables, but not the following database objects:

  • Indexes

  • Toast tables

  • Materialized views

  • Partitioned tables

  • Child partitioned tables

  • System tables

  • Foreign tables

  • Tables that contain child toast tables

• You cannot flash back a table on which you have executed the following DDL statements from the specified time to the current time:

  • DROP TABLE

  • ALTER TABLE SET WITH OIDS

  • ALTER TABLE SET WITHOUT OIDS

  • TRUNCATE TABLE

  • Modify the data type of a column. The types before and after the modification cannot be implicitly converted, and the USING clause does not contain other values for secure forced conversion.

  • Change the table to UNLOGGED or LOGGED.

  • Add a column as IDENTITY.

  • Add a column where only limited data types can be selected.

  • Add a column where the default value expression contains volatile functions.

    Note

    You can use the drop flashback feature of PolarDB for PostgreSQL to flash back DROP TABLE operations.

Suggestions

If you delete table data by accident, we recommend that you quickly discover the operation time from audit logs and then use the flashback logs to restore the table data to a point in time before the operation. During table flashback, an exclusive lock is held on the table. Therefore, you can only query data in the table. When a table is flashed back, the table-related pages are swapped in and out of the shared memory pool. This may cause jitters in the access performance of other databases. Therefore, we recommend that you perform flashback operations during off-peak hours.

The flashback speed is related to the size of the table. If the table size is large, you can increase the value of the polar_workers_per_flashback_table parameter to increase the number of workers for parallel flashbacks. This increases the speed of flashback operations.

After the table flashback operation is complete, you can query the data of the table and compare the data with the original table based on the NOTICE information. No indexes are created in the flashback operation. You can create indexes based on your query requirements. After the comparison of the data in the two tables is complete, the missing data can be sent back to the original table.

Parameters