Migrate self-managed PostgreSQL to PolarDB PostgreSQL engine - PolarDB

This topic describes how to use the pg_dumpall, pg_dump, and pg_restore commands to migrate a self-managed PostgreSQL database to .

If your source database is an ApsaraDB RDS for PostgreSQL instance, see Migrate data from an ApsaraDB RDS for PostgreSQL instance to a PolarDB for PostgreSQL cluster.

Prerequisites

The target cluster must have more storage space than your self-managed PostgreSQL database.

Usage notes

This is a full data migration. To prevent data discrepancies, stop all services and write operations on your self-managed database before you start the migration.

Preparations

Create an ECS instance that runs Linux. This tutorial uses an ECS instance that runs 64-bit Ubuntu 16.04. For more information, see Create an ECS instance.
Note
- The ECS instance and the target cluster must be in the same virtual private cloud (VPC).
- You can create a pay-as-you-go ECS instance and release it after the migration is complete.
Install PostgreSQL on the ECS instance to restore data. For more information, see the PostgreSQL official documentation.

Note
Make sure that the version of PostgreSQL you install is the same as the version of your self-managed PostgreSQL database.

Step 1: Back up the self-managed database

On your self-managed PostgreSQL database server, run the following command to back up all role information.
```
pg_dumpall -U <username> -h <hostname> -p <port> -r -f <filename>
```
Parameters:
- <username>: The login account for your self-managed PostgreSQL database.
- <hostname>: The endpoint of your self-managed PostgreSQL database. Use localhost if you run the command on the database server.
- <port>: The database port number.
- <filename>: The name of the output backup file.
Example:
```
pg_dumpall -U postgres -h localhost -p 5432 -r -f roleinfo.sql
```
When prompted for Password:, enter the password for the database account.

Use the vim command to replace SUPERUSER with polar_superuser in the role information backup file.

Note

If the role information backup file does not contain SUPERUSER, you can skip this step.

-- PostgreSQL database cluster dump
--
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE data1;
ALTER ROLE data1 WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5xxx';
CREATE ROLE manisha;
ALTER ROLE manisha WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'mdxxx';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md5xxx';
CREATE ROLE testuser;
ALTER ROLE testuser WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
--
-- PostgreSQL database cluster dump complete

On your self-managed PostgreSQL database server, run the following command to back up the database data.
```
pg_dump -U <username> -h <hostname> -p <port> <dbname> -Fd -j <njobs> -f <dumpdir>
```
Parameters:
- <username>: The login account for your self-managed PostgreSQL database.
- <hostname>: The endpoint of your self-managed PostgreSQL database. Use localhost if you run the command on the database server.
- <port>: The database port number.
- <dbname>: The name of the database to back up.
- <njobs>: The number of concurrent jobs to use for the backup.
  Note
  - A higher value for this parameter reduces the dump time but also increases the load on the database server.
  - If your self-managed PostgreSQL database is a version earlier than 9.2, you must also specify the --no-synchronized-snapshots parameter.
- <dumpdir>: The output directory for the backup files.
Example:
```
pg_dump -U postgres -h localhost -p 5432 mytestdata -Fd -j 5 -f postgresdump
```
When prompted for Password:, enter the password for the database account.
Wait for the backup to complete. The data is backed up to the specified directory, which is postgresdump in this example.

Step 2: Migrate data to

Upload the directory that contains the backup files to the ECS instance.

Note
This includes the role information backup file and the database backup files.
On the ECS instance, run the following command to import the role information from the backup file into the cluster.
```
psql -U <username> -h <hostname>  -p <port> -d <dbname>  -f <filename>
```
Parameters:
- <username>: The login account for the database.
- <hostname>: The primary private endpoint of the cluster.
- <port>: The database port number. For more information, see View the endpoint and port number.
- <dbname>: The name of the database to connect to.
- <filename>: The name of the role information backup file.
```
psql -U gctest -h pc-xxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -d testdb -p 1921 -f roleinfo.sql
```
When prompted for Password:, enter the password for the database account.
On the ECS instance, run the following command to restore the database data to the cluster.
```
pg_restore -U <username> -h <hostname> -p <port> -d <dbname> -j <njobs> <dumpdir>
```
Parameters:
- <username>: The login account for the database.
- <hostname>: The primary private endpoint of the cluster. For more information, see View or apply for an endpoint.
- <port>: The database port number. For more information, see View the endpoint and port number.
- <dbname>: The name of the target database to restore the data to.
  
  Note
  The target database must exist. If it does not, create it first.
- <njobs>: The number of concurrent jobs to use for the restore.
  
  Note
  This option reduces the restore time but also increases the load on the database server.
- <dumpdir>: The directory that contains the backup files.
Example:
```
pg_restore -U gctest -h pc-mxxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -p 1921 -d mytestdata -j 6 postgresdump
```
When prompted for Password:, enter the password for the database account.

Note
If you forgot the password, see Manage a database account.

Wait for the data migration to complete.