Migrate data from RDS for PostgreSQL to self-managed PostgreSQL using a logical backup - ApsaraDB RDS

Use pg_dump and psql to export a logical backup from your ApsaraDB RDS for PostgreSQL instance and restore it to a self-managed PostgreSQL instance.

Prerequisites

Before you begin, ensure that you have:

PostgreSQL installed on your computer at the same major version as the source RDS instance. The pg_dump and psql client tools must also be the same major version as the source database — using a mismatched client version may cause the migration to fail.
A target database created in your self-managed PostgreSQL instance with no existing data.
A superuser account on your self-managed PostgreSQL instance.
The hostname, port, database name, and privileged account credentials for the source RDS instance.

To create a privileged account on the RDS instance, see Create an account on an ApsaraDB RDS for PostgreSQL instance.

Export data from the RDS instance

Run the following command to export a logical backup from the RDS instance:

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

Parameter	Description
`username`	Username of the privileged account for the RDS instance
`hostname`	Hostname of the RDS instance
`port`	Port number of the RDS instance
`databasename`	Name of the database to export
`filename`	Output file name for the backup (for example, `backup.sql`)
`--exclude-table=public.ha_health_check`	Excludes the high availability check table, which is specific to ApsaraDB RDS

Example:

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

Restore data to the self-managed instance

Run the following command to restore the backup to your self-managed PostgreSQL instance:

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

Parameter	Description
`username`	Username of the superuser account on the self-managed PostgreSQL instance
`hostname`	Hostname of the self-managed instance. Use `localhost` if restoring locally.
`port`	Port number of the self-managed PostgreSQL instance
`databasename`	Name of the target database
`filename.sql`	Path to the backup file generated in the previous step

Example:

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

Expected warnings and errors

Because the RDS instance and the self-managed instance have different internal role configurations, you will see permission-related messages during the restore. These are expected and can be ignored:

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

The ERROR: role "pg_rds_superuser" does not exist message is also expected. The pg_rds_superuser role is internal to ApsaraDB RDS and does not exist in self-managed PostgreSQL instances. The restore completes successfully despite this error.

FAQ

What do I do if `ERROR: role "pg_rds_superuser" does not exist` appears during the restore?

This is expected. The pg_rds_superuser role is an internal ApsaraDB RDS role that does not exist in self-managed PostgreSQL. Ignore this error and verify your data after the restore completes.