PG中逻辑订阅的秘密

精选原创

wx656401c57a623 2024-08-13 00:02:57 博主文章分类：postgresql ©著作权

©著作权归作者所有：来自51CTO博客作者wx656401c57a623的原创作品，请联系作者获取转载授权，否则将追究法律责任

简介

在postgresql中，库级别的复制，通常使用主从复制，但是主从暂时不支持跨大版本复制，使用跨版本复制以及表级别的复制通常使用发布订阅。postgresql15在逻辑订阅上做了一些修改，对于使用者的便利性更高。

1、 publication 发布

创建发布，需要数据库的两个核心参数
1）wal_level
wal_level 必须是logical，wal_level 有三个级别minimal, replica, logical将wal_level 设置为logical 并不会影响主从之间的主从流复制。
2）max_replication_slots 当创建发布之后，并不会拉起进程，而是会等到remote 端发起订阅之后，才会拉起一个进程，用于发布订阅点对点的wal传输。所以max_replication_slots 参数的值必须够用于订阅发布的进程数量。

创建语法

发布的创建语法有三种
FOR ALL TABLES :指定库级别的所有表，这个指令会监控未来新增的表，也会被加入到发布中，用于被订阅.当执行drop table、drop column 表\字段会被移除发布列表中，订阅端不会再接收到被删掉表的信息。新增表时，此时你订阅端需要schemaname 和tablename都需要和发布端的一致。当使用该指令，发布中的表便无法增加或删除。
执行以下指令，用于对发布端新增表的初始接收

alter subscription all_tables refresh publication

FOR TABLES IN SCHEMA ：在PG15版本后新出的功能，用于指定schema级别下的表，这个指令会监控未来新增的表，也会被加入到发布中，用于被订阅.当执行drop table 表会被移除发布列表中，订阅端不会再接收到被删掉表的信息。新增表时，此时你订阅端需要tablename和发布端的一致。当使用该指令，发布中的表便无法增加或删除。
执行以下指令，用于对发布端新增表的初始接收

alter subscription all_tables refresh publication

FOR TABLE ：用于对指定的表、或者一组表进行发布。该语句只会对for table 的表进行发布，不会动态添加表
执行以下命令可以增加表到发布中去

-- 查看哪些表在发布中
select *  from pg_publication_tables ;

-- 增加/删除 表到发布中
alter  publication all_tables add table <tablename>;
alter  publication all_tables drop table <tablename>;

publication 可以针对DB级别、schema级别(PG15以后的特性)、表级别进行发布以外，也可以针对列、行级别进行发布（PG15以后的特性），但是仍然有缺陷，订阅段无法动态变更发布表中的字段。需要增加外部插件 logial_ddl才能得以实现，下文中再介绍该插件的使用方法。

行\列发布

进行指定的行、列发布的时候，只允许在FOR TABLE 模式下的发布中.
执行样例

--使用where 对符合条件的行进行筛选发布，schema_t1.t1 (id)括号内指定需要发布的列

postgres=# create publication t1 for table schema_t1.t1 (id) where (t1='t');
CREATE PUBLICATION

-- 查看哪些表、字段被用到发布中
select *  from pg_replication_tables

发布规则

在PG15中支持meger语法
对于MERGE命令，发布将为每个插入、更新或删除的行发布一个 INSERT、UPDATE或DELETE。

select  *  from pg_publication;

pubinsert 如果为真，复制INSERT操作。

pubupdate 如果为真，复制UPDATE操作。

pubdelete 如果为真，复制DELETE操作。

pubtruncate 如果为真，复制TRUNCATE操作。

puballtables 如果为真，发布以for table进行发布。

pubviaroot 如果为真，发布以publish_via_partition_root为true进行发布。

注：当你在对发布表执行delete、update时，会报错

postgres=# delete from pub1 where id =3;
ERROR:  cannot delete from table "pub1" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

在发布中可以看到delete 其实是被应用到发布的。

但是在创建发布的时候，是无法指定replica identity，默认是使用表中得主键（唯一约束都不行）。如果表中没有primary key 就会报错

-- 除了给表增加主键约束外，还可以使用以下语句，将在执行update、delete 使用所有列进行标识。
alter table <tablename> replica identity full ;

在分区表中的应用

在创建发布的时候。可以指定参数publish_via_partition_root，这个参数的默认值false,表示当对分区表的进行操作发布的时候，会将表名变更为其具体的子表语句。这个参数本博主觉得没有关注的必要。因为订阅端进行订阅的时候，也是需要进行子表的排查，只有分区表的母表和子表的schemaname、tablename完全一致的情况下，才会允许被订阅。

例

2、subscription 订阅

创建订阅前，需要保证当前数据库下的schemaname\tablename 与发布端保持一致

导出发布端所有DDL 
pg_dump -d postgres --schema-only --no-publications -c    --if-exists  --no-tablespaces --no-subscriptions     > ddl.sql 

订阅端导入到对应的数据库中去
psql -f ddl.sql -d tect  -U postgres

创建语法

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

订阅的创建比较常规，在PG15中新增了三个参数
streaming ：指定是否启用此订阅的事务流式处理。默认情况下，所有事务在发布者上完全解码，然后作为整体发送给订阅者。
two_phase ：两阶段提交，如果启用，发布端在未提交的时候，也会发送给订阅者，在订阅者端，也采用两阶段提交模式。本博主暂时没发现该功能的适用场景。默认值是false
disable_on_error :表示在传送过程中遇到报错是否需要停止订阅，默认值是false,不停止。

订阅规则

订阅端，需要schemaname\tablename 与发布端相同,同时支持一个订阅同时订阅多个发布端，需要多个发布都在一个连接串上，会在发布端创建一个复制槽，多个发布应用一个复制槽进行复制。
当发布端，进行动态增加字段和表时，订阅端与发布端不一致的时候，复制流会停住，需要订阅新增对应的表和字段，会自上一次字段变更开始的数据进行接收数据。

在订阅段有参数可以设置，但是我觉得比较常用的就是synchronous_commit参数
开启 synchronous_commit，

logical_ddl

用于解决发布端动态增加表和字段，订阅端无法动态变更的问题。进入logical_ddl 下载，然后上传包

tar -zxvf logical_ddl-0.1.0.tar.gz 
 cd logical_ddl-0.1.0/
make && make install 
-- 进入psql 客户端进行创建插件
postgres=# create extension logical_ddl;
CREATE EXTENSION
postgres=#

此插件需要在发布端和订阅端同步创建
发布端

创建扩展

CREATE EXTENSION logical_ddl;

自定义一个 发布源 名称，用于订阅端logical_ddl插件使用,名称得字节长度必须大于等于3。设置true 用于发布端，设置为false 用于订阅端

INSERT INTO logical_ddl.settings VALUES (true, 'vm10');

将需要同步得表加入到logical_ddl.publish_tablelist中

INSERT INTO logical_ddl.publish_tablelist (relid) 
select distinct a.prrelid  from pg_catalog.pg_publication_rel  a  
	join pg_class b 
	on a.prrelid = b.oid 
	 join pg_namespace c 
	 on b.relnamespace = c.oid
	 join   pg_catalog.pg_publication  b1
on a.prpubid= b1.oid
where 
	--指定发布名
	b1.pubname = ''
	--指定表名称
	and b.relname = ''
	--指定schema 
	and c.nspname = '';

将shadow_table添加到publication 中

ALTER PUBLICATION  p1  ADD TABLE logical_ddl.shadow_table;

订阅端

创建扩展

CREATE EXTENSION logical_ddl;

添加 DDL 发布者源名称，设置true 用于发布端，设置为false 用于订阅端

INSERT INTO logical_ddl.settings VALUES (false, 'vm10');

为 DDL 操作添加目标表

INSERT INTO logical_ddl.subscribe_tablelist (source,relid) 
select distinct 'vm10',a.srrelid  from pg_catalog.pg_subscription_rel  a  
	join pg_class b 
	on a.srrelid = b.oid 
	 join pg_namespace c 
	 on b.relnamespace = c.oid
	 join   pg_catalog.pg_subscription  b1
on a.srsubid= b1.oid
where 
	--指定发布名
	b1.subname = ''
	--指定表名称
	and b.relname = ''
	--指定schema 
	and c.nspname = '';

注： pg_catalog.pg_subscription 并不会排除其他数据库下得订阅后续文章再说这个问题吧。

刷新出版物

ALTER SUBSCRIPTION s1 REFRESH PUBLICATION;

这个插件可以解决在指定的

案例测试

发布端、订阅端创建复制表
 create table t11(id int);

发布端
create publication t1_pub for all tables ;


订阅端
v108=#  create subscription t1_to_vm108_sub CONNECTION 'host=10.0.0.107 user=postgres dbname=t1' publication t1_pub;
NOTICE:  created replication slot "t1_to_vm108_sub" on publisher
CREATE SUBSCRIPTION

注：在创建订阅的时候，都会在发布端创建同名复制槽，此时订阅端的命名应该尽可能有标识度。

CREATE EXTENSION logical_ddl;

注： logical_ddl只是支持表字段增加和删除等的DDL 如果是使用for all tables 的发布方式，当你新增表的时候，复制并不会被hang住

ALTER TABLE .. RENAME TO ..
ALTER TABLE .. RENAME COLUMN .. TO ..
ALTER TABLE .. ADD COLUMN ..
ALTER TABLE .. ALTER COLUMN .. TYPE ..
ALTER TABLE .. DROP COLUMN ..

编辑logical_ddl的配置信息

INSERT INTO logical_ddl.settings VALUES (true, 't1_ddl');  --“t1_ddl”名称需要发布端和订阅端保持一致，并不需要使用发布名和订阅名
--添加需要同步DDL 的表名
INSERT INTO logical_ddl.publish_tablelist (relid) VALUES ('public.t11'::regclass);

--向t1_pub 发布中添加表logical_ddl.shadow_table
ALTER PUBLICATION t1_pub ADD TABLE logical_ddl.shadow_table;


订阅端
INSERT INTO logical_ddl.settings VALUES (false, 't1_ddl');
INSERT INTO logical_ddl.subscribe_tablelist (source,relid) VALUES ('t1_ddl','public.t11'::regclass);
ALTER SUBSCRIPTION t1_to_vm108_sub REFRESH PUBLICATION;

插入数据
--依次查看订阅段复制情况
insert into t11 (id) select generate_series(1,1000);
INSERT 0 1000   

ALTER TABLE t11 RENAME COLUMN id TO id1
ALTER TABLE t11 add column t1 varchar;
ALTER TABLE t11 ALTER COLUMN t1 TYPE timestamp ;
ALTER TABLE t11 DROP COLUMN id ;
ALTER TABLE t11 RENAME TO t11_rename;

逻辑解码

逻辑复制中，会创建逻辑复制槽，通过流复制协议接口传输数据，pg提供有SQL的接口通过pg_logical_slot_get_changes（）函数进行逻辑解码，也可使用自带程序pg_recvlogical 进行逻辑解码用于查看未被消费的data。在github上也有其他一些逻辑解码插件例如:wal2json\debezium。下篇文章再说吧！！！