PolarDB:Migrate data from an RDS PostgreSQL instance to a PolarDB for PostgreSQL cluster

To migrate data from a self-managed PostgreSQL database, see Migrate self-managed PostgreSQL to PolarDB for PostgreSQL.

Scope of application

• RDS PostgreSQL instances: Serverless instances are not supported for migration.

• Storage capacity: The storage capacity of the PolarDB for PostgreSQL cluster must be greater than that of the RDS PostgreSQL database.

Precautions

This operation performs a full data migration. To avoid data inconsistency, stop all services on the RDS PostgreSQL database and stop all writes before you start the migration.

Preparations

1. Create a Linux ECS instance. This example uses an Ubuntu 16.04 64-bit ECS instance. For more information, see Create an ECS instance.

   Note

   • The ECS instance must have network connectivity to both the source RDS PostgreSQL database and the destination PolarDB for PostgreSQL cluster.

   • You can create a pay-as-you-go ECS instance and release it after the migration is complete.

2. Install a PostgreSQL client on the ECS instance to run data restoration commands. For more information, see PostgreSQL official documentation.

   Note

   Make sure that the PostgreSQL client version is compatible with both the RDS PostgreSQL database and the PolarDB for PostgreSQL cluster.

Step 1: Back up the RDS PostgreSQL database

This operation performs a full data migration. To avoid data inconsistency, stop all services on the RDS PostgreSQL database and stop all writes before you start the migration.

1. Run the following command on the ECS instance to back up the database.

   pg_dump -U <username> -h <hostname> -p <port> <dbname> -Fd -j <njobs> -f <dumpdir>

   Parameters:

   • <username>: The account used to log on to the RDS PostgreSQL database.

   • <hostname>: The endpoint of the RDS PostgreSQL database. For more information, see View and change endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

   • <port>: The port number of the database service.

   • <dbname>: The name of the database to connect to. Default value: postgres.

   • <njobs>: The number of concurrent backup jobs.

     Note

     • The <njobs> parameter reduces the dump time but increases the load on the database server.

     • If your RDS PostgreSQL database version is earlier than 9.2, you must also specify the --no-synchronized-snapshots parameter.

   • <dumpdir>: The directory where the backup files are saved.

   Example:

   pg_dump -U postgres -h pgm-xxxxxxxxx.pg.rds.aliyuncs.com -p 5432 postgres -Fd -j 5 -f postgresdump

2. When prompted for Password:, enter the password for the database account. The backup starts.

3. Wait for the backup to complete. The RDS PostgreSQL database data is backed up to the specified directory, which is postgresdump in this example.

Step 2: Restore data to the PolarDB for PostgreSQL

1. Create an account in the destination PolarDB for PostgreSQL cluster. For more information, see Create a database account and Account permissions.

2. Create a database in the destination PolarDB for PostgreSQL cluster for data restoration. Specify the database owner as the account you created in the previous step. For more information, see Create a database.

3. Run the following command on the ECS instance to restore data from the RDS PostgreSQL database to the PolarDB for PostgreSQL cluster.

   pg_restore -U <username> -h <hostname> -p <port> -d <dbname> -j <njobs> <dumpdir>

   Parameters:

   • <username>: The account used to log on to the PolarDB for PostgreSQL database.

   • <hostname>: The endpoint of the PolarDB for PostgreSQL cluster.

   • <port>: The port number of the database service. For more information, see View endpoints and port numbers.

   • <dbname>: The name of the target database to restore to.

   • <njobs>: The number of concurrent restoration jobs.

     Note

     This option reduces the restoration time but increases the load on the database server.

   • <dumpdir>: The directory where the backup files are 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. When prompted for Password:, enter the password for the database account. The data migration starts.

   Note

   If you have forgotten the password, see Manage database accounts.

Wait for the data migration to complete.