使用pg_dump和pg_restore将自建PostgreSQL数据库迁移至RDS PostgreSQL

操作步骤

步骤一：确认待迁移数据库的相关信息

连接自建PostgreSQL数据库，在psql命令行中，使用\l查询数据库列表信息，确认待迁移数据库的Owner、Encoding、Collate和Ctype信息。

本文以迁移testdb数据库为例，Owner为testuser、Encoding为UTF8、Collate为en_US.UTF-8、Ctype为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)

步骤二：迁移用户

1. 连接自建PostgreSQL数据库所在的服务器，使用如下命令导出待迁移数据库的Owner用户testuser。

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

   返回结果示例：

   说明

   在返回结果中的Roles部分，即可看到导出用户testuser的相关权限语句。

   --
   -- 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. 使用高权限账号连接RDS PostgreSQL实例，具体操作，请参见连接PostgreSQL实例。

3. 修改获取的testuser用户的相关权限SQL语句（将SUPERUSER修改为rds_SUPERUSER），在RDS PostgreSQL实例中创建与自建PostgreSQL权限相同的账号。

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

步骤三：在RDS PostgreSQL中创建目标数据库

1. 使用高权限账号连接RDS PostgreSQL实例。

2. 使用如下SQL语句，创建目标数据库，需要确保与自建PostgreSQL中待迁移数据库具有相同字符集、Collate和Ctype。例如步骤一中获取的Encoding为UTF8、Collate为en_US.UTF-8、Ctype为en_US.UTF-8。

   CREATE DATABASE <目标数据库的名称> WITH OWNER <数据库Owner名称> TEMPLATE template0 ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';

   说明

   • 目标数据库名称可以与自建PostgreSQL中待迁移数据库名称不同。

   • 目标数据库的Owner必须与自建PostgreSQL中迁移数据库Owner相同。

步骤四：数据迁移

本文提供如下数据迁移方案，任选其一即可。

• 如果您的自建PostgreSQL数据库所在服务器可以直接连接RDS PostgreSQL数据库，则推荐使用在线迁移方式。

• 如果您的自建PostgreSQL数据库所在服务器无法直接连接RDS PostgreSQL数据库，则推荐使用离线迁移方式，先使用pg_dump导出文件，然后将文件拷贝到能连接RDS PostgreSQL的主机，最后使用pg_restore导入。

相关文档

本文涉及的命令均可根据业务需求配置更多相关参数，具体详见如下官方文档：

• pg_dump

• pg_restore

• pg_dumpall