13.PG之逻辑复制-526互联

1.逻辑复制介绍　　

　　PostgreSQL10版本中增加了一个新特性，即逻辑复制（Logical Replication）。PostgreSQL 9的流复制是基于WAL日志的物理复制，其原理是主库不间断地发送WAL日志流到备库，备库接收主库发送的WAL日志流后应用WAL；而逻辑复制是基于逻辑解析（Logical Decoding），其核心原理是主库将WAL日志流解析成一定格式的数据流，订阅节点收到解析后的WAL数据流后进行应用，从而实现数据同步，逻辑复制并不是使用WAL原始日志文件进行复制，而是将WAL日志解析成了一定格式的数据。

　　逻辑复制使用类似消息队列的发布者（publication）/订阅者（subscription）模型，使用订阅复制槽技术，可并行地传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目保持复制表的数据同步，注意，这里不是“SQL”复制，而是复制SQL操作的结果

2.应用场景

　　·将多个数据库的数据合并到一个数据仓库的数据库中，用于数据仓库的数据分析。

　　·不同大版本PostgreSQL之间的数据复制，可以辅助数据库跨大版本升级。

　　·捕获本机数据库的增量更新，发送给指定数据库或通知其他应用。

　　·多个数据库之间共享部分数据。

3.逻辑复制的发布

　　要想成功配置逻辑复制的发布，需要了解以下知识点：

　　·逻辑复制的前提是将数据库中的wal_level参数设置成“logical”；同时开启足够的worker，设置足够大的Replication Slot，设置足够多的Sender。因为每一个订阅，都要消耗掉一个Replication Slot，需要消耗一个WAL Sender，一个worker进程。

　　·源库上逻辑复制的用户必须具有replicatoin或superuser角色并且订阅者要使用该用户通过流复制协议连接到源数据库的发布者上。

　　·发布者的pg_hba.conf需要设置replication条目，允许订阅者连接。

　　·逻辑复制目前仅支持数据库表逻辑复制，其他对象如函数、视图不支持。

　　·目前仅仅支持发布表，不允许发布其他对象。

　　·同一张表，可以发布多次。

　　·允许发布时，选择发布INSERT、UPDATE、DELETE，比如只发布INSERT，而不发布UPDATE、DELETE。

　　·当发布了表的UPDATE、DELETE时，表必须设置replica identity，即如何标识旧行（OLDTUPLE），通过pk或者uk或者full。如果设置了nothing，则执行UPDATE、DELETE时会报错。

　　·逻辑复制支持DML（UPDATE、INSERT、DELETE）操作，不支持TRUNCATE和DDL操作。

　　·一个数据库中可以有多个发布，但是不能重名，通过pg_publication查看；

　　·允许一次发布所有表，语法：CREATE PUBLICATION alltables FOR ALL TABLES。

　　·一个发布允许有多个订阅者。

　　·使用创建发布或者使用在发布内容中添加或者删除表时，都是事务级别，不会出现只复制部分事务的情况。

　　创建语法：　

postgres=# \h create publication
Command:     CREATE PUBLICATION
Description: define a new publication
Syntax:
CREATE PUBLICATION name
    [ FOR ALL TABLES
      | FOR publication_object [, ... ] ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

where publication_object is one of:

    TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]

　　示例：　　

创建发布的示例如下：
CREATE PUBLICATION mypub1 FOR TABLE table01, table02;
其中“mypub1”是一个发布名，而“table01,table02”表示表“table01”“table02”上的DML变化都会发布出去

　　修改语法　

postgres=# \h  alter publication
Command:     ALTER PUBLICATION
Description: change the definition of a publication
Syntax:
ALTER PUBLICATION name ADD publication_object [, ...]
ALTER PUBLICATION name SET publication_object [, ...]
ALTER PUBLICATION name DROP publication_object [, ...]
ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name RENAME TO new_name

where publication_object is one of:

    TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]

URL: https://www.postgresql.org/docs/16/sql-alterpublication.html
postgres=#

4.逻辑复制的订阅

　　要想成功配置逻辑复制的订阅，需要了解以下知识点：

　　·订阅节点需要指定发布者的连接信息。

　　·订阅者需要通过流复制协议连接到发布者，同时需要在发布者上创建Replication Slot，因此发布者的pg_hba.conf中需要配置相应的REPLICATION条目，允许订阅者通过流复制协议连接，同时连接发布者的用户必须具备REPLICATION权限，或者具备超级用户权限。

　　·一个数据库中可以有多个订阅者。

　　·同一个数据库中可以创建多个订阅者，这些订阅者可以被同一个或多个发布者使用。

　　·每一个订阅都需要在发布端创建一个Replication Slot，可以使用“slot name=?”指定，或者默认为subscription name。即使是同一个发布端，只要订阅了多次，就需要创建多个Replication Slot，因为其中记录了同步的LSN信息。

　　·可以使用ENABLE/DISABLE命令启用/暂停该订阅。

　　·发布节点和订阅节点表的模式名、表名必须一致，订阅节点允许表有额外字段。

　　·发布节点增加表名，订阅节点需要执行“ALTER SUBSCRIPTION sub1 REFRESHPUBLICATION”。

　　·pg_dump导出数据库逻辑数据时，默认不会导出subscription的定义，除非使用选项“--include-subscriptions”。

　　·如果要完全删除订阅，使用DROP SUBSCRIPTION命令，注意，删除订阅后，本地的表不会被删除，数据也不会被清除，仅仅是不再接收该订阅传过来的同步数据。

　　·删除订阅后，如果要重新使用该订阅，数据会全部重新同步过来，比如订阅的上游表中有100万条数据，RESYNC会将这100万条数据同步过来，随后进入增量同步。

　　·订阅时，不会自动创建发布端的表，所以需要在订阅端先创建好表，目前发布端和订阅端的表定义必须完全一致，包括Schema，表名必须一致。字段名和字段类型必须一致，字段顺序可以不一致。

　　·必须使用超级用户创建订阅。

　　创建订阅者的语法：

postgres=# \h create subscription
Command:     CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

URL: https://www.postgresql.org/docs/16/sql-createsubscription.html

postgres=#

　　示例：

　　创建一个到远程服务器的订阅，复制发布“mypublication”和“insert_only”中的表，并在提交时立即开始复制：

　　CREATE SUBSCRIPTION mysubCONNECTION 'host=10.197.162.101 port=5432 user=repdbname=repdb'PUBLICATION mypub1, mypub2;

　　修改订阅者的语法：

postgres=# \h alter subscription
Command:     ALTER SUBSCRIPTION
Description: change the definition of a subscription
Syntax:
ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name SKIP ( skip_option = value )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name

URL: https://www.postgresql.org/docs/16/sql-altersubscription.html

　　查看订阅状态的命令如下：

　　select subname,subenabled,subpublications from pg_subscription;

　　禁用和启用订阅的方法如下：

　　ALTER SUBSCRIPTION mysub DISABLE;ALTER SUBSCRIPTION mysub ENABLE;

5.逻辑复制的冲突处理

　　逻辑复制的行为与普通的DML操作类似，因为即使订阅者节点本地更改了数据，数据也将被更新。传入数据违反任何约束，如主键冲突，逻辑复制都将停止，这被称为冲突。当复制UPDATE或者DELETE操作时，丢失的数据不会产生冲突，这样的操作将被忽略。冲突会产生错误，并会停止复制，它必须由用户手动解决。有关冲突的详细信息可以在订阅者的服务器日志中找到。冲突的修复方法主要有以下两种。

　　·方法一：通过修改订阅端的数据解决冲突。例如，INSERT违反了唯一约束时，可以先删除订阅端造成唯一约束冲突的记录，然后再使用ALTER SUBSCRIPTION name ENABLE让订阅继续。

　　·方法二：在订阅端调用pg_replication_origin_advance(node_name text,pos pg_lsn)函数，node_name就是subscription name，“pos”指重新开始的LSN，从而跳过有冲突的事务。

6.逻辑复制的限制

逻辑复制的数据库版本限制有以下几种：

　　·数据源发布和订阅节点需要运行PostgreSQL 9.4+。

　　·复制源过滤和冲突检测需要PostgreSQL 9.5+。

　　·pglogical支持跨PostgreSQL主要版本之间的复制，但在订阅服务器上，不同版本之间进行复制时可能会出现问题。

　　·支持从旧版本复制到新版本，因为PostgreSQL具有向后兼容性，但只有有限的向前兼容性比较安全。

　逻辑复制的其他限制主要有以下几种：

　　·DDL操作不支持复制，发布节点上发布表进行DDL操作后，DDL操作不会复制到订阅节点，需要在订阅节点对发布表手动执行DDL操作。

　　·序列本身不支持复制，当前逻辑复制仅支持普通表，序列、视图、物化视图、分区表、外部表等对象都不支持。

　　·TEMPORARY表和UNLOGGED表不会被复制。