作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
擅长主流数据Oracle、MySQL、PG、openGauss运维
备份恢复，安装迁移，性能优化、故障应急处理等

可提供技术业务：
1.DB故障处理/疑难杂症远程支援
2.Mysql/PG/Oracle/openGauss
数据库部署及数仓搭建
•••
微信:jem_db
QQ交流群:587159446
公众号：IT邦德
•••

前言

本文详细讲解了postgreSQL逻辑复制采用发布者及订阅者模型的全过程。

1.逻辑复制简介

逻辑复制原理∶ 逻辑主库将表的WAL日志解析成一定格式的数据流并发送给逻辑备库，
逻辑备库接收到解析后的WAL日志后进行重做，从而实现表数据同步。
逻辑复制:基于部分库及表复制。
1.从PostgreSQL10开始，出现了基于表级别的复制，即逻辑复制。
2.逻辑复制，适合于发布端与订阅端都有读写的情况。
3.逻辑复制，更适合于小事务，或者低密度写（轻度写)的同步。

2.源端参数配置

alter system set wal_level='logical';
alter system set listen_addresses='*';
SELECT pg_reload_conf();

3.源端创建复制用户

发布节点创建复制用户和表

-- 用于逻辑复制的用户必须至少是replication 角色
CREATE USER repuser REPLICATION LOGIN 
CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'jeames';

--授权
grant connect on database postgres to repuser;
grant USAGE on SCHEMA public to repuser;

--创建复制表
create table test(id int primary key ,name text); 
insert into test values (1,'a');
\dp+ test;

此处给repuser授权使用该表很重要
postgres=# grant select on test to repuser;

--发布节点创建发布

订阅节点的postgresql.conf配置文件设置如下参数：
#设置数据库复制槽数量，应大于订阅节点的数量
max_replication_slots=8
#设置逻辑复制进程数，应大于订阅节点的数量，
并且给表同步预留一些进程数量，此参数默认值为4
max_logical_replication_workers=10	

#为表test创建发布
create PUBLICATION pub1 FOR TABLE test;

#查看创建的发布
select * from pg_publication;

4.目标端订阅

-- 订阅节点创建接收表及订阅
create table test(id int primary key ,name text);
create subscription sub1 connection 
'host=192.168.3.10 port=5432 dbname=postgres 
user=repuser password=jeames' publication pub1;

select * from pg_subscription;

5.测试同步

--  发布节点分别向表中插入、删除数据
insert into test values (2,'b');
delete from test where id=1;


-- 订阅节点查看逻辑复制效果
select * from  test ;

6.总结

Postgresql支持“逻辑”和“物理”两种复制方式，逻辑复制是一种基于数据对象的复制标识（通常是主键）复制数据对象及其修改的方法，逻辑复制允许数据复制和安全性上更细粒度的控制