debezium同步postgresql数据至kafka

0 实验环境

全部部署于本地虚拟机
debezium docker部署
postgresql、kafka本机部署

1 postgresql

1.1 配置

设置postgres密码为123
仿照example，创建database postgres，scheme inventory，table customers
因为postgres用户有replication权限，所以可以直接使用
修改postgresql.conf文件

listen_addresses = '*' #确保容器可以访问到
shared_preload_libraries = '' #使用默认的pgoutput
wal_level = logical 

以postgres用户重启pg

pg_ctl restart

1.2 测试

show wal_level;

2 kafka

2.1 启动

参考博文
单节点kafka部署笔记

2.2 配置

修改kafka目录下的config/kraft/server.properties，确保容器可以访问到

listeners=PLAINTEXT://:9092,CONTROLLER://:9093
advertised.listeners=PLAINTEXT://172.17.0.1:9092

启动后无需创建topic，connect启动后会自动创建
如果手工创建，注意cleanup.policy一定要设置为compact模式，否则connect会出错停止

bin/kafka-topics.sh --create --topic debezium --config cleanup.policy=compact --bootstrap-server 172.17.0.1:9092

2.3 测试

列出所有topic

bin/kafka-topics.sh --bootstrap-server 172.17.0.1:9092 --list

3 启动connector

3.1 启动

下载docker镜像并启动，通过BOOTSTRAP_SERVERS指定kafka

docker pull debezium/postgres
docker run -d --name connect -p 8083:8083 -e GROUP_ID=1 -e CONFIG_STORAGE_TOPIC=debezium_configs -e OFFSET_STORAGE_TOPIC=debezium_offsets -e STATUS_STORAGE_TOPIC=debezium_statuses -e BOOTSTRAP_SERVERS=172.17.0.1:9092 debezium/connect:latest

3.2 配置

由于默认数据格式是avro，非常长，改为json格式会简洁很多
修改容器中的配置文件/kafka/config/connect-standalone.properties

key.converter.schemas.enable设置为false
value.converter.schemas.enable设置为false

可以通过docker cp将文件拷贝出来，修改后再复制进去，或者直接挂载配置文件

3.3 创建connect

在pgsql-inventory-connector.json中写入请求数据，通过database.hostname确定postgresql

{
  "name": "inventory-connector",  
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector", 
    "plugin.name": "pgoutput",
    "database.hostname": "172.17.0.1", 
    "database.port": "5432", 
    "database.user": "postgres", 
    "database.password": "123", 
    "database.dbname" : "postgres", 
    "topic.prefix": "dbserver1", 
    "table.include.list": "inventory.customers"
  }
}

添加

curl -i -X POST -H "Accept:application/json" -H "Content-Type:application/json" 172.17.0.1:8083/connectors/ -d @pgsql-inventory-connector.json

删除

curl -i -X DELETE 172.17.0.1:8083/connectors/inventory-connector/

查询

curl -i -X GET -H "Accept:application/json" 172.17.0.1:8083/connectors/inventory-connector

重启

curl -X POST -H "Accept:application/json" 172.17.0.1:8083/connectors/inventory-connector/restart

4 测试

postgresql、kakfa、connect启动完成后

4.1 kafka消费

bin/kafka-console-consumer.sh --topic dbserver1.inventory.customers --from-beginning --bootstrap-server 172.17.0.1:9092

4.2 postgresql修改

insert into inventory.customers values (1005,'aA','bB','aAbB@home.com');

4.3 kafka结果

Avro格式

{"schema":{"type":"struct","fields":[{"type":"struct","fields":[{"type":"int32","optional":false,"default":0,"field":"id"},{"type":"string","optional":false,"field":"first_name"},{"type":"string","optional":false,"field":"last_name"},{"type":"string","optional":false,"field":"email"}],"optional":true,"name":"dbserver1.inventory.customers.Value","field":"before"},{"type":"struct","fields":[{"type":"int32","optional":false,"default":0,"field":"id"},{"type":"string","optional":false,"field":"first_name"},{"type":"string","optional":false,"field":"last_name"},{"type":"string","optional":false,"field":"email"}],"optional":true,"name":"dbserver1.inventory.customers.Value","field":"after"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"version"},{"type":"string","optional":false,"field":"connector"},{"type":"string","optional":false,"field":"name"},{"type":"int64","optional":false,"field":"ts_ms"},{"type":"string","optional":true,"name":"io.debezium.data.Enum","version":1,"parameters":{"allowed":"true,last,false,incremental"},"default":"false","field":"snapshot"},{"type":"string","optional":false,"field":"db"},{"type":"string","optional":true,"field":"sequence"},{"type":"string","optional":false,"field":"schema"},{"type":"string","optional":false,"field":"table"},{"type":"int64","optional":true,"field":"txId"},{"type":"int64","optional":true,"field":"lsn"},{"type":"int64","optional":true,"field":"xmin"}],"optional":false,"name":"io.debezium.connector.postgresql.Source","field":"source"},{"type":"string","optional":false,"field":"op"},{"type":"int64","optional":true,"field":"ts_ms"},{"type":"struct","fields":[{"type":"string","optional":false,"field":"id"},{"type":"int64","optional":false,"field":"total_order"},{"type":"int64","optional":false,"field":"data_collection_order"}],"optional":true,"name":"event.block","version":1,"field":"transaction"}],"optional":false,"name":"dbserver1.inventory.customers.Envelope","version":1},"payload":{"before":null,"after":{"id":1005,"first_name":"aA","last_name":"bB","email":"aAbB@home.com"},"source":{"version":"2.2.0.Alpha3","connector":"postgresql","name":"dbserver1","ts_ms":1687946054175,"snapshot":"false","db":"postgres","sequence":"[\"34244288\",\"34244576\"]","schema":"inventory","table":"customers","txId":758,"lsn":34244576,"xmin":null},"op":"c","ts_ms":1687946054536,"transaction":null}}

JSON格式

瞬间简洁很多

{"before":null,"after":{"id":1005,"first_name":"aA","last_name":"bB","email":"aAbB@home.com"},"source":{"version":"2.2.0.Alpha3","connector":"postgresql","name":"dbserver1","ts_ms":1688112418157,"snapshot":"false","db":"postgres","sequence":"[\"85230368\",\"85230656\"]","schema":"inventory","table":"customers","txId":1637,"lsn":85230656,"xmin":null},"op":"c","ts_ms":1688112418467,"transaction":null}