Recover PostgreSQL Backup to a New Instance Without Data Loss - AnalyticDB for PostgreSQL

Restore backup data from an AnalyticDB for PostgreSQL instance to a new instance by cloning from a backup set. The original instance is not modified during this process.

Prerequisites

Before you begin, make sure that:

The source instance uses Elastic Storage Mode
The source instance has at least one completed backup on the Data Backup tab

Limitations

Constraint	Description
Storage mode	Both source and destination instances must use Elastic Storage Mode
Region	The destination instance must be in the same region as the source instance
Storage capacity	The total storage capacity of the destination instance must be greater than or equal to that of the source instance
Engine version	The destination engine version must match the source instance
Restore time	Varies based on data volume. Typically completes within a few hours

Restore point availability

The availability of restore points depends on whether the mechanism of proactively discarding xlog archiving has been triggered for instance nodes.

Before restoring, confirm that the xlog archiving discard mechanism was not triggered for all nodes during the period from the end of the previous full backup to the desired restore point.

To confirm restore point availability, submit a ticket.

Restore an instance from a backup set

Log on to the AnalyticDB for PostgreSQL console.
In the upper-left corner, select a region.
Click the ID of the instance whose data you want to restore.
In the left-side navigation pane, click Backup and Restoration.
On the Data Backup tab, find the target backup and click Restore to New Instance in the Actions column.

On the Clone Instance page, configure the following parameters.

Parameter	Description
Source Instance	The source instance whose data you want to restore.
Restore Mode	Automatically set to Backup Set.
Backup Set	The point in time to which you want to restore data.
Region	Automatically set to the region of the source instance.
Zone	The zone for the new instance.
Network Type	Automatically set to VPC.
VPC	The virtual private cloud (VPC) for the new instance.
vSwitch	The vSwitch within the selected VPC.
Multi-zone Deployment	Select Disable or Enable. To enable multi-zone deployment, configure a secondary zone network.
Instance Resource Type	Automatically set to Elastic Storage Mode.
Edition	Select High-performance Edition (Basic Edition) or High-availability Edition based on your requirements. Basic Edition is available only in specific regions and zones. For more information, see AnalyticDB for PostgreSQL Basic Edition and High-performance Edition.
Engine Version	Must match the source instance engine version.
Coordinator Node Resources	The coordinator node specifications. Must be greater than or equal to compute node specifications. For more information, see Manage coordinator node resources.
Compute Node Specifications	The specifications of compute nodes in the new instance.
Compute Nodes	The number of compute nodes.
Disk Storage Type	Valid values: PL1 ESSD (Recommended), PL0 ESSD, and PL2 ESSD (High-throughput). For more information, see Change the disk storage type.
Encryption Type	If set to Disk Encryption, specify a CMK.
Single-node Storage Capacity	The storage capacity per compute node in GB. The total storage across all compute nodes must be greater than or equal to the source instance.
SSL Encryption	Select Disable SSL Encryption or Enable SSL Encryption.

Read and agree to the Terms of Service.
Click Buy Now.

Note

A new instance is created with the restored data. Restore time varies based on data volume and typically completes within a few hours.

FAQ

How do I restore a single table?

AnalyticDB for PostgreSQL does not allow you to directly restore the data of a single table. To restore the data of a single table, perform the following steps:

Follow the steps in Restore an instance from a backup set to restore data to a new instance in the same VPC.
On the source instance, go to the External Data Source Management page and add the new instance as a remote AnalyticDB data source. For details, see Step 1 in Remote AnalyticDB data source access.

Create a foreign table on the source instance mapped to the table on the new instance:

   CREATE FOREIGN TABLE s01.t1(a int, b int)
       SERVER example_name OPTIONS(schema_name 's02', table_name 't1');

Rename the original table on the source instance. For example, rename the testtable table to testtable_old:
```
   ALTER TABLE testtable RENAME TO testtable_old;
```
Create a new table and import the data from the foreign table:
Important
Do not write data to the source table during the rename, create, and import operations. If new data is written, supplement the data after the import completes.
```
   CREATE TABLE testtable(a int, b int);
   INSERT INTO testtable SELECT * FROM s01.t1;
```
After verifying the imported data, release the new instance.