Database and table restoration method 1: Restore data to an earlier point in time

Overview

The database and table restoration feature of PolarDB does not overwrite or delete existing databases or tables in the cluster, or directly write data to existing databases or tables in the cluster. The feature creates new databases or tables in the cluster. You can specify new database names or table names during the restoration process to restore data to the new databases or tables. For example, you can specify db2 as the destination database to restore backup data from db1 to db2.

Database and table restoration does not interrupt access to the cluster but may consume the computing resources of the cluster, which increases the CPU utilization and IOPS of the cluster.

Limits

Usage notes

• You can restore databases or tables to an earlier point in time only from level-1 backups.

• Only the tables that you specify are restored. Make sure that you select all tables that you want to restore.

  Note

  If you cannot determine the tables that you want to restore, we recommend that you restore all data in the current cluster to a new cluster and then migrate the data to the current cluster. For more information, see Restoration method 1: Restore data to a specific point in time and Restoration method 2: Restore data from a backup set (snapshot).

• The database and table restoration task fails if you restore databases or tables in the following scenarios:

  • The name that you specify for a destination database or table is identical to one that exists in the original cluster.

  • From the time when the backup set is generated to the time when the backup set is restored, the restored table is deleted or a database or table with the same name as the destination database or table exists in the cluster.

• If you want to restore only parts of a database, you can restore up to 100 tables in the database at the same time. If you restore an entire database, all tables in the database are restored.

  Note

  • A long period of time is required to restore a large number of tables at the same time. We recommend that you do not restore a large number of tables at the same time.

  • To restore a large number of tables, we recommend that you perform a full restoration to a new cluster. For more information, see Restoration method 1: Restore data to a specific point in time and Restoration method 2: Restore data from a backup set (snapshot).

• You can use the database and table restoration feature in a cluster that contains more than 50,000 tables (including system tables).

  Note

  • The feature is in canary release. To use the feature, contact us.

  • To query the number of tables in a cluster, including system tables, execute the following SQL statement:

    SELECT COUNT(*) FROM information_schema.tables;

  • To query the number of system tables in a cluster, execute the following SQL statement:

    SELECT COUNT(*) FROM pg_class WHERE oid < 16384 AND relkind = 'r';

• When you use the database and table restoration feature to restore a table, the triggers in the table cannot be restored.

• When you use the database and table restoration feature to restore a table, the foreign keys in the table cannot be restored.

Procedure

1. Log on to the PolarDB console. Click Clusters in the left-side navigation pane. Select a region in the upper-left corner and click the ID of the cluster in the list to go to the Basic Information page.

2. In the left-side navigation pane, choose Settings and Management > Backup and Restoration.

3. On the Backup and Restoration page, click Restore Databases/Tables.

4. In the dialog box that appears, set the Restoration Type parameter to Point in Time and set the Restoration Time parameter to the point in time to which you want to restore data.

   

   Note

   The point in time you specify for the Restoration Time parameter must fall within the range of the Restore To parameter. To use the Point in Time restoration method, make sure that the full backup set that is closest to the specified point in time contains the tables that you want to restore. The time range of the Restore To parameter depends on the value of the Log Retention Period (Days) parameter. The default value is seven days.

5. On the left side of the Databases and Tables to Restore section, select the database that you want to restore. On the right side, select the tables that you want to restore.

   

   Note

   • If you do not specify a name for a destination database or table, the system automatically generates a new database or table name by suffixing the name of the original database and table with _backup. For example, if the name of an original table is test, the destination table is automatically named test_backup.

   • If you do not select a table after you select a database, all tables in the database are restored.

6. Click OK.

   Note

   During restoration, the database is in the Restoring from Backup state. You can still access the databases, but the CPU usage and IOPS of the cluster may increase.

Related API operations