基于kafka connector+debezium实现PG到MySQL的数据实时同步

〇、相关资料

1、debezium官网地址

（1）官网

Debezium Release Series 2.5

（2）官方文档

Debezium Documentation :: Debezium Documentation

（3）pg connector介绍

Debezium connector for PostgreSQL :: Debezium Documentation

2、红帽官网（有中文）

第 8 章 PostgreSQL 的 Debezium 连接器 Red Hat build of Debezium 2.3.7 | Red Hat Customer Portal

3、容器环境搭建

CDC-Debezium-docker部署oracle kafka connector整套环境_哥们要飞的blog的技术博客_51CTO博客

4、pg关于wal日志的文档

PostgreSQL: Documentation: 16: 30.5. WAL Configuration

一、环境准备

1.1 容器环境准备

参考3、容器环境搭建

CDC-Debezium-docker部署oracle kafka connector整套环境_哥们要飞的blog的技术博客_51CTO博客

1.2 PG wal日志准备

1、概述

Debezium只支持pgout逻辑复制流

2、配置逻辑解码使用复制插槽

wal_level=logical
max_wal_senders=1
max_replication_slots=1

参考pg关于wal日志的文档：PostgreSQL: Documentation: 16: 30.5. WAL Configuration

1.3 基础角色权限配置

1、概述

postgres超管自带REPLICATION&LOGIN权限

推荐最低权限用户

2、流程

管理权限，执行命令

CREATE ROLE <name> REPLICATION LOGIN;

1.4 创建&查询权限配置

1、介绍

2、流程

CREATE ROLE <replication_group>;
GRANT REPLICATION_GROUP TO <original_owner>;
GRANT REPLICATION_GROUP TO <replication_user>;
ALTER TABLE <table_name> OWNER TO REPLICATION_GROUP;

filtered

1.5 pg网络开放

1、文件介绍

pg_hba.conf 中添加条目

2、示例

0.0.0.0

1.6 WAL 磁盘空间

1.7 多连接器和插槽配置

1.8 数据库升级继续捕获防止丢失

8.5. 设置 PostgreSQL 以运行 Debezium 连接器 Red Hat build of Debezium 2.3.7 | Red Hat Customer Portal

二、postgresql-source配置

2.1 json编写

{
  "name": "postgresql-source-connector-di-new",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "database.hostname": "192.169.51.22",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "w354erg",
    "database.dbname": "wer43sd",
    "database.schema": "public",
    "schema.include.list": "public",
    "schemas.enable": "false",
    "database.history.kafka.topic": "pg_schema-changes.test",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "pg.schema-changes.test",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "topic.prefix": "pg.dev",
    "slot.name": "ljhtest1005",
    "plugin.name": "pgoutput",
    "slot.drop.on.stop": "true",
    "snapshot.mode": "never",
    "producer.max.request.size": "9999999",
    "producer.override.max.request.size": "9999999",
    "connector.client.config.override.policy":"ALL"
  }
}

2.2 生成connector

curl -i -X POST -H "Accept: application/json" -H "Content-Type: application/json" http://localhost:8083/connectors/ -d @postgresql-source-connector.json

三、jdbc-sink配置

3.1 json编写

{
    "name": "jdbc-mysql-sink-connector",  
    "config": {
        "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector",  
        "tasks.max": "1",  
        "connection.url": "jdbc:mysql://192.168.57.165:3306/a",  
        "connection.username": "root",  
        "connection.password": "sdrf34643e1",  
        "insert.mode": "upsert", 
        "topics": "pg.dev.public.biz_operate_log", 
        "delete.enabled": "true",  
        "primary.key.mode": "record_key",  
        "schema.evolution": "basic",  
        "database.time_zone": "UTC"  
    }
}

3.2 生成connector

curl -i -X POST -H "Accept: application/json" -H "Content-Type: application/json" http://localhost:8083/connectors/ -d @jdbc-mysql-sink-connector.json

四、验证

4.1 connector

4.2 topic

4.3 consumer

4.4 数据验证

1、删除前

2、删除后

4.5 PG库验证

SELECT pg_drop_replication_slot('debezium')

select * from pg_replication_slots