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

Procedure

Step 1: Check the information about the database that you want to migrate

Connect to the self-managed PostgreSQL instance. In the psql command line, run the \l command to query the information about databases and check the values of Owner, Encoding, Collate, and Ctype of the database that you want to migrate.

In this example, the database named testdb is migrated. The following information is returned for the database: Owner is testuser, Encoding is UTF8, Collate is en_US.UTF-8, and Ctype is en_US.UTF-8.

                                                 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)

Step 2: Migrate users

1. Connect to the server on which the self-managed PostgreSQL instance is deployed and run the following command to export the owner named testuser of the database that you want to migrate:

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

   Sample output:

   Note

   In the Roles section of the command output, you can obtain the statement that is related to the permissions of the testuser user.

   --
   -- PostgreSQL database cluster dump
   --
   
   SET default_transaction_read_only = off;
   
   SET client_encoding = 'UTF8';
   SET standard_conforming_strings = on;
   
   --
   -- Roles
   --
   
   CREATE ROLE postgres;
   ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
   CREATE ROLE testuser;
   ALTER ROLE testuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0IEvNqWupQsGcaoDS3SX****3U=';
   
   --
   -- User Configurations
   --
   
   --
   -- PostgreSQL database cluster dump complete
   --

2. Use the privileged account to connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.

3. Change SUPERUSER in the obtained SQL statement that is related to the permissions of the testuser user to rds_SUPERUSER. On the RDS instance, create an account that has the same permissions as the testuser user of the self-managed PostgreSQL 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 a destination database on the RDS instance

1. Use the privileged account to connect to the RDS instance.

2. Create a destination database. Make sure that the destination database has the same character set and the same Collate, and Ctype settings as the source database on the self-managed PostgreSQL instance. For example, the destination database must have the same settings as the source database that is obtained in Step 1: Encoding is UTF8, Collate is en_US.UTF-8, and Ctype is en_US.UTF-8.

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

   Note

   • The name of the destination database can be different from the name of the source database on the self-managed PostgreSQL instance.

   • The owner of the destination database must be the same as the owner of the source database on the self-managed PostgreSQL instance.

Step 4: Migrate data

You can use one of the following methods to migrate data:

• If the server on which the self-managed PostgreSQL instance is deployed can connect to the RDS instance, we recommend that you use online migration.

• If the server on which the self-managed PostgreSQL instance is deployed cannot connect to the RDS instance, we recommend that you use offline migration. Specifically, use pg_dump to export the data to a file, copy the file to the host to which the RDS instance connects, and then use pg_restore to import the file to the RDS instance.

References

You can configure more parameters for the commands in this topic based on your business requirements. For more information, see the following documents:

• pg_dump

• pg_restore

• pg_dumpall