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

Prerequisites

The available storage space of the PolarDB for PostgreSQL cluster is larger than that of the ApsaraDB RDS for PostgreSQL instance.

Usage notes

Full data is migrated in this procedure. To prevent data inconsistency before and after migration, stop business activities and do not write data to the ApsaraDB RDS for PostgreSQL instance during 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, source ApsaraDB RDS for PostgreSQL instance, and destination PolarDB for PostgreSQL cluster must reside in the same virtual private cloud (VPC).

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

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

   Note

   Make sure that the installed PostgreSQL client matches the version of the source ApsaraDB RDS for PostgreSQL instance and the destination PolarDB for PostgreSQL cluster.

Step 1: Back up the data of the ApsaraDB RDS for PostgreSQL instance

Full data is migrated in this procedure. To prevent data inconsistency before and after migration, stop business activities and do not write data to the ApsaraDB RDS for PostgreSQL instance during the migration.

1. Run the following command on the ECS instance to back up the data of the ApsaraDB RDS for PostgreSQL instance:

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

   Parameters:

   • <username>: the account username used to log on to the ApsaraDB RDS for PostgreSQL instance.

   • <hostname>: the endpoint of the ApsaraDB RDS for PostgreSQL instance. For information about how to view the endpoint of a database, see View and change the endpoints and port numbers.

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

   • <dbname>: the name of the database to which you want to connect. Default value: postgres.

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

     Note

     • Concurrent backup jobs can reduce the required time to back up data but increase the load on the database server.

     • If the version of the ApsaraDB RDS for PostgreSQL instance is earlier than 9.2, you must specify the --no-synchronized-snapshots parameter.

   • <dumpdir>: the directory in which the generated backup file is stored.

   Example:

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

2. When the Enter password: prompt is displayed, enter the password of the database to start the backup process.

3. Wait until the backup process is complete. The data in the ApsaraDB RDS for PostgreSQL instance is backed up to the specified directory. In this example, the data is stored in the postgresdump directory.

Step 2: Migrate data to the PolarDB for PostgreSQL cluster

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 for data restoration in the destination PolarDB for PostgreSQL cluster. Set the owner of the database to the account that you created in the previous step. For more information, see Create a database.

3. Run the following command on the ECS instance to migrate data from the ApsaraDB RDS for PostgreSQL instance to the PolarDB for PostgreSQL cluster:

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

   Parameters:

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

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

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

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

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

     Note

     Concurrent data restoration jobs can reduce the amount of time that is required to restore data but increase the load on the database server.

   • <dumpdir>: the directory in which the backup file is stored.

   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 the Enter password: prompt is displayed, enter the password of the database to start the data migration.

   Note

   If you forget the password of the database, see Manage database accounts.

Wait until the data migration is complete.