PostgreSQL数据库逻辑复制

逻辑复制

• 逻辑复制

流复制是基于实例级别的复制，而逻辑复制是基于表级别的选择性复制，例如可以复制主库的一部分表到备库，这是一种粒度更细的复制，逻辑复制主要使用场景为：

1. 根据业务需求，将一个数据库中的一部分表同步到另一个数据库
2. 满足报表库取数需求，从多个数据库采集报表数据
3. 实现PostgreSQL跨大版本数据同步
4. 实现PostgreSQL大版本升级

流复制是基于WAL日志的物理复制；而逻辑复制是基于逻辑解析（logical decoding），其核心原理是主库将WAL日志流解析成一定格式，订阅节点收到解析的WAL数据流后进行应用，从而实现数据同步，逻辑复制并不是使用WAL原始日志文件进行复制，而是将WAL日志解析成了一定格式。

• 逻辑解析

逻辑解析（logical decoding）是逻辑复制的核心，逻辑解析读取数据库的WAL并将数据变化解析成目标格式；

逻辑复制的前提是设置wal_level参数为logical并且设置max_replication_slots参数至少为1；

wal_level 参数用于确定写入日志（WAL）的详细程度，以支持逻辑复制。它可以设置为以下三个级别之一： minimal：最小级别，只记录必要的信息，不支持逻辑复制。 replica：默认级别，记录足够的信息以支持流复制和基于时间点恢复，但不支持逻辑复制。 logical：最高级别，记录所有信息以支持逻辑复制。 如果要使用逻辑复制功能，需要将 wal_level 设置为 logical。 max_replication_slots 参数确定系统可以支持的最大复制槽数量。复制槽是一种用于接收 WAL 日志的缓冲区，用于支持物理和逻辑复制。每个复制槽都代表一个复制订阅者或复制目标。通过复制槽，数据可以从主服务器复制到一个或多个辅助服务器。 max_replication_slots 的默认值是 0，表示禁用复制槽。要启用逻辑复制，需要设置一个大于0的适当值。可以根据系统资源和需求来调整该值。 在启用逻辑复制并设置合适的 max_replication_slots 值后，系统将为每个逻辑复制订阅创建一个复制槽。

• 逻辑复制架构

逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日之后进行重做，从而实现表数据同步；

逻辑复制架构图中最重要的两个角色为Publication和Subscription。

Publication(发布)可以定义在任何可读写的PostgreSQL实例上，对于已创建Publication的数据库成为发布节点，一个数据库中允许创建多个发布，目前允许加入发布的对象只有表，允许多个表注册到一个发布中。加入发布的表通常需要有复制标识(replica identity)，从而使逻辑主库表上的DELETE/UPDATE操作可以标记到相应数据行并复制到逻辑备库上的相应表，默认情况下使用主键作为数据标识，如果没有主键，也可以是唯一索引，如果没有主键或者唯一索引，可设置复制标识为full，意思是整行数据作为键值，这种情况下复制效率会变低。如果加入发布的表没有指定复制标识，表上的UPDATE/DELETE将会报错。

Subscription(订阅)实时同步指定发布者的表数据，位于逻辑复制的下游节点，对于已创建Subscription的数据库称为订阅节点，订阅节点上的数据库同时也能创建发布。发布节点发布的表的DDL不会被复制，因此，如果发布节点上发布的表结构更改了，订阅节点上需要手工对订阅的表进行DDL操作，订阅节点通过逻辑复制槽获取发布节点发送的WAL数据变化。

• 逻辑复制部署  

1. 实验环境

角色	ip	数据库版本	用户名	数据库名	同步表
发布节点	192.168.2.48	HGDB4.5.8	wxz	wxz	t3
订阅节点	192.168.2.120	HGDB6.0.4	highgo	highgo	t3

发布节点： wxz=> select * from t3;  id | name ----+------   1 | wxz   2 | wcx   3 | txg (3 行记录) 订阅节点： highgo=>create table t3 (id serial,name text, constraint pk_test_a_id primary key(id)); highgo=> select * from t3;  id | name ----+------ (0 行记录)

1. 参数配置

发布节点： wal_level = 'logical'         #设置成logical才支持逻辑复制 max_replication_slots = 10  #设置值需大于订阅节点的数量 max_wal_senders = 10      #由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此参数设置值需大于max_replication_slots参数值加上物理备库数量 订阅节点： max_replication_slots = 10  #设置数据库最大复制槽数量，应大于订阅节点的数量 max_logical_replication_workers = 10   #设置逻辑复制进程数，应大于订阅节点的数量，并且给同步表预留一些数量，此参数默认值为4

同时max_logical_replication_workers会消耗后台进程数，并且从max_worker_processes参数设置的后台进程数中消费，因此max_worker_processes参数需要设置较大； max_worker_processes = 10

1. 权限配置

发布节点上的逻辑复制用户需要具备replication(创建复制槽)权限： alter table t3  owner to wxz; 或 create user 用户名 replication login connection limit 8 encrypted password ‘密码’; 创建一个拥有replication权限且最多8个并发连接的连接限制密码用encrypted关键字加密；

该逻辑复制用户要有对这些表的读权限

1. 创建发布节点

highgo=# create publication ljfz_wxz for table t3; CREATE PUBLICATION 语法： CREATE PUBLICATION 名称     [ FOR TABLE [ ONLY ] 表名 [ * ] [, ...]       | FOR ALL TABLES ] [ WITH ( 发布参数 [= 值] [, ... ] ) ] for table：指加入到发布的表列表，目前仅支持普通标的发布，临时表、外部表、视图、物化视图、分区表暂不支持发布，如果想将分区表添加到发布中，需逐个添加分区表分区到发布 for all tables：将当前库中所有的表添加到发布中，包括以后在这个库中新建的表。这种模式相当于在全库级别逻辑复制所有的表。这种模式相当于在全库级别逻辑复制所有的表。当然一个PostgreSQL实例上可以运行多个库，这仍然是仅复制了PostgreSQL实例上的一部分数据。

1. 查询已创建发布信息

可以通过在发布节点上查询pg_publication视图即可 wxz=> select * from pg_publication;   oid  | pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate -------+----------+----------+--------------+-----------+-----------+-----------+-------------  96905 | ljfz_wxz |     9999 | f            | t         | t         | t         | t pubname：指发布的名称 pubowner：指发布的属主，和pg_user视图的usesysid字段关联 puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表 pubinsert：t 指是否允许插入(insert)操作被复制到订阅节点的标志。 pubupdate：t指是否允许更新(update)操作被复制到订阅节点的标志。 pubdelete：t 指是否允许删除(delete)操作被复制到订阅节点的标志 pubtruncate：t 指是否允许截断操作被复制到订阅节点的标志。截断操作是指从表中删除所有行的操作，它相当于执行 DELETE FROM table_name; 语句，但更高效。通过在逻辑复制发布中启用 pubtruncate，您可以确保在发布节点上执行的截断操作也会被复制到订阅节点，从而保持两个节点的数据一致性。

1. 创建订阅

highgo=# create subscription lufz_wxz connection 'host=192.168.2.48 port=5866 dbname=wxz user=wxz' publication ljfz_wxz; 注意:  在发布服务器上创建复制槽 "lufz_wxz" CREATE SUBSCRIPTION

CREATE SUBSCRIPTION Description: 建立新的订阅 Syntax: CREATE SUBSCRIPTION 订阅_名称     CONNECTION '连接信息'     PUBLICATION 发布名 [, ...] [ WITH ( 订阅参数 [= 值] [, ... ] ) ] CONNECTION：连接信息通常包括host、port、dbname、user、password等连接属性，从安全角度考虑，密码文件建议写入~/.pgpass隐藏文件 with：支持的参数有copy_data(boolean)、create_slot(boolean)、enabled(boolean)、slot_name(string)等，一般默认配置即可；

1. 查询已创建订阅信息

highgo=# select * from pg_subscription;   oid  | subdbid | subname  | subowner | subenabled |                   subconninfo                   | subslotname | subsynccommit | subpublications -------+---------+----------+----------+------------+-------------------------------------------------+-------------+---------------+-----------------  57364 |   15070 | lufz_wxz |       10 | t          | host=192.168.2.48 port=5866 dbname=wxz user=wxz | lufz_wxz    | off           | {ljfz_wxz}

1. 查询表t3数据是否同步

订阅节点： highgo=# select * from t3;  id | name ----+------   1 | wxz   2 | wcx   3 | txg (3 行记录) 完成数据同步