This document describes how to use psql commands to restore the PostgreSQL data backup file to the target RDS.

Background information

PostgreSQL supports logical backup. To import PostgreSQL data, use the pg_dump logical backup function to export backup files and then import the files to the RDS through psql.

Prerequisite

You have set a whitelist, applied for an Internet IP address, and created databases and accounts for the RDS instance. For more information, see Quick Start.

Prepare local data

  1. Connect to the local PostgreSQL database through the PostgreSQL client.
  2. Run the following command to back up data:
    pg_dump -U username -h hostname -p port databasename -f filename

    Parameters are described as follows:

    • username: User name for the local database
    • hostname: Local database host name. localhost can be used if you log on to the local database host.
    • port: Local database port number
    • databasename: Name of the local database to be backed up
    • filename: Name of the backup file to be generated

    For example, to use the database account William to back up the local PostgreSQL database,  log on to the PostgreSQL host and run the following command:

    pg_dump -U William -h localhost -p 3433 pg001 -f pg001.sql

Migrate data

Note
Network stability and data security are improved when data is restored through the intranet. We recommend that you upload the data to ECS and then restore the data to the target RDS through the intranet. If a data file is too large, compress it before uploading. This scenario is explained in the following example:
  1. Log on to ECS.
  2. Run the following command through the PostgreSQL client to import data into the RDS:
    psql -U username -h hostname -d desintationdb -p port -f dumpfilename.sql

    Parameters are described as follows:

    • username: PostgreSQL database user name on the RDS
    • hostname: PostgreSQL database address on the RDS
    • port: PostgreSQL database port number on the RDS
    • databasename: PostgreSQL database name on the RDS
    • filename: Local backup data file name

    For example:

    psql -U William -h postgresql.rds.aliyuncs.com -d pg001 -p 3433 -f pg001.sql

    Since the permission configuration of the RDS database is inconsistent with that of the local database, some permission-related warnings or errors may occur during data import. They can be ignored, for example:

    WARNING: no privileges could be revoked for "xxxxx"
    ERROR: role "xxxxx" does not exist