文档

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

更新时间:

如果您准备将自建PostgreSQL数据库的指定数据库迁移到RDS PostgreSQL实例,同时您已经熟悉pg_dump和pg_restore工具,暂时不希望了解和使用阿里云提供的其他迁移服务(RDS PostgreSQL一键上云功能或数据传输DTS服务)时,您可以参考本文档完成数据库迁移工作。

前提条件

  • 已创建与自建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 --versionpg_restore --version命令进行查询。

操作步骤

使用本方案适合于单库迁移,如果您需要迁移多个库或整个自建PostgreSQL数据库,阿里云提供了迁移服务帮助您快速上云:

  • 将自建PostgreSQL数据库中的部分库一次性迁移到RDS PostgreSQL时,建议使用数据库传输DTS进行迁移,更多信息,请参见使用DTS将自建PostgreSQL迁移至RDS PostgreSQL

  • 将整个自建PostgreSQL数据库迁移上云时,建议使用RDS PostgreSQL提供的一键上云功能,更多信息,请参见一键上云

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

连接自建PostgreSQL数据库,在psql命令行中,使用\l查询数据库列表信息,确认待迁移数据库的OwnerEncodingCollateCtype信息。

本文以迁移testdb数据库为例,OwnertestuserEncodingUTF8Collateen_US.UTF-8Ctypeen_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。例如步骤一中获取的EncodingUTF8Collateen_US.UTF-8Ctypeen_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导入。

在线迁移

  1. 配置密码文件。更多信息,请参见The Password File

    1. 在自建PostgreSQL数据库所在服务器执行如下命令创建并编辑.pgpass文件,用于存储密码。

      vi ~/.pgpass
    2. i进入编辑模式,输入如下密码相关内容。

      说明
      • 格式为host:port:dbname:username:password

      • dbname和username必须小写,因为PostgreSQL元数据默认使用小写字母存储, 除非您的数据库和用户在创建时使用双引号包括并且大写。

      RDS PostgreSQL连接地址:5432:目标数据库名:目标数据库Owner用户名:密码
      127.0.0.1:5432:自建PostgreSQL待迁移数据库名:superuser用户名:密码
    3. Esc,输入:wq退出编辑模式。

    4. 修改.pgpass文件权限。

      chmod 700 ~/.pgpass
  2. 在自建PostgreSQL数据库所在服务器执行如下命令迁移数据。

    nohup pg_dump -F p -h 127.0.0.1 -p 5432 -U <自建数据库superuser用户> -d <待迁移数据库> --no-tablespaces | time psql -h <RDS PostgreSQL实例连接地址> -p 5432 -U <目标数据库Owner用户名> --single-transaction <目标数据库> > ./pg.dump.log 2>&1 & 
    说明
    • 您可以查看pg.dump.log文件检查是否存在错误日志,如果该文件为空,则表示迁移成功,可以前往RDS PostgreSQL实例查看迁移后的数据。

    • 上述命令中设置了--single-transaction选项,在恢复数据库时将所有操作封装在一个事务中,确保恢复操作要么全部成功,要么全部失败,因此解决报错后可以直接重新导入。

离线迁移

  1. 在自建PostgreSQL数据库所在服务器执行如下命令导出文件。

    nohup pg_dump -F c -h 127.0.0.1 -p 5432 -U <自建数据库superuser用户> -d <待迁移数据库> --no-tablespaces -f ./pg.dump > ./pg.dump.log 2>&1 &
    说明

    等待导出完成,检查pg.dump.log文件,确认没有报错。

  2. pg.dump文件拷贝到可以连接RDS PostgreSQL实例的主机上,执行如下命令导入文件。

    说明

    使用此方案时,需要为RDS PostgreSQL实例配置白名单,允许该主机访问RDS PostgreSQL,具体操作,请参见设置白名单

    • 单线程导入。

      pg_restore -h <RDS PostgreSQL连接地址> -p 5432 -U <目标数据库Owner用户名> -d <目标数据库> --no-tablespaces --single-transaction  pg.dump  >./pg.restore.log
      说明
      • 耐心等待导入结束,您可以查看pg.restore.log文件检查是否存在错误日志,如果该文件为空,则表示迁移成功,可以前往RDS PostgreSQL实例查看迁移后的数据。

      • 上述命令中设置了--single-transaction选项,在恢复数据库时将所有操作封装在一个事务中,确保恢复操作要么全部成功,要么全部失败,因此解决报错后可以直接重新导入。

    • 多线程并行导入。

      pg_restore -h <RDS PostgreSQL连接地址> -p 5432 -U <目标数据库Owner用户名> -d <目标数据库> --no-tablespaces -j <并行线程数> pg.dump  >./pg.restore.log 2>&1 &
      说明
      • 并行参数-j--single-transaction参数不能同时使用。

      • 参数-j的取值范围为正整数,但推荐的最大值为系统CPU核心数的两倍或四倍。这样可以确保充分利用系统资源,同时避免过多的并行任务导致资源竞争和性能下降。

      • 因为此方式未使用--single-transaction参数进行事务封装,因此一旦恢复过程中报错,则在解决报错后,需要在RDS PostgreSQL侧清空目标数据库下的所有表,再重新开始导入,以确保数据一致性。

相关参考

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

使用本方案适合于单库迁移,如果您需要迁移多个库或整个自建PostgreSQL数据库,阿里云提供了迁移服务帮助您快速上云:

  • 将自建PostgreSQL数据库中的部分库一次性迁移到RDS PostgreSQL时,建议使用数据库传输DTS进行迁移,更多信息,请参见使用DTS将自建PostgreSQL迁移至RDS PostgreSQL

  • 将整个自建PostgreSQL数据库迁移上云时,建议使用RDS PostgreSQL提供的一键上云功能,更多信息,请参见一键上云