This topic describes how to migrate data from a user-created PostgreSQL database to PolarDB-P by running the pg_dump and pg_restore commands.

For details about how to migrate data from an ApsaraDB RDS PostgreSQL database, see Migrate data from a user-created PostgreSQL database to POLARDB for PostgreSQL.

Prerequisites

The storage capacity of the PolarDB-P instance must be greater than that of the ApsaraDB RDS PostgreSQL instance.

Precautions

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

Step 1: Back up the ApsaraDB RDS PostgreSQL database

This is a full migration. To avoid inconsistencies in data, stop the services related to the ApsaraDB RDS PostgreSQL database and stop data writing before migration.

  1. Run the following command on the ECS instance to back up data in the 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 ApsaraDB RDS PostgreSQL database.
    • <hostname>: the endpoint of the ApsaraDB RDS 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 connect to. The default value is postgres.
    • <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 your ApsaraDB RDS 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 postgres -Fd -j 5 -f postgresdump
  2. Enter the password in the Password: prompt to start data backup.
  3. 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-P

  1. Connect to the PolarDB-P database from the ECS instance.
    psql -U <username> -h <hostname> -p <port> -d <dbname>

    Parameter description:

    • <username>: the account used to log on to the PolarDB-P database.
    • <hostname>: the primary endpoint (private network) of the PolarDB-P instance. For more information, see View connection endpoints.
    • <port>: the port number of the database service. The default value is 1921.
    • <dbname>: the name of the database to connect to.

    Example:

    psql -h pc-mxxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -p 3433 -d postgres -U gctest
  2. Create a role in the destination PolarDB-P instance based on the role information in the source ApsaraDB RDS PostgreSQL database and grant permissions to the destination database for data restoration. For more information, see CREATE ROLE and GRANT in official documentation.
  3. Run the following command on the ECS instance to migrate data of the source database to the PolarDB-P 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-P database.
    • <hostname>: the primary endpoint (private network) of the PolarDB-P instance.
    • <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 postgres -j 6 postgresdump
  4. 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 Reset the password of a database account.

Wait until the data migration is complete.