Database and table restoration method 2: Restore data from a backup set

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 and tables only from level-1 backup sets.

• 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).

• If you specify a database or table name that already exists in the cluster during the restoration process, the task fails.

• 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 Backup Set and select the backup set that you want to use from the backup set drop-down list.

   

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