PostgreSQL逻辑复制

PostgreSQL 10 一重量新特性为逻辑复制（ Logical Replication ），这一新特性的主要提交者来自于 2ndquadrant 开发者，感谢他们的付出！

目录

流复制是基于实例级别的复制，相当于主库的一个热备，也就是说备库的数据库对象和主库一模一样，而逻辑复制是基于表级别的选择性复制，例如可以复制主库的一部分表到备库，这是一种粒度更细的复制，逻辑复制主要使用场景为：

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

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

1 逻辑解析

逻辑解析（ logical decoding ）是逻辑复制的核心，理解逻辑解析有助于理解逻辑复制原理，逻辑解析读取数据库的 WAL 并将数据变化解析成目标格式，这小节将对逻辑解析进行演示。

逻辑解析的前提是设置 wal_level 参数为 logical并且设 max_replication_slots 参数至少为1，如下所示：

wal_level=logical
max_replication_slots=8

wal_level 参数控制 WAL 日志信息的级别，有 minimal 、replica 、logical 三种模式，此参数调整后需重启数据库生效。

max_replication_slots 参数指允许的最大复制槽数，此参数调整后需重启数据库生效。

host1 节点上创建逻辑复制槽，如下所示：

postgres=# select pg_create_logical_replication_slot('logical_slot1', 'test_decoding');
 pg_create_logical_replication_slot
------------------------------------
 (logical_slot1,0/22000108)
(1 row)

查看pg_replication_slots视图，如下所示：

postgres=# select * from pg_replication_slots;
-[ RECORD 1 ]-------+--------------
slot_name           | logical_slot1
plugin              | test_decoding
slot_type           | logical
datoid              | 13214
database            | postgres
temporary           | f
active              | f
active_pid          |
xmin                |
catalog_xmin        | 744
restart_lsn         | 0/220000D0
confirmed_flush_lsn | 0/22000108

之后使用逻辑复制槽 logical_slotl1 查看所解析的数据变化，如下所示：

postgres=# select * from pg_logical_slot_get_changes('logical_slot1', null, null);
 lsn | xid | data
-----+-----+------
(0 rows)

pg_logical_slot_get_changes函数用来查看指定逻辑复制槽所解析的数据变化，每执行一次，所解析的数据变化将被消费掉，也就是说查询结果不能复现。

接下来创建一张测试表，测试逻辑复制槽是否可以捕获DDL数据，如下所示：

postgres=# create table t_logical(id int);
CREATE TABLE
postgres=# select * from pg_logical_slot_get_changes('logical_slot1', null, null);
    lsn     | xid |    data
------------+-----+------------
 0/22000270 | 744 | BEGIN 744
 0/220150A0 | 744 | COMMIT 744
(2 rows)

以上只返回事务信息，没有显示建表 DDL ，说明逻辑复制槽不会捕获 DDL 。

再次查看 logical_slot1 捕获的数据变化，发现为空，如下所示

postgres=# select * from pg_logical_slot_get_changes('logical_slot1', null, null);
 lsn | xid | data
-----+-----+------
(0 rows)

由于此函数捕获的数据将被消费掉，因此，此函数查询结果仅能显现一次。

在表t_logical中插入一条数据，再次查看logical_slot1捕获的数据变化，如下所示：

postgres=# insert into t_logical values(1);
INSERT 0 1
postgres=# select * from pg_logical_slot_get_changes('logical_slot1', null, null);
    lsn     | xid |                     data
------------+-----+-----------------------------------------------
 0/220151E8 | 745 | BEGIN 745
 0/220151E8 | 745 | table public.t_logical: INSERT: id[integer]:1
 0/22015258 | 745 | COMMIT 745
(3 rows)

从以上看出，这条INSERT语句被解析出来了。

注意 pg_logical_slot_get_changes 函数用来查看指定逻辑复制槽所解析的数据变化，每执行行一次，所解析的数据变化将被消费掉，如果想解析的数据能重复查询可执行 pg_logical_slot_peek_changes 函数获取逻辑复制槽所解析的数据，但是此函数只能显示

pg_logical_slot_get_changes 函数没有消费的数据，如果数据被 pg_logical_slot_get_changes 函数消费掉了， pg_logical_slot_peek_changes函数返回为空。

以上介绍了使用系统函数捕获逻辑复制槽解析的数据变化，也可以使用 pg_recvlogical命令行工具捕获逻辑复制槽解析的数据变化，先在主库上插入一条记录，如下所示：

postgres=# insert into t_logical values(2);
INSERT 0 1

主库上使用pg_recvlogical命令获取逻辑复制槽logical_slot1捕获的数据变化，如下所示：

pg_recvlogical -d postgres --slot  logical_slot1 --start -f -
BEGIN 747
table public.t_logical: INSERT: id[integer]:2
COMMIT 747
  • -d指定数据库名称，
  • --slot指定逻辑复制槽名称，
  • --start表示通过--slot选项指定的逻辑复制槽来解析数据变化，
  • -f将解析的数据变化写入指定文件，
  • "-"表示输出到终端，

从以上输出信息可以看到INSERT语句被解析出来。

如果逻辑复制槽不需要使用了，需要解释删除，如下所示：

postgres=# select pg_drop_replication_slot('logical_slot1');
 pg_drop_replication_slot
--------------------------

(1 row)

2 逻辑复制架构

逻辑复制架构图

图中的逻辑主库和逻辑备库为不同的 PostgreSQL 实例，可以在同一主机上，也可以在不同主机上，并且逻辑主库的表 table1 和 table2 加入了 Publication ，备库上的 Subscription 能够实时同步逻辑主库上的 table1 和 table2;

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

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

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

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

3 逻辑复制部署

逻辑复制的架构主要由发布和订阅组成，本节将演示逻辑复制的部署。

逻辑复制实验环境

角色 主机名 IP 端口 库名 用户名 版本
发布节点 host1 192.168.137.129 1921 postgres pg_user (PostgreSQL) 10.23
订阅节点 host2 192.168.137.130 1921 postgres pg_user (PostgreSQL) 10.23

发布节点的postgresql.conf配置文件设置以下参数：

wal_level=logical			#设置成 ogica 才支持逻辑复制
max_replication_slots=8		#设置值需大于订阅点的数量
max_wal_senders=10			#由于每个订阅节点和流复制备库在主库上都会占用主库WAL发送进程，因此此参数设置值需大于 max_replication_slots 参数值加上物理备库数量。

订阅节点的postgresql.conf配置文件设置如下参数：

max_replication_slots=8				#设置数据库复制槽数量，应大于订阅节点的数量
max_logical_replication_workers=10	#设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4

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

发布节点上创建逻辑复制用户，逻辑复制用户需要具备 REPLICATION 权限， 如下所示：

CREATE USER logical_user REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_user';

逻辑复制用户需要 REPLICATION 权限即可，可以不需要 SUPERUSER 权限， 之后需要在发布节点上将需要同步的表赋权给 logical_user 用户，使 logical_user 具有对这些表的读权限。

发布节点上创建测试表，并给予logical_user访问权限，如下所示：

postgres=# create table t_r1(id int primary key,name varchar(100));
CREATE TABLE
postgres=# insert into t_r1 values(1,'a');
INSERT 0 1
postgres=# grant usage on schema public to logical_user;
GRANT
postgres=# grant select on t_r1 to logical_user;
GRANT

之后在发布节点上创建发布，如下所示：

postgres=# CREATE PUBLICATION pub1 FOR TABLE t_r1;
CREATE PUBLICATION

创建发布的语法如下：

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

如果想查询刚创建的发布信息，在发布节点上查询问 pg_publication 视图即可，如下所示

postgres=# select * from pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
 pub1    |       10 | f            | t         | t         | t
(1 row)
  • pubname：指发布的名称；
  • pubowner：指发布的属主，和 pg_user 视图的 usesysid 字段关联；
  • puballtables：是否发布数据库中的所有表， t表示发布数据库中所有已存在的表和以后新建的表；
  • pubinsert：表示仅发布表上的 INSERT 操作；
  • pubupdate：表示仅发布表上的 UPDATE 操作；
  • pubdelete：表示仅发布表上的 DELETE 操作；

订阅节点上创建表 t_r1，注意仅创建表结构，不插入数据，如下所示：

postgres=# create table t_r1(id int primary key,name varchar(200));
CREATE TABLE

之后计划在订阅节点上创建订阅，语法如下

CREATE SUBSCRIPTION subscription_name
    CONNECTION 'conninfo'
    PUBLICATION publication_name [, ...]
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]
  • subscription_name：指订阅的名称；
  • CONNECTION：订阅的数据库连接串，通常包括host、port、dbname、user、password等连接属性，从安全角度考虑，密码文件建议写人~/.pgpass 隐藏文件。
  • PUBLICATION：指定需要订阅的发布名称。
  • WITH ( subscription_parameter [= value] [, ... ] )：支持的参数配置有copt_data(boolean)、create_slot(boolean)、enabled(boolean)、slot_name(string)等，一般默认配置即可。

稍后创建订阅，现在订阅节点上创建~/.pgpass 文件，并写入以下代码：

192.168.137.129:1921:postgres:logical_user:logical_user

对~/.pgpass 文件进行权限设置，如下所示：

chmod 0600 ~/.pgpass

发布节点的 pg_hba.conf 需要设置相应策略，允许订阅节点连接。

之后在订阅节点上创建发布，只有超级用户才有权限创建发布，如下所示：

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.137.129 port=1921 dbname=postgres user=logical_user' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

从以上信息看出，订阅创建成功，并且在发布节点上创建了一个名为 subl 的复制槽，在发布节点上查看复制槽，如下所示：

postgres=# select * from pg_replication_slots where slot_name = 'sub1';
-[ RECORD 1 ]-------+-----------
slot_name           | sub1
plugin              | pgoutput
slot_type           | logical
datoid              | 13214
database            | postgres
temporary           | f
active              | t
active_pid          | 14903
xmin                |
catalog_xmin        | 756
restart_lsn         | 0/220358A8
confirmed_flush_lsn | 0/220358E0

注意plugin模块为pgoutput，这是逻辑复制的默认plugin。

订阅节点上查看 pg_subscription 视图以查看订阅信息，如下所示：

postgres=# select * from pg_subscription;
-[ RECORD 1 ]---+-----------------------------------------------------------------
subdbid         | 13214
subname         | sub1
subowner        | 10
subenabled      | t
subconninfo     | host=192.168.137.129 port=1921 dbname=postgres user=logical_user
subslotname     | sub1
subsynccommit   | off
subpublications | {pub1}
  • subdbid：数据库的OID，
  • subname ：订阅的名称，
  • subowner：订阅的属主，
  • subenabled：是否启用订阅，
  • subconninfo：订阅的连接串信息，显示发布节点连接串信息，
  • subslotname：复制槽名称，
  • subsynccommit：
  • subpublications：订阅节点订阅的发布列表。

之后在订阅节点上验证表t_r1数据是否同步过来，如下所示：

postgres=# select * from t_r1;
 id | name
----+------
  1 | a
(1 row)

订阅节点上 t_rl 表数据已同步，以上仅验证了原始数据己同步。

这时候在发布节点主机上可以看到新增了 WAL 发布进程，如下所示：

postgres: wal sender process logical_user 192.168.137.130(46130) idle

订阅节点主机上可以看到新增了一个 WAL 订阅进程，如下所示：

postgres: bgworker: logical replication worker for subscription 24612

4 逻辑复制添加表、删除表

发布节点上创建一张表t_big，并将表t_big的SELECT的权限赋给逻辑复制用户logical_user。

postgres=# create table t_big(id int,info text);
CREATE TABLE
postgres=# insert into t_big select generate_series(1,10000),md5(random()::text);
INSERT 0 10000
postgres=# grant select on t_big to logical_user;
GRANT

之后在发布节点上将表t_big加入到发布pub1，如下所示：

postgres=# ALTER PUBLICATION pub1 ADD TABLE t_big;
ALTER PUBLICATION

查看发布中的表列表，执行\dRp+元命令即可，如下：

postgres=# \dRp+ pub1
                 Publication pub1
 Owner | All tables | Inserts | Updates | Deletes
-------+------------+---------+---------+---------
 pg10  | f          | t       | t       | t
Tables:
    "public.t_big"
    "public.t_r1"

以上看出t_big已加入到发布pub1中。

也可以通过查看 pg_publication_tables 视图查看发布中的表列表，如下所示：

postgres=# select * from pg_publication_tables;
 pubname | schemaname | tablename
---------+------------+-----------
 pub1    | public     | t_r1
 pub1    | public     | t_big
(2 rows)

订阅节点也需要创建t_big表，注意仅创建表结构，不插入数据，如下：

postgres=# create table t_big(id int,info text);
CREATE TABLE

由于 t_big 表是发布节点上新增加的表，这里订阅节点上 t_big 表的数据还没有复制过来，订阅节点需要执行以下命令：

postgres=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
ALTER SUBSCRIPTION

这条命令执行之后，订阅节点的 t_big 表在同步发布节点的数据了，同时在发布节点主机上产生了逻辑复制 COPY 发送进程，过一阵订阅节点 t_big 上的1w条数据已完成同步，如下所示：

postgres=# select count(1) from t_big;
 count
-------
 10000
(1 row)

如果需求调整，逻辑复制中t_big表不再需要逻辑同步，只需要在发布节点上将t_big从发布pub1中去掉即可，如下：

postgres=# ALTER PUBLICATION pub1 DROP TABLE t_big;
ALTER PUBLICATION

这条命令执行之后，发布节点、订阅节点上的 t_big 表将没有任何同步关系，两张表为不同库中独立的表，只是表名一样而已。

5 逻辑复制启动、停止

逻辑复制配置完成之后，默认情况下订阅节点的表会实时同步发布节点中的表，逻辑复制通过启用、停止订阅方式实现逻辑复制的启动和停止。订阅节点上停止 sub1 订阅从而中断实时同步数据，执行如下命令：

postgres=# ALTER SUBSCRIPTION sub1 DISABLE;
ALTER SUBSCRIPTION

查询 pg_subscription 视图的 subenabled 字段判断是否已停止订阅，如下所示：

postgres=# select * from pg_subscription;
-[ RECORD 1 ]---+-----------------------------------------------------------------
subdbid         | 13214
subname         | sub1
subowner        | 10
subenabled      | f
subconninfo     | host=192.168.137.129 port=1921 dbname=postgres user=logical_user
subslotname     | sub1
subsynccommit   | off
subpublications | {pub1}

如果想开启订阅，执行如下命令：

postgres=# ALTER SUBSCRIPTION sub1 ENABLE ;
ALTER SUBSCRIPTION

查询 pg_subscription 视图， subenabled 字段值变成了t ，如下所示：

postgres=# select * from pg_subscription;
-[ RECORD 1 ]---+-----------------------------------------------------------------
subdbid         | 13214
subname         | sub1
subowner        | 10
subenabled      | t
subconninfo     | host=192.168.137.129 port=1921 dbname=postgres user=logical_user
subslotname     | sub1
subsynccommit   | off
subpublications | {pub1}

6 逻辑复制配置注意事项和限制

前面的内容演示了逻辑复制的部署和功能验证，逻辑复制部署过程中最主要的两个角色为发布和订阅，以下介绍两者配置的注意事项。

发布节点配置注意事项如下：

  • 发布节点的 wal_level 参数需要设置成 logical；
  • 发布节点上逻辑复制用户至少需要 replication 角色权限；
  • 发布节点上需要发布的表如果需要将 UPDATE/DELETE 操作同步到订阅节点，要给发布表配置复制标识;
  • 发布时可以选择发布 INSERT、UPDATE、DELETE DML 操作中的一项或多项，默认是发布这三项;
  • 支持一次发布一个数据库中的所有表；
  • 一个数据库中可以有多个发布；
  • 逻辑复制目前仅支持普通表，序列、视图、物化视图、分区表、外部表等对象目前不支持；
  • 发布节点配置文件 pg_hba.conf 需做相应配置，允许订阅节点连接；
  • 发布表上的 DDL 操作不会自动同步到订阅节点，如果发布节点上发布的表执行了DDL 操作， 需手工给订阅节点的相应表执行 DDL。

订阅节点配置注意事项如下：

  • 一个数据库中可以有多个订阅;
  • 必须具有超级用户权限才可以创建订阅；
  • 创建订阅时需指定发布节点连接信息和发布名称；
  • 创建订阅时默认不会创建发布节点的表，因此创建订阅前需手工创建表；
  • 订阅支持启动、停止操作；
  • 订阅节点的表结构建议和发布节点一致，尽管订阅节点的表允许有额外的字段；
  • 发布节点给发布增加表时，订阅结点需要刷新订阅才能同步新增的表。

对于配置了复制标识的表， UPDATE/DELETE 操作可以逻辑复制到订阅节点，但目前的版本中逻辑复制有以下限制：

  • DDL 操作不支持复制，发布节点上发布表进行 DDL 操作后， DDL 操作不会复制到订阅节点，需在订阅节点对发布表于工执行 DDL 操作；
  • 序列本身不支持复制，当前逻辑复制仅支持普通表，序列、视图、物化视图、分区表、外部表等对象都不支持；
  • TRUNCATE 操作不支持复制；
  • 大对象（ Large Object ）字段不支持复制。

以上只是 PostgreSQL10 版本逻辑复制的限制事项，或许以后新版本的限制条件能够减少。

posted on 2023-11-25 19:19  jl1771  阅读(719)  评论(0)  编辑  收藏  举报

Copyright © 2024 jl1771
Powered by .NET 9.0 on Kubernetes Powered By博客园