阿里云RDS数据库使用pg_dump和pg_restore将自建PostgreSQL数据库迁移至RDS PostgreSQL-云淘科技

前提条件

• 已创建与自建PostgreSQL大版本相同的RDS PostgreSQL实例，具体操作，请参见创建RDS PostgreSQL实例。

  说明

  • 使用pg_dump和pg_restore迁移数据库时，虽然支持跨大版本迁移，但是可能面临不同大版本间的兼容问题，为确保迁移顺利，强烈建议自建PostgreSQL与RDS PostgreSQL实例的大版本相同。

  • 您可以在自建PostgreSQL数据库所在的服务器中，使用psql --version命令查询自建数据库的版本。

  • 如果您需要升级大版本，可以在迁移完成后，使用大版本升级功能进行升级，更多信息，请参见升级数据库大版本。

  • 如果您的自建PostgreSQL数据库所在的服务器是阿里云云服务器ECS，则推荐将目标RDS PostgreSQL实例创建在与云服务器ECS相同的地域，使用同一个VPC，迁移数据时使用内网连接更快更安全。

• RDS PostgreSQL实例已配置白名单，允许自建PostgreSQL数据库所在服务器访问，具体操作，请参见设置白名单。

• RDS PostgreSQL实例已创建高权限账号，具体操作，请参见创建账号。

• 已获取RDS PostgreSQL实例的连接地址和端口，具体操作，请参见查看或修改连接地址和端口。

  说明

  如果您的自建PostgreSQL数据库所在的服务器是阿里云云服务器ECS，且与RDS PostgreSQL在同一地域同一VPC，则获取RDS PostgreSQL实例的内网连接地址和端口，否则，获取外网地址和端口。

• 自建PostgreSQL数据库已创建superuser权限的管理员账号。

• 自建PostgreSQL数据库所在服务器已安装pg_dump和pg_restore。

  说明

  在本地安装自建PostgreSQL时，已默认安装了pg_dump和pg_restore，您可以通过pg_dump --version和pg_restore --version命令进行查询。

操作步骤

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

连接自建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  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