Migrating data from ApsaraDB RDS PostgreSQL to an on-premises database by using logical backup files is supported.

Procedure

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

    The following list describes the parameters:

    • username: The username to log on to the ApsaraDB RDS PostgreSQL database.
    • hostname: The hostname of the ApsaraDB RDS PostgreSQL database.
    • port: The port number of the ApsaraDB RDS PostgreSQL database.
    • databasename: The ApsaraDB RDS PostgreSQL database name that you want to back up.
    • filename: The name of the backup file to be generated.

    For example,

    pg_dump -U myuser -h rds2z2tp80v3752wb455.pg.rds.aliyuncs.com -p 3433 pg001 -f pg001.sql
  3. Save the pg001.sql backup file to the target server.
  4. Run the following command to restore data to the on-premises database:
    psql -U username -h hostname -d desintationdb -p port -f dumpfilename.sql

    The following list describes the parameters:

    • username: The username to log on to the on-premises PostgreSQL database.
    • hostname: The hostname of the on-premises PostgreSQL database.
    • port: The port number of the on-premises PostgreSQL database.
    • desintationdb: The database name of the on-premises PostgreSQL database.
    • dumpfilename: The name of the backup file to be restored.

    For example,

    psql -U myuser -h localhost -d pg001 -p 5432 -f pg001.sql

    Due to the permission settings of the ApsaraDB RDS PostgreSQL database are different from those of the on-premises database, some permission-related warnings or errors may occur during the data import. These warnings and errors can be ignored, for example,

    WARNING: No privileges could be revoked for "xxxxx".
    ERROR: Role "xxxxx" does not exist.