ApsaraDB RDS:Migrate data from an ApsaraDB RDS for PostgreSQL instance to a self-managed PostgreSQL instance

Prerequisites

• PostgreSQL is installed on your computer. Your self-managed PostgreSQL instance runs the same major engine version as the RDS instance.

  Note

  In this example, the built-in PostgreSQL tool pg_dump is used to back up data. When you install PostgreSQL on your computer, the tool is automatically installed.

• A database is created in your self-managed PostgreSQL instance and no data is stored in the database.

• The superuser account is created in your self-managed PostgreSQL instance.

Procedure

1. In the CLI of your computer, run the following command to back up data:

   pg_dump -U <username> -h <hostname> -p <port> <databasename> -f <filename> --exclude-table=public.ha_health_check

   The following table describes the parameters in the previous command.

   Parameter	Description
   username	The username of the privileged account for the RDS instance. For more information about how to create a privileged account, see Create an account on an ApsaraDB RDS for PostgreSQL instance.
   hostname	The name of the host on which the RDS instance resides.
   port	The port number that is used to connect to the RDS instance.
   databasename	The name of the database that you want to back up.
   filename	The name that you want to use for the generated backup file.
   --exclude-table=public.ha_health_check	Specifies that the high availability check is skipped.

   Example:

   pg_dump -U testuser -h xxxx.pg.rds.aliyuncs.com -p 5432 testdb -f bkfile.sql --exclude-table=public.ha_health_check

2. Run the following command to restore data from the logical backup file to your self-managed PostgreSQL instance:

   psql -U <username> -h <hostname> -d <databasename> -p <port> -f <filename.sql>

   The following table describes the parameters in the previous command.

   Parameter	Description
   username	The username of the superuser account for your self-managed PostgreSQL instance.
   hostname	The name of the host on which your self-managed PostgreSQL instance resides. Set this parameter to localhost.
   port	The port number that is used to connect to your self-managed PostgreSQL instance.
   databasename	The name of the destination database.
   filename	The name of the backup file.

   Example:

   psql -U testuser -h localhost -d testdb -p 5432 -f bkfile.sql

The RDS instance and the self-managed PostgreSQL instance have different parameter settings. As a result, permission-related warnings or errors may occur when you import data to the self-managed PostgreSQL instance. You can ignore the warnings and errors. Examples:

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