逻辑复制介绍

原理：

逻辑复制是解析WAL日志，将Publication（发布端）中表的WAL日志解析成一定格式并发送给Subscription（订阅端），Subscription接收到解析后的WAL日志后进行重放应用，从而实现表数据的同步。

优点：

• 流复制只能做到整个cluster的复制，而逻辑复制则更加灵活，可以做到库级别、表级别的同步
• 流复制的standby备库是只读的，不能写⼊，⽽逻辑复制备库读写都可以。
• 逻辑复制⽀持不同os架构，不同数据库版本之间的数据复制

缺点：

• 逻辑复制的配置和流复制相比相对复杂。
• 相对于流复制，逻辑复制可能存在一定的复制延迟。逻辑复制需要等待事务提交后，发布端才会使用wal_sender进程将decode后的数据发送给订阅端。
• 逻辑复制不支持DDL复制(ALTER TABLE/CREATE TABLE)
• 逻辑复制只支持表复制，不支持其他对象的复制

适用场景：

• 指定库或部分表的复制需求
• 将多个数据库实例的数据汇聚到同一个目标库
• 将一个库的数据分发到多个不同的库
• 不同的版本之间的复制
• 不同库名之间的表同步
• 无停机升级数据库
• 跨版本升级数据库

注意：
逻辑复制会在发布端创建逻辑复制槽，如果订阅端数据库异常，一直没有消费发布端的wal日志，会导致发布端wal日志的堆积进而存在磁盘撑爆的风险，需要特别注意。

逻辑复制搭建

搭建流程

环境说明

发布端数据库参数要求

max_replication_slots = 10 #复制槽的最大数量
hot_standby = on    #允许在应用WAL日志的同时，提供只读服务
wal_level =  logical  #逻辑复制中必须是 logical
#hot_standby_feedback = on   #数据接收端需要进行回馈
max_wal_senders = 10     #wal sender的最大数量

#pg17中需要修改下面两个参数：
postgres=# alter system set wal_level=logical;
ALTER SYSTEM
postgres=# alter system set max_replication_slots=10;
ALTER SYSTEM
postgres=# alter system set max_wal_senders=12; --应该将此参数设置的比 max_replication_slots 参数要大

#修改参数后需要重启数据库才能生效：
pg_ctl restart

订阅端数据库参数要求

max_replication_slots = 10 #复制槽的最大数量
max_logical_replication_workers #逻辑复制worker进程的最大数量

#pg17中需要修改下面两个参数：
postgres=# alter system set max_replication_slots=10;
ALTER SYSTEM
postgres=# alter system set max_logical_replication_workers=10; 
ALTER SYSTEM
postgres=# 

#修改参数后需要重启数据库才能生效：
pg_ctl restart

发布端创建逻辑复制用户

create user replusr with password '******';
alter user replusr replication;
#创建测试表
create table tab1(id int primary key,name varchar(100));
insert into tab1 values(1,'a');
insert into tab1 values(2,'b');
insert into tab1 values(3,'c');
insert into tab1 values(4,'d');
insert into tab1 values(5,'e');

发布端创建发布

创建发布语法如下：
postgres=# \h create publication 
Command:     CREATE PUBLICATION
Description: define a new publication
Syntax:
CREATE PUBLICATION name
    [ FOR ALL TABLES
      | FOR publication_object [, ... ] ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

where publication_object is one of:

    TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]

URL: https://www.postgresql.org/docs/17/sql-createpublication.html

postgres=# 

创建发布：

create publication pub_tab1 for table tab1;
# 查询发布信息
select * from pg_publication;

订阅端创建空表

create table tab1(id int primary key,name varchar(100));

订阅端创建订阅

postgres=# \h create subscription 
Command:     CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

URL: https://www.postgresql.org/docs/17/sql-createsubscription.html

postgres=# 

创建订阅示例：

create subscription sub_tab1 connection 'host=192.*.*.60 port=5555 dbname=postgres user=replusr password=******' publication pub_tab1;
说明：创建订阅的同时会在发布端创建逻辑复制槽，保证没有被订阅端消费的wal日志不能被删除。

#查询订阅信息
select * from pg_subscription;

订阅端查询数据同步情况

postgres=# select * from tab1;
 id | name 
----+------
(0 rows)
数据没有同步过来。

数据没有同步的分析过程：

检查订阅端的数据库日志

2024-12-14 23:16:41.711 CST [19256] LOG:  logical replication table synchronization worker for subscription "sub_tab1", table "tab1" has started
2024-12-14 23:16:41.762 CST [19256] ERROR:  could not start initial contents copy for table "public.tab1": ERROR:  permission denied for table tab1
2024-12-14 23:16:41.764 CST [18578] LOG:  background worker "logical replication tablesync worker" (PID 19256) exited with exit code 1

从日志可以发现，复制用户replusr对表所属的schema（public）及表（tab1）没有读写权限。
在发布端进行表授权

postgres=# grant usage on schema public to replusr;
GRANT
postgres=#  grant select on tab1 to replusr ;
GRANT
postgres=# alter default privileges in schema public grant select on tables to replusr ;
ALTER DEFAULT PRIVILEGES

订阅端再次检查数据同步情况

postgres=# select * from tab1;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
  4 | d
  5 | e
(5 rows)

postgres=# 

附录

PostgreSQL 17.2安装部署

请参考文章 PostgreSQL 17.2 数据库编译安装

总结

PostgreSQL逻辑复制搭建过程还是挺容易的，但是实际生产过程中可能会碰到各种问题，对数据同步有高要求的系统需要慎用。

关于作者：
专注于Oracle、MySQL、PG、OpenGauss和国产数据库的研究，热爱生活，热衷于分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同进步~~~