PG的逻辑复制快速上手

三杯酒coO 2020-02-23

510

逻辑复制是数据同步的一种方式，数据变化同步的一种方式。该方式一般是基于主键或者是唯一键来做的。逻辑复制使用的是分发订阅的模式实现的。

一、逻辑复制的应用场景

备库需要提供写能力的情况，例如双主库

只需要部分表的同步情况，例如数据上报、OLAP数仓等

不通平台数据库之间的复制，例如linux、windows上的pg之间的复制

二、逻辑复制的限制

不支持分区表的同步，分区表只能按照子分区表来实现分发订阅

只支持普通表和普通表上的DML，不支持序列、视图、物化视图、外部表、大对象，不支持truncate、DDL

需要同步的表必须设置REPLICA IDENTITY ，通常使用主键或者唯一键

一个publisher可以包含一张或多张表，一张表可以有一个或多个publishers

逻辑复制不同于流复制，不是严格的主从关系，订阅者端的普通表依然可以进行增删改操作

同步表的表结构需要在发布者和订阅者两边保持一致通常使用 pg_dump来初始化

三、参数配置

修改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的个数，默认值是10
       b、订阅者端设置
          设置wal_level级别为logical：wal_level = logical
          设置max_logical_replication_workers，此参数值不少于订阅者的个数，默认是4
          设置max_worker_processes，此参数值不少于max_logical_replication_workers值+1 
2、在pg_hba.conf添加白名单（根据真实情况自行限制网段）
      复制

 host     all     repuser     0.0.0.0/0     md5
复制


3、创建专门用于逻辑复制的用户。
        该用户需要具有复制表的读权限，一般用超级用户。
      复制

 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)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
--添加到发布者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)
复制


复制

关于约束对逻辑复制的影响

一般来说，逻辑复制，不管是pg官方还是第三方，例如ogg等，目标的的触发器、约束都要禁用掉。
官方关于逻辑复制的文档

postgresql

文章转载自三杯酒coO，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。