逻辑复制是数据同步的一种方式，数据变化同步的一种方式。该方式一般是基于主键或者是唯一键来做的。逻辑复制使用的是分发订阅的模式实现的。
一、逻辑复制的应用场景
- 备库需要提供写能力的情况，例如双主库
- 只需要部分表的同步情况，例如数据上报、OLAP数仓等
- 不通平台数据库之间的复制，例如linux、windows上的pg之间的复制
二、逻辑复制注意事项
- 不支持分区表的同步，分区表只能按照子分区表来实现分发订阅
- 只支持普通表和普通表上的DML，不支持序列、视图、物化视图、外部表、大对象，不支持truncate、DDL
- 需要同步的表必须设置REPLICA IDENTITY ，通常使用主键或者唯一键
- 一个publisher可以包含一张或多张表，一张表可以有一个或多个publishers
- 逻辑复制不同于流复制，不是严格的主从关系，订阅者端的普通表依然可以进行增删改操作
- 同步表的表结构需要在发布者和订阅者两边保持一致
- 源库上逻辑复制的用户必须具有 replicatoin 或 superuser 角色；
三、参数配置
修改postgresql.conf和 pg_hba.conf两个配置文件，关于配置文件的说明可参考 配置文件详解
1、修改postgresql.conf数据库参数文件（修改这些参数需要重启数据库）a、发布者端设置设置wal_level 级别为logical：wal_level = logical设置max_wal_senders，此参数值要不小于max_replication_slots的参数值，默认值是10设置max_replication_slots，此参数值不少于subscriptions的个数，默认值是10b、订阅者端设置设置wal_level级别为logical：wal_level = logical设置max_logical_replication_workers，此参数值不少于订阅者的个数，默认是4设置max_worker_processes，此参数值不少于max_logical_replication_workers值+12、在pg_hba.conf添加白名单（根据真实情况自行限制网段）host all repuser 0.0.0.0/0 md53、创建专门用于逻辑复制的用户。该用户需要具有复制表的读权限，一般用超级用户。create user repl superuser login password 'repl654321';
四、发布者配置
publisher是逻辑复制的数据发布方，也是第一步。
--查看source_db数据库的发布者
source_db=# \dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
------+-------+------------+---------+---------+---------
(0 rows)
--在crmdb数据库上创建名为pub的发布者
source_db=# CREATE PUBLICATION pub;
CREATE PUBLICATION
source_db=#
source_db=# \dRp
List of publications
Name | Owner | All tables | Inserts | Updates | Deletes
-------+----------+------------+---------+---------+---------
pub | postgres | f | t | t | t
(1 row)
--查看pub发布的详细信息
source_db=# \dRp+
Publication pub
Owner | All tables | Inserts | Updates | Deletes
----------+------------+---------+---------+---------
postgres | f | t | t | t
(1 row)
五、订阅端配置
subscriber是逻辑复制的接收者。
--查看target_db数据库下的订阅者
target_db=# \dRs
List of subscriptions
Name | Owner | Enabled | Publication
------+-------+---------+-------------
(0 rows)
--在sub_db数据库上创建名为mysub的订阅者
target_db=# CREATE SUBSCRIPTION sub CONNECTION 'dbname=source_db host=source_ip user=repl password=repl654321 port=6432' PUBLICATION pub;
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION
target_db=#
target_db=# \dRs
List of subscriptions
Name | Owner | Enabled | Publication
-------+----------+---------+-------------
sub | postgres | t | {pub}
(1 row)
--查看订阅者mysub的详细信息
target_db=# \dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
-------+----------+---------+-------------+--------------------+-------------------------------------------------------------------------------------
sub | postgres | t | {pub} | off | dbname=source_ip host=source_ip user=repl password=repl654321 port=6432
(1 row)
六、添加需要同步的表
#在源端，也就是发布者的数据库
--创建表，注意需要有replication identity ，也即是需要主键
source_db=# create table tb1(id int primary key,col1 varchar(20));
CREATE TABLE
source_db=# insert into tb1(id,col1) select generate_series(1,3000),'teststring';
INSERT 0 3000
--一定要将表的权限赋予repl用户，否则日志会报权限不足无法复制
source_db=# grant usage in schema public to repl;
source_db=# grant all on on public.tb1 to repl;
-- 本例的表 有主键，逻辑复制会自动以主键作为 relica identity,下面三条sql1-3不用执行
-- 如果表没有主键 ，要添加一个唯一键作为 replica identity，否则就要以全部字段作为replica identity
source_db=# create unique index tb1_uk on tb1(id); -- sql 1
source_db=# alter user tb1 replica identity using index tb1_uk; -- sql 2 唯一键作为replica identity
source_db=# alter user tb1 replica identity full; -- sql 3全部字段作为replica identity
--添加到发布者pub
source_db=# alter publication pub add table tb1;
ALTER PUBLICATION
--查看发布者的详细信息
source_db=# \dRp+ pub
Publication pub
Owner | All tables | Inserts | Updates | Deletes
----------+------------+---------+---------+---------
postgres | f | t | t | t
Tables:
"public.tb1"
#在目标库，也就是订阅者端
--创建相同的表
target_db=# create table tb1(id int primary key,col1 varchar(20));
CREATE TABLE
--刷新一下订阅者
source_db=# ALTER SUBSCRIPTION sub REFRESH PUBLICATION;
ALTER SUBSCRIPTION
--查询数据是否已经同步过来
source_db=# select count(*) from tb1;
count
-------
3000
(1 row)
七、逻辑复制简单维护
7.1 查看复制
通过字典视图查看或者查看 主备库的 日志 ，日志开启详见 PG配置文件详解
字典视图的官方解释
source_db=# \x
Expanded display is on.
source_db=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 73466 ---wal_sender进程的进程ID
usesysid | 45135 ---(PostgreSQL) 用户连接到系统的唯一标识符
usename | repl ---它存储与用户相关的 usesysid 的名字
application_name | sub ---同步复制的通常设置，一般是 subscribe 名字
client_addr | 192.168.20.155---目标端的IP地址
client_hostname | target_db ---目标端的主机名
client_port | 42840 ---目标端和WALsender进行通信使用的TPC端口号
backend_start | 2020-02-27 21:55:07.852239+08 ---slave什么时间创建了流连接
backend_xmin | --This standby's xmin horizon reported by hot_standby_feedback.
state | streaming -- Current WAL sender state. Possible values are:
-- startup: This WAL sender is starting up.
-- catchup: This WAL sender's connected standby is catching up with the primary.
-- streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary.
-- backup: This WAL sender is sending a backup.
-- stopping: This WAL sender is stopping.
sent_lsn | 6FC/7B1CD9F0 -- Last transaction location sent to standby.
write_lsn | 6FC/7B1CD9F0 -- Last transaction written on disk at standby
flush_lsn | 6FC/7B1CC2C8 -- Last transaction flush on disk at standby.
replay_lsn | 6FC/7B1CD9F0 -- Last write-ahead log location replayed into the database on this standby server
write_lag | 00:00:00.000805
flush_lag | 00:00:00.000805
replay_lag | 00:00:00.000805
sync_priority | 0
sync_state | async
source_db=#
7.2 查看复制槽
select * from pg_replication_slots;
7.3 删除复制槽
slave在使用 primary_slot_name 参数时是无法删除 replication slots
#删除命令
source_db=# SELECT * FROM pg_drop_replication_slot('sub');
关于约束对逻辑复制的影响
一般来说，逻辑复制，不管是pg官方还是第三方，例如ogg等，目标的的触发器、约束都要禁用掉。
官方关于逻辑复制的文档
若有收获，就点个赞吧
0 人点赞
