Database-level restore lets you recover specific databases in an ApsaraDB RDS for PostgreSQL instance to a previous state without restoring the entire instance. Use it when you need to undo accidental changes, recover deleted data, or query a historical snapshot.
This feature is in public preview and is free of charge.
Use cases
Accidental data loss: A developer drops a table or overwrites rows by mistake. Restore only the affected database to a point before the incident.
Historical data analysis: Pull a point-in-time copy of a database to run analytics without affecting the production instance.
How it works
Restore reads from either a backup set or a sequence of full backup + incremental log backup data, then writes the selected databases to the original instance or another existing instance in the same region. Restoration speed is approximately 20 Mbit/s and varies with data volume.
Naming behavior: Before the restore begins, the system renames each restored database by appending _backup to its name (for example, orders becomes orders_backup). You can change the name before confirming.
The restoration range varies based on data backup retention period, log backup retention period, and the point in time at which you enable the database-level restore feature on your RDS instance.
Prerequisites
Before you begin, ensure that:
The RDS instance meets all of the following requirements:
Major engine version: PostgreSQL 10 through PostgreSQL 17
Edition: RDS Basic Edition, RDS High-availability Edition, or RDS Cluster Edition
Storage type: Enhanced SSD (ESSD) or Premium ESSD
Billing type: pay-as-you-go or subscription (Serverless instances are not supported)
If the instance was created before October 10, 2022 and uses the original architecture, complete service-linked role (SLR) authorization and update the minor engine version to the latest version before proceeding.
The database-level restore feature is enabled on the instance. See Enable database-level restore.
Tip: To check the instance edition, storage type, and billing type, go to the Basic Information page of the instance.
Limitations
| Limitation | Details |
|---|---|
| Restore target | Only the original instance or an existing instance in the same region with the same major engine version. Restoring to a new instance is not supported. |
| Restore granularity | Databases only. Individual tables and views cannot be restored. |
| Table size | Tables larger than 100 GB cannot be restored. |
| Reserved database names | Databases whose names start with postgres, rdsadmin, or template cannot be restored. |
Enable database-level restore
Enabling this feature does not affect running workloads.
Go to the Instances page. In the top navigation bar, select the region where the instance resides. Find the instance and click its ID.
In the left-side navigation pane, click Backup and Restoration.
Click the Backup Strategy tab.
Click Edit next to Data Backup Settings. In the dialog box, turn on Restore Individual Database/Table.
Click Save.
Restore databases
Go to the Instances page. In the top navigation bar, select the region where the instance resides. Find the instance and click its ID.
In the left-side navigation pane, click Backup and Restoration. On the page that appears, click Restore Individual Database/Table.
If the Restore Individual Database/Table button is not visible, verify that all prerequisites are met.
Configure the restore parameters.
The maximum retention period for both data backup files and log backup files is 730 days. To check the restorable time range for your instance, call the DescribeLocalAvailableRecoveryTime API operation.
Parameter Description Restore To The destination instance: the original instance, or another instance in the same region with the same major engine version. Restore Speed Fixed as Standard. Restore Method By Backup Set: restore from a specific backup set. By Time: available only when log backup is enabled. The system replays full backup data followed by incremental log backup data, letting you restore to any point within the log backup retention period. For example, if both the data backup retention period and log backup retention period are 7 days, you can restore to any point in the last 7 days. Select the databases to restore, then click OK.
Select up to 50 databases at a time.
The system appends
_backupto each restored database name (for example,mydbbecomesmydb_backup). You can change the name before confirming.Make sure the destination instance has enough available storage to hold all selected databases.
Verify the restore
After the restore task completes, go to the Databases page of the destination instance to confirm the restored databases appear. Verify that:
The restored databases are accessible and the data looks correct.
Connection strings pointing to the restored data use the correct database name (the
_backupversion, unless you renamed it during restore).
FAQ
The console shows "The operation failed. The RDS instance is not in a ready state." What should I do?
Restore tasks run sequentially. This error means another restore task is still in progress or incomplete. Wait for the current task to finish, then retry.
The restored database is empty. Why?
The original database had no data at the point in time you selected. Choose a point in time when the database contained data.
Are there other ways to restore individual databases?
You can use Data Disaster Recovery to back up and restore RDS instances and self-managed databases on Elastic Compute Service (ECS) instances, and to download backup sets locally. See Overview and Restore data by database or table.
What's next
Restore all data of an ApsaraDB RDS for PostgreSQL instance — restore the entire instance instead of individual databases.
Use pg_restore to restore data from a logical backup file — restore specific tables from a logical backup using pg_restore.
Restore data to a self-managed PostgreSQL instance — export backup data to a self-managed PostgreSQL instance using a CSV file or an SQL file.