PG 数据库迁移

本文将基于Pigsty沙箱环境，用实例演示基于PostgreSQL逻辑复制的在线不停机数据库迁移。

本文基于Pigsty沙箱中的实例，介绍基于逻辑复制进行主从切换与数据库迁移的原理，细节与注意事项。

逻辑复制相关基础知识可参考 Postgres逻辑复制详解 一文。

0 逻辑复制迁移

逻辑复制通常可用于跨大版本跨操作系统在线升级PostgreSQL，例如从PG 10到PG 13，从Windows到Linux。

0.1 逻辑迁移的优点

相比原地pg_upgrade升级与pg_dump升级，逻辑复制的好处有：

  • 在线：迁移可以在线进行，不需要或者只需要极小的停机窗口。
  • 灵活：目标库的结构可以与源库不同，例如普通表改为分区表，加列等。可以跨越大版本使用。
  • 安全：相比物理复制，目标库是可写的，因此在最终切换前，可以随意进行测试并重建。
  • 快速：停机窗口很短，可以控制在秒级到分钟级。

0.2 逻辑迁移的局限性

逻辑复制的局限性主要在于设置相对繁琐，初始时刻拷贝数据较物理复制更慢，对于单实例多DB的情况需要迁移多次。大对象与序列号需要在迁移时手动同步。

  • 不能复制DDL变更

  • 不能复制序列号（Sequence）

  • 如果逻辑从库上某张被外键引用的表被Truncate，但因为引用该表的表不在订阅集中（所以无法在不truncate该表的情况下继续，但在订阅集之外的表上执行truncate违反语义），那么就会出现冲突。

  • 大对象无法复制。

  • 只支持普通表的复制，包括分区表。不支持视图，物化视图，外部表。

总体来说都，属于可以解决或可以容忍的问题。

0.3 逻辑迁移的基本流程

整体上讲，基于逻辑复制的迁移遵循以下步骤：

其中准备工作与存量迁移部分耗时较长，但不需要停机，不会对生产业务产生影响。

切换时刻需要短暂的停机窗口，采用自动化的脚本可以将停机时间控制在秒级到分钟级。

下面将基于Pigsty沙箱介绍这些步骤涉及到的具体细节。

1 准备工作

1.1 准备源宿集群

在进行迁移之前，首先要确定迁移的源端集群与目标集群配置正确。

Pigsty标准沙箱由四个节点与两套数据库集群构成。

两套数据库集群pg-meta与pg-test将分别作为逻辑复制的源端（SRC）与宿端（DST）。

本例将pg-meta-1作为发布者，pg-test-1作为订阅者，将pgbench相关表从pg-meta迁移至pg-test。

1.1.1 用户

迁移通常需要在原宿两端拥有两个用户，分别用于管理与复制。

  1. CREATE USER dbuser_admin SUPERUSER;              -- 超级用户用于创建发布与订阅
  2. CREATE USER replicator REPLICATION BYPASSRLS;    -- 复制用户用于订阅变更

1.1.2 HBA规则

同时，还需要配置相应的HBA规则，允许复制用户在原宿集群间相互访问。

此外，迁移通常会从中控机发起，应当允许管理用户从中控机访问原/宿集群。

因为创建订阅需要超级用户权限，建议为管理用户（永久或临时）配置SUPERUSER权限。

1.1.3 配置项

必选的配置项是wal_level，您必须在源端将wal_level配置为logical，方能启用逻辑复制。

其他一些关于复制的相关参数也需要合理配置，但除了wal_level外的参数默认值都不会影响逻辑复制正常工作，均为可选。

推荐在源端与宿端使用相同的配置项，下面是在64核机器上，一些相关配置的参考值：

  1. wal_level: logical                      # MANDATORY!    
  2. max_worker_processes: 64                # default 8 -> 64, set to CPU CORE 64
  3. max_parallel_workers: 32                # default 8 -> 32, limit by max_worker_processes
  4. max_parallel_maintenance_workers: 16    # default 2 -> 16, limit by parallel worker
  5. max_parallel_workers_per_gather: 0      # default 2 -> 0,  disable parallel query on OLTP instance
  6. # max_parallel_workers_per_gather: 16   # default 2 -> 16, enable parallel query on OLAP instance
  8. max_wal_senders: 24                     # 10 -> 24
  9. max_replication_slots: 16               # 10 -> 16 
  10. max_logical_replication_workers: 8      # 4 -> 8, 6 sync worker + 1~2 apply worker
  11. max_sync_workers_per_subscription: 6    # 2 -> 6, 6 sync worker

对于数据库来说，通常还需要关注数据库的 编码（encoding）与 本地化 （locale）配置项是否正确，通常建议统一使用C.UTF8。

1.1.4 连接信息

为了执行管理命令，您需要通过连接串访问原/宿集群的主库。

建议不要在连接串中使用明文密码，密码可以通过~/.pgpass，~/.pg_service，环境变量等方式管理，下面使用时将不会列出密码。

  1. PGSRC='postgres://dbuser_admin@10.10.10.10/meta'        # 源端发布者 (SU)
  2. PGDST='postgres://dbuser_admin@10.10.10.11/test'        # 宿端订阅者 (SU)

建议在中控机/元节点上执行迁移命令，并在操作过程中保持上面两个变量生效。

1.2 确定迁移对象

相比于物理复制，逻辑复制允许用户对复制的内容与过程施加更为精细的控制。您可以选择数据库内容的一个子集进行复制。不过在这个例子中，我们将进行整库复制。

在本例中，我们采用pgbench提供的用例作为迁移标的。因此可以在源端集群使用pgbench初始化相关表项。

  1. pgbench -is64 ${PGSRC}

此外，考虑到测试的覆盖范围，我们还将创建一张额外的测试数据表（用于测试Sequence的迁移）

  1. psql ${PGSRC} -qAXtw <<-EOF
  2. DROP TABLE IF EXISTS pgbench_extras;
  3. CREATE TABLE IF NOT EXISTS pgbench_extras
  4.   (id BIGSERIAL PRIMARY KEY,v  TIMESTAMP NOT NULL UNIQUE);
  5. EOF

要注意，只有 基本表 （包括分区表）可以参与逻辑复制，其他类型的对象，包括 视图，物化视图，外部表，索引，序列号都无法加入到逻辑复制中。使用以下查询，可以列出当前数据库中可以加入逻辑复制的表的完全限定名。

  1. SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name
  2. FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
  3. WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')

在准备阶段，您需要筛选出希望进行复制的表。在存量迁移中将这些表的结构定义同步至宿集群中，并建立在这些表上的逻辑复制。

1.3 修复复制标识

并不是所有的表都可以直接纳入逻辑复制中并正常工作。在进行迁移前，您需要对所有待迁移的表进行检查，确认它们都已经正确配置了复制标识。

复制身份模式\表上的约束主键(p)非空唯一索引(u)两者皆无(n)
default有效xx
indexx有效x
full低效低效低效
nothingxxx

  • 如果表上有主键，则会默认使用 REPLICA IDENTITY default，这是最好的，不用进行任何修改。

  • 如果表上没有主键，有条件的话请创建一个，没有条件的话，一个建立在非空列集上的唯一索引也可以起到同样的作用。在这种情况下需要显式的为表配置REPLICA IDENTITY USING <tbl_unique_key_idx_name>。

  • 如果表上既没有主键，也没有唯一索引，那么您可以为表配置REPLICA IDENTITY FULL，将完整的一行作为复制标识。

    使用FULL身份标识的性能非常差，发布侧和订阅侧的删改操作都会导致顺序扫表，建议只将其作为保底手段使用。

    另一种选择是为表配置REPLICA IDENTITY NOTHING，这样任何在发布端对此表进行UPDATE|DELETE操作都会直接报错中止。

使用以下查询，可以列出所有表的完全限定名，复制标识配置，以及表上是否有主键或唯一索引，

  1. SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name, con.ri AS keys,
  2.        CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS identity
  3. FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid, LATERAL (SELECT array_agg(contype) AS ri FROM pg_constraint WHERE conrelid = c.oid) con
  4. WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
  5. ORDER BY 2,3;

以1.2的测试场景为例：

  1.           name           | keys  | identity
  2. -------------------------+-------+----------
  3.  public.spatial_ref_sys  | {c,p} | default
  4.  public.pgbench_accounts | {p}   | default
  5.  public.pgbench_branches | {p}   | default
  6.  public.pgbench_tellers  | {p}   | default
  7.  public.pgbench_extras   | {p,u} | default
  8.  public.pgbench_history  | NULL  | default

如果表上只有唯一索引，例如您需要检查该唯一索引是否满足要求：所有列都为非空，not deferrable，not partial，如果满足，则可以使用以下命令将表的复制身份修改为index模式。

  1. -- 一个例子：即使pgbench_extras上有主键，但也可以使用唯一索引作为身份标识
  2. ALTER TABLE pgbench_extras REPLICA IDENTITY USING INDEX pgbench_extras_v_key;

如果表上没有主键，也没有唯一约束。如上面的pgbench_history表，那就需要通过以下命令将其复制身份设置为FULL|NOTHING。

  1. ALTER TABLE pgbench_history REPLICA IDENTITY FULL;

完成修复后，所有表都应当具有合适的复制身份：

  1.           name           | keys  | identity
  2. -------------------------+-------+----------
  3.  public.spatial_ref_sys  | {c,p} | default
  4.  public.pgbench_accounts | {p}   | default
  5.  public.pgbench_branches | {p}   | default
  6.  public.pgbench_tellers  | {p}   | default
  7.  public.pgbench_extras   | {p,u} | index
  8.  public.pgbench_history  | NULL  | full

2 存量迁移

2.1 同步数据库模式

2.1.1 转储

使用以下命令转储所有对象定义，并复制到宿端应用。

  1. pg_dump ${PGSRC} --schema-only -n public | psql ${PGDST}

可以通过pg_dump的-n，-t参数进行灵活控制，只转储所需的对象。例如，如果只需要public模式下pgbench的相关表，则可以通过以下命令转储：

  1. pg_dump ${PGSRC} --schema-only -n public -t 'pgbench_*' | psql ${PGDST}

2.1.2 校验

同步完成后，通常需要进行模式校验。

  • 所有目标表及其索引、序列号是否已经建立
  • 函数、类型、模式、用户、权限是否均符合预期？

数据库模式需要根据用户自己的需求进行同步与校验，没有什么通用的方式。

2.2 在源端创建发布

源端集群主库作为发布者，需要创建发布，将所需的表加入到发布集中。

2.2.1 创建发布的方式

创建发布的语法如下所示：

  1. CREATE PUBLICATION name
  2.     [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
  3.       | FOR ALL TABLES ]
  4.     [ WITH ( publication_parameter [= value] [, ... ] ) ]

针对所有表创建发布（需要超级用户权限）：

  1. CREATE PUBLICATION "pg_meta_pub" FOR ALL TABLES;

注意无论是发布还是订阅，名称都建议遵循PostgreSQL对象标识符命名规则（[a-z][0-9a-z_]+），特别是不要在名称中使用-。以免不必要的麻烦，例如创建订阅同名的复制槽因命名不规范而失败。

如果需要控制订阅的事件类型（不常见），可以通过参数publish指定，默认为insert, update, delete, truncate。

如果源端上有分区表，有一个参数可以用于控制其复制行为。把分区表当成一张表（使用分区根表的复制标识），还是当成多张子表（使用子表上的复制标识）来处理。启用这个选项可以把分区表在逻辑上看成一张表（分区根表），而不是一系列的分区子表，所以订阅端只需要存在一张分区根表的同名表即可正常复制，这是13版本引入的新选项。该选项默认为false，也就是说逻辑复制分区表时，源端的每一个分区都必须在订阅端存在。

额外的参数可以通过以下的形式传入：

  1. CREATE PUBLICATION "pg_meta_pub" FOR ALL TABLES 
  2. WITH(publish = 'insert', publish_via_partition_root = true);

2.2.2 发布的内容

如果不希望发布所有的表，则可以在发布中具体指定所需的表名称。

例如在这个例子中spatial_ref_sys是一张postgis扩展使用的常量表，并不需要迁移，我们可以将其排除。利用以下SQL，可以直接在数据库中拼接出创建发布的SQL命令：

  1. SELECT E'CREATE PUBLICATION pg_meta_pub FOR TABLE\n' ||
  2.        string_agg(quote_ident(nspname) || '.' || quote_ident(relname), E',\n') || ';' AS sql
  3. FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid
  4. WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')
  5. AND relname ~ 'pgbench'; -- 只复制表名形如 pgbench* 的表
  6. \gexec    -- 在psql中执行上面命令生成的SQL语句

在这个例子中，实际生成并执行的命令如下：

  1. psql ${PGSRC} -Xtw <<-EOF
  2.   CREATE PUBLICATION pg_meta_pub FOR TABLE
  3.     public.pgbench_accounts,
  4.     public.pgbench_branches,
  5.     public.pgbench_tellers,
  6.     public.pgbench_history,
  7.     public.pgbench_extras;
  8. EOF

2.2.3 确认发布状态

建立完发布后，可以从 pg_publication 视图看到所创建的发布。

  1. $ psql ${PGSRC} -Xxwc 'table pg_publication;'
  2. -[ RECORD 1 ]+------------
  3. oid          | 24679
  4. pubname      | pg_meta_pub
  5. pubowner     | 10
  6. puballtables | f
  7. pubinsert    | t
  8. pubupdate    | t
  9. pubdelete    | t
  10. pubtruncate  | t
  11. pubviaroot   | f

可以从pg_publication_tables确认纳入到发布中的表有哪些。

  1. $ psql ${PGSRC} -Xwc 'table pg_publication_tables;'
  2.    pubname   | schemaname |    tablename
  3. -------------+------------+------------------
  4.  pg_meta_pub | public     | pgbench_history
  5.  pg_meta_pub | public     | pgbench_tellers
  6.  pg_meta_pub | public     | pgbench_accounts
  7.  pg_meta_pub | public     | pgbench_branches
  8.  pg_meta_pub | public     | pgbench_extras

确认无误后，发布端的工作完成。接下来要在宿端集群主库上创建订阅，订阅源端集群主库上的这个发布。

2.3 在宿端创建订阅

宿端集群主库作为订阅者，需要创建订阅，从发布者上订阅所需的变更。

2.3.1 创建订阅

创建订阅需要SUPERUSER权限，创建订阅的语法如下所示：

  1. CREATE SUBSCRIPTION subscription_name
  2.     CONNECTION 'conninfo'
  3.     PUBLICATION publication_name [, ...]
  4.     [ WITH ( subscription_parameter [= value] [, ... ] ) ]

创建订阅必须使用CONNECTION子句指定发布者的连接信息，通过PUBLICATION子句指定发布名称。这里使用replicator用户连接发布者，该用户的密码已经写入宿端实例下~/.pgpass，因此这里可以在连接串中省去。

创建订阅还有一些其他的参数，通常只有手动管理复制槽时才需要修改这些参数：

  • copy_data，默认为true，当复制开始时，是否要复制全量数据。
  • create_slot，默认为true，该订阅是否会在发布实例上创建复制槽。
  • enabled，默认为true，是否立即开始订阅。
  • connect，默认为true，是否连接至订阅实例，如果不连接，上面几个选项都会被重置为false。

这里，创建订阅的实际命令为：

  1. psql ${PGDST} -Xtw <<-EOF
  2.     CREATE SUBSCRIPTION "pg_test_sub" 
  3.       CONNECTION 'host=10.10.10.10 user=replicator dbname=meta' 
  4.       PUBLICATION "pg_meta_pub";
  5. EOF

2.3.2 订阅状态确认

成功创建订阅后，可以从 pg_subscription 视图看到所创建的发布。

  1. $ psql ${PGDST} -Xxwc 'TABLE pg_subscription;'
  2. -[ RECORD 1 ]---+---------------------------------------------
  3. oid             | 20759
  4. subdbid         | 19351
  5. subname         | pg_test_sub
  6. subowner        | 16390
  7. subenabled      | t
  8. subconninfo     | host=10.10.10.10 user=replicator dbname=meta
  9. subslotname     | pg_test_sub
  10. subsynccommit   | off
  11. subpublications | {pg_meta_pub}

可以从pg_subscription_rel中确认哪些表被纳入到订阅的范围，及其复制状态。

  1. $ psql ${PGDST} -Xwc 'table pg_subscription_rel;'
  2.  srsubid | srrelid | srsubstate |  srsublsn
  3. ---------+---------+------------+------------
  4.    20759 |   20742 | r          | 0/B0BC1FB8
  5.    20759 |   20734 | r          | 0/B0BC20B0
  6.    20759 |   20737 | r          | 0/B0BC20B0
  7.    20759 |   20745 | r          | 0/B0BC20B0
  8.    20759 |   20731 | r          | 0/B0BC20B0

2.4 等待逻辑复制同步

创建订阅后，首先必须监控 发布端与订阅端两侧的数据库日志，确保没有错误产生。

2.4.1 逻辑复制状态机

如果一切正常，逻辑复制会自动开始，针对每张订阅中的表执行复制状态机逻辑，如下图所示。

当所有的表都完成复制，进入r（ready）状态时，逻辑复制的存量同步阶段便完成了，发布端与订阅端整体进入同步状态。

stateDiagram-v2 [*] —> init : 表被加入到订阅集中 init —> data : 开始同步表的初始快照 data —> sync : 存量数据同步完成 sync —> ready : 同步期间的增量变更应用完毕，进入就绪状态

当创建或刷新订阅时，表会被加入到 订阅集 中，每一张订阅集中的表都会在pg_subscription_rel视图中有一条对应纪录，展示这张表当前的复制状态。刚加入订阅集的表初始状态为i，即initialize，初始状态。

如果订阅的copy_data选项为真（默认情况），且工作进程池中有空闲的Worker，PostgreSQL会为这张表分配一个同步工作进程，同步这张表上的存量数据，此时表的状态进入d，即拷贝数据中。对表做数据同步类似于对数据库集群进行basebackup，Sync Worker会在发布端创建临时的复制槽，获取表上的快照并通过COPY完成基础数据同步。

当表上的基础数据拷贝完成后，表会进入sync模式，即数据同步，同步进程会追赶同步过程中发生的增量变更。当追赶完成时，同步进程会将这张表标记为r（ready）状态，转交逻辑复制主Apply进程管理变更，表示这张表已经处于正常复制中。

2.4.2 同步进度跟踪

数据同步（d）阶段可能需要花费一些时间，取决于网卡，网络，磁盘，表的大小与分布，逻辑复制的同步worker数量等因素。

作为参考，1TB的数据库，20张表，包含有250GB的大表，双万兆网卡，在6个数据同步worker的负责下大约需要6~8小时完成复制。

在数据同步过程中，每个表同步任务都会源端库上创建临时的复制槽。请确保逻辑复制初始同步期间不要给源端主库施加过大的不必要写入压力，以免WAL撑爆磁盘。

发布侧的 pg_stat_replication，pg_replication_slots，订阅端的pg_stat_subscription，pg_subscription_rel提供了逻辑复制状态的相关信息，需要关注。

  1. psql ${PGDST} -Xxw <<-'EOF'
  2.     SELECT subname, json_object_agg(srsubstate, cnt) FROM
  3.     pg_subscription s JOIN
  4.       (SELECT srsubid, srsubstate, count(*) AS cnt FROM pg_subscription_rel 
  5.        GROUP BY srsubid, srsubstate) sr
  6.     ON s.oid = sr.srsubid GROUP BY subname;
  7. EOF

可以使用以下SQL确认订阅中表的状态，如果所有表的状态都显示为r，则表示逻辑复制已经成功建立，订阅端可以用于切换。

  1.    subname   | json_object_agg
  2. -------------+-----------------
  3.  pg_test_sub | { "r" : 5 }

当然，最好的方式始终是通过监控系统来跟踪复制状态。

3 切换时刻

3.1 准备工作

一个良好的工程实践是，在搞事情之前，在源端宿端都执行几次存盘操作，避免后续操作因被内存刷盘拖慢。

也可以执行分析命令更新统计信息，便于后续快速对比校验数据完整性。

  1. psql ${PGSRC} -Xxwc 'CHECKPOINT;ANALYZE;CHECKPOINT;'
  2. psql ${PGSRC} -Xxwc 'CHECKPOINT;ANALYZE;CHECKPOINT;'

在此之后的操作，都处于服务不可用状态，因此尽可能快地进行。通常情况下在分钟级内完成较为合适。

3.2 停止源端写入流量

3.2.1 选择合适的停止方式

暂停源端写入有多种方式，请根据实际业务场景选择与组合：

  • 告知业务方停止流量
  • 停止解析源端主库域名
  • 停止或暂停负载均衡器（Haproxy | VIP）的流量转发
  • 停止或暂停连接池Pgbouncer
  • 停止或暂停Postgres实例
  • 修改数据库主库的参数，设置默认事务模式为只读。
  • 修改数据库主库的HBA规则，拒绝业务访问。

通常建议使用修改HBA，修改连接池，修改负载均衡器的方式停止主库的写入流量。

请注意，无论使用何种方式，建议保持PostgreSQL存活，并且管理用户和复制用户仍然可以连接到源端主库。

3.2.2 确认源端写入流量停止

当源端主库停止接受写入后，首先执行确认逻辑，通过观察pg_stat_replication，确认逻辑订阅者已经与发布者保持同步。

  1. psql ${PGSRC} -Xxw <<-EOF
  2.     SELECT application_name AS name,
  3.            pg_current_wal_lsn() AS lsn,
  4.            pg_current_wal_lsn() - replay_lsn AS lag 
  5.     FROM pg_stat_replication;
  6. EOF
  8. -[ RECORD 1 ]-----
  9. name | pg_test_sub
  10. lsn  | 0/B0C24918
  11. lag  | 0

重复执行上述命令，如果lsn字段保持不变，lag始终为0，就说明主库的写入流量已经正确停止，且逻辑从库上已经没有复制延迟，可以用于切换。

3.2.3 建立反向逻辑复制（可选）

如果要求迁移失败后业务可以随时回滚，可以在停止源端写入流量后，设置反向的逻辑复制，将后续订阅端（新主库）的变更反向同步至原来的发布端（旧主库）。不过此过程需要重新同步数据，耗时太久。通常情况下，只有在数据非常重要，且数据量不大或停机窗口足够长的情况下才适用于此方法。

首先停止宿端现有的逻辑订阅。必须停止现有逻辑复制才能继续后面的步骤，否则会形成循环复制。

停止源端写入流量后，继续维持逻辑复制没有意义，因此可以停止宿端的订阅。但建议保留该订阅，只是禁用它，以备迁移失败回滚。

  1. psql ${PGDST} -qAXtwc 'ALTER SUBSCRIPTION pg_test_sub DISABLE;'

然后依照上述流程重新建立 反向的逻辑复制，这里只给出命令：

  1. # 在宿端创建发布：pg_test_pub
  2. psql ${PGDST} -Xtw <<-EOF
  3.   CREATE PUBLICATION pg_test_pub FOR TABLE
  4.     public.pgbench_accounts,
  5.     public.pgbench_branches,
  6.     public.pgbench_tellers,
  7.     public.pgbench_history,
  8.     public.pgbench_extras;
  9.   TABLE pg_publication;
  10. EOF
  12. # 在源端创建订阅
  13. psql ${PGSRC} -Xtw <<-EOF
  14.     CREATE SUBSCRIPTION "pg_meta_sub" 
  15.       CONNECTION 'host=10.10.10.11 user=replicator dbname=test' 
  16.       PUBLICATION "pg_test_pub";
  17.     TABLE pg_subscription;
  18. EOF
  20. # 清空源端所有相关表（危险），等待/或者不等待同步完成
  21. psql ${PGSRC} -Xtw <<-EOF
  22.   TRUNCATE TABLE
  23.     public.pgbench_accounts,
  24.     public.pgbench_branches,
  25.     public.pgbench_tellers,
  26.     public.pgbench_history,
  27.     public.pgbench_extras;
  28.   TABLE pg_publication;
  29. EOF

3.3 同步序列号与其他对象

逻辑复制不复制序列号（Sequence），因此基于逻辑复制做Failover时，必须在切换前手工同步序列号的值。

3.3.1 从源端同步序列号值

如果您的序列号都是从表上的SERIAL列定义自动创建的，而且宿端库也单纯只从源端订阅，那么同步序列号比较简单。从订阅端找出所有需要同步的序列号：

  1. PGSRC='postgres://dbuser_admin@10.10.10.10/meta'        # 源端发布者 (SU)
  2. PGDST='postgres://dbuser_admin@10.10.10.11/test'        # 宿端订阅者 (SU)
  4. -- 查询订阅端，生成的用于同步SEQUENCE的shell命令
  5. psql ${PGDST} -qAXtw <<-'EOF'
  6.     SELECT 'pg_dump ${PGSRC} -a ' ||
  7.     string_agg('-t ' || quote_ident(schemaname) || '.' || quote_ident(sequencename), ' ') ||
  8.     ' | grep setval | psql -qAXtw ${PGDST}'
  9.     FROM pg_sequences;
  10. EOF

在本例中，只有pgbench_extras.id上有一个对应的SEQUENCE pgbench_extras_id_seq。这里生成的同步语句为

  1. pg_dump ${PGSRC} -a -t public.pgbench_extras_id_seq | grep setval | psql -qAXtw ${PGDST}

比较复杂的情况，需要您手工生成这条命令，通过-t依次指定需要转储的序列号。

3.3.2 基于业务数据设置序列号值

另一种管理序列号的方式是直接根据表中的数据设置序列号的值，而无需从源端同步。

例如，表pgbench_extras.id的最大值为100，那么将订阅端端pgbench_extras_id_seq直接设置为一个足够大的值，例如100+10000 = 10100，就可以保证迁移后使用该序列号分配的新id不会与已有数据冲突。

采用这种方式，可以直接在故障切换前进行序列号的设置，减少迁移切换所需的停机时间。但这样可能会导致业务数据序列号分配出现空洞，对于一些边界条件与特殊的序列号使用场景需要特别小心。例如：序列号从未被使用过，序列号的增长步长为负数，采用函数发号器调用Sequence等。

直接设置序列号的命令如下所示：

  1. psql ${PGDST} -qAXtw <<-'EOF'
  2.   SELECT pg_catalog.setval('public.pgbench_extras_id_seq', (SELECT max(id) + 1000 FROM pgbench_extras));
  3. EOF

3.3.3 其他对象的同步

某些无法被逻辑复制处理的对象，也需要在这里一并进行同步。

例如：刷新物化视图，手工迁移大对象等。但这些功能很少有人会用到，所以在此不详细展开。

3.4 校验数据一致性

如果逻辑复制工作正常，通常不用校验数据，您可以在第二步中间执行多次对比校验以增强对逻辑复制的信心。

在停机窗口期间，建议只进行简单基本的数据校验，例如，比较表中的行数，主键的最大最小值是否一致。

以下函数用于执行这一校验

  1. function compare_relation(){
  2.     local relname=$1
  3.     local identity=${2-'id'}
  4.     psql ${3-${PGSRC}} -AXtwc "SELECT count(*) AS cnt, max($identity) AS max, min($identity) AS min FROM ${relname};"
  5.     psql ${4-${PGDST}} -AXtwc "SELECT count(*) AS cnt, max($identity) AS max, min($identity) AS min FROM ${relname};"
  6. }
  7. compare_relation pgbench_accounts aid
  8. compare_relation pgbench_branches bid
  9. compare_relation pgbench_history  tid
  10. compare_relation pgbench_tellers  tid
  1. function compare_relation() {
  2.     local src_url=${1}
  3.     local dst_url=${2}
  4.     local relname=${3}
  5.     res1=$(psql "${src_url}" -AXtwc "SELECT count(*) AS cnt FROM ${relname};")
  6.     res2=$(psql "${dst_url}" -AXtwc "SELECT count(*) AS cnt FROM ${relname};")
  7.     if [[ "${res1}" == "${res2}" ]]; then
  8.         echo -e "[ok] ${relname}\t\t\t${res1}\t${res2}"
  9.     else
  10.         echo -e "[xx] ${relname}\t\t\t${res1}\t${res2}"
  11.     fi
  12. }
  14. function compare_all() {
  15.     local src_url=${1}
  16.     local dst_url=${2}
  17.     tables=$(psql ${src_url} -AXtwc "SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relkind = 'r' AND nspname NOT IN ('pg_catalog', 'information_schema', 'monitor', 'repack', 'pg_toast')")
  18.     for tbl in $tables; do
  19.         result=$(compare_relation "${src_url}" "${dst_url}" ${tbl})
  20.         echo ${result}
  21.     done
  22. }
  24. compare_all ${PGSRC} ${PGDST}

同时，也可以过一遍3.3中同步的序列号，确认其配置是否相同。

  1. psql ${PGSRC} -qwXtc "SELECT schemaname || '.' || sequencename AS name, last_value AS v FROM pg_sequences;"
  2. psql ${PGDST} -qwXtc "SELECT schemaname || '.' || sequencename AS name, last_value AS v FROM pg_sequences;"

其他在3.3.3中手工同步的对象请按需自行校验。如果需要进行其他业务侧的校验，也在这里进行。但停机窗口时间宝贵，花费在这里的时间越长，服务不可用时间也越久。

校验完成后，就可以进行最终的流量切换了。

3.5 流量切换与善后

完成数据校验后就可以进行流量切换。

流量切换的方式取决于您所使用的访问方式，通常与3.2中停流量的方式对偶。例如：

  • 修改应用端连接串，并应用生效
  • 将源端主库域名解析至新主库
  • 将负载均衡器（Haproxy | VIP）的流量转发至新主库
  • 将原主库上Pgbouncer连接池的流量转发至新主库

通过监控系统或其他方式，确认写入流量已经正确应用订阅端的新主库后，基于逻辑复制的迁移就完成了。

不要忘记一些善后清理工作，停用并删除订阅端的订阅，删除发布端的发布。

同时，应当继续确保原主库拒绝新的写入，以免有未清理干净的流量因为配置失误错漏仍然向旧主库访问。

  1. # 删除订阅侧的 订阅
  2. psql ${PGDST} -qAXtw <<-'EOF'
  3.     ALTER SUBSCRIPTION pg_test_sub DISABLE;
  4.     DROP SUBSCRIPTION pg_test_sub;
  5. EOF
  7. # 删除发布侧的 发布
  8. psql ${PGSRC} -qAXtw <<-'EOF'
  9.     DROP PUBLICATION pg_meta_sub;
  10. EOF

至此，基于逻辑复制的完整迁移结束。

内置剧本

Pigsty内置了一个 数据库在线迁移的辅助脚本：pgsql-migration.yml ，提供了一个开箱即用的基于逻辑复制的不停机数据库迁移方案。

填入源集群与宿集群相关信息，该剧本即会自动创建出迁移中所需的脚本，在数据库迁移时只需要依次执行即可，包括：

  1. activate                          # 激活迁移上下文，注册环境变量
  2. check-replica-identity            # 准备阶段：检查源集群所有表是否都具有复制身份（主键，或非空唯一候选键）
  3. check-replica-identity-solution   # 准备阶段：针对没有合理复制身份表，生成修复SQL语句
  4. check-special-object              # 准备阶段：检查物化视图，复合类型等特殊对象
  5. compare                           # 比较：对源宿集群中的表进行快速比较（行数计算）
  6. copy-schema                       # 存量迁移：将源集群中的模式复制到宿集群中（可以幂等执行）
  7. create-pub                        # 存量迁移：在源集群中创建发布
  8. create-sub                        # 存量迁移：在宿集群中创建订阅，建立源宿集群之间的逻辑复制
  9. progress                          # 存量迁移：打印逻辑复制的进度
  10. copy-seq                          # 存量/增量迁移：将源集群中的序列号复制到宿集群中（可以幂等执行，在切换时需要再次执行）
  11. next-seq                          # 切换时刻：将宿集群的所有序列号紧急步进1000，以避免主键冲突。
  12. remove-sub                        # 移除宿集群中的逻辑订阅

准备工作

准备源宿集群

现在假设我们希望迁移沙箱中的pg-meta集群（包含Pigsty元数据库与pgbench测试表）至pg-test集群。

  1. pg-meta-1    10.10.10.10  --> pg-test-1    10.10.10.11 (10.10.10.12,10.10.10.13)

首先，新创建好空的目标集群pg-test，然后编辑pgsql-migration.yml 中的变量清单部分，填入相关信息（原宿集群主库的连接信息）

  1. #--------------------------------------------------------------#
  2. #                   MIGRATION CONTEXT                          #
  3. #--------------------------------------------------------------#
  5. # src cluster (the old cluster)
  6. src_cls: pg-meta                       # src cluster name
  7. src_db: meta                           # src database name
  8. src_ip: 10.10.10.10                    # ip address of src cluster primary
  9. src_list: [ ]                          # ip address list of src cluster members (non-primary)
  11. #--------------------------------------------------------------#
  12. # dst cluster (the new cluster)
  13. dst_cls: pg-test                       # dst cluster name
  14. dst_db: test                           # dst database name
  15. dst_ip: 10.10.10.11                    # dst cluster leader ip addressh
  16. dst_list: [ 10.10.10.12, 10.10.10.13 ] # dst cluster members (non-primary)
  18. # dst cluster access information
  19. dst_dns: pg-test                       # dst cluster dns records
  20. dst_vip: 10.10.10.3                    # dst cluster vip records
  22. #--------------------------------------------------------------#
  23. # credential (assume .pgpass viable)
  24. pg_admin_username: dbuser_dba          # superuser @ both side
  25. pg_replicatoin_username: replicator    # repl user @ src to be used
  26. migration_context_dir: ~/migration     # this dir will be created
  27. #--------------------------------------------------------------#

执行pgsql-migration.yml，该脚本默认会在元节点上创建 ~/migration/pg-meta.meta 目录，包含有迁移使用的资源与脚本。

迁移模板

公告

准备工作

存量迁移

切换时刻

最后修改 2022-05-27: init commit (1e3e284)