自建PostgreSQL 9.X迁移至RDS PostgreSQL

前置概念

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

迁移技术介绍

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

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

• 结构迁移：在开始迁移数据之前，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的整体流程如下所示。

准备工作

创建账号

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. 对业务应用的正确性进行观察。