操作记录:PostgreSQL的Debezium连接器

最新推荐文章于 2024-06-22 19:44:23 发布
最新推荐文章于 2024-06-22 19:44:23 发布
阅读量2.9k 收藏 5
点赞数 1
文章标签： 大数据 postgresql kafka shell
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Zznxz0601/article/details/116166269
版权

需求：

Kafka实时采集PostgreSQL数据库有关增删改事务（DML）的WAL信息。

使用组件：

debezium-connector-postgres（1.0.3版本以上）

postgresql数据库（10版本以上）

kafka可用集群（2.11-1.1.0版本，3台）

pgoutput（逻辑解码插件，版本数据库自带）

修改postgresql.conf配置文件：

vim $PGDATA/postgresql.conf

wal_level=logical
checkpoint_timeout = 15min[自定义，其它wal相关的，默认配置就好]

重启数据库服务。

安装并配置Debezium：

  1. 下载：wget https://repo1.maven.org/maven2/io/debezium/debezium-connector-postgres/1.0.3.Final/debezium-connector-postgres-1.0.3.Final-plugin.tar.gz
  2. 解压：tar -zxvf  ???/debezium-connector-postgres-0.8.3.Final-plugin.tar.gz
  3. 重命名：mv debezium-connector-postgres debezium
  4. 复制debezium的jar包至kafka的libs目录下(略)

建立Kafka连接器：

请选择任一台kafka集群服务器。

  • 修改kafka的配置文件

vim $KAFKA_HOME/config/connect-standalone.properties

bootstrap.servers=[ip]:9092

  • 编写连接器信息

vim $KAFKA_HOME/config/postgres.properties

name=[自定义]

slot.name=[自定义]

connector.class=io.debezium.connector.postgresql.PostgresConnector

database.hostname=[数据库ip]

database.port=[数据库端口]

database.user=[用户:需有replication权限]

database.password=[用户密码]

database.dbname=[监视数据库]

database.history.kafka.bootstrap.servers=[kafka服务器ip:9092]

database.server.name=[自定义]

table.whitelist=[监视表:如public.demo]

publication.name=[自定义]

decimal.handling.mode=string

plugin.name=pgoutput

#snapshot.mode=never

#heartbeat.interval.ms=10000

#slot.drop.on.stop=true

#publication.autocreate.mode=filtered

#time.precision.mode=adaptive_time_microseconds

#schema.refresh.mode=columns_diff_exclude_unchanged_toast

具体连接器配置可参阅官方文档

https://debezium.io/documentation/reference/1.0/connectors/postgresql.html

启动kafka连接器：

nohup $KAFKA_HOME/bin/connect-standalone.sh $KAFKA_HOME/config/connect-standalone.properties $KAFKA_HOME/config/postgres.properties >>$KAFKA_HOME/logs-debezium/connect_kafka_postgresql.log 2>&1 &

注意：

刚建立新的kafka连接器，服务器需要导入数据库快照，恢复数据(这里是更新lsn，在kafka服务器生成相应log文件)；

快照模式（snapshot.mode）可根据官方文档修改；

若数据库的数据量很大，请确保kafka集群服务器有足够大的磁盘空间；

故需要一段较长的时间，复制槽的active才会更新为true，连接器才可正式使用。

修改Postgresql的发布表：

待数据恢复完成后，即复制槽的active更新为true，可修改发布表。

debezium的发布状态默认为alltables(发布所有表)，生产环境尽量只发布相应表。

update pg_publication set puballtables=false where pubname is not null;
alter publication debezium_kafka_postgresql add table [发布表];
select * from pg_publication_tables;

加入心跳机制（可忽略）：

当数据库执行大事务操作时（如reindexdb,vacuumdb等），复制槽中的订阅端无法消费这些数据，若发布端数据库在该时段内没有变更发布数据表操作，很大概率就会造成pg_replication_slots的<restart_lsn>一直停滞不前，

导致wal空间不断增大且无法自动清理，影响数据库性能，故可加入心跳机制。

（本人认为）心跳机制可分为两种。

  • Debezium的心跳机制

连接器信息加入：

heartbeat.interval.ms=1000

具体连接器配置可参阅官方文档

https://debezium.io/documentation/reference/1.0/connectors/postgresql.html

我一阵捣鼓后发现效果不佳，故放弃了。如果有人应用过该配置，可分享下使用经验。

  • Postgresql模拟心跳机制

官方文档有这么一条说明：

WAL磁盘空间消耗

在某些情况下，WAL文件占用的PostgreSQL磁盘空间可能会出现峰值或超出正常比例。有三种可能的原因可以解释这种情况：

  • Debezium定期向数据库确认已处理事件的LSN。这confirmed_flush_lsn在pg_replication_slots插槽表中可见。数据库负责回收磁盘空间，并且可以从restart_lsn同一表中计算WAL大小。因此，如果confirmed_flush_lsn定期增加并且restart_lsn滞后，则数据库确实需要回收空间。磁盘空间通常以批处理块的形式回收，因此这是预期的行为，无需在用户方面采取任何措施。

  • 受监视的数据库中有许多更新，但是只有很少量与受监视的表和/或架构有关。通过使用heartbeat.interval.ms配置选项启用定期心跳事件，可以轻松解决这种情况。

  • PostgreSQL实例包含多个数据库，其中一个是高流量数据库。与另一个数据库相比，Debezium监视另一个低流量的数据库。然后Debezium无法确认LSN，因为复制插槽针对每个数据库工作，并且不调用Debezium。由于WAL被所有数据库共享，因此它趋于增长，直到Debezium监视的数据库发出事件为止。

为了克服第三个原因，有必要

  • 使用heartbeat.interval.ms配置选项启用定期心跳记录生成

  • 定期从Debezium跟踪的数据库中发出变更事件

    • 对于wal2json解码器插件，生成空事件就足够了。例如，这可以通过截断一个空的临时表来实现。

    • 对于其他解码器插件，建议创建一个不受Debezium监视的补充表。

然后，一个单独的过程将定期更新表（插入新事件或全部更新同一行）。然后，PostgreSQL将调用Debezium，后者将确认最新的LSN，并允许数据库回收WAL空间。

①创建debezium_heartbeat表

CREATE TABLE public.debezium_heartbeat
 (
    id INT8 NOT NULL,
    now_heartbeat_ts TIMESTAMPTZ DEFAULT NOW(),
    last_heartbeat_ts TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT debezium_heartbeat_pkey PRIMARY KEY (id)
);
INSERT INTO public.debezium_heartbeat (id,now_heartbeat_ts,last_heartbeat_ts) VALUES (202104,NOW(),NOW());

注意：

创建完后，务必将debezium_heart表添加至kafka连接器信息的table.writelist，并且加入postgresql的发布表pg_publication。

②数据库服务器上编写脚本

vim ???/shells/auto_update_debezium_heartbeat.sh

#!bin/bash

#数据库bin命令存放目录
pg_path="???"
#执行的SQL存放目录
sql_path="???"

db_host='ip'
db_port='端口'
db_username='用户'
db_name='数据库'
#密码可保存至~/.pgpass
#格式：ip:端口:数据库:用户:密码
#chmod 700 ~/.papass

step=10 #间隔的秒数，不能大于60

for (( i = 0; i < 60; i=(i+step) )); do
  echo '['$(date "+%Y-%m-%d %H:%M:%S")'] 每10s更新<debezium_heartbeat>>表'
  echo '['$(date "+%Y-%m-%d %H:%M:%S")'] 开始更新'
$pg_path/psql -h $db_host -p $db_port -U $db_username --dbname $db_name -e -f $sql_path'update_debezium_heartbeat.sql'
echo '['$(date "+%Y-%m-%d %H:%M:%S")'] 完成更新'
  sleep $step 
done
exit 0

chmod 755 auto_update_debezium_heartbeat.sh

vim ???/shells/update_debezium_heartbeat.sql

update public.debezium_heartbeat set now_heartbeat_ts=NOW(),last_heartbeat_ts=now_heartbeat_ts where id=202104;

③加入定时任务

vim /etc/crontab

#更新debezium_heartbeat
* 15 * * * root ???/shells/auto_update_debezium_heartbeat.sh >> ???/shells/auto_update_debezium_heartbeat.log 2>&1

测试：

数据库服务执行vacuumdb full(该指令订阅端无法消费)：

pg_wal目录最大可增长至10~12G(wal_sender进程跟不上checkpoint进程或受网络波动)，

所以data目录得预留20G左右磁盘空间(根据自身数据量)。

期间pg_wal目录根据checkpoint点记录的lsn位置（且是订阅端返回的lsn位置）自动清理wal文件，

最后保留max_wal_size配置(默认1G)定义的wal文件大小。

自动清理成功！

vacuumdb full期间也执行一些DDL操作，

订阅端也能正常接收（可通过$KAFKA_HOME/logs查看或更下游消费端测试）；

结论：

加入心跳机制，实质上是推动复制槽的<restart_lsn>。

请根据实际情况选择是否加入心跳机制！

后续：

使用PostgreSQL的Debezium连接器，并相应解决了一些可能会出现的问题，

Kafka成功实时采集到Postgresql数据库相应的DML（增删改）操作。

如何消费Kafka订阅信息并作如何处理，目前我还没有一个完整完善的流程操作。

计划流程：postgresql -> debezium -> kafka -> spark streaming(spark sql) -> mysql(postgresql/hive) ->superset

待整个(伪)实时流程建立好后，再抽空编写后面操作的博文。

如有任何不足、错误或可补充的地方，欢迎各位一起讨论，一起学习！

附1：停止连接器并删除复制槽

kill -9 [ConnectStandalone进程pid]
select * from pg_replication_slots;
select pg_drop_replication_slot('debezium_kafka_postgresql');
delete from pg_publication where pubname='debezium_kafka_postgresql';

附2：相关Kafka指令

#查看topic
kafka-topics.sh --list --zookeeper ???:2181,???:2181,???:2181
#删除topic
kafka-topics.sh --zookeeper ???:2181,???:2181,???:2181 --delete --topic [topic]

附3：手动清除wal文件

#!/bin/bash
##pg_wal_clean.sh

CUR_DATE=`date +"%Y%m%d"`
CUR_TIME=`date +"%Y-%m-%d %H:%M:%S"`

pg_bin_path="???/bin"
pg_data_path="???/data"
pg_wal_path="???/data/pg_wal"
#pg_archive_path="???/archive/archdir"
sql_path="???/shells"

db_host='???'
db_port='???'
db_username='postgres'
db_name='???'
#密码可保存至~/.pgpass
#格式：ip:端口:数据库:用户:密码
#chmod 700 ~/.papass

echo '['$CUR_TIME'] 开始:针对性清除wal文件'

lastwal=`$pg_bin_path/pg_controldata  -D $pg_data_path |grep "Latest checkpoint's REDO WAL file" |awk '{print $6}'`
echo '['$CUR_TIME'] [1]Latest checkpoint's REDO WAL file:$lastwal

echo '['$CUR_TIME'] 执行checkpoint'
$pg_bin_path/psql -h $db_host -p $db_port -U $db_username --dbname $db_name -e -f $sql_path'/pg_checkpoint.sql'
lastwal=`$pg_bin_path/pg_controldata  -D $pg_data_path |grep "Latest checkpoint's REDO WAL file" |awk '{print $6}'`
echo '['$CUR_TIME'] [2]Latest checkpoint's REDO WAL file:$lastwal

#echo '['$CUR_TIME'] 清除归档目录的wal文件'
#$pg_bin_path/pg_archivecleanup -d $pg_archive_path/$CUR_DATE/ $lastwal

echo '['$CUR_TIME'] 清除pg_wal目录的wal文件'
$pg_bin_path/pg_archivecleanup -d $pg_wal_path/ $lastwal

echo '['$CUR_TIME'] 完成:针对性清除wal文件'
##pg_checkpoint.sql
checkpoint;

我就住在江边路
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 8
    评论
利用Debezium捕获PostgreSQL的数据变化
Auspicious航的博客
03-23 3077
最近团队接到这样一个需求，其他团队开发的业务系统需要监控我们负责的业务系统的数据库变更情况，当表发生INSERT,UPDATE及DELETE操作时，相关的业务系统能实时获取数据的变化信息。 经过讨论，团队决定使用Debezium实现需求，于是便对Debezium进行了较深入的学习。下面给大家分享一下自己对Debezium认识与理解。
【Debezium系列】订阅Postgresql数据保姆级案例分享
xmops的博客
04-22 1376
搁在过去的文章当中，或者应该称作笔记，我只会记录一些命令和代码，是不会或者很少写几个注释，更别说写一些文章背景，软件版本，问题背景，需求梳理，解决思路，收获小结等等，按照上面的写作方法存在了一些问题，比如，无从知道前因后果，无法知晓适用环境，无法知道以后需要规避哪些风险，所以过去写的文章顶多能拿来作为实施的参考，没有更多的价值和作用。痛定思痛之后，我决定从这一篇文章开始，按照写文章的正确思路，逐一表达清楚，争取让读者朋友一眼看懂，同时为今后回顾的时候减少熟悉前前后后关联事情的时间和精力。
推荐使用：Npgsql Entity Framework Core 提供的 PostgreSQL 数据库连接器
gitblog_00056的博客
05-13 541
推荐使用：Npgsql Entity Framework Core 提供的 PostgreSQL 数据库连接器 项目地址:https://gitcode.com/npgsql/efcore.pg Npgsql.EntityFrameworkCore.PostgreSQL 是一个开源的 Entity Framework Core 提供程序，专为 PostgreSQL 数据库设计。它使您能够在 Mic...
debezium-connector-postgres自定义快照
qq_49588762的博客
09-02 986
1 debezium简介 debezium是用于捕获变更数据的开源分布式平台。可以响应数据库的所有插入，更新和删除操作。 2 debezium连接 PostgreSQL debezium第一次连接到 PostgreSQL 服务器或集群时，连接器会拍摄所有模式的一致快照。快照完成后，连接器会持续捕获插入、更新和删除数据库内容以及提交到 PostgreSQL 数据库的行级更改。 3 快照问题 2.1 问题引出 我正在做一个数据库迁移工具，需要支持离线迁移和在线迁移。那么，就要确定一个离线迁移和在线迁移的分界点，
Flink CDC实时同步PG数据库
酸菜鱼的博客
05-12 3559
Flink CDC同步PG数据库全流程
springboot+debezium捕获数据库变更（mysql、sql-server、mongodb、oracle.........）
热门推荐
胖虎儿的博客
07-04 1万+
如何捕获数据库数据变更？使用canal? 但是canal只能捕获mysql啊，我要捕获MongoDB、sqlServer呢？使用debezium即可！
postgres-cdc-flink:使用Debezium流式传输PostgreSQL CDC，并使用Flink使用状态流进行充实
04-10
参考在您的机器/集群上设置Apache Kafka（使用Kafka Connect） 从安装Debezium PostgreSQL连接器运行Apache Kafka和Kafka Connect 在PostgreSQL中创建表transactions和customers （SQL文件） 使用请求主体向您的...
sqlpp11-connector-postgresql:PostgreSQL连接器用于sqlpp11库
05-25
sqlpp11-connector-postgresql PostgreSQL连接器用于sqlpp11库执照sqlpp11在。地位例子有关如何使用此库的示例 auto config = std::make_shared();config->host = " 127.0.0.1 " ;config->user = " someuser " ;...
pgvm：PostgreSQL版本管理器
02-05
pgvm：PostgreSQL版本管理器
wal-listener：PostgreSQL WAL侦听器
02-05
WAL侦听器 一种帮助实现事件驱动的体系结构的服务。 为了保持系统中数据的一致性，我们将使用事务消息传递-在具有域模型更改的单个事务中发布事件。 该服务允许您使用其逻辑解码功能订阅PostgreSQL数据库中的更改...
pgbackman：PostgreSQL备份管理器
02-05
PostgreSQL备份管理器/ PgBackMan是用于管理使用pg_dump和pg_dumpall创建的PostgreSQL逻辑备份的工具。 它旨在管理来自在多个PostgreSQL节点中运行的数千个数据库的备份，并且支持多种备份服务器拓扑。 ...
PostgreSQL实时采集方案：PostgreSQL-＞Debezium-＞Kafka
qq_42784856的博客
10-27 2997
PostgreSQL实时采集方案：PostgreSQL->Debezium->Kafka 原文来自大佬博客，此文章为转载文章，我本人也是参考博主文章和博主教导搭建成功的，附上原文链接 https://blog.csdn.net/u012551524/article/details/82798066 关注博主之后，博主主页有联系方式，有啥不懂的可以请教 目的：构建基于hbase的实时数仓 解决的问题：RDBMS到Hbase的数据实时采集 方法：Postgresql ----->
使用debezium、kafka-connect将postgres数据实时同步到kafka中，表topic重新路由
LeoHan
08-10 2444
使用debezium和kafka-connect实现postgres数据库实时同步到kafka中
Debezium系列之：使用正则表达式路由器修改表级别Topic名称
zhengzaifeidelushang的博客
05-04 534
Debezium系列之：使用正则表达式路由器修改表级别Topic名称
PostgreSQL 变化数据捕捉（CDC）
sunny05296的博客
02-09 3960
函数：pg_logical_slot_get_changes(slot_name name, upto_lsn pg_lsn, upto_nchanges int, VARIADIC options text[])
Debezium系列之：使用Debezium接入PostgreSQL数据库数据到Kafka集群的详细技术文档
zhengzaifeidelushang的博客
09-01 2445
Debezium系列之：PostgreSQL数据库的Debezium连接器
Debezium:kafka 连接器配置
高矮
10-24 8352
目的：构建基于hbase的实时数仓 解决的问题：RDBMS到Hbase的数据实时采集 方法：Postgresql-----&gt;Debezium-----&gt;Kafka------&gt;Sparkstreaming------&gt;Phoenix 本文：本文主要是kafka连接器一些相关配置 官网参考地址...
【热】查询大数据信用风险等级要注意什么?去哪里比较好？
最新发布
SEOsys的博客
06-22 663
市面上提供大数据信用报告查询的地方比较多，但是素质也是参差不齐的，细心的朋友会发现，只要搜索一下大数据信用查询，会出现很多信息，也能通过这些入口进行查询，但是在选择之前一定要考虑到自己适用的版本，特别是只有一个单一的查询页面，没有人工客服的网页一定不要选择。因为投广告的服务商，一般为了走量，没有售后服务。之前有一个网友找到我解读报告，搜索了相关的关键词进来的，但是被网页里的广告混淆了视线，选错了，选了某下信用，获取了报告，但是找不到人解读，后来我们也为她免费提供了报告解读服务。
jdbc:postgresql
03-29
jdbc:postgresql是Java语言中用于连接和操作PostgreSQL数据库的一种标准接口。它是Java Database Connectivity（JDBC）规范的一部分，提供了一组类和方法，用于在Java应用程序中与PostgreSQL数据库进行交互。 通过jdbc:postgresql，你可以使用Java编程语言来执行各种数据库操作，如连接到数据库、执行SQL查询和更新、事务管理等。它提供了一种简单、灵活和可靠的方式来访问和操作PostgreSQL数据库。 要使用jdbc:postgresql，你需要首先下载并安装PostgreSQL数据库，并确保数据库服务器已启动。然后，你需要在Java项目中引入相应的JDBC驱动程序，以便能够与PostgreSQL数据库建立连接。 以下是使用jdbc:postgresql连接和操作PostgreSQL数据库的一般步骤： 1. 加载并注册PostgreSQL的JDBC驱动程序。 2. 创建一个数据库连接，指定数据库的URL、用户名和密码。 3. 创建一个Statement对象，用于执行SQL语句。 4. 执行SQL查询或更新操作，并获取结果。 5. 处理查询结果或更新操作的返回结果。 6. 关闭数据库连接和相关资源。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 8
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值