Use pg_restore to restore logical backup data - ApsaraDB RDS

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

Note

For small amounts of data, such as the data in a single table, you can use a logical backup for a quick recovery. For large amounts of data, we recommend restoring data from a full physical backup to a new instance and then using Data Transmission Service (DTS) to migrate the data back to the original instance.
For more information about data recovery methods, see Recover.

Prerequisites

You have created a logical backup of your PostgreSQL data.

Usage notes

Do not connect to the default postgres database when you restore data.
Restoring a specific table does not restore other database objects that the table depends on. Therefore, the operation may not succeed when restoring to a clean database.

Restore a database

Log on to the ECS instance or on-premises host where the backup data is stored and run the following command to restore the database.

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

Parameter	Description
hostname	The endpoint of the ApsaraDB RDS for PostgreSQL instance. Note To connect from an ECS instance by using an internal endpoint, ensure that the ECS instance and the ApsaraDB RDS for PostgreSQL instance use the same network type. If both instances are in a VPC, they must reside in the same VPC. To connect from an on-premises host or an ECS instance by using a public endpoint, ensure that a public endpoint is enabled for the instance.
username	The username of the privileged account for the ApsaraDB RDS for PostgreSQL instance.
port	The port number of the ApsaraDB RDS for PostgreSQL instance.
dbname	The name of the database that you want to restore.
-c (optional)	The `-c` option drops existing database objects before recreating them. For more information about other options, see the official pg_restore documentation.
dumpdir	The path and name of the backup file.

Example:

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

When prompted for Password:, enter the password for the instance's privileged account and press Enter.
```
[root@iZbpxxx ~]# pg_restore -h 'pgm-xxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht02 -c /tmp/testdb.dump
Password:
[root@iZbpxxx ~]#
```
Note
You can ignore any warnings about the built-in plpgsql extension.

Restore a specific table

Log on to the ECS instance or on-premises host where the backup data is stored and run the following command to restore a specific table.

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

Parameter	Description
hostname	The endpoint of the ApsaraDB RDS for PostgreSQL instance. Note To connect from an ECS instance by using an internal endpoint, ensure that the ECS instance and the ApsaraDB RDS for PostgreSQL instance use the same network type. If both instances are in a VPC, they must reside in the same VPC. To connect from an on-premises host or an ECS instance by using a public endpoint, ensure that a public endpoint is enabled for the instance.
username	The username of the privileged account for the ApsaraDB RDS for PostgreSQL instance.
port	The port number of the ApsaraDB RDS for PostgreSQL instance.
dbname	The name of the database that you want to restore.
table	The name of the table that you want to restore.
-c (optional)	The `-c` option drops existing database objects before recreating them. For more information about other options, see the official pg_restore documentation.
dumpdir	The path and name of the backup file.

Example:

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

When prompted for Password:, enter the password for the instance's privileged account and press Enter.

[root@iZbpxxx ~]# pg_restore -h 'pgm-bpxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht01 -t zhttest0808 -c /tmp/testdb2.dump
Password:
[root@iZbpxxx ~]#

Restore a database schema only

Log on to the ECS instance or on-premises host where the backup data is stored and run the following command to restore only the database schema.

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

Parameter	Description
hostname	The endpoint of the ApsaraDB RDS for PostgreSQL instance. Note To connect from an ECS instance by using an internal endpoint, ensure that the ECS instance and the ApsaraDB RDS for PostgreSQL instance use the same network type. If both instances are in a VPC, they must reside in the same VPC. To connect from an on-premises host or an ECS instance by using a public endpoint, ensure that a public endpoint is enabled for the instance.
username	The username of the privileged account for the ApsaraDB RDS for PostgreSQL instance.
port	The port number of the ApsaraDB RDS for PostgreSQL instance.
dbname	The name of the target database.
-s	The `-s` option restores only the schema (data definition), not the data. For more information about other options, see the official pg_restore documentation.
dumpdir	The path and name of the backup file.

Example:

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

When prompted for Password:, enter the password for the instance's privileged account and press Enter.
```
[root@iZbpxxx ~]# pg_restore -h 'pgm-bpxxx.pg.rds.aliyuncs.com' -U zht -p 5432 -d zht03 -s /tmp/testdb2.dump
Password:
[root@iZbpxxx ~]#
```
Note
You can ignore any warnings about the built-in plpgsql extension.