Use pg_dump and pg_restore to migrate self-managed PostgreSQL to RDS for PostgreSQL - ApsaraDB RDS

This guide shows how to migrate a single database from a self-managed PostgreSQL instance to ApsaraDB RDS for PostgreSQL using pg_dump and pg_restore.

When to use this approach: Use this approach if you are familiar with pg_dump and pg_restore and need to migrate a single database. For other scenarios:

Multiple databases: Use Data Transmission Service (DTS).
Entire instance: Use the cloud migration feature of ApsaraDB RDS for PostgreSQL.

Prerequisites

Before you begin, ensure that you have:

An ApsaraDB RDS for PostgreSQL instance running the same PostgreSQL version as your self-managed instance. For details, see Create an ApsaraDB RDS for PostgreSQL instance
A privileged account on the RDS instance. For details, see Create an account
An administrator account with the superuser role on the self-managed PostgreSQL instance
The endpoint and port number of the RDS instance. For details, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance
The RDS instance whitelist configured to allow connections from the server hosting your self-managed instance. For details, see Configure an IP address whitelist
pg_dump and pg_restore installed on the server hosting your self-managed instance. Run pg_dump --version and pg_restore --version to confirm

You can migrate across PostgreSQL versions, but the same version is strongly recommended to avoid compatibility issues. To check the version of your self-managed instance, run psql --version. After migration, you can upgrade the major engine version of your RDS instance. For details, see Upgrade the major engine version.

If your self-managed instance runs on an Elastic Compute Service (ECS) instance, create the RDS instance in the same region and virtual private cloud (VPC). Data migrated over an internal network is faster and more secure. Use the internal endpoint of the RDS instance in this case; otherwise, use the public endpoint.

Step 1: Check the source database

Connect to your self-managed PostgreSQL instance. In the psql command line, run \l to list all databases and note the Owner, Encoding, Collate, and Ctype values for the database you want to migrate.

In this example, the source database is testdb:

                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =Tc/postgres         +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | testuser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

Record these values — you will need them in Step 3.

Step 2: Migrate users

RDS does not support the PostgreSQL SUPERUSER privilege directly. Before migrating data, recreate the source database owner on the RDS instance with the rds_SUPERUSER role.

On the server hosting your self-managed instance, export the user definitions:

pg_dumpall -g -h 127.0.0.1 -p 5432 -U testuser

The output includes a Roles section. For testuser, it looks similar to:

CREATE ROLE testuser;
ALTER ROLE testuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0IEvNqWupQsGcaoDS3SX****3U=';

Connect to the RDS instance using the privileged account. For details, see Connect to an ApsaraDB RDS for PostgreSQL instance.

Replace SUPERUSER with rds_SUPERUSER in the role statement, then run it on the RDS instance:

CREATE ROLE testuser;
ALTER ROLE testuser WITH rds_SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0IEvNqWupQsGcaoDS3SX****3U=';

Step 3: Create the destination database

On the RDS instance, create a database with the same character set, Collate, and Ctype values as the source database recorded in Step 1.

CREATE DATABASE <destination-database-name> WITH OWNER <database-owner> TEMPLATE template0 ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';

Replace the placeholders with actual values:

Placeholder	Description	Example
`<destination-database-name>`	Name of the new database on RDS (can differ from the source name)	`testdb_rds`
`<database-owner>`	Must match the owner of the source database	`testuser`

Step 4: Migrate data

Choose the method that fits your network setup:

Method	When to use
Online migration	The server hosting your self-managed instance can reach the RDS instance directly
Offline migration	No direct network path between the two; export first, then copy and import

Online migration

All steps run on the server hosting your self-managed instance.

Create and configure the .pgpass password file so pg_dump and psql can authenticate without interactive prompts. For details on the password file format, see The Password File.
```
vi ~/.pgpass
```
Add the following two lines. The format is host:port:dbname:username:password. Values for dbname and username must be lowercase unless the names were created with double-quoted mixed-case identifiers.
```
<RDS-endpoint>:5432:<destination-database-name>:<destination-database-owner>:<password>
127.0.0.1:5432:<source-database-name>:<superuser>:<password>
```
For example, if the RDS endpoint is rm-bp1xxxx.pg.rds.aliyuncs.com, the destination database is testdb_rds, and the source database is testdb:
```
rm-bp1xxxx.pg.rds.aliyuncs.com:5432:testdb_rds:testuser:YourPassword
127.0.0.1:5432:testdb:testuser:YourPassword
```
Set the required file permissions:
```
chmod 700 ~/.pgpass
```

Run the migration. This command streams the export directly into the RDS instance:

nohup pg_dump -F p -h 127.0.0.1 -p 5432 -U <superuser> -d <source-database-name> --no-tablespaces \
  | time psql -h <RDS-endpoint> -p 5432 -U <destination-database-owner> --single-transaction <destination-database-name> \
  > ./pg.dump.log 2>&1 &

After the command completes, check the log file. An empty pg.dump.log means the migration succeeded.
```
cat ./pg.dump.log
```

The --single-transaction flag wraps all import operations in a single transaction. If any operation fails, the entire import rolls back, letting you resolve the issue and re-import cleanly.

Offline migration

Use this method when the two servers cannot connect directly. Export the database to a file, copy the file to a host that can reach the RDS instance, then import.

Configure the RDS whitelist to allow the intermediate host before importing. For details, see Configure an IP address whitelist.

Export the database

On the server hosting your self-managed instance:

nohup pg_dump -F c -h 127.0.0.1 -p 5432 -U <superuser> -d <source-database-name> --no-tablespaces \
  -f ./pg.dump > ./pg.dump.log 2>&1 &

Wait for the export to finish, then confirm pg.dump.log contains no errors.

Import the database

Copy pg.dump to a host that can reach the RDS instance, then choose one of the following import methods:

Option 1: Single-thread import

pg_restore -h <RDS-endpoint> -p 5432 -U <destination-database-owner> \
  -d <destination-database-name> --no-tablespaces --single-transaction \
  pg.dump > ./pg.restore.log

An empty pg.restore.log means the import succeeded.

The --single-transaction flag ensures the import is atomic. On failure, all changes roll back and you can re-import after fixing the issue.

Option 2: Multi-thread parallel import

For large databases, parallel import can reduce migration time significantly:

pg_restore -h <RDS-endpoint> -p 5432 -U <destination-database-owner> \
  -d <destination-database-name> --no-tablespaces \
  -j <number-of-threads> pg.dump > ./pg.restore.log 2>&1 &

Set -j to a positive integer. A value of 2–4 times the number of CPU cores on the host is a good starting point — enough to use available resources without causing contention.

--single-transaction and -j cannot be used together. If the parallel import fails, clear all tables in the destination database and re-import to ensure data consistency.

Verify the migration

After the import completes, log in to the RDS instance and confirm that the migrated tables, data, and row counts match the source database.

What's next

To upgrade the PostgreSQL version, see Upgrade the major engine version.

ApsaraDB RDS:Use pg_dump and pg_restore to migrate data from a self-managed PostgreSQL instance to an ApsaraDB RDS for PostgreSQL instance