逻辑复制原理
逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日之后进行重做，从而实现表数据同步；
逻辑复制架构中最重要的两个角色为Publication和Subscription。
Publication(发布)可以定义在任何可读写的PostgreSQL实例上，对于已创建Publication的数据库成为发布节点，一个数据库中允许创建多个发布，目前允许加入发布的对象只有表，允许多个表注册到一个发布中。加入发布的表通常需要有复制标识(replica identity)，从而使逻辑主库表上的DELETE/UPDATE操作可以标记到相应数据行并复制到逻辑备库上的相应表，默认情况下使用主键作为数据标识，如果没有主键，也可以是唯一索引，如果没有主键或者唯一索引，可设置复制标识为full，意思是整行数据作为键值，这种情况下复制效率会变低。如果加入发布的表没有指定复制标识，表上的UPDATE/DELETE将会报错。
Subscription(订阅)实时同步指定发布者的表数据，位于逻辑复制的下游节点，对于已创建Subscription的数据库称为订阅节点，订阅节点上的数据库同时也能创建发布。发布节点发布的表的DDL不会被复制，因此，如果发布节点上发布的表结构更改了，订阅节点上需要手工对订阅的表进行DDL操作，订阅节点通过逻辑复制槽获取发布节点发送的WAL数据变化。
主库操作
（1）创建复制用户
create user logicrepl with replication login password 'logicrepl';
（2）给复制用户授权
grant usage on schema public to logicrepl;
--revoke usage on schema public from logicrepl;
注意：grant usage on schema是未来在这个schema建表，有usage的权限，而对于已存在的表，仍没有权限，需要通过grant单独授权。更坑的是，如果没有单独grant授权，届时复制搭建起来之后是从节点没有权限的错误，而不是主节点报错
grant select on t1 to logicrepl;
--revoke select on t1 from logicrepl;
（3）确认wal_level为logical级别
select * from pg_settings where name = 'wal_level';
wal_level 参数用于确定写入日志（WAL）的详细程度，可以设置为以下三个级别之一：
minimal：最小级别，只记录必要的信息，不支持逻辑复制。
replica：默认级别，记录足够的信息以支持流复制和基于时间点恢复，但不支持逻辑复制。
logical：最高级别，记录所有信息以支持逻辑复制。
如果wal_level不为logical需要修改配置文件
wal_level = logical
重启数据库
（4）创建逻辑复制槽，逻辑复制槽的作用就是记录标记当前发布与订阅日志发送的位置信息
select * from pg_create_logical_replication_slot('logic_replication_slot01','pgoutput');
#删除逻辑复制槽
select pg_drop_replication_slot('logic_replication_slot01');
max_replication_slots 参数确定系统可以支持的最大复制槽数量。复制槽是一种用于接收 WAL 日志的缓冲区，用于支持物理和逻辑复制。每个复制槽都代表一个复制订阅者或复制目标。通过复制槽，数据可以从主服务器复制到一个或多个辅助服务器。
max_replication_slots 的默认值是 0，表示禁用复制槽。要启用逻辑复制，需要设置一个大于0的适当值。可以根据系统资源和需求来调整该值。
在启用逻辑复制并设置合适的 max_replication_slots 值后，系统将为每个逻辑复制订阅创建一个复制槽。
查看复制槽信息，active: t正在使用,f未使用
SELECT * FROM pg_replication_slots;
slot_name | logic_replication_slot01
plugin | pgoutput
slot_type | logical
datoid | 5
database | postgres
temporary | f
active | t
active_pid | 11783
xmin |
catalog_xmin | 756
restart_lsn | 0/1201D048
confirmed_flush_lsn | 0/1201D080
wal_status | reserved
safe_wal_size |
two_phase | f
conflicting | f
（5）创建发布（publication）
创建发布master_pulication并添加表
create publication master_pulication for table t1 with (publish = 'insert,update');
#如果要同步整个库
CREATE PUBLICATION pub_all_tables FOR ALL TABLES;
#添加表到发布对象master_pulication
alter publication master_pulication add table t2;
#删除发布
drop publication master_pulication;
#查看发布
select * from pg_catalog.pg_publication;
-[ RECORD 1 ]+------------------
oid | 24587
pubname | master_pulication
pubowner | 10
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | f
pubtruncate | f
pubviaroot | f
select * from pg_publication;
-[ RECORD 1 ]+------------------
oid | 24587
pubname | master_pulication
pubowner | 10
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | f
pubtruncate | f
pubviaroot | f
同样待会搭建好之后，订阅者可以select * from pg_subscription;
查看发布包含的对象
select current_database(),pgtab.tablename,pgpub.pubname,pgpub.puballtables,pgpub.pubinsert,pgpub.pubupdate,pgpub.pubdelete,pgpub.pubtruncate
from pg_publication as pgpub
inner join pg_publication_tables as pgtab on pgpub.pubname = pgtab.pubname;
-[ RECORD 1 ]----+------------------
current_database | postgres
tablename | t1
pubname | master_pulication
puballtables | f
pubinsert | t
pubupdate | t
pubdelete | f
pubtruncate | f
（6）发布对象的测试数据insert一些数据
postgres=# select * from t1;
id
----
1
2
3
1
5
(5 rows)
备库操作
（1）创建订阅
create subscription slave_subscription
connection 'host=43.138.214.171 port=5432 dbname=postgres user=logicrepl password=logicrepl'
publication master_pulication with (create_slot = false,slot_name =logic_replication_slot01,copy_data = true);
（2）查看订阅
select * from pg_subscription;
postgres=# select * from pg_subscription;
-[ RECORD 1 ]-------+---------------------------------------------------------------------------------
oid | 24588
subdbid | 5
subskiplsn | 0/0
subname | slave_subscription
subowner | 10
subenabled | t
subbinary | f
substream | f
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subconninfo | host=43.138.214.171 port=5432 dbname=postgres user=logicrepl password=logicrepl
subslotname | logic_replication_slot01
subsynccommit | off
subpublications | {master_pulication}
suborigin | any
（3）启动订阅
alter subscription slave_subscription enable;
#删除订阅的步骤
--停止订阅
alter subscription slave_subscription disable;
alter subscription slave_subscription SET (slot_name =NONE);
drop subscription slave_subscription;
（4）查看订阅数据同步
postgres=# select * from pg_subscription;
oid | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | subpasswordrequired | subrunasowner |
subconninfo | subslotname | subsynccommit | subpublications | suborigin
-------+---------+------------+--------------------+----------+------------+-----------+-----------+------------------+-----------------+---------------------+---------------+--
--------------------------------------------------------------------------------+--------------------------+---------------+---------------------+-----------
24588 | 5 | 0/0 | slave_subscription | 10 | t | f | f | d | f | t | f | h
ost=43.138.214.171 port=5432 dbname=postgres user=logicrepl password=logicrepl | logic_replication_slot01 | off | {master_pulication} | any
(1 row)
postgres=# select * from t1;
id
----
1
2
3
1
5
(5 rows)
PG逻辑复制特点
和MySQL相比，他局限性很明显，举个例子
例如包含了多张表的时候create publication master_pulication for table t1,t2 with (publish = ‘insert,update’);其发布类型只能是一样的，比如master_pulication 包含了2张表，如果想让t1表的增删改（insert/update/delete）都发布，t2表只发布增改(insert/update)，postgresql的逻辑发布做不到，但是在其他数据库是可以的。
另外PG逻辑复制不支持 DDL 复制，配置复杂度明显高于MySQL，不适合整个实例的备份。
同样逻辑复制都有延迟的问题。
和流复制相比
流复制只能做到整个cluster的复制，逻辑复制则更加灵活，可以做到表级同步。
流复制的standby备库是只读的，不能写入，而逻辑复制读写都可以。
逻辑复制支持在不同版本数据库之间进行逻辑复制，所以可以利用来进行跨版本升级。
支持异构架构之前的同步，比如Linux -> Windows。
扫一扫
590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
