介绍
发布和订阅使用了 pg 的逻辑复制功能，通过发布端创建 publication 与表绑定，订阅端创建 subscription 同时会在发布端创建逻辑复制槽实现逻辑复制功能

逻辑复制基于 发布（Publication） 与 订阅（Subscription）模型

一个 发布者（Publisher） 上可以有多个发布，一个 订阅者（Subscriber） 上可以有多个 订阅 。
一个发布可被多个订阅者订阅，一个订阅只能订阅一个发布者，但可订阅同发布者上的多个不同发布。

典型用法

迁移，跨 PostgreSQL 大版本，跨操作系统平台进行复制。
CDC，收集数据库（或数据库的一个子集）中的增量变更，在订阅者上为增量变更触发触发器执行定制逻辑。
分拆，将多个数据库集成为一个，或者将一个数据库拆分为多个，进行精细的分拆集成与访问控制。

复制标识

一个被纳入发布中的表，必须带有复制标识（Replica Identity），只有这样才可以在订阅者一侧定位到需要更新的行，完成 UPDATE 与 DELETE 操作的复制。
默认情况下，主键 （Primary Key）是表的复制标识，非空列上的唯一索引 （UNIQUE NOT NULL）也可以用作复制标识。
如果没有任何复制标识，可以显式将复制标识设置为 FULL，也就是把整个行当作复制标识
使用 FULL 模式的复制标识效率很低（因为每一行修改都需要在订阅者上执行全表扫描，很容易把订阅者拖垮），所以这种配置只能是保底方案。
使用 FULL 模式的复制标识还有一个限制，订阅端的表上的复制身份所包含的列，要么与发布者一致，要么比发布者更少

创建发布订阅

环境介绍

version: pg16
发布端: 5433
订阅端: 5434
主机 IP: 192.168.28.11

创建发布订阅

在发布者端，wal_level 必须被设置为 logical，而 max_replication_slots 中设置的值必须至少是预期要连接的订阅数加上保留给表同步的连接数。max_wal_senders 应该至少被设置为 max_replication_slots 加上同时连接的物理复制体的数量。
订阅者必须配置 max_replication_slots。它必须设置为至少是订阅者数，加上一些用于表同步的预留。max_logical_replication_workers 必须至少被设置为订阅数加上保留给表同步的连接数。此外，可能需要调整 max_worker_processes 以容纳复制工作者，至少为 (max_logical_replication_workers + 1)。注意，一些扩展和并行查询也会从 max_worker_processes 中取得工作者槽。

发布端

1. 修改配置文件

vim postgres.conf
wal_level = logical 
max_wal_senders = 100 #max_wal_senders大于max_replication_slots 

vim pg_hba.conf
host    all    repl    订阅者IP/32    md5

重启 pgsql ，配置生效

su postgres
pg_ctl restart -D /data/postgresql/pgdata/pg-5433/ 

2. 发布者节点创建用户，赋权

su postgres
psql -P 5433
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.10.1))
Type "help" for help.
#创建用户
postgres=# create user repuser password 'repuser' replication;
CREATE ROLE
#在postgres库中创建表,并插入一条数据
postgres=# create table test1(id int primary key,name varchar(10) );
postgres=# insert into test1 values(1,'a'); # 赋予复制用户select表权限
postgres=# grant select on table test1 to repuser; 

3. 创建表 test1 的发布

create publication pub_test01 for table test1;

订阅端

1. 修改 posetgres.conf

vim postgres.conf
max_replication_slots=8
max_logical_replication_workers=10
max_worker_processes=8

max_logical_replication_workers 逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量
2. 重启 pgsql

pg_ctl restart -D /data/postgresql/pgdata/pg-5434/ 

3. 创建订阅

CREATE SUBSCRIPTION subs_test01  CONNECTION 'host=192.168.28.11 dbname=postgres port=5433 user=repuser password=repuser' PUBLICATION pub_test01;

4. 查看表是否同步
   在发布端执行 test1 表的 insert、delete、update、truncate 操作，看订阅端是否能同步

新加表同步

发布端

1. 新建表
   create table test3 (id int,name varchar(10));
2. 设置复制标识
   alter table test3 REPLICA IDENTITY full;
3. 将表加入发布
   ALTER PUBLICATION pub1 ADD TABLE test3;

订阅端

在订阅端执行刷新
ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION ;
在发布端执行 test3 表的 insert、delete、update、truncate 操作，看订阅端是否能同步

发布订阅查询状态语句

发布端

select * from pg_replication_slots; --查看所有复制槽 select * from pg_stat_replication; --查看复制槽的同步状态 select * from pg_publication; --查看所有发布 select * from pg_publication_tables; --查看所有发布对应的表 select usename,a.pubname,c.*,pubinsert,pubupdate,pubdelete,pubtruncate from pg_publication a,pg_user b,pg_publication_tables c where a.pubowner=b.usesysid and c.pubname=a.pubname;

订阅端

select * from pg_subscription; --查看所有订阅 select srrelid::regclass from pg_subscription_rel; --查看订阅的所有表 select usename,datname,subname,srrelid::regclass,srsublsn,subconninfo from pg_subscription a,pg_user b,pg_subscription_rel c ,pg_database d where a.oid=c.srsubid and a.subowner=b.usesysid and a.subdbid=d.oid;

删除发布订阅

删除订阅

订阅在运行的情况下，需要一下操作：

ALTER SUBSCRIPTION pub_test01;
##首先需要停止当前的订阅 ALTER SUBSCRIPTION subs_test01 (slot_name =NONE);
##然后将订阅的复制槽设置成空
drop subscription subs_test01;
##最后就可以删除订阅了。 ##另外一个问题，复制订阅中，如果订阅的服务停止，或无法再次连接的情况下，需要关注 发布端的数据wal log 无法清理以及膨胀的问题。 ##所以在复制订阅中的订阅停止后，如果确认订阅无法再次恢复，或者不确认多长时间恢复，则需要删除复制槽 select * from pg_replication_slots；

select pg_drop_replication_slot(slot_name) from pg_replication_slots where slot_name = 'pub_test01';