一、逻辑复制部署

1.1 概念

• 逻辑复制是基于表级别的选择性复制，可以复制主库的一部分表到备库。
• 逻辑复制是基于逻辑解析，其核心原理是主库将WAL日志流解析成一定格式，订阅节点收到解析的WAL数据流后进行应用，从而实现数据同步，逻辑复制并不是使用WAL原始日志文件文件进行复制，而是将WAL日志解析成了一定格式。
• 逻辑解析的前提是设置wal_level参数为logical，并且设置max_replication_slots参数至少为1。

1.2 主要使用场景

• 根据业务需求，将一个数据库中的一部分表同步到另一个数据库。
• 满足报表库取数需求，从多个数据库采集报表数据。
• 实现PostgreSQL跨大版本数据同步。
• 实现PostgreSQL大版本升级。

1.3 发布节点配置

vim pg_hba.conf

host all all 10.9.0.100/32 trust

vim postgresql.conf

wal_level = logical

max_wal_senders = 10

max_replication_slots = 10

创建逻辑复制用户

create user logocal_user replication login connection limit 8 encrypted password 'logical_user';

发布节点上创建测试表

create database mydb;

\c mydb;

create table t_lr1(id int4,name text);

insert into t_lr1 values (1,'a');

在发布节点创建发布

create publication pub1 for table t_lr1;

1.4 订阅节点配置

vim postgresql.conf

max_replication_slots = 8

max_logical_replication_workers = 8

在订阅节点创建表

create database des;

\c des;

create table t_lr1(id int4,name text);

在订阅节点创建订阅

create subscription sub1 connection 'host=10.9.0.99 port=5432 dbname=mydb user=logical_user' publication pub1;

在发布节点查看复制槽

select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name = 'sub1';

订阅节点查看订阅信息

select * from pg_subscription;

之后在订阅节点验证表t_lr1数据是否同步过来。

select * from pg_subscription;

发现订阅节点数据为空，查看订阅节点数据库日志

发现订阅节点sub1上表t_lr1的逻辑复制已开始，但是无法初始化复制数据，原因是没有对pguser模式的读权限。

在发布节点对logical_user赋权。

grant usage on schema public to logical_user;

grant select on t_lr1 to logical_user;

此时再次查看订阅节点的数据库日志

再次在订阅节点查看表数据进行验证

以上仅验证了原始数据已同步

这时在发布节点主机可以看到新增了一个WAL发布进程。

订阅节点可以看到新增了一个WAL订阅进程。

二、逻辑复制DML数据验证

2.1 验证发布节点的INSERT/UPDATE/DELETE操作是否会同步到订阅节点

insert into t_lr1 values (2,'b');

订阅节点验证，数据已复制

发布节点更新数据

update t_lr1 set name = 'bb' where id = 2;

之前提到，如果需要将发布节点表上的update/delete操作逻辑复制到订阅节点，加入发布的表需要有replica identity，默认情况下使用主键作为复制标识，如果没有主键也可以是唯一索引。

发布节点和订阅节点给表加上主键。

alter table t_lr1 add primary key(id);

此时再在发布节点更新数据

update t_lr1 set name = 'bb' where id = 2;

订阅节点验证数据

2.2 验证delete操作是否会同步到订阅节点

发布节点操作

delete from t_lr1 where id = 2;

订阅节点验证

上图说明delete操作已同步。

三、逻辑复制添加表、删除表

实际生产维护过程中有增加逻辑同步表的需求，逻辑复制支持向发布中添加同步表，并且操作非常方便。

发布节点创建一张大表t_big，并插入1000万数据

create table t_big(id int4 primary key,create_time timestamp(0) without time zone default clock_timestamp(),name character varying(32));

insert into t_big(id,name) select n,n*random()*10000 from generate_series(1,10000000) n;

将t_big的select权限赋给逻辑复制用户logical_user

grant select on t_big to logical_user;

在发布节点上将t_big加入到发布pub1

alter publication pub1 add table t_big;

查看发布中的表列表

\dRp+ pub1

select * from pg_publication_tables;

订阅节点创建t_big表，注意，仅创建表结构，不插入数据

create table t_big(id int4 primary key,create_time timestamp(0) without time zone default clock_timestamp(),name character varying(32));

由于是发布节点新增的表，订阅节点上表的数据此时还未复制过来，因此订阅节点需要执行以下命令：

alter subscription sub1 refresh publication;

此命令执行后，订阅节点开始同步发布节点的表数据了，

订阅节点验证数据

select count(*) from t_big;

如果由于需求调整，逻辑复制中t_big表不再需要逻辑同步，只需要在发布节点上将t_big表从发布pub1中去掉即可。

alter publication pub1 drop table t_big;

此命令执行后，发布节点、订阅节点上的t_big表将没有任何同步关系，两张表为不同库中独立的表，只是表名一样而已。

四、逻辑复制启动和停止

订阅节点操作停止订阅，中断实时同步数据

alter subscription sub1 disable;

查询pg_subscription视图的subenabled字段判断是否已停止订阅

select subname,subenabled,subpublications from pg_subscription;

订阅节点操作开启订阅

alter subscription sub1 enable;

select subname,subenabled,subpublications from pg_subscription;

五、逻辑复制注意事项及限制

• 发布节点的wal_level参数需要设置成logical。
• 发布节点上逻辑复制用户至少需要replication角色权限。
• 发布节点上需要发布的表如果需要update/delete操作同步到订阅节点，需要给发布表配置复制标识（主键、唯一约束）。
• 发布时可选择DML操作中的一项或多项，默认是发布三项（insert、update、delete）。
• 支持一次发布一个数据库中的所有表。
• 一个数据库中可以有多个发布。