PolarDB: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 user-created PostgreSQL database.

Precautions

This is a full migration. To avoid inconsistencies in data, stop the services related to the user-created 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 user-created PostgreSQL database.

Step 1: Back up the user-created PostgreSQL database

This is a full migration. To avoid inconsistencies in data, stop the services related to the user-created database and stop data writing before migration.

1. Run the following command on the user-created PostgreSQL database server to back up all the role information in the database.

   pg_dumpall -U <username> -h <hostname> -p <port> -r -f <filename>

   Parameter description:

   • <username>: the account used to log on to the user-created PostgreSQL database.
   • <hostname>: the endpoint of the user-created PostgreSQL database. localhost can be used for a local host.
   • <port>: the port number of the database service.
   • <filename>: the name of the generated backup file.

   Example:

   pg_dumpall -U postgres -h localhost -p 5432 -r -f roleinfo.sql

2. Enter the password in the Password: prompt to start role information backup.
3. Run the vim command to replace SUPERUSER in the role information backup file with polar_superuser.
   Note If the role information backup file does not contain SUPERUSER information, you can skip this step.
   
4. Run the following command to back up data of the user-created PostgreSQL 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 user-created PostgreSQL database.
   • <hostname>: the endpoint of the user-created 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 be backed up.
   • <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 the version of the user-created 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 mytestdata -Fd -j 5 -f postgresdump

5. Enter the password in the Password: prompt to start data backup.
6. 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. Upload the directory of backup files to the ECS instance.
   Note Backup files include role information backup files and database backup files.
2. Run the following command on the ECS instance to migrate role information in backup files to the PolarDB for PostgreSQL instance.

   psql -U <username> -h <hostname>  -p <port> -d <dbname>  -f <filename>

   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 database to connect to. The default value is postgres.
   • <filename>: the name of the role information backup file.

   psql -U gctest -h pc-xxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -d postgres -p 1921 -f roleinfo.sql

3. Enter the password in the Password: prompt to start role information import.
4. Run the following command on the ECS instance to restore data 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. 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 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 mytestdata -j 6 postgresdump

5. 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 Change the password.

Wait until the data migration is complete.