PG逻辑复制插件之pglogical使用说明

0    565    3

👉 本文共约8333个字,系统预计阅读时间或需32分钟。

简介

参考:

https://gitee.com/mirrors/pglogical

https://github.com/2ndQuadrant/pglogical

https://www.xmmup.com/pgluojifuzhichajianzhipglogicalguanfangshuoming.html

Logical Replication extension for PostgreSQL 14、13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.

pglogical 是 PostgreSQL 的拓展模块, 为 PostgreSQL 数据库提供了逻辑流复制发布和订阅的功能。pglogical 重用了 BDR 项目中的一部分相关技术。pglogical 是一个完全作为PostgreSQL 扩展实现的逻辑复制系统。完全集成,它不需要触发器或外部程序。这种物理复制的替代方法是使用发布/订阅模型复制数据以进行选择性复制的一种高效方法。支持 PG14、13、12、11、10、9.6、9.5、9.4 ,提供比 Slony、Bucardo 或 Londiste 更快的复制速度,以及跨版本升级。

我们使用的下列术语来描述节点和数据流之间的关系,重用了一些早期的Slony技术中的术语:

  • 节点 - PostgreSQL数据库实例
  • 发布者和订阅者 - 节点的角色名称
  • 复制集 - 关系表的集合

pglogical是新技术组件,使用了最新的PostgreSQL 数据库中的一些核心功能,所以存在一些数据库版本限制:

  • 数据源发布和订阅节点需要运行 PostgreSQL 9.4 +
  • 复制源过滤和冲突检测需要 PostgreSQL 9.5 +

支持的使用场景:

  • 主版本数据库之间的升级(存在上述的版本限制)
  • 完整的数据库复制
  • 利用复制集,选择性的筛选的关系表
  • 可从多个上游服务器,做数据的聚集和合并

架构上的细节︰

  • pglogical 工作在每个数据库层面上,而不是像物理流复制一样工作在整个数据库集簇实例级别
  • 一个发布程序提供给多个订阅者不会引起额外的磁盘写开销
  • 一个订阅服务器可以从几个起源的更改合并和检测与自动和可配置冲突决议 (一些,但并不是所有方面所需的多主机)的更改之间的冲突。
  • 级联复制是在变更集转发的过程中实现的。

必要条件

  • 要使用pglogical,提供发布和订阅服务器必须运行PostgreSQL 9.4或更高版本。
  • pglogical扩展必须同时安装在提供发布和订阅服务器上。您必须同时创建扩展“CREATE EXTENSION pglogical;”。
  • 提供发布和订阅服务器上的表必须具有相同的名称并位于相同的schema中。将来的修订版可能会添加映射功能。
  • 提供发布和订阅服务器上的表必须具有相同的列,每列中的数据类型相同。订阅服务器上的CHECK约束、NOT NULL约束等必须与提供发布相同或较弱(更宽松)。
  • 表必须具有相同的主键。不建议添加主键以外的其他唯一约束。

安装插件

安装包安装

yum安装

1、安装yum源:

  • PostgreSQL 9.4: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.4/rpm | bash
  • PostgreSQL 9.5: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.5/rpm | bash
  • PostgreSQL 9.6: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/9.6/rpm | bash
  • PostgreSQL 10: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/10/rpm | bash
  • PostgreSQL 11: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/11/rpm | bash
  • PostgreSQL 12: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/12/rpm | bash
  • PostgreSQL 13: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/13/rpm | bash
  • PostgreSQL 14: curl https://techsupport.enterprisedb.com/api/repository/dl/default/release/14/rpm | bash

2、安装插件

  • PostgreSQL 9.4: yum install postgresql94-pglogical
  • PostgreSQL 9.5: yum install postgresql95-pglogical
  • PostgreSQL 9.6: yum install postgresql96-pglogical
  • PostgreSQL 10: yum install postgresql10-pglogical
  • PostgreSQL 11: yum install postgresql11-pglogical
  • PostgreSQL 12: yum install postgresql12-pglogical
  • PostgreSQL 13: yum install postgresql13-pglogical
  • PostgreSQL 14: yum install postgresql14-pglogical

APT安装

1、安装源:

2、安装插件

  • PostgreSQL 9.4: sudo apt-get install postgresql-9.4-pglogical
  • PostgreSQL 9.5: sudo apt-get install postgresql-9.5-pglogical
  • PostgreSQL 9.6: sudo apt-get install postgresql-9.6-pglogical
  • PostgreSQL 10: sudo apt-get install postgresql-10-pglogical
  • PostgreSQL 11: sudo apt-get install postgresql-11-pglogical
  • PostgreSQL 12: sudo apt-get install postgresql-12-pglogical
  • PostgreSQL 13: sudo apt-get install postgresql-13-pglogical
  • PostgreSQL 14: sudo apt-get install postgresql-14-pglogical

编译安装

https://github.com/2ndQuadrant/pglogical/releases

Source code installs are the same as for any other PostgreSQL extension built using PGXS.

Make sure the directory containing pg_config from the PostgreSQL release is listed in your PATH environment variable. You might have to install a -dev or -devel package for your PostgreSQL release from your package manager if you don't have pg_config.

Then run make to compile, and make install to install. You might need to use sudo for the install step.

安装完之后,查看:

使用配置

配置参数

首先 PostgreSQL服务器必须正确配置才能够支持逻辑解码︰

配置:

操作过程:

如果你想要处理解决与上一次/第一次更新之间的冲突 wins(参阅冲突章节), 你的数据库版本需要为PostgreSQL 9.5+ (在9.4中无效) 您可以向 PostgreSQL.conf 添加此额外的选项:

配置pg_hba.conf

pg_hba.conf 需要配置成允许从本地主机复制,用户拥有复制权限,连接权限。

创建扩展

其它

数据字典

复制集

在复制集default中: update/delete/truncate 操作也是同步复制。

复制集INSERTUPDATEDELETETRUNCATE
default
default_insert_only××
ddl_sql×××

复制特性扩展

延迟复制

参数:

  • subscription_name - 订阅的名称,必须是唯一的
  • provider_dsn - 提供者的连接字符串
  • replication_sets - 要订阅的复制集数组,这些必须已存在,默认为“{default,default_insert_only,ddl_sql}”
  • synchronize_structure - 指定是否将提供者与订阅者之间的结构同步,默认为false
  • synchronize_data - 指定是否将数据从提供者同步到订阅者,默认为true
  • forward_origins - 要转发的原始名称数组,当前只支持的值是空数组,意味着不转发任何不是源自提供者节点的更改,或“{all}”这意味着复制所有更改,无论它们的来源是什么,默认是全部}”
  • apply_delay - 延迟复制多少,默认为0秒

示例:数据表结构同步;且延迟复制1分钟

对源端进行 行/列 过滤

过滤机制需要 PostgreSQL 9.5 +

参数:

  • set_name - 现有复制集的名称
  • relation - 要添加到集合中的表的名称或OID
  • synchronize_data - 如果为true,则表数据将在订阅给定复制集的所有订户上同步,默认为false
  • columns - 要复制的列的列表。通常,当应复制所有列时,这将设置为NULL,这是默认值
  • row_filter - 行过滤表达式,默认为NULL(无过滤),有关详细信息,请参阅(行过滤)。警告:在使用有效行筛选器同步数据时要小心。使用synchronize_data=true有效row_filter就像对表的一次性操作。使用修改后再次执行它将row_filter不会将数据同步到订户。订阅者可能需要pglogical.alter_subscription_resynchronize_table()来修复它。

示例:对表tbl_lottu02中字段{id, name, job} 字段列过滤;且对条件 ‘id > 10’ 进行行过滤

为新表自动分配复制集

事件触发器工具可用于描述为新创建的表定义复制集的规则。

冲突检测

冲突检测需要 PostgreSQL 9.5 +
如果节点订阅多个提供程序,或当本地写入在订阅服务器上发生,可能会发生冲突,尤其是对传入的变化。这些都自动检测,并可以就此采取行动取决于配置。
解决冲突的办法是通过配置 pglogical.conflict_resolution 参数。
pglogical.conflict_resolution 支持的配置参数选项为︰

  • error - 复制将停止上错误如果检测到冲突和手动操作需要解决
  • apply_remote - 总是应用与本地数据有冲突的更改,这是默认值
  • keep_local - 保留数据的本地版本,并忽略来自远程节点相互冲突的更改
  • last_update_wins - 时间戳为提交最新的版本(newest commit timestamp)的数据将会被保存(这可以是本地或远程版本)
  • first_update_wins - 时间戳为最旧的版本(oldest timestamp)的数据将会被保存(这可以是本地或远程版本)

当参数track_commit_timestamp被禁用时,唯一允许的配置值是 apply_remote。 PostgreSQL 9.4 不支持 track_commit_timestamp 配置参数只能配置参数apply_remote(该参数是默认值)。

后记: 在订阅者的表需要主键约束;不然检测不到冲突;是否需要主键约束当然这个也是根据需求而定。

示例一:1个发布端,2个订阅端

现有实验环境

PG逻辑复制插件之pglogical使用说明

数据库版本操作系统IP数据库角色
PostgreSQL 13.4Debian GNU/Linux 11172.72.6.30lhrdbprovider
PostgreSQL 13.4Debian GNU/Linux 11172.72.6.31lhrdbsubscriber
PostgreSQL 12.8Debian GNU/Linux 11172.72.6.32lhrdbsubscriber

环境准备

提供者节点(发布端)配置

运行过程:

订阅者PG13节点配置

pglogical 可以同步表/序列结构;在创建订阅者 'pglogical.create_subscription' ; 里面参数synchronize_structure - 指定是否将提供者与订阅者之间的结构同步,默认为false。可以同步表/序列/索引,该功能仅限于同版本,不同版本会报错,具体可以根据情况来测试。

过程:

订阅端:

发布端查询:

发布端告警日志:

订阅端告警日志:

订阅者PG12节点配置

订阅端:

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

发布端:

可见,一个订阅端会产生一个复制槽。

发布端告警日志:

订阅端告警日志:

验证复制

发布端创建表

由于需要验证insert/update/delete/truncate操作是否同步;所以创建的表必须要有主键,若没有主键,则不能同步。

将表添加对应的复制集

对新建的表;并没有为其分配对应的复制集;需要手动添加。当然可以利用触发器自动添加。

有2种方法:

方法1:将public架构中的所有表添加到default复制集中

方法2:将表添加到对应的复制集中

例如:

查询:

发布端执行过程:

订阅端操作

PG31操作过程:

PG32操作过程:

发布端新增表

订阅端:

进程

发布端:

订阅端:

示例二:2个发布端,1个订阅端

现有实验环境

数据库版本操作系统IP数据库角色
PostgreSQL 13.4Debian GNU/Linux 11172.72.6.30lhrdb2provider
PostgreSQL 13.4Debian GNU/Linux 11172.72.6.31lhrdb2provider
PostgreSQL 12.8Debian GNU/Linux 11172.72.6.32lhrdb2subscriber

PG逻辑复制插件之pglogical使用说明

可从多个上游服务器,做数据的聚集和合并;

发布者跟订阅者的关系:一个发布者可以被多个订阅者订阅。多个发布者可以被同一个订阅者订阅。

环境

3个节点都操作:

3个节点都创建测试表; 订阅者创建的表可以无主键;若订阅者有主键,可利用序列自增来解决冲突。(例如:本例是两个发布者,则发布者1可取奇数;发布者二可取偶数)。若无主键;数据不受影响。

发布端

在6.30和6.31创建节点,注意修改IP地址:

订阅端

订阅端的数据来自于2个发布端,所以,需要在相应的数据库下创建1个订阅者节点和2个订阅。

测试数据

示例三:云环境RDS中配置pglogical(未成功)

国内的华为云、阿里云和腾讯云的RDS for PG都已经内置了pglogical插件!

华为云支持的插件列表可以参考:https://support.huaweicloud.com/usermanual-rds/rds_09_0045.html

购买2台华为云rds环境:

image-20220115142958399

数据库版本环境内网IPEIP数据库角色
PostgreSQL 12.6华为云RDS10.0.0.73119.3.169.211lhrdb发布者
PostgreSQL 12.6华为云RDS10.0.0.74139.9.129.179lhrdb订阅者

环境

2个节点都操作:

2个节点都创建测试表:

发布端

订阅端

这里发现华为云rds的源端并没有生成复制槽,所以,数据并不会同步!!

排错

failed: fe_sendauth: no password supplied

在创建订阅端的时候报错:

解决:

密码应该在node和subscription里都提供。

总结

1、经过多次试验,发现创建表的操作往往不能成功!!! 若发布端新增表,那么目标端最好也需要新建表。

2、创建节点和订阅时,一定要注意参数的值,例如:dbname、user和host

3、创建一个发布者节点,会同时在后台启动一个进程,叫“postgres: pglogical manager 16384”

4、创建一个订阅者,会在发布端后台启动一个进程,叫“postgres: walsender postgres 172.72.6.32(50340) idle”;同时会在订阅节点后台启动一个进程,叫“postgres: pglogical apply 16760:810137120”

4、排错请查看发布端和订阅端的数据库告警日志内容。

5、pglogical实现的这些功能,完全可以使用OGG来替代,具体可以参考:https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html

6、在华为云端rds中,虽然有pglogical插件,但是我自己没有配置成功,创建订阅的时候不能使用公网,使用内网IP后,虽然创建订阅成功了,但是源端也不能生成复制槽,导致数据并不能同步到目标端。其它云没有测试过!!

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

9 + 13 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部