PostgreSQL逻辑复制：新手入门指南

发表时间: 2023-08-15 14:38

一、逻辑复制部署

逻辑复制是基于表级别的选择性复制，可以复制主库的一部分表到备库。
逻辑复制是基于逻辑解析，其核心原理是主库将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;