Use pg_dumpall, pg_dump, and pg_restore to perform a full offline migration from a self-managed PostgreSQL database to a PolarDB for PostgreSQL cluster through an intermediate Elastic Compute Service (ECS) instance.
To migrate from an ApsaraDB RDS for PostgreSQL instance instead, see Migrate data from an ApsaraDB RDS for PostgreSQL instance to a PolarDB for PostgreSQL cluster.
How it works
The migration follows four steps:
Prepare an ECS instance in the same virtual private cloud (VPC) as the destination cluster.
Back up role information and database data from the source PostgreSQL database.
Upload the backup files to the ECS instance and restore them to the PolarDB for PostgreSQL cluster.
Verify that the data migrated successfully.
Prerequisites
Before you begin, ensure that you have:
A PolarDB for PostgreSQL cluster with available storage larger than the source database size
A Linux ECS instance running 64-bit Ubuntu 16.04 in the same VPC as the destination cluster (you can use a pay-as-you-go instance and release it after migration)
A PostgreSQL client installed on the ECS instance that matches the major version of the source database — see PostgreSQL documentation for installation instructions
Mismatched client versions are a common cause of migration failure. Make sure the client version matches your source database version.
For instructions on creating an ECS instance, see Create an ECS instance.
Usage notes
This procedure performs a full data migration. Stop all write operations on the source database before starting and keep it read-only throughout the migration to prevent data inconsistency.
Step 1: Back up the self-managed PostgreSQL database
Run these commands on the server hosting the self-managed PostgreSQL database.
Back up role information
pg_dump does not export roles. Use pg_dumpall -r to back up all role definitions separately.
pg_dumpall -U <username> -h <hostname> -p <port> -r -f <filename>| Parameter | Description |
|---|---|
<username> | Account used to log on to the source database |
<hostname> | Endpoint of the source database. Use localhost if running the command on the same server |
<port> | Port of the database service |
<filename> | Output file name for the role backup |
Example:
pg_dumpall -U postgres -h localhost -p 5432 -r -f roleinfo.sqlWhen the Password: prompt is displayed, enter the password of the database to start the backup process.
Replace SUPERUSER with polar_superuser
PolarDB for PostgreSQL uses polar_superuser in place of the standard PostgreSQL SUPERUSER privilege. Before restoring roles, update the backup file to reflect this difference.
Run the vim command to replace SUPERUSER in the role information backup file with polar_superuser.
If the backup file contains no SUPERUSER entries, skip this step.
Back up database data
pg_dump -U <username> -h <hostname> -p <port> <dbname> -Fd -j <njobs> -f <dumpdir>| Parameter | Description |
|---|---|
<username> | Account used to log on to the source database |
<hostname> | Endpoint of the source database. Use localhost if running the command on the same server |
<port> | Port of the database service |
<dbname> | Name of the database to back up |
<njobs> | Number of concurrent backup jobs. More jobs reduce backup time but increase server load |
<dumpdir> | Output directory for the backup files |
If the source PostgreSQL database is earlier than version 9.2, add the --no-synchronized-snapshots flag.Example:
pg_dump -U postgres -h localhost -p 5432 mytestdata -Fd -j 5 -f postgresdumpThe backup files are saved to the specified directory (postgresdump in this example).
When the Password: prompt is displayed, enter the password of the database to start the backup process.
Step 2: Migrate data to the PolarDB for PostgreSQL cluster
Run these commands on the ECS instance.
Upload backup files to the ECS instance
Upload the role backup file and the database backup directory to the ECS instance.
Restore role information
psql -U <username> -h <hostname> -p <port> -d <dbname> -f <filename>| Parameter | Description |
|---|---|
<username> | Account used to log on to the PolarDB for PostgreSQL cluster |
<hostname> | Primary private endpoint of the cluster. See View or apply for an endpoint |
<port> | Port of the database service. See View the endpoint and port number |
<dbname> | Name of the destination database |
<filename> | Name of the role backup file |
Example:
psql -U gctest -h pc-xxxxxxxx.pg.polardb.cn-qd-pldb1.rds.aliyuncs.com -d testdb -p 1921 -f roleinfo.sqlWhen the Password: prompt is displayed, enter the password of the database to start the data migration.
Restore database data
pg_restore -U <username> -h <hostname> -p <port> -d <dbname> -j <njobs> <dumpdir>| Parameter | Description |
|---|---|
<username> | Account used to log on to the PolarDB for PostgreSQL cluster |
<hostname> | Primary private endpoint of the cluster. See View or apply for an endpoint |
<port> | Port of the database service. See View the endpoint and port number |
<dbname> | Name of the destination database. The database must already exist in the cluster — create it first if needed |
<njobs> | Number of concurrent restoration jobs. More jobs reduce restoration time but increase server load |
<dumpdir> | Directory containing 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 postgresdumpIf you need to reset the database account password, see Manage a database account.
Wait for the migration to complete before proceeding.
What's next
Resume write operations by pointing your application's connection string to the PolarDB for PostgreSQL cluster endpoint.
If you created a pay-as-you-go ECS instance for this migration, release it to stop incurring charges.