This topic describes how to restore data from a logical backup file to an RDS PostgreSQL instance or to an on-premises PostgreSQL database.

Background information

A logical backup file is used to restore a small volume of data, the data of a table for example. For a large volume of data, we recommend that you restore it from a full physical backup file to a new RDS instance and then use Alibaba Cloud Data Transmission Service (DTS) to migrate the data to the original RDS instance.

Prerequisites

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

Precautions

  • We recommend that you do not restore data to the default postgres database.
  • When you restore the data of a specific table, the system does not try to restore the database objects on which the table depends. Therefore, the restoration to a clean database may fail.

Restore the data of a database

  1. 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 database:
    pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> <dumpdir>
    Parameter Description
    hostname The endpoint used to connect to your RDS instance.
    Note
    • If the ECS instance connects to your RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC.
    • If the on-premises host or ECS instance connects to your RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to your RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of your RDS instance.
    port The port used to connect to your RDS instance.
    dbname The name of the database whose data you want to restore.
    dumpdir The directory and name of the logical backup file to use.

    Example:

    pg_restore -h 'pgm-bpxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -d testdb2 /tmp/testdb.dump
  2. When Password: appears, enter the password of the privileged account of your RDS instance and press Enter.
    Note You can ignore the alerts generated by the embedded plpgsql plug-in.
    Restore the data of a database

Restore the data of a table

  1. 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 used to connect to your RDS instance.
    Note
    • If the ECS instance connects to your RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC.
    • If the on-premises host or ECS instance connects to your RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to your RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of your RDS instance.
    port The port used to connect to your RDS instance.
    dbname The name of the database that houses the table whose data you want to restore.
    table The name of the table whose data you want to restore.
    -c -c: specifies to delete the database objects on which the table depends before data restoration. For more information, visit pg_restore.
    dumpdir The directory and name of the logical backup file to use.

    Example:

    pg_restore -h 'pgm-bpxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -d testdb2 -t products -c /tmp/testdb.dump
  2. When Password: appears, enter the password of the privileged account of your RDS instance and press Enter.
    Restore the data of a table

Restore the schema of a database with data excluded

  1. Log on to the ECS instance or on-premises host that houses the logical backup file and run the following command to only restore the schema of a database:
    pg_restore -h '<hostname>' -U <username> -p <port> -d <dbname> -s <dumpdir>
    Parameter Description
    hostname The endpoint used to connect to your RDS instance.
    Note
    • If the ECS instance connects to your RDS instance by using an internal endpoint, you must make sure that the ECS and RDS instances have the same network type. If both instances use the VPC network type, you must also make sure that they reside in the same VPC.
    • If the on-premises host or ECS instance connects to your RDS instance by using a public endpoint, you must make sure that a public endpoint has been allocated to your RDS instance. For more information, see Apply for a public endpoint for an RDS PostgreSQL instance.
    username The username of the privileged account of your RDS instance.
    port The port used to connect to your RDS instance.
    dbname The name of the database whose schema you want to restore.
    -s -s: specifies to only restore the schema of the database. The data of the database is not restored. For more information, visit pg_restore.
    dumpdir The directory and name of the logical backup file to use.

    Example:

    pg_restore -h 'pgm-bpxxxxx.pg.rds.aliyuncs.com' -U test123 -p 3433 -d testdb4 -s /tmp/testdb2.dump
  2. When Password: appears, enter the password of the privileged account of your RDS instance and press Enter.
    Note You can ignore the alerts generated by the embedded plpgsql plug-in.
    Restore the schema of a database