PolarDB:Method 2 for database and table restoration: Restore to a point in time

How it works

When you initiate a point-in-time restore, PolarDB creates new databases and tables in the original cluster. This feature does not overwrite, delete, or write data to existing databases and tables. The original databases and tables remain accessible throughout the process. After the restoration completes, both the original and restored databases or tables coexist in the cluster.

The restoration process consumes computing resources and may increase CPU utilization and input/output operations per second (IOPS).

Prerequisites

Before you begin, make sure that:

• Your cluster runs Enterprise Edition or Standard Edition with the required revision version. For details, see Supported editions and versions.

• Your cluster is not Multi-master Cluster (Limitless) Edition and is not a secondary cluster in a global database network (GDN).

• The target databases or tables use the InnoDB storage engine and are not archived as cold data.

• The tables do not contain a global secondary index (GSI) or a columnstore index (IMCI).

• Level-1 backups exist that cover the target point in time. Point-in-time restore is not supported for level-2 backups.

Supported editions and versions

The feature supports Enterprise Edition and Standard Edition clusters. The following table lists the minimum revision versions required.

• Basic features: The minimum revision version to use the database and table restoration feature.

• GDN primary cluster/New restoration process: The minimum revision version to use the feature on a global database network (GDN) primary cluster or to benefit from the speed improvements of the new restoration process.

To view the kernel version of your cluster, go to the Basic Information page of your cluster and check the Configuration Information section.

Limitations

Cluster limitations:

• Not supported for Multi-master Cluster (Limitless) Edition clusters.

• Not supported for secondary clusters in a GDN.

• Not supported for clusters with more than 50,000 tables when the storage type is enterprise SSD (ESSD) or the cluster has no read-only (RO) nodes.

Table schema limitations:

• Tables with a global secondary index (GSI) are not supported.

• Restoring a columnstore index (IMCI) is not supported.

• Only InnoDB storage engine tables can be restored.

• Tables archived as cold data cannot be restored.

Quantity and scope limitations:

• Point-in-time restore is supported only for level-1 backups, not level-2 backups.

• Restoring specific tables: up to 100 tables per operation. Restoring an entire database restores all its tables.

• Triggers and foreign keys are not restored. If an original table has triggers or foreign keys, they will not appear in the restored table.

If your cluster does not meet the prerequisites above, perform a full restoration to a new cluster, then migrate the data back to the source cluster.

Clusters with more than 50,000 tables (including system tables):

Using the feature on clusters with more than 50,000 total tables (including system tables) is in canary release. To enable this, contact us to add your cluster to the whitelist.

To count all tables in your cluster, run:

SELECT COUNT(*) FROM information_schema.tables;

To count only system tables, run:

SELECT COUNT(*) FROM information_schema.tables WHERE table_schema IN ('sys', 'performance_schema', 'mysql', 'information_schema', '__recycle_bin__');

Usage notes

• Only the tables you explicitly select are restored. Select all the tables you need before submitting the task.

• If you are unsure which tables to restore, perform a full restoration to a new cluster instead. For details, see Method 1 for full restoration: Restore data from a backup set and Method 2 for full restoration: Restore data to an earlier point in time.

• Restoring a large number of tables takes significantly longer. Avoid restoring too many tables in a single operation; for large-scale recovery, use a full restoration to a new cluster instead.

• The task fails in the following scenarios:

  • A database or table with the same name already exists in the original cluster.

  • Between the backup time and the target point in time, the table to be restored was deleted, or a conflicting name was created in the cluster.

Restore databases or tables to a point in time

1. Log on to the PolarDB console. In the left navigation pane, click Clusters. Select the region of your cluster, then click the cluster ID.

2. In the left navigation pane, choose Settings and Management > Backup and Restoration. Click Restore Databases/Tables.

3. In the dialog box, set Restoration Type to Point in Time and select a time for Restoration Time.

   

   The selected time must fall within the Restore To range. The restorable range is determined by the Log Retention Period (Days) setting, which defaults to 7 days. The full backup set closest to the selected point in time must contain the tables to be restored.

4. Select an IOPS configuration based on your cluster's workload at the time of restoration.

   Configuration	IOPS consumption	When to use
   Quick	~60%	Off-peak hours
   Regular (recommended)	~30%	Balanced workload
   Secure	~15%	May significantly slow the restoration

   For estimated restoration durations, see Database and table restoration speed test data reference.

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

   

   If you do not specify new names for the restored databases or tables, the system automatically appends the _backup suffix to the original names. For example, a table named test is restored as test_backup. If you select an entire database without selecting individual tables, all tables in that database are restored.

6. After confirming your selections, click OK.

API reference

What's next

• To restore an entire cluster instead of specific databases or tables, see Method 1 for full restoration: Restore data from a backup set and Method 2 for full restoration: Restore data to an earlier point in time.

• To restore from a backup set instead of a point in time, see Method 1 for database and table restoration: Restore from a backup set.