PostgreSQL-逻辑复制「实战」

逻辑复制的原理描述呢，网上很多，书上也很多，这里就不多说了。

好吧，网上找了一个，和书里说的一样，照搬 逻辑复制。其实，我觉得这篇逻辑复制后面的操作，有点乱，真的。

以下，才是真实的测试实战（有图有真相）！

角色	主机名	IP	端口	数据库名	用户名	版本
发布端	pgccc01	192.168.8.128	5432	pubdb	ljfz_u01	PostgreSQL15.2
订阅端	pgccc02	192.168.8.129	5432	subdb	ljfz_u01	PostgreSQL15.2

发布端配置

发布端- postgresql.conf 配置

listen_addresses = '*'
wal_level=logical
max_wal_senders=10
max_replication_slots=10

发布端- pg_hba.conf 配置

host    all          all         192.168.8.129/32        trust

发布端-创建逻辑复制用户

create user ljfz_u01;
alter user ljfz_u01 with password 'pgccc';
alter user ljfz_u01 with replication;
create database pubdb;

发布端-创建逻辑复制表

\c pubdb ljfz_u01
create table t_ztjs (id int2 primary key,name varchar(20));
insert into t_ztjs values(1,'叶凡');
insert into t_ztjs values(2,'段德');
insert into t_ztjs values(3,'庞博');
insert into t_ztjs values(4,'黑皇');

发布端-授权逻辑复制用户

\c pubdb postgres
grant connect on database pubdb to ljfz_u01;
grant usage on schema public to ljfz_u01;
grant select on t_ztjs to ljfz_u01;
grant create on database pubdb to ljfz_u01;

发布端-创建逻辑复制的“发布”

\c pubdb ljfz_u01
create publication pub1 for table t_ztjs;

发布端-相关监控和视图

\c pubdb postgres
select * from pg_publication;
select * from pg_publication_tables;
select * from pg_stat_replication;
select * from pg_replication_slots;

订阅端配置

订阅端- postgresql.conf 配置

listen_addresses = '*'
wal_level=logical
max_replication_slots=10
max_logical_replication_workers = 10

订阅端-创建逻辑复制用户

create user ljfz_u01;
alter user ljfz_u01 with replication;
alter user ljfz_u01 with password 'pgccc';
create database subdb;

订阅端-授权逻辑复制用户

\c subdb postgres
grant connect on database subdb1 to ljfz_u01;
grant usage on schema public to ljfz_u01;
grant create on schema public to ljfz_u01;

订阅端-创建逻辑复制表

\c subdb1 ljfz_u01
create table t_ztjs (id int2 primary key,name varchar(20));

订阅端-创建逻辑复制的“订阅”

\c subdb postgres
create subscription sub1 connection 'host=192.168.8.128 port=5432 dbname=pubdb user=ljfz_u01 password=pgccc' publication pub1;

订阅端-相关监控和视图

\c subdb postgres
select * from pg_subscription;
select * from pg_subscription_rel;
select * from pg_stat_subscription;

逻辑复制的测试

插入数据

创建新表

在这里，订阅端不会自动刷新，所以需要手动执行命令 

alter subscription sub1 refresh publication;

其他的一些命令

# 回收权限
revoke create on database subdb from ljfz;
# 删除订阅
drop subscription sub1;
# 授权
grant create on all tables in schema public to ljfz;
# 开启订阅
alter subscription sub1 enable;

# 跳过冲突的事务。
# node_name 就是 subscription name
# pos 指重新开始的 LSN
pg_replicatioin_origin_advance(node_name text,pos pg_lsn)