All Products
Search
Document Center

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

Last Updated:Mar 25, 2024

This topic describes how to migrate data from an ApsaraDB RDS for PostgreSQL instance to a self-managed PostgreSQL instance by using a logical backup file.

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

FAQ

  • What do I do if the ERROR: role "pg_rds_superuser" does not exist and WARNING: no privileges could be revoked for "xxxxx" error messages are displayed during data restoration?

    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.