This topic describes how to use the pg_dump and pg_restore commands to migrate an RDS PostgreSQL database to PolarDB for PostgreSQL.
To migrate data from a self-managed PostgreSQL database, see Migrate self-managed PostgreSQL to PolarDB for PostgreSQL.
Scope
RDS PostgreSQL instances: You cannot migrate Serverless instances.
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 the services that run on the RDS PostgreSQL database and stop data writes before you start the migration.
Preparations
Create an ECS instance that runs a Linux operating system. In this example, the ECS instance runs Ubuntu 16.04 64-bit. For more information, see Create an ECS instance.
NoteThe 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.
Install a PostgreSQL client on the ECS instance so that you can run the data restoration commands. For more information, see PostgreSQL official documentation.
NoteMake sure that the version of the PostgreSQL client that you install is compatible with the connected 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 the services that run on the RDS PostgreSQL database and stop data writes before you start the migration.
Run the following command on the ECS instance to back up the data in the database.
pg_dump -U <username> -h <hostname> -p <port> <dbname> -Fd -j <njobs> -f <dumpdir>Parameters:
<username>: The account that is used to log on to the RDS PostgreSQL database.
<hostname>: The endpoint of the RDS PostgreSQL database. To view the database endpoint, 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 which you want to connect. Default value: postgres.
<njobs>: The number of backup jobs that run at the same time.
NoteThe <njobs> parameter reduces the dump time but increases the load on the database server.
If your RDS PostgreSQL database runs a version earlier than 9.2, you must also specify the
--no-synchronized-snapshotsparameter.
<dumpdir>: The directory where the generated backup files are stored.
Example:
pg_dump -U postgres -h pgm-xxxxxxxxx.pg.rds.aliyuncs.com -p 5432 postgres -Fd -j 5 -f postgresdumpWhen the command line prompts you for
Password:, enter the password of the database account. The backup starts.Wait for the backup to complete. The data of the RDS PostgreSQL database is backed up to the specified directory, which is postgresdump in this example.
Step 2: Migrate data to PolarDB for PostgreSQL
Create an account in the destination PolarDB for PostgreSQL cluster. For more information, see Create a database account and Account permissions.
Create a database for data restoration in the destination PolarDB for PostgreSQL cluster. Set the database owner to the account that you created in the previous step. For more information, see Create Database.
Run the following command on the ECS instance to migrate the data of 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 that is 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 destination database to which data is restored.
<njobs>: The number of data restoration jobs that run at the same time.
NoteThis option reduces the data 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 postgresdumpWhen the command line prompts you for
Password:, enter the password of the database account. The data migration starts.NoteIf you forget the password, see Manage database accounts.
Wait for the data migration to complete.