PostgreSQL14-逻辑复制（10版本开始）

14.逻辑复制（10版本开始）

逻辑复制，同步一张表

发布节点

vim postgresql.conf
wal_level = logical
max_replication_slots = 8
max_wal_senders = 10

允许订阅库连接
vim pg_hba.conf
host    postgres        logical_user    172.21.16.10/32         md5

创建逻辑复制用户，创建测试表，赋予权限，创建发布
create user logical_user replication login connection limit 8 encrypted password '123456';
create table t1 (id int4, name text, primary key(id));
grant select on t1 to logical_user;
create publication pub1 for table t1;

select * from pg_replication_slots;
select * from pg_publication;

订阅节点

vim postgresql.conf
max_replication_slots = 8
max_logical_replication_workers = 8

create subscription sub1 connection 'host=172.21.16.6 port=5432 dbname=postgres user=logical_user password=123456' publication pub1;

select * from pg_subscription;

逻辑复制添加表/删除表

发布节点

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()*1000 from generate_series(1,100000) n;

grant select on t_big to logical_user;
alter publication pub1 add table t_big;
查看发布中的表列表
\dRp+ pub1

从发布列表中删除t_big表
alter publication pub1 drop table 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;