详谈pg逻辑复制系列之逻辑复制适用场景及原理介绍(一)

作者:瀚高PG实验室 (Highgo PG Lab)- 禹晓

本文属于详谈pg逻辑复制系列文章的第一部分,作者计划分成3个部分分别介绍逻辑复制的原理、搭建及维护,感兴趣的朋友可以持续关注,所涉及的原理介绍及操作记录基于的pg版本为pg12.4。

一、逻辑复制的应用场景

我们在使用postgresql数据库作为生产用数据库时,我们经常采用流复制的形式来实现数据库的热备,当原主库发生故障时,备库可以快速的切换为主库来减少业务的停止时间。流复制是数据库物理级别的数据同步,在实际使用的场景中存在一些无法满足的需求,比如省公司的某张业务表要汇集所有地市的业务数据,或者总部某张基础表更新后,子公司需要实时同步这张基础表等等,对于以上场景,物理复制时无法满足的,此时我们可以使用逻辑复制来满足这些要求。
逻辑复制使用场景:

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

二、逻辑复制的原理

逻辑复制是基于WAL日志的逻辑解析,我们可以将WAL日志理解为ORACLE的redo日志。逻辑复制的原理是发布端的主库将Publication中表的WAL日志解析成一定格式并发送给备库的Subscription,Subscription接收到解析后的WAL日志后进行重放应用,从而实现表数据的同步。

详谈pg逻辑复制系列之逻辑复制适用场景及原理介绍(一)
可以看到与逻辑解析相关的两个重要部分是Publication和Subscription。

Publication(发布)创建在可读写的业务数据库实例上,对于已创建Publication的数据库称为发布节点。对于发布节点的功能及限制,详见第三部分。发布节点负责WAL日志的逻辑解析,这点大家要区别清楚。
Subscription(订阅)创建在逻辑复制备库上,对于已创建Subscription的数据库成为订阅节点,创建订阅时,默认情况下会在发布节点创建用于传输解析后的WAL日志的逻辑复制槽,订阅节点通过逻辑复制槽获取发布节点发送的WAL数据变化,因此当因为网络原因导致逻辑复制中断时,发布节点会一直保存订阅节点未应用的WAL日志,业务较为繁忙的情况下容易造成发布节点磁盘爆满,此问题大家一定要注意。

三、逻辑复制搭建步骤

详谈pg逻辑复制系列之逻辑复制适用场景及原理介绍(一)
1、发布节点创建逻辑复制用户
举例:create user logicalrep replication login encrypted password ‘logicalrep’; 必须有流复制权限

2、发布节点创建发布
创建发布节点语法

Command:     CREATE PUBLICATION  
Description: define a new publication  
Syntax:  
CREATE PUBLICATION name    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
      | FOR ALL TABLES ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ] where option can be:  
      PUBLISH INSERT | NOPUBLISH INSERT  
    | PUBLISH UPDATE | NOPUBLISH UPDATE  
    | PUBLISH DELETE | NOPUBLISH DELETE  
默认发布insert,update,delete。

举例:create publication pub1 for table t1

修改发布节点语法

Command:     ALTER PUBLICATION  
Description: change the definition of a publication  
Syntax:  
ALTER PUBLICATION name WITH ( option [, ... ] )  where option can be:  
      PUBLISH INSERT | NOPUBLISH INSERT  
    | PUBLISH UPDATE | NOPUBLISH UPDATE  
    | PUBLISH DELETE | NOPUBLISH DELETE  
ALTER PUBLICATION name ADD TABLE [ ONLY ] table_name [ * ] [, ...]ALTER PUBLICATION name SET TABLE [ ONLY ] table_name [ * ] [, ...]ALTER PUBLICATION name DROP TABLE [ ONLY ] table_name [ * ] [, ...]ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER PUBLICATION name RENAME TO new_name

发布者小结
①目前仅仅支持发布表,不允许发布其他对象。
②同一张表,可以发布多次。
③在同一个数据库中,可以创建多个publication,但是不能重名,通过系统表查看已创建的publication
④允许使用all tables发布所有表。
⑤一个publication允许有多个订阅者。
⑥目前publication仅支持insert, update, delete。允许发布时,选择发布insert、update、delete,比如只发布insert,而不发布update, delete。当发布了表的update, delete时,表必须设置replica identity,即如何标示OLD TUPLE,通过pk或者uk或者full。如果设置了nothing,则执行update,delete时会报错。
⑦create publication或者alter publication,发布或者修改发布内容中添加或者删除表时,都是事务级别,不会出现复制了部分事务的情况。⑧发布者需要设置wal_level=logical,同时开启足够的worker,设置足够大的replication slot,设置足够多的sender。
⑨发布者的pg_hba.conf需要设置replication条目,允许订阅者连接。
⑩发布者的数据库中,必须有replication角色的用户,或者超级用户,并且订阅者要使用它通过流复制协议连接到发布者。

3、订阅端创建订阅表
创建表结构同发布端表结构相同的空表 注意主键约束,没有的话发布端的update delete等操作会报错
例:create table t1 (id int primary key,name text)

4、订阅端创建订阅

创建订阅语法

Command:     CREATE SUBSCRIPTION  
Description: define a new subscription  
Syntax:  
CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION { publication_name [, ...] } [ WITH ( subscription_parameter [= value] [, ... ] ) ]

举例:create subscription sub1 connection ‘host=192.168.230.81 port=5866 dbname=postgres user=logicalrep password=logicalrep’ publication pub1;

修改订阅语法

Command:     ALTER SUBSCRIPTION  
Description: change the definition of a subscription  
Syntax:  
ALTER SUBSCRIPTION name WITH ( option [, ... ] ) ]  where option can be:  
  SLOT NAME = slot_name  
ALTER SUBSCRIPTION name CONNECTION 'conninfo'ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }ALTER SUBSCRIPTION name RENAME TO new_name

订阅者小结
①必须使用超级用户创建订阅
②订阅者需要通过流复制协议连接到发布者,同时需要在发布者创建replication slot。
③同一个数据库中,可以创建多个subscription,这些subscription可以连自一个或多个发布者。
④订阅者的同一张表,不能接受来自同一个源的多个发布。
⑤每一个订阅,都需要在发布端创建一个slot,可以使用slot name = ?指定,或者默认为subscription name。
⑥在创建subscription或者alter subscription时,可以使用enable来启用该订阅,或者使用disable暂停该订阅。
⑦如果要完全删除订阅,使用drop subscription,注意,删除订阅后,本地的表不会被删除,数据也不会清除,仅仅是不在接收该订阅的上游信息。
⑧创建订阅时,不会自动创建发布端的表,所以表需要在订阅端先创建好。


上一篇:Django模型与后台管理


下一篇:深圳ccc认证办理需要准备什么资料?周期时间多久?