PostgreSQL数据库逻辑复制

乐乐镇厉害

已于 2023-10-17 10:15:50 修改

阅读量521

点赞数 1

分类专栏： PostgreSQL 文章标签：数据库 postgresql 运维 linux

于 2023-10-17 10:14:37 首次发布

本文链接：https://blog.csdn.net/qq_58883939/article/details/133877727

版权

PostgreSQL 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

逻辑复制

逻辑复制

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

根据业务需求，将一个数据库中的一部分表同步到另一个数据库
满足报表库取数需求，从多个数据库采集报表数据
实现PostgreSQL跨大版本数据同步
实现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数据变化。

逻辑复制部署

实验环境

角色	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 行记录)

参数配置

发布节点：

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

权限配置

发布节点上的逻辑复制用户需要具备replication(创建复制槽)权限：

alter table t3 owner to wxz;

或

create user 用户名 replication login connection limit 8 encrypted password ‘密码’;

创建一个拥有replication权限且最多8个并发连接的连接限制密码用encrypted关键字加密；

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

创建发布节点

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实例上的一部分数据。

查询已创建发布信息

可以通过在发布节点上查询pg_publication视图即可

wxz=> select * from pg_publication;

-------+----------+----------+--------------+-----------+-----------+-----------+-------------

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，您可以确保在发布节点上执行的截断操作也会被复制到订阅节点，从而保持两个节点的数据一致性。

创建订阅

highgo=# create subscription lufz_wxz connection 'host=192.168.2.48 port=5866 dbname=wxz user=wxz' publication ljfz_wxz;

注意: 在发布服务器上创建复制槽 "lufz_wxz"

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)等，一般默认配置即可；

查询已创建订阅信息

highgo=# select * from pg_subscription;

-------+---------+----------+----------+------------+-------------------------------------------------+-------------+---------------+-----------------

57364 | 15070 | lufz_wxz | 10 | t | host=192.168.2.48 port=5866 dbname=wxz user=wxz | lufz_wxz | off | {ljfz_wxz}