一、逻辑复制简单介绍¶

逻辑复制是10版本以后支持的，与流复制不同的地方是逻辑复制并不是使用WAL原始日志文件进行复制，而是将WAL日志解析成了一定格式的的逻辑数据

逻辑复制使用的方式类似消息队列的发布者（publication）/订阅者（subscription）模型，使用订阅复制槽技术，可并行地传输WAL日志，通过在订阅端回放WAL日志中的逻辑条目保持复制表的数据同步，

但是需要注意注意：这里的逻辑复制不是“SQL”复制，而是复制SQL操作的结果

1.1 逻辑复制应用的场景¶

将多个数据库的数据合并到一个数据仓库的数据库中，用于数据仓库的数据分析。
不同大版本PostgreSQL之间的数据复制，可以辅助数据库跨大版本升级
捕获本机数据库的增量更新，发送给指定的异构数据库，或者kafka等等

1.2 如何配置逻辑复制的“发布”“订阅”¶

1.2.1 逻辑复制需要满足的条件¶

逻辑复制的前提是将数据库中的wal_level参数设置成“logical”；同时开启足够的worker 因为每一个订阅，都要消耗掉一个Replication Slot，需要消耗一个WAL Sender，一个worker进程
源库上逻辑复制的用户必须具有replicatoin或superuser角色并且订阅者要使用该用户连接数据库进订阅
发布者的pg_hba.conf需要设置replication条目，允许订阅者连接

host replication repuser 0.0.0.0/0 md5

发布者 max_replication_slots：设置数据库复制槽数量，应大于订阅节点的数量
max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量
在发布节点上创建逻辑复制用户，逻辑复制用户需要具备REPLICATION权限

create user rep replication login connection limit 8 password 'rep123';

1.2.2 逻辑复制的限制¶

逻辑复制目前仅支持数据库表逻辑复制，其他对象如函数、视图不支持
目前仅仅支持发布表，不允许发布其他对象
同一张表，可以发布多次
允许发布时，选择发布INSERT、UPDATE、DELETE，比如只发布INSERT，而不发布UPDATE、DELETE
逻辑复制支持DML（UPDATE、INSERT、DELETE）操作，不支持TRUNCATE和DDL操作
一个数据库中可以有多个发布，但是不能重名，通过pg_publication查看
允许一次发布所有表，语法：CREATE PUBLICATION alltables FOR ALL TABLES
一个发布允许有多个订阅者

二、如何创建逻辑复制¶

2.1 在主库创建发布¶

1、发布端配置参数

listen_addresses = '*'

wal_level=logical # 必须设置为“logical”，让WAL日志文件中记录逻辑解码所需的信息 max_replication_slots=10 # 支持的最大复制插槽数，重启服务才能生效

max_wal_senders=15 # 客户端的最大并发连接数，必须大于max_replication_slots参数值加上物理备库数，重 启服务才能生效

2、目标端配置参数

listen_addresses = '*'

wal_level=logical # 必须设置为“logical”，让WAL日志文件中记录逻辑解码所需的信息 max_replication_slots=10 # 支持的最大复制插槽数，设置数据库复制槽数量，应大于订阅节点的数量，重启服务才 能生效

max_logical_replication_workers=20 # 逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程 数量，重启服务才能生效

3、服务端发布命令

通过如下命令查看发布命令的执行

\h create publication

在主库执行发布

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

修改发布命令

ALTER PUBLICATION name ADD TABLE table_name 
ALTER PUBLICATION name drop TABLE table_name 
ALTER PUBLICATION name SET TABLE table_name

查看发布情况

SELECT * FROM pg_publication;

pubname： 指发布的名称
pubowner： 指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息 puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表 pubinsert： t表示仅发布表上的INSERT操作
pubupdate： t表示仅发布表上的UPDATE操作
pubdelete： t表示仅发布表上的DELETE操作

2.2 创建订阅¶

1、订阅知识点须知

订阅节点需要指定发布者的连接信息
一个数据库中可以有多个订阅者
同一个数据库中可以创建多个订阅者，这些订阅者可以被同一个或多个发布者使用
可以使用ENABLE/DISABLE命令启用/暂停该订阅
发布节点和订阅节点表的模式名、表名必须一致
发布节点增加表名，订阅节点需要执行“ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION”
如果要完全删除订阅，使用DROP SUBSCRIPTION命令，注意，删除订阅后，本地的表不会被删除
删除订阅后，如果要重新使用该订阅，数据会全部重新同步过来，比如订阅的上游表中有100万条数据， RESYNC会将这100万条数据同步过来，随后进入增量同步
订阅时，不会自动创建发布端的表，所以需要在订阅端先创建好表，目前发布端和订阅端的表定义必须完全一致，包括Schema，表名必须一致

2、创建一个订阅

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

CREATE SUBSCRIPTION mysub CONNECTION 'host=192.168.10.90 port=5432 user=rep dbname=test password=rep123' PUBLICATION mypub1, mypub2;

三、逻辑复制的限制¶

数据源发布和订阅节点需要运行PostgreSQL 9最新版本以上
支持从旧版本复制到新版本，因为PostgreSQL具有向后兼容性，但只有有限的向前兼容性比较安全
DDL操作不支持复制，发布节点上发布表进行DDL操作后， DDL操作不会复制到订阅节点，需要在订阅节点对发布表手动执行DDL操作
当前逻辑复制仅支持普通表，序列、视图、物化视图、分区表、外部表等对象都不支持
TEMPORARY表和UNLOGGED表不会被复制
大对象（Large Object）字段不支持复制
表名与列名必须结构相同，列的数据类型也必须相同
只有超级用户才有权限添加所有表
不支持双向复制
表必须有主键或者唯一约束，否则像UPDATE或者DELETE这样的操作无法被复制

四、逻辑复制如何做监控¶

我们设置好逻辑复制，现在我们已经设置好逻辑复制，那么我们也应该看看发生了什么以及它是否正常工作需要用到两个监视视图

select * from pg_stat_replication;

显示的是连接到当前主服务器的所有订阅者的连接信息

select * from pg_stat_subscription; 显示了订阅端服务器上订阅状态的信息。它包含每个订阅的一个条目，以及当前正在同步的每一个表（正在复制现有数据）

在发布上查看延迟

SELECT pid, usename,state , pg_wal_lsn_diff(pg_current_wal_lsn() , replay_lsn) replay_dely FROM pg_stat_replication WHERE application_name=’subl ’;

其中replay_dely 表示延迟的字节数

五、逻辑复制的冲突处理¶

因为逻辑复制的订阅着跟mysql的从库一样是可以去修改数据的，普通的DML操作类似，因为即使订阅者节点本地更改了数据，数据也将被更新。

所以就有可能产生如主键冲突，逻辑复制都将停止，这被称为冲突

所以冲突冲突会产生错误，并会停止复制，它必须由用户手动解决。有关冲突的详细信息可以在订阅者的服务器日志中找到

冲突的修复方法主要有以下两种：

方法一：通过修改订阅端的数据解决冲突。例如，INSERT违反了唯一约束时，可以先删除订阅端造成唯一约束冲突的记录，然后再使用ALTER SUBSCRIPTION name ENABLE让订阅继续
方法二：在订阅端调用pg_replication_origin_advance(node_name text,pos pg_lsn)函数，node_name 就是subscription name，“pos”指重新开始的LSN，从而跳过有冲突的事务。

六、逻辑复制搭建实践¶

发布端

创建用户命令：用于逻辑复制的用户必须是replication角色或者superuser角色。
create user rep replication login connection limit 8 password 'rep123';

发布节点为复制表创建发布
create database pubdb;

用rep用户切换到 pubdb
\c pubdb rep

创建表
create table tt(id int4 primary key ,name text);
insert into tt values (1,'a');

订阅端

创建相同的数据库
create database subdb;

创建user
create user rep replication login connection limit 8 password 'rep123';

切到用户下
\c subdb rep

创建和发布端相同的表
create table tt (id int4 primary key,name text);

进行订阅
\c subdb postgres
create subscription sub1 connection 'host=192.168.10.90 port=5432 dbname=pubdb user=rep password=rep123' publication pub1;

查看订阅信息
select * from pg_subscription;

授权
grant connect on database subdb to rep;
grant usage on schema public to rep;
grant select on tt to rep;

发布节点查询信息

SELECT slot_name,plugin,slot_type,database,active,restart_lsn FROM pg_replication_slots
where slot_name='sub1';

插入数据
insert into tt values (2,'tt');

订阅端查看效果

select * from tt;

PostgreSQL 逻辑复制实践：发布订阅、监控与冲突处理