Use pg_restore to restore data from a logical backup file - ApsaraDB RDS

This topic describes how to use pg_restore to restore data from the logical backup file suffixed with .dump to an ApsaraDB RDS for PostgreSQL instance or an on-premises PostgreSQL database.

Note

For a small amount of data, such as data in a table, we recommend that you use a logical backup file to restore the data. For a large amount of data, we recommend that you restore it from a full physical backup file to a new RDS instance and then use Data Transmission Service (DTS) to migrate data to the original RDS instance. For more information, see Migrate data between ApsaraDB RDS for PostgreSQL instances.
For more information about data restoration methods, see Overview of data restoration methods.

Prerequisites

A logical backup is created for your RDS instance. For more information, see Create a logical backup for an ApsaraDB RDS for PostgreSQL instance.

Usage notes

We recommend that you do not restore data to the default Postgres database.
When you restore the data of a table, the system does not restore the database objects on which the table depends. The restoration may fail.

Restore the data of a database

Log on to the Elastic Compute Service (ECS) instance or on-premises host that houses the logical backup file and run the following command to restore the data of a database:

pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -c <dumpdir>

Parameter	Description
hostname	The endpoint that is used to connect to the RDS instance. Note If you want to connect your ECS instance to the RDS instance by using an internal endpoint, make sure that the ECS instance and the RDS instance use the same network type. If both instances use the virtual private cloud (VPC) network type, make sure that they reside in the same VPC. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. If you want to connect the on-premises host or ECS instance to your RDS instance by using a public endpoint, make sure that a public endpoint is allocated to your RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance and 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 that is used to connect to the RDS instance.
dbname	The name of the database whose data you want to restore.
-c (optional)	`-c` specifies whether to clear the data of the required database before data restoration. For more information, see pg_restore.
dumpdir	The directory and name of the logical backup file.

Example:

pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht02 -c /tmp/testdb.dump

Enter the password of the privileged account of the RDS instance if Password: appears in the CLI, and press the Enter key.
Note
You can ignore the alerts that are generated by the built-in plpgsql extension.

Restore the data of a table

Log on to the ECS instance or on-premises host that houses the logical backup file and run the following command to restore the data of a table:

pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -t <table> -c <dumpdir>

Parameter	Description
hostname	The endpoint that is used to connect to the RDS instance. Note If you want to connect your ECS instance to the RDS instance by using an internal endpoint, make sure that the ECS instance and the RDS instance use the same network type. If both instances use the VPC network type, make sure that they reside in the same VPC. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. If you want to connect the on-premises host or ECS instance to your RDS instance by using a public endpoint, make sure that a public endpoint is allocated to your RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance and 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 that is used to connect to the RDS instance.
dbname	The name of the database whose data you want to restore.
table	The name of the table whose data you want to restore.
-c (optional)	`-c` specifies whether to clear the data of the required database before data restoration. For more information, see pg_restore.
dumpdir	The directory and name of the logical backup file.

Example:

pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht01 -t zhttest0808 -c /tmp/testdb2.dump

Enter the password of the privileged account of the RDS instance if Password: appears in the CLI, and press the Enter key.

Restore the schema of a database with data excluded

Log on to the ECS instance or on-premises host that houses the logical backup file and run the following command to restore only the schema of a database:

pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -s <dumpdir>

Parameter	Description
hostname	The endpoint that is used to connect to the RDS instance. Note If you want to connect your ECS instance to the RDS instance by using an internal endpoint, make sure that the ECS instance and the RDS instance use the same network type. If both instances use the VPC network type, make sure that they reside in the same VPC. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance. If you want to connect the on-premises host or ECS instance to your RDS instance by using a public endpoint, make sure that a public endpoint is allocated to your RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance and 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 that is used to connect to the RDS instance.
dbname	The name of the database whose schema you want to restore.
-s	`-s`: specifies to restore only the schema of the database. The data of the database is not restored. For more information, see pg_restore.
dumpdir	The directory and name of the logical backup file.

Example:

pg_restore -h 'pgm-bpxxxxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht03 -s /tmp/testdb2.dump

Enter the password of the privileged account of the RDS instance if Password: appears in the CLI, and press the Enter key.
Note
You can ignore the alerts that are generated by the embedded plpgsql extension.