这篇PostgreSQL逻辑复制文章够用了吧？

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：OCP、PCP
Skill：Oracle、Mysql、PostgreSQL
Platform：CSDN、墨天伦、公众号（呆呆的私房菜）

select * from pg_replication_slots;

# 设置日志模式
wal_level=logical
# 指定复制槽数
max_replication_slots
# 设置wal日志发送进程数量
max_wal_senders

# 指定逻辑复制工作的最大数据量，默认是4
max_logical_replication_workers
# 指定每个订阅最大同步工作者数量，默认是2
max_sync_workers_per_subscriptio
# 指定工作进程数
max_worker_processes

# 发布订阅实例
1. 创建一个发布，发布两个表中的所有修改
create publication p1 for table t1, t2;


2. 创建一个发布，发布所有表中的所有更改
create publication allp for all tables;


3. 创建一个发布，只发布一个表中的insert操作
create publication insertp for table t3 with (publish='insert');


4. 将发布修改为只发布删除和修改
alter publication noinsertp set (publish='update,delete');


5. 给发布添加一些表
alter publication p1 add table t4, t5;


6. 查看发布
select * from pg_publicatin;


# 订阅常用命令
1. 创建一个到远程服务器的订阅，复制发布p1和insertp中的表（即t1,t2,t3），并在提交时立即复制
create subscription s1 
connection 'host=10.0.0.21 port=5432 user=test dbname=testdb' 
publication p1, insertp;


2. 创建一个到远程服务器的订阅，复制发布insertp中的表，并且不开始复制直到启用复制
create subscription s2
connection 'host=10.0.0.21 port=5432 user=test dbname=testdb' 
publication insertp with (enabled=false);


3. 更改订阅发的发布：
alter subscription s1 set publication insertp;


4. 停止订阅
alter subscription s1 disable;

一、环境说明
1. 发布端：
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 行记录)


2. 订阅端：
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 行记录)


二、环境准备
1. 发布端配置参数
vi $PGDATA/postgresql.conf
listen_addresses = '*'
wal_level = logical
max_replication_slots=10


2. 订阅端配置参数
vi $PGDATA/postgresql.conf
max_logical_replication_workers=2
max_sync_workers_per_subscription=2


3. 创建同步数据库和用户
create user repl replication login connection limit 10 encrypted password 'repl';


4. 模拟业务用户和表
create user test;
create database testdb owner test;
create schema testdb authorization test;
# 创建表1
create table testdb.t1 (id int);
insert into testdb.t1 values (1),(2),(3);


5. 授予同步用户权限
grant connect on database testdb to repl;
grant usage on schema testdb to repl;
grant select on testdb.t1 to repl;
\dp+ t1;




三、创建发布
testdb=> create publication p1 for table testdb.t1;
CREATE PUBLICATION
testdb=> select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------+------------
 16430 | p1      |    16413 | f            | t         | t         | t         | t           | f
(1 行记录)


四、创建订阅
1. 创建接收表
create user test;
create database testdb owner test;
create schema testdb authorization test;
create table testdb.t1 (id int);


2. 创建订阅
postgres=# create subscription s1 connection 'host=10.28.12.21 port=5432 dbname=testdb user=repl' publication p1;
NOTICE:  created replication slot "s1" on publisher
CREATE SUBSCRIPTION


postgres=# select * from pg_subscription;
  oid  | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner |                    subconninfo                     | subslotname | subs
ynccommit | subpublications | suborigin 
-------+---------+------------+---------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+----------------------------------------------------+-------------+-----
----------+-----------------+-----------
 16397 |       5 | 0/0        | s1      |       10 | t          | f         | f         | d                | f               | t                   | f             | host=10.28.12.21 port=5432 dbname=testdb user=repl | s1          | off 
          | {p1}            | any
(1 row)


五、测试同步
1. 发布端：
testdb=> select * from testdb.t1;
 id 
----
  1
  2
  3
(3 行记录)


testdb=> insert into testdb.t1 values (4);
INSERT 0 1


2. 目标端
postgres=# select * from testdb.t1;
 id 
----
  1
  2
  3
  4
(4 rows)

解决方案：
1. 主库创建复制槽，检查备库wal文件是否连续；
2. 主库复制$PGDATA/pg_repslot包含slot信息的物理文件到备库;
3. 备库重启，重启后才可以看到复制槽信息，原因是读取slot物理文件的函数只会在postmaster进程启动时调用；
4. 定期查询主库slot状态，使用pg_replication_slot_advance函数推进备库复制槽

解决方案：使用事务触发器来处理
1. 使用事件触发器记录变动的表结构，记录到ddl_change表中，并将该表通过逻辑复制进行发布；
2. 订阅端接收到该表的数据变更后，处理为相应的ddl语句去执行。

解决方案：
1. 使用占位符的方式处理，订阅端接收到占位符就不对这一列进行处理，过程比较麻烦。

解决方案：
1. 创建一张心跳表，周期性写入数据并发布，推进xmin，防止wal堆积。