Use the psql command to migrating PostgreSQL data

Last Updated: May 10, 2017

This example describes how to use the psql command to restore the PostgreSQL data backup file to the target RDS.

Background information

PostgreSQL supports logical backup. We use the pg_dump logical backup function to export the backup file and then import it to the RDS through psql, thus importing the PostgreSQL data to the RDS.

Prerequisites

If you haven’t created the RDS instance database, please refer to Configuring the Connection Mode and Creating a Database and an Account before continue.

Preparation of local data

  1. Connect to the local PostgreSQL database through the PostgreSQL client.
  2. Run the following command to back up the data.

    1. pg_dump -U username -h hostname -p port databasename -f filename

    Parameters are described as follows:

    • username: User name for the local database
    • hostname: The local database host name; localhost can be used if you log in 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 back up the local PostgreSQL database, the database user William logs in to the PostgreSQL host and runs the command below to back up data.

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

Performing migration

Note: The network stability and data security will be improved when data is restored through the RDS Intranet. You are advised to upload the data to the ECS and then restore the data to the target RDS through the Intranet. If the data file is too large, compress it before uploading. This scenario is explained in the following example:

  1. Log in to the ECS.
  2. Run the following command through the PostgreSQL client to import the data into the RDS.

    1. psql -U username -h hostname -d desintationdb -p port -f dumpfilename.sql

    Parameters are described as follows:

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

    For example:

    1. 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:

    1. WARNING: no privileges could be revoked for "xxxxx"
    2. ERROR: role "xxxxx" does not exist
Thank you! We've received your feedback.