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

If the source is a self-managed PostgreSQL database, see Migrate data from a self-managed PostgreSQL database to a PolarDB for PostgreSQL cluster.

Scope

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

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

Usage notes

This is a full data migration. To prevent data inconsistency, stop all services that are connected to the RDS PostgreSQL database and stop writing data before you start the migration.

Preparations

1. Create a Linux Elastic Compute Service (ECS) instance. In this example, an ECS instance that runs 64-bit Ubuntu 16.04 is used. For more information, see Create an ECS instance.

   Note

   • The ECS instance must have network connectivity to 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 the official PostgreSQL documentation.

   Note

   Make sure that the version of the installed PostgreSQL client is compatible with the versions of the source RDS PostgreSQL database and the destination PolarDB for PostgreSQL cluster.

Step 1: Back up the RDS PostgreSQL database

This is a full data migration. To prevent data inconsistency, stop all services that are connected to the RDS PostgreSQL database and stop writing data before you start the migration.

1. On the ECS instance, run the following command to back up the database.

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

   Parameters:

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

   • <hostname>: The endpoint of the RDS PostgreSQL database. To view the database endpoint, see View or change an endpoint and port number.

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

   • <dbname>: The name of the database that you want to connect to. The default value is 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 is a version earlier than 9.2, you must also specify the --no-synchronized-snapshots parameter.

   • <dumpdir>: The directory for the generated backup file.

   Example:

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

2. When prompted for a Password:, enter the password for the database account to start the backup.

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

Step 2: Migrate data to the PolarDB for PostgreSQL cluster

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

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

3. On the ECS instance, run the following command to migrate the 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 database 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 an endpoint and port number.

   • <dbname>: The name of the destination database to which you want to connect and restore data.

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

     Note

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

   • <dumpdir>: The directory that contains the backup file.

   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 a Password:, enter the password for the database account to start the data migration.

   Note

   If you forget the password, see Manage database accounts.

Wait for the data migration to complete.