文档

自建PostgreSQL 9.X迁移至RDS PostgreSQL

更新时间:

随着信息技术的迅猛发展,云计算已经成为企业和开发者提升效率、扩展业务和实现创新的关键平台。阿里云RDS PostgreSQL云数据库结合了PostgreSQL数据库的强大功能和阿里云基础设施的灵活性和便利性,为用户提供了高性能、弹性伸缩、便捷管理和稳固安全的云数据库服务。本文将介绍如何通过阿里云DTS服务将自建的PostgreSQL 9.X实例迁移到阿里云RDS PostgreSQL,从而实现自建PostgreSQL 9.X实例迁移上云,并完成数据库的大版本升级。

前置概念

阅读本文前,您可能需要了解什么是数据传输服务DTS

迁移技术介绍

PostgreSQL从9.4版本开始支持逻辑复制技术。PostgreSQL的逻辑复制(Logical Replication)是一种基于发布(Publication)和订阅(Subscription)的复制技术。这种方式允许数据被复制到不同实例或同一实例的不同数据库中,即使这些数据库实例运行在不同版本的PostgreSQL上。与流复制相比,逻辑复制提供了更高层次的灵活性,可以实现更精细的数据复制控制,例如只复制特定的表或者只复制表的某些列等。由于逻辑复制具有跨版本兼容性、选择性复制、灵活性和易用性等优势,逻辑复制在跨版本数据库升级、异构数据库迁移、异构系统数据集成等应用场景中得到了广泛应用。阿里云DTS服务基于PostgreSQL的逻辑复制技术,利用自定义逻辑解码插件实现了自建PostgreSQL 9.X到RDS PostgreSQL实例的迁移。

image

数据迁移过程包括三个阶段:结构迁移、全量数据迁移和增量数据迁移。在结构迁移完成后,随之开展结构校验;全量数据迁移完成后,将开始全量数据校验;增量数据写入启动后,亦将开展增量数据校验。其中,增量数据写入与增量数据校验将持续进行,直至迁移任务结束。

  • 结构迁移:在开始迁移数据之前,DTS会读取和解析源数据库中的元数据,并根据元数据中的相关信息生成符合目标数据库语法的DDL语句。随后,这些DDL语句将在目标数据库中执行,以创建相应的结构对象。

  • 全量数据迁移:在全量数据迁移阶段,DTS会将源数据库中的全量数据进行拆分,并且并发写入到目标数据库中。同时,当全量数据迁移开始时,增量数据读取模块将被激活,并通过自定义的逻辑解码插件ali_decoding对增量数据进行解析和重新格式化,随后将格式化完成的数据存储在本地DTS服务器上。

  • 增量数据迁移:当全量数据迁移完成后,DTS会检索本地存储的增量数据,并将数据更新应用到目标数据库中。同时,通过ali_decoding插件对实时产生的增量数据进行解析和重新格式化,随后将增量数据实时写入到目标数据库中。该过程将持续进行,直至源数据库中所有正在进行的数据变更均已写入目标数据库,确保源数据库与目标数据库完全同步。

  • 数据校验:在每个阶段完成后,DTS将对该阶段写入到目标数据库的数据进行一次数据校验,以确保源数据库和目标数据库的数据完全一致。同时,增量数据校验会对写入到目标数据库的增量数据进行实时校验,以确保写入到目标数据库的增量数据与源数据库完全一致。

注意事项

迁移的注意事项主要有自建PostgreSQL实例版本检查、RDS PostgreSQL实例版本检查、自建PostgreSQL实例待迁移数据库的限制等。

  • 自建PostgreSQL实例的版本范围为9.4.8至10.0。

    您可以通过select version();查询自建PostgreSQL的版本。

  • RDS PostgreSQL实例的版本范围为11.0及以上。

  • 自建PostgreSQL实例待迁移数据库的限制:

    • 待迁移的表需具备主键或唯一约束,并确保字段的唯一性,否则可能会导致目标数据库中出现重复数据。

    • 待迁移的数据库名称中间不能包含短划线(-),例如dts-testdata。

    • 不支持迁移源库中的临时表、系统内部的触发器(TRIGGER)、部分函数(关于PROCEDURE与FUNCTION的C语言函数和内部函数);支持迁移的自定义参数类型包括COMPOSITE、ENUM或RANGE;支持迁移的约束类型包括主键、外键、唯一约束以及CHECK约束。

    • 自建PostgreSQL实例迁移期间不允许进行主备切换,否则将导致迁移失败。

    • 在库表结构迁移和全量迁移阶段,不允许执行库或表结构变更的DDL操作,否则将导致数据迁移任务的失败。

更多的注意事项请参见注意事项

迁移流程

自建PostgreSQL 9.X实例迁移到RDS PostgreSQL的整体流程如下所示。

image

准备工作

创建账号

  1. 在自建PostgreSQL实例上创建用于迁移的superuser账号(本文以migrate_test为例)。

    CREATE USER migrate_test WITH superuser password 'your_password';
  2. 验证账号是否创建成功。

    \du+
                                                List of roles
      Role name   |                         Attributes                         | Member of | Description
    --------------+------------------------------------------------------------+-----------+-------------
     migrate_test | Superuser                                                  | {}        |
     postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

修改参数

检查参数是否符合预期。如果不符合预期,需要修改参数以满足迁移的要求。

  1. 检查wal_level参数取值是否为logical

    SHOW wal_level;
     wal_level
    -----------
     minimal
    (1 row)

    如果不是,需要执行如下命令,设置wal_level参数取值为logical

    ALTER SYSTEM SET wal_level='logical';
  2. 查看max_wal_senders参数取值是否为0。

    SHOW max_wal_senders;
     max_wal_senders
    -----------------
     0
    (1 row)

    如果取值为0 ,需要修改max_wal_senders参数取值使其大于0,建议大于等于64。

    ALTER SYSTEM SET max_wal_senders=64;
  3. 查看max_replication_slots参数取值是否为0。

    SHOW max_replication_slots;
     max_replication_slots
    -----------------------
     0
    (1 row)

    如果取值为0 ,需要修改max_replication_slots参数取值使其大于0,建议大于等于64。

    ALTER SYSTEM SET max_replication_slots=64;
  4. 重启PostgreSQL服务使配置生效。

放行DTS服务

  1. 修改pg_hba.conf文件,添加以下两条规则,以便DTS服务能够访问自建PostgreSQL实例。

    host all all 100.104.0.0/16 md5
    host replication all 100.104.0.0/16 md5
  2. 编辑完成后,请登录数据库并执行以下命令以使配置生效。

    SELECT pg_reload_conf();
     pg_reload_conf
    ----------------
     t
    (1 row)

编译安装用于迁移的插件

在自建PostgreSQL服务器中,下载DTS提供的插件ali_decoding并完成编译与安装。

  1. 查询自建PostgreSQL实例的版本。

    本文以PostgreSQL版本9.4.14为例,使用时请务必替换为实际所用版本。

    SELECT version();
  2. PostgreSQL官网下载与自建PostgreSQL实例版本号相对应的源代码压缩包,并上传到自建PostgreSQL实例所在的服务器。

    例如,在CentOS 7.9下载PostgreSQL 9.4.14对应的源代码压缩包postgresql-9.4.14.tar.gz

    wget https://ftp.postgresql.org/pub/source/v9.4.14/postgresql-9.4.14.tar.gz
  3. 解压PostgreSQL源代码压缩包,并对源代码进行编译安装。

    1. 创建一个目录(例如postgres),并将PostgreSQL源代码压缩包移动到该目录中。

      mkdir postgres
      mv ~/postgresql-9.4.14.tar.gz postgres
    2. 进入postgres目录,并解压PostgreSQL源代码压缩包。

      cd postgres/
      tar -zxvf postgresql-9.4.14.tar.gz
    3. 进入源代码所在目录,并创建安装目录。

      cd postgresql-9.4.14/
      mkdir -p /usr/local/pgsql94
    4. 配置、编译和安装源代码。

      1. 配置。

        sudo ./configure --prefix=/usr/local/pgsql94

        执行echo $?检查配置结果。若输出为0,则表示成功,可以进入下一步;若输出为非0值,则表示失败,需要进行原因排查并尝试重试。

        重要
        • prefix参数值不能与自建PostgreSQL的安装路径相同。

        • 编译安装PostgreSQL时,PostgreSQL的操作系统版本需与GCC(GNU编译器套件)版本保持一致。

        • 如执行sudo ./configure遇到报错,您可以根据报错提示调整命令。例如,报错信息为readline library not found. Use --without-readline to disable readline support.,则您可以调整命令为sudo ./configure --without-readline.

      2. 编译。

        sudo make

        执行echo $?检查配置结果。若输出为0,则表示成功,可以进入下一步;若输出为非0值,则表示失败,需要进行原因排查并尝试重试。

      3. 安装。

        sudo make install

        执行echo $?检查配置结果。若输出为0,则表示成功,可以进入下一步;若输出为非0值,则表示失败,需要进行原因排查并尝试重试。

  4. 下载ali_decoding插件,并上传到自建PostgreSQL实例所在的服务器。

    本文以Linux系统为例,下载及解压命令如下。

    重要

    ali_decoding插件的分支版本必须为ali_decoding_94。

    sudo wget https://github.com/YeFuner/rds_dbsync/archive/refs/heads/ali_decoding_94.zip
    unzip ali_decoding_94.zip
  5. 进入解压后获得的rds_dbsync-ali_decoding_94目录中,将ali_decoding整个目录拷贝至PostgreSQL(已完成编译安装)的contrib目录中。

    cd rds_dbsync-ali_decoding_94/
    cp -r ali_decoding/ /home/postgres/postgresql-9.4.14/contrib/
    cd /home/postgres/postgresql-9.4.14/contrib/ali_decoding/
    说明

    /home/postgres/postgresql-9.4.14/contrib/需要替换为PostgreSQL源代码对应的目录。

  6. 进入ali_decoding目录,将Makefile文件的内容替换为如下:

    # contrib/ali_decoding/Makefile
    MODULE_big = ali_decoding
    MODULES = ali_decoding
    OBJS    = ali_decoding.o
    
    DATA = ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql
    
    EXTENSION = ali_decoding
    
    NAME = ali_decoding
    
    #subdir = contrib/ali_decoding
    #top_builddir = ../..
    #include $(top_builddir)/src/Makefile.global
    #include $(top_srcdir)/contrib/contrib-global.mk
    
    #PG_CONFIG = /usr/pgsql-9.6/bin/pg_config
    #pgsql_lib_dir := $(shell $(PG_CONFIG) --libdir)
    #PGXS := $(shell $(PG_CONFIG) --pgxs)
    #include $(PGXS)
    
    # 源码安装用以下
    ifdef USE_PGXS
    PG_CONFIG = pg_config
    PGXS := $(shell $(PG_CONFIG) --pgxs)
    include $(PGXS)
    else
    subdir = contrib/ali_decoding
    top_builddir = ../..
    include $(top_builddir)/src/Makefile.global
    include $(top_srcdir)/contrib/contrib-global.mk
    endif
  7. 编译和安装ali_decoding,生成安装ali_decoding所需的文件。

    1. 编译。

      sudo make

      执行echo $?检查配置结果。若输出为0,则表示成功,可以进入下一步;若输出为非0值,则表示失败,需要进行原因排查并尝试重试。

    2. 安装。

      sudo make install

      执行echo $?检查配置结果。若输出为0,则表示成功,可以进入下一步;若输出为非0值,则表示失败,需要进行原因排查并尝试重试。

  8. 将生成的ali_decoding文件拷贝到自建PostgreSQL的指定目录中。

    1. 使用pg_config工具查找当前PostgreSQL实例对应内核使用的lib目录,并把插件编译产物ali_decoding.so安装到该目录。

      /usr/local/pgsql94/bin/pg_config --pkglibdir
      # 返回如下内容
      /usr/local/pgsql94/lib
      
      /usr/bin/install -c -m 755 ali_decoding.so '/usr/local/pgsql94/lib/ali_decoding.so'
      /usr/bin/install -c -m 755 ali_decoding.so '/usr/local/pgsql94/lib/'
    2. 使用pg_config工具查找当前PostgreSQL实例对应内核使用的share目录,并把插件编译产物ali_decoding.control、ali_decoding--0.0.1.sql和ali_decoding--unpackaged--0.0.1.sql安装到该目录下的extension目录。

      /usr/local/pgsql94/bin/pg_config --sharedir
      # 返回如下内容
      /usr/local/pgsql94/share
      
      /usr/bin/install -c -m 644 ali_decoding.control '/usr/local/pgsql94/share/extension/'
      /usr/bin/install -c -m 644 ali_decoding--0.0.1.sql ali_decoding--unpackaged--0.0.1.sql '/usr/local/pgsql94/share/extension/'

购买RDS PostgreSQL实例

购买RDS PostgreSQL实例,并在目标RDS PostgreSQL实例上创建用于迁移的高权限账号。根据待迁移对象所属的数据库信息,在目标RDS PostgreSQL实例中创建相应数据库。

  • 数据库名称需要和自建PostgreSQL实例待迁移的数据库名称一致。

  • 数据库的Owner必须是用于迁移的高权限账号

操作步骤

  1. 创建RDS PostgreSQL实例

  2. 创建高权限账号

  3. 创建数据库

在RDS PostgreSQL实例上创建需要使用的插件

使用\dxSQL命令,查询自建PostgreSQL实例上需要迁移的数据库使用了哪些插件,并在RDS PostgreSQL实例的目标数据库上创建相同的插件。

对于使用了postgis插件且源端和目标端插件版本不一致的场景,目标端需要执行以下SQL创建兼容性处理的插件。

CREATE EXTENSION IF NOT EXISTS postgis_raster;

使用DTS迁移数据

配置DTS迁移任务请参见操作步骤

割接上云

  1. DTS任务数据同步完成后,请对目标RDS PostgreSQL实例进行全量备份,并执行备份集的克隆,生成一个与目标RDS PostgreSQL实例版本和数据一致的实例。该实例将用于在业务测试环境中进行全面验证。在确认兼容性问题无误后,方可进行切换至云端。克隆实例请参见克隆实例

  2. 当DTS任务进入到增量写入阶段,并且延迟达到可割接的阈值(延迟在1秒内)后,停止业务应用,并对源实例实施停止写入操作。

    1. 配置pg_hba.conf文件,将以下规则优先级设置到最高,禁止业务账号(本文以test_acc1为例)连接实例。

      host all test_acc1 0.0.0.0/0 reject
      host replication test_acc1 0.0.0.0/0 reject

      编辑文件完成之后,登录源数据库执行 SELECT pg_reload_conf(); 使修改生效。

    2. 执行以下SQL语句,终止所有存量连接。

      SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND usename NOT IN ('migrate_test');
    3. 使用业务应用账号连接源数据库时出现以下错误,表明修改已生效。

      FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "test_acc1", database "postgres"
  3. 由于业务切换到目标端后,新写入的Sequence不会根据源库的最大值递增,因此在自建PostgreSQL库停止写入后,需要查询对应Sequence的最大值,然后在目标RDS PostgreSQL数据库中将该值设置为对应Sequence的初始值。

    执行如下SQL,查询自建PostgreSQL数据库中的Sequence值,并在目标RDS PostgreSQL数据库中执行返回的SQL命令。

    DO LANGUAGE plpgsql $$
    DECLARE
      nsp name;
      rel name;
      val int8;
    BEGIN
      FOR nsp,rel IN SELECT nspname,relname FROM pg_class t2 , pg_namespace t3 WHERE t2.relnamespace=t3.oid AND t2.relkind='S'
      LOOP
        EXECUTE format($_$SELECT last_value FROM %I.%I$_$, nsp, rel) INTO val;
        raise notice '%',
        format($_$SELECT setval('%I.%I'::regclass, %s);$_$, nsp, rel, val+1);
      END LOOP;
    END;
    $$;

    返回SQL示例如下。

    NOTICE:  select setval('public.my_sequence'::regclass, 2);
  4. 在DTS控制台设置目标RDS PostgreSQL实例到自建PostgreSQL实例的数据回流任务,即设置由RDS PostgreSQL到自建PostgreSQL的同步任务,详情请参见操作步骤

    重要
    • 该操作必须在业务应用启动之前执行。

    • 同步任务的源库为RDS PostgreSQL,目标库为自建PostgreSQL。

    • 同步类型仅需选择增量迁移,库表结构同步和全量同步无需进行选择。

  5. 把所有的业务应用的连接地址修改为目标RDS PostgreSQL实例的连接地址,并启动业务应用。查看目标RDS PostgreSQL实例的连接地址请参见查看或修改连接地址和端口

  6. 对业务应用的正确性进行观察。