数据库迁移

基于Pigsty沙箱环境，用一个实际的例子演示基于逻辑复制的Postgres数据库迁移。

这里，我们将基于Pigsty沙箱环境，用一个实际的例子演示基于逻辑复制的Postgres数据库迁移。

我们有两套数据库集群pg-meta与pg-test，现在我们希望将pg-meta的数据在线迁移至pg-test中。

逻辑复制概述

逻辑复制通常可以用于跨大版本在线升级PostgreSQL，例如从10到13。

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

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

逻辑复制的缺点包括：

设置较为繁琐，初始时刻拷贝数据较慢，对于单实例多DB的情况需要迁移多次。以及，LO与Sequence需要手动处理。

准备测试环境

首先准备用户，下面使用Pigsty默认沙箱用户。

PGSRC_ADMIN='postgres://dbuser_admin:DBUser.Admin@pg-meta/meta'
PGSRC_REPL='postgres://replicator:DBUser.Replicator@pg-meta/meta'

PGDST_ADMIN='postgres://dbuser_admin:DBUser.Admin@pg-test/test'
PGDST_REPL='postgres://replicator:DBUser.Replicator@pg-meta/meta'

在pg-meta集群的meta数据库中创建以下测试用表

psql ${PGSRC_ADMIN} <<-'EOF'
CREATE TABLE t_normal(id BIGSERIAL PRIMARY KEY,v  TIMESTAMP); -- 常规表，带有主键
CREATE TABLE t_unique(id SERIAL UNIQUE,v TIMESTAMP);          -- 没有主键，只有UNIQUE约束的表
CREATE TABLE t_tricky(id BIGINT,v  TIMESTAMP);                 -- 无主键也无UNIQUE的表
EOF

然后使用以下命令，生成一些负载，模拟针对表的修改

# insert into common_table per second
while true; do psql ${PGSRC_ADMIN} -c 'INSERT INTO t_normal(v) VALUES(CURRENT_TIMESTAMP);'; sleep 1 ; done

迁移准备工作

修改配置

保证拥有一个具有REPLICATION权限的用户，本例中我们使用Pigsty沙箱replicator用户

必须在旧库上配置wal_level = 'logical'

修复特例

检查需要复制的表，是否都具有REPLICA IDENTITY。具体来讲，就是这些表是不是都有主键，或者退一步，虽然没有主键，但是有UNIQUE NOT NULL的索引（NOT NULL很重要，因为NULL != NULL，所以UNIQUE索引允许多个记录都带有“相同”的取值NULL）

用以下的查询检查是否存在没有主键的表。

SELECT name, CASE WHEN has_primary THEN 'good' WHEN NOT has_primary AND has_unique THEN 'check' ELSE 'bad' END AS status,
    CASE relreplident WHEN 'd' THEN 'default' WHEN 'n' THEN 'nothing' WHEN 'f' THEN 'full' WHEN 'i' THEN 'index' END AS replica_identity,
    has_primary, has_unique
FROM
(
    SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS name, relreplident,
        EXISTS (SELECT * FROM pg_index WHERE indrelid = c.oid AND indisprimary AND indisvalid AND indisready AND indislive) AS has_primary,
        EXISTS (SELECT * FROM pg_index WHERE indrelid = c.oid AND indisunique AND indisvalid AND indisready AND indislive) AS has_unique
    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')
    ORDER BY 2,3
) res;

例如，这里标记为good表示该表具有主键，bad表示没有合适的复制身份，check表示虽然没有主键但是有UNIQUE约束，最好检查一下。

          name          | status | replica_identity | has_primary | has_unique
------------------------+--------+------------------+-------------+------------
 public.t_tricky        | bad    | default          | f           | f
 public.t_unique        | check  | default          | f           | t
 public.t_normal        | good   | default          | t           | t

如果无法为表创建主键

为没有主键的表配置复制身份：

ALTER TABLE t_unique REPLICA IDENTITY USING INDEX t_unique_id_key;
ALTER TABLE t_tricky REPLICA IDENTITY FULL;

检查设置之后的身份

          name          | status | replica_identity | has_primary | has_unique
------------------------+--------+------------------+-------------+------------
 public.t_tricky        | bad    | full             | f           | f
 public.t_unique        | check  | index            | f           | t
 public.t_normal        | good   | default          | t           | t

同步模式

使用以下命令转储public模式下的所有表结构，或者根据你自己的需求手工处理。

pg_dump ${PGSRC_ADMIN} --schema-only -n public | psql ${PGDST_ADMIN}

创建发布

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

PUBLICATION的名称不要用特殊字符，特别是-，因为创建同名的复制槽时会有非法字符问题。

CREATE PUBLICATION "pg_meta_pub" FOR ALL TABLES;

创建订阅

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

创建订阅有一些参数：

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

CREATE SUBSCRIPTION "pg_test_sub" CONNECTION 'host=10.10.10.10 user=replicator password=DBUser.Replicator dbname=meta' PUBLICATION "pg_meta_pub";

订阅创建后，会开始复制数据。

检查系统视图pg_subscription_rel，如果所有的表都处于ready状态，就说明复制就绪了。

迁移时刻

检查复制状态

On Src

CHECKPOINT;
CHECKPOINT;

On Dst

TABLE pg_subscription_rel;

暂停写入

修改Src主库Pgbouncer配置文件指向Dst，但先不应用。

停止原主库

同步序列

生成转储所有非空Sequence的SQL：

psql ${PGSRC_ADMIN} -qwAXt > seq.sql <<-'EOF'
SELECT $$SELECT setval('$$ || quote_ident(schemaname) || $$.$$ || quote_ident(sequencename) || $$', $$ || last_value || $$); $$ AS sql FROM pg_sequences WHERE last_value IS NOT NULL;
EOF

psql ${PGDST_ADMIN} -qwAxt -f seq.sql

导出的SQL结果为：

SELECT setval('public.t_normal_id_seq', 2);

比较两边数据库的Sequence区别

psql ${PGSRC_ADMIN} -qwAXtc "SELECT schemaname || '.' || sequencename AS name, last_value AS value FROM  pg_sequences ORDER BY 1;" > seq-src.res

psql ${PGDST_ADMIN} -qwAXtc "SELECT schemaname || '.' || sequencename AS name, last_value AS value FROM  pg_sequences ORDER BY 1;" > seq-dst.res

diff seq-src.res seq-dst.res

如果有区别，再重新执行一遍，或者检查哪里有错漏。

校验数据

切换写入

如果流量切换由DBA负载，则重载Pgbouncer配置，将其指向新实例。并通知业务方切换流量。

后事料理


DROP SUBSCRIPTION upgrade_sub;


ALTER SUBSCRIPTION  upgrade_sub DISABLE ;
ALTER SUBSCRIPTION  upgrade_sub SET (slot_name = NONE);
DROP SUBSCRIPTION upgrade_sub;
8) Time for some bubbly drinks
Note that if you won’t keep the old “publisher” accessible in read-only or normal primary mode (dangerous!) though, some extra steps are needed here before dropping:

SELECT table_schema || '.' || table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
  AND table_schema || '.' || table_name NOT IN (
    select table_schema || '.' || table_name
    from information_schema.table_constraints
    WHERE constraint_type in ('PRIMARY KEY', 'UNIQUE')
)
  AND table_schema NOT IN ('information_schema', 'pg_catalog');

逻辑复制基础知识

发布的性质

只能在逻辑复制的主库上创建
发布是一组表所构成的复制集合（Replication set），发布包含了这些表上产生的变更。
发布是属于数据库的（per database），而不是属于集簇的（not per cluster）
发布与表是一对多的关系，每个表可以加入到多个发布中。除非使用FOR ALL TABLES，每个表必须被显式加入到发布中。
发布与订阅是多对多的关系

复制身份

被发布到表必须具有复制身份（Replica Identity），只有这样，才可以在订阅者上定位UPDATE|DELETE所操作的行。

订阅的性质

一个订阅（subscription）可以订阅另一个数据库实例上的一个或多个发布（publication）
订阅需要通过复制槽来接受变更，订阅在初始数据拷贝阶段会创建临时的复制槽。
逻辑订阅者可以作为同步复制的 standby，订阅名会作为application_name。
只有Superuser可以dump订阅，其他用户执行dump

发布 `pg_publication`

postgres@meta:5432/meta=# table pg_publication;
-[ RECORD 1 ]+------------
oid          | 20453
pubname      | pg_meta_pub
pubowner     | 10
puballtables | t
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f

puballtables：是否包含所有的表
pubinsert|update|delete|truncate 是否发布这些操作
pubviaroot：如果设置了该选项，任何分区表（叶表）都会使用最顶层的（被）分区表的复制身份。所以可以把整个分区表当成一个表，而不是一系列表进行发布。

发布表内容系统视图 `pg_publication_tables`

由pg_publication，pg_class和pg_namespace拼合而成的视图，记录了发布中包含的表信息。

postgres@meta:5432/meta=# table pg_publication_tables;
   pubname   | schemaname |    tablename
-------------+------------+-----------------
 pg_meta_pub | public     | spatial_ref_sys
 pg_meta_pub | public     | t_normal
 pg_meta_pub | public     | t_unique
 pg_meta_pub | public     | t_tricky

使用pg_get_publication_tables可以根据订阅的名字获取订阅表的OID

SELECT * FROM pg_get_publication_tables('pg_meta_pub');
SELECT p.pubname,
       n.nspname AS schemaname,
       c.relname AS tablename
FROM pg_publication p,
     LATERAL pg_get_publication_tables(p.pubname::text) gpt(relid),
     pg_class c
         JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.oid = gpt.relid;

复制槽 `pg_replication_slot`

postgres@meta:5432/meta=# table pg_replication_slots ;
-[ RECORD 1 ]-------+------------
slot_name           | pg_test_sub
plugin              | pgoutput
slot_type           | logical
datoid              | 19355
database            | meta
temporary           | f
active              | t
active_pid          | 89367
xmin                | NULL
catalog_xmin        | 1524
restart_lsn         | 0/2A08D40
confirmed_flush_lsn | 0/2A097F8
wal_status          | reserved
safe_wal_size       | NULL

SELECT pg_current_wal_lsn() - confirmed_flush_lsn AS lag,* FROM pg_replication_slots;

订阅 `pg_subscription`

每一个逻辑订阅都会有一条记录，注意这个视图是跨数据库集簇范畴的，每个数据库中都可以看到整个集簇中的订阅信息。

oid             | 20421
subdbid         | 19356
subname         | pg_test_sub
subowner        | 10
subenabled      | t
subconninfo     | host=10.10.10.10 user=replicator password=DBUser.Replicator dbname=meta
subslotname     | pg_test_sub
subsynccommit   | off
subpublications | {pg_meta_pub}

subenabled：订阅是否启用
subconninfo ：因为包含敏感信息，会针对普通用户进行隐藏。
subslotname：订阅使用的复制槽名称，也会被用作逻辑复制的源名称（Origin Name），用于除重。

订阅状态 `pg_stat_subscription`

每个活跃订阅都会在这个视图中有至少一条记，即Main Worker（负责应用Logical日志）。

Main Workker的relid = NULL，如果有负责初始数据拷贝的进程，也会在这里有一行记录，relid为负责拷贝数据的表。

subid                 | 20421
subname               | pg_test_sub
pid                   | 5261
relid                 | NULL
received_lsn          | 0/2A4F6B8
last_msg_send_time    | 2021-02-22 17:05:06.578574+08
last_msg_receipt_time | 2021-02-22 17:05:06.583326+08
latest_end_lsn        | 0/2A4F6B8
latest_end_time       | 2021-02-22 17:05:06.578574+08

received_lsn ：最近收到的日志位置。
lastest_end_lsn：最后向WAL Sender回报的LSN位置，即主库上的confirmed_flush_lsn。

局限性

不能复制DDL变更（通过手工pg_dump --schema-only解决）
不能复制序列号（Sequence），（需要Failover到逻辑从库时，通过pg_dump解决）
如果逻辑从库上某张被外键引用的表被Truncate，但因为引用该表的表不在订阅集中（所以无法在不truncate该表的情况下继续，但在订阅集之外的表上执行truncate违反语义），那么就会出现冲突。
大对象无法复制。
只支持普通表的复制，包括分区表。不支持视图，物化视图，外部表。

逻辑复制QA

Q：将没有主键的表加入发布？

Q：ALTER PUB的生效方式

Q：在同一对发布者-订阅者上如果存在多对订阅，且发布包含的表重叠？

Q：订阅者和发布者的表定义有什么限制？

Q：pg_dump是如何处理订阅的

Q：什么情况下需要手工管理订阅复制槽？

Q：冲突的原因？如何定位冲突？如何解决冲突

最后修改 2021-02-23: update deploy doc (ed50323)