You can use logical backup files to migrate data from an ApsaraDB RDS for PPAS instance to an on-premises PPAS database.

Procedure

  1. Connect the PostgreSQL client to ApsaraDB RDS for PPAS.
  2. Run the following command to back up data:
    pg_dump -U username -h hostname -p port databasename -f filename
    The parameters are described as follows:
    • username: the username that is used to log on to the ApsaraDB RDS for PPAS database.
    • hostname: the hostname of the ApsaraDB RDS for PPAS database.
    • port: the port number of the ApsaraDB RDS for PPAS database.
    • databasename: the name of the ApsaraDB RDS for PPAS database that you want to back up.
    • filename: the name of the backup file to be generated. Example:
      pg_dump -U ppas_user -h rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com -p 3433 edb -f ppas.sql
  3. Save the ppas.sql backup file to the destination 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 parameters are described as follows:
    • username: the username that is used to log on to the on-premises database.
    • hostname: the hostname of the on-premises database.
    • port: the port number of the on-premises database.
    • databasename: the name of the on-premises database.
    • filename: the name of the backup file. Example:
      psql -U ppas_user -h localhost -d edb -p 5444 -f ppas.sql
      Permission settings of the ApsaraDB RDS for PPAS database are different from those of the on-premises database. Therefore, some permission-related warnings or errors may occur during data import. Examples of warnings or errors that can be ignored are as follows:
      WARNING:  no privileges could be revoked for "xxxxx"
      ERROR:  role "xxxxx" does not exist