Flashback table

Prerequisites

The following PolarDB for PostgreSQL (Compatible with Oracle) versions are supported:

PolarDB for PostgreSQL (Compatible with Oracle) 1.0 (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 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 must enable the flashback logging and fast recovery area features by configuring the polar_enable_flashback_log and polar_enable_fast_recovery_area parameters and restarting the database. You must modify other parameters based on your business requirements. We recommend that you modify the parameters at the same time and restart the cluster during off-peak hours. If you enable the flashback table feature, the memory and disk usage increases, and the performance is degraded. We recommend that you evaluate the impacts on your business before you use the flashback table 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, the flashback logs and WAL logs of the specified point in time must be retained, as well as the LogIndex files of these logs. This requires more disk space. Theoretically, the larger the polar_fast_recovery_area_rotation value is, the higher 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, more write operations generate more flashback logs.

• The larger the values of the polar_flashback_point_segments and polar_flashback_point_timeout parameters 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. You can adjust the polar_flashback_log_bgwrite_delay and polar_flashback_log_insert_list_delay parameters to set longer working intervals for the two background processes and reduce CPU overheads. 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 syntax 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 that contains no values for secure forced conversion is not used.

  • 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 flashback delete feature of PolarDB for PostgreSQL (Compatible with Oracle) to delete DROP TABLE flashbacks.

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 speed of flashback 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 data comparison is complete, you can send the missing data back to the original table.

Parameters