This topic describes how to migrate data from an ApsaraDB RDS for PostgreSQL database to PolarDB for PostgreSQL by running the pg_dump and pg_restore commands.

For details about how to migrate data from a user-created PostgreSQL database, see Migrate data from a user-created PostgreSQL database to PolarDB for PostgreSQL.

Prerequisites

The storage capacity of the PolarDB for PostgreSQL instance must be greater than that of the ApsaraDB RDS for PostgreSQL instance.

Precautions

This is a full migration. To avoid inconsistencies in data, stop the services related to the ApsaraDB RDS for PostgreSQL database and stop data writing before migration.

Preparations

  1. Create a Linux ECS instance. This example uses an ECS instance running 64-bit Ubuntu 16.04. For more information, see Create an ECS instance.
    Note
    • The ECS instance and the destination PolarDB for PostgreSQL instance must be in the same VPC.
    • You can create a pay-as-you-go ECS instance and release it after the migration.
  2. Install PostgreSQL on the ECS instance to run the data restoration commands. For more information, see PostgreSQL official documentation.
    Note Ensure that the version of the installed PostgreSQL database is the same as that of the ApsaraDB RDS for PostgreSQL database.

Step 1: Back up the ApsaraDB RDS for PostgreSQL database

This is a full migration. To avoid inconsistencies in data, stop the services related to the ApsaraDB RDS for PostgreSQL database and stop data writing before migration.

  1. Run the following command on the ECS instance to back up data in the database.
    pg_dump -U <username> -h <hostname> -p <port> <dbname> -Fd -j <njobs> -f <dumpdir>

    Parameter description:

    • <username>: the account used to log on to the ApsaraDB RDS for PostgreSQL database.
    • <hostname>: the endpoint of the ApsaraDB RDS for PostgreSQL database. localhost can be used for a local host.
    • <port>: the port number of the database service.
    • <dbname>: the name of the database to connect to. The default value is postgres.
    • <njobs>: the number of concurrent backup jobs.
      Note
      • Specifying the <njobs> parameter can shorten the dump time, but it also increases the load on the database server.
      • If your ApsaraDB RDS for PostgreSQL database is earlier than 9.2, you must specify the --no-synchronized-snapshots parameter.
    • <dumpdir>: the directory of the generated backup file.
    Example:
    pg_dump -U postgres -h localhost -p 5432 postgres -Fd -j 5 -f postgresdump
  2. Enter the password in the Password: prompt to start data backup.
  3. Wait until the backup is completed. The data in the PostgreSQL database is backed up to the specified directory. In this example, the data is stored in the postgresdump directory.

Step 2: Migrate data to PolarDB for PostgreSQL

  1. Connect to the PolarDB for PostgreSQL database from the ECS instance.
    psql -U <username> -h <hostname> -p <port> -d <dbname>

    Parameter description:

    • <username>: the account used to log on to the PolarDB for PostgreSQL database.
    • <hostname>: the primary endpoint (private network) of the PolarDB for PostgreSQL instance. For more information, see View or apply for an endpoint.
    • <port>: the port number of the database service. The default value is 1921.
    • <dbname>: the name of the database to connect to.

    Example:

    psql -h pc-mxxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -p 3433 -d postgres -U gctest
  2. Create a role in the destination PolarDB for PostgreSQL instance based on the role information in the source ApsaraDB RDS for PostgreSQL database and grant permissions to the destination database for data restoration. For more information, see CREATE ROLE and GRANT in official documentation.
  3. Run the following command on the ECS instance to migrate data of the source database to the PolarDB for PostgreSQL instance.
    pg_restore -U <username> -h <hostname> -p <port> -d <dbname> -j <njobs> <dumpdir>

    Parameter description:

    • <username>: the account used to log on to the PolarDB for PostgreSQL database.
    • <hostname>: the primary endpoint (private network) of the PolarDB for PostgreSQL instance.
    • <port>: the port number of the database service. The default value is 1921.
    • <dbname>: the name of the destination database to connect to and restore data.
      Note A destination database must be available. If not, create a database in the destination instance.
    • <njobs>: the number of concurrent data restoration jobs.
      Note Specifying this parameter can shorten data restoration time, but it also increases the load on the database server.
    • <dumpdir>: the directory where the backup file is located.

    Example:

    pg_restore -U gctest -h pc-mxxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -p 1921 -d postgres -j 6 postgresdump
  4. Enter the password in the Password: prompt to start data migration.
    Note For details about how to change the password if you forget your password, see Manage a database account.

Wait until the data migration is complete.