作者：Digital Observer（施嘉伟） Oracle ACE Pro: Database PostgreSQL ACE Partner 11年数据库行业经验，现主要从事数据库服务工作 拥有Oracle OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证 ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
第一部分 说明
逻辑复制是一种基于数据对象的复制标识（通常是主键）复制数据对象及其更改的方法。我们使用术语“逻辑”来与物理复制加以区分，后者使用准确的块地址以及逐字节的复制方式。PostgreSQL两种机制都支持。逻辑复制允许在数据复制和安全性上更细粒度的控制。 逻辑复制使用一种发布和订阅模型，其中有一个或者更多订阅者订阅一个发布者节点上的一个或者更多发布。订阅者从它们所订阅的发布拉取数据并且可能后续重新发布这些数据以允许级联复制或者更复杂的配置。 一个表的逻辑复制通常开始于对发布者服务器上的数据取得一个快照并且将快照拷贝给订阅者。一旦这项工作完成，发布者上的更改会被实时发送给订阅者。订阅者以与发布者相同的顺序应用那些数据，这样在一个订阅中能够保证发布的事务一致性。这种数据复制的方法有时候也被称为事务性复制。 逻辑复制的典型用法是： 在一个数据库或者一个数据库的子集中发生更改时，把增量的改变发送给订阅者。 在更改到达订阅者时引发触发器。 把多个数据库联合到单一数据库中（例如用于分析目的）。 在PostgreSQL的不同主版本之间进行复制。 在不同平台上（例如Linux到Windows）的PostgreSQL实例之间进行复制。 将复制数据的访问给予不同的用户组。 在多个数据库间共享数据库的一个子集。
第二部分 环境搭建
2.1 环境准备
在搭建逻辑复制环境前，首先初始化两个集簇，并配置好相关配置信息，例如连接信息等，本文不再赘述，具体参考PG安装标准化文档。 pg版本：pg13
2.2 主库配置
1.2.1 修改配置文件
配置postgresql.conf，修改一下参数
# vi postgresql.confwal_level = logical max_wal_senders = 10 max_replication_slots = 8
参数说明：
| 参数 | 描述 |
|---|---|
| wal_level | 设置成logical才支持逻辑复制 |
| max_wal_senders | 由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此此参数设置值需大于max_replication_slots参数值加上物理备库数量 |
| max_replication_slots | 设置值需大于订阅节点的数量 |
1.2.2 修改pg_hba.conf白名单文件
主备库都需要添加如下信息
host replication all 192.168.0.0/16 trust
1.2.3 创建逻辑复制账号
逻辑复制账号需要replication权限，在发布节点（即主库）上将需要同步的表赋权限给logical_repl用户。
postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl';
1.2.4 创建用于复制的数据库和表
$ createdb sourcedb $ psql -d sourcedbsourcedb=# create table logical_tb1(id int primary key,name varchar(20));
1.2.5 创建发布
$ psql -d sourcedbsourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1;
查看发布信息
sourcedb=# SELECT * FROM pg_publication;
参数说明
| 参数 | 描述 |
|---|---|
| pubname | 发布的名称 |
| pubowner | 发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息 |
| puballtables | 是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表 |
| pubinsert | t表示仅发布表上的INSERT操作 |
| pubupdate | t表示仅发布表上的UPDATE操作 |
| pubdelete | t表示仅发布表上的DELETE操作 |
1.2.6 授权账号
sourcedb=# GRANT USAGE ON SCHEMA public TO logical_repl; GRANT sourcedb=# GRANT SELECT ON logical_tb1 TO logical_repl; GRANT
2.3 从库配置
1.3.1 修改配置文件
配置postgresql.conf，修改一下参数
# vi postgresql.confwal_level = logical max_replication_slots = 8 max_logical_replication_workers = 8
参数说明：
| 参数 | 描述 |
|---|---|
| wal_level | 设置成logical才支持逻辑复制,逻辑从库可以视情况设置 |
| max_replication_slots | 设置数据库复制槽数量，应大于订阅节点的数量 |
| max_logical_replication_workers | 设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数缺省值为4 |
1.3.2 创建同步发布节点的库和表
$ createdb desdb $ psql -d desdb desdb=# create table logical_tb1(id int primary key,name varchar(20));
1.3.3 创建订阅
desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.22.128 port=543 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1;
第三部分 测试同步
3.1 测试已发布表数据
逻辑主库中插入测试数据
sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca'), (3,'abc');
逻辑从库中查看数据是否同步
desdb=# select * from logical_tb1;
3.2 测试发布新表
逻辑主库中创建新表并授权
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100)); sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl;
逻辑从库上也手动创建该表
desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
逻辑主库中添加新表到发布列表
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2;
查看已经发布的列表表名
sourcedb=# SELECT * FROM pg_publication_tables;
插入数据到新表中
sourcedb=# insert into logical_tb2 values(1,'hangzhou');
逻辑从库中查看，没有查询到数据
desdb=# select * from logical_tb2;
刷新一下订阅
desdb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
再次查询数据，数据已正常同步
