Restore databases and tables from a backup set

Overview

PolarDB's database and table restoration creates new databases and tables in the original cluster instead of overwriting or modifying existing ones. During the process, you can specify new names for the restored databases and tables. For example, you can restore data from a database named db1 to a new database named db2.

Although this process does not interrupt database access, it consumes computing resources from the original cluster and may increase CPU utilization and IOPS.

Limitations

• By default, this feature is not supported for clusters that contain more than 50,000 tables.

• You cannot use this feature to restore an In-Memory Column Index (IMCI).

Usage notes

• You can restore databases and tables from level-1 backup sets only; restoring from level-2 backup sets is not supported.

• Ensure you select all required tables before proceeding.

  Note

  If you are unsure which tables to restore, restore the entire cluster to a new cluster and then migrate the necessary data to the original cluster. For more information, see Restoration method 1: Restore data to a specific point in time and Restore a cluster from a backup set (snapshot).

• The restoration task fails if you specify a database or table name that already exists in the original cluster.

• You can restore a maximum of 100 tables at a time from a single database. If you select a database without specifying individual tables, all tables within that database are restored.

  Note

  • Avoid restoring a large number of tables in a single operation, as it can be time-consuming.

  • If you need to restore a large number of tables, restore the entire cluster to a new cluster. For more information, see Restoration method 1: Restore data to a specific point in time and Restore a cluster from a backup set (snapshot).

• This feature can also be used on clusters where the number of tables (including system tables) exceeds 50,000.

  Note

  • This feature is in canary release. To use it, you can contact us to have your account added to the allowlist.

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

    SELECT COUNT(*) FROM information_schema.tables;

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

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

• This feature does not restore triggers.

• This feature does not restore foreign keys.

Procedure

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the Region of the target cluster, and then click the cluster ID.

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, set Restoration Type to Backup Set and select a target backup set from the list.

   Set Restore Location to Restore to Current Cluster.

5. In the Databases and Tables to Restore section, select the source databases on the left and the source tables on the right.

   The selected items appear in the Selected Databases and Tables section below. You can specify custom names in the New Database Name and New Table Name columns, or click Remove to deselect an item.

   Note

   • If you do not specify new names, the system appends _backup to the original names. For example, a source table named test is restored as test_backup.

   • If you select a database but do not specify any tables, all tables in that database are restored by default.

6. Click OK.

   Note

   Although the process does not interrupt database access, it consumes computing resources and may increase CPU utilization and IOPS.

API reference