Use pg_restore to restore data from a .dump logical backup file to an ApsaraDB RDS for PostgreSQL instance or an on-premises PostgreSQL database.
For small amounts of data, such as a single table, logical backup restore is straightforward. For large datasets, restore from a full physical backup to a new RDS instance, then use Data Transmission Service (DTS) to migrate the data to the original instance. For all restore methods, see Restoration.
Prerequisites
Before you begin, ensure that you have:
A logical backup created for your RDS instance. See Create a logical backup for an ApsaraDB RDS for PostgreSQL instance
The
.dumpbackup file available on the Elastic Compute Service (ECS) instance or on-premises host from which you will runpg_restore
Usage notes
Do not restore data to the default
postgresdatabase.When restoring a single table,
pg_restoredoes not restore the database objects that the table depends on. If those dependencies are missing in the target database, the restore may fail.
Restore a database
Log on to the ECS instance or on-premises host that stores the backup file and run:
pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -c <dumpdir>| Parameter | Description |
|---|---|
hostname | The endpoint used to connect to the RDS instance. To use an internal endpoint, the ECS instance and the RDS instance must use the same network type. If both use the virtual private cloud (VPC) network type, they must be in the same VPC. See View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. To use a public endpoint, allocate one first. See Apply for or release a public endpoint on an ApsaraDB RDS for PostgreSQL instance. |
username | The username of the privileged account of the RDS instance. |
port | The port used to connect to the RDS instance. |
dbname | The name of the target database. |
-c (optional) | Drop database objects before recreating them. See pg_restore. |
dumpdir | The path to the logical backup file. |
Example:
pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht02 -c /tmp/testdb.dumpWhen prompted with Password:, enter the password of the privileged account and press Enter.
Ignore alerts from the built-in plpgsql extension.
Restore a table
Log on to the ECS instance or on-premises host that stores the backup file and run:
pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -t <table> -c <dumpdir>The -t <table> flag specifies the table to restore. All other parameters are the same as in Restore a database.
| Parameter | Description |
|---|---|
table | The name of the table to restore. |
Example:
pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht01 -t zhttest0808 -c /tmp/testdb2.dumpWhen prompted with Password:, enter the password of the privileged account and press Enter.
When restoring a single table, database objects that the table depends on are not restored. Make sure those objects already exist in the target database, or the restore may fail.
Restore a database schema
To restore only the schema of a database without restoring any data, log on to the ECS instance or on-premises host and run:
pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -s <dumpdir>The -s flag restores only the schema without loading any data. All other parameters are the same as in Restore a database.
Example:
pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht03 -s /tmp/testdb2.dumpWhen prompted with Password:, enter the password of the privileged account and press Enter.
Ignore alerts from the built-in plpgsql extension.