14 抽取PostgreSQL

本文目标

开启PG的日志，配置DBZ从其捕获数据变化。

开启PG日志

data/postgresql.conf
wal_level = logical

重启数据库。

配置Debezium

quarkus.http.port=8999
rkus.log.level=INFO
quarkus.log.console.json=false

debezium.source.connector.class=io.debezium.connector.postgresql.PostgresConnector
debezium.source.offset.storage.file.filename=data/pg_offsets.dat
debezium.source.offset.flush.interval.ms=0
debezium.source.plugin.name=pgoutput
debezium.source.snapshot.mode=always

debezium.source.database.hostname=127.0.0.1
debezium.source.database.port=6432
debezium.source.database.user=test
debezium.source.database.password=test
debezium.source.database.dbname=test
debezium.source.schema.include.list=public
debezium.source.table.include.list=public.t1

debezium.source.topic.prefix=yyx
debezium.source.key.converter.schemas.enable=false
debezium.source.value.converter.schemas.enable=false
debezium.source.schema.history.internal.kafka.bootstrap.servers=127.0.0.1:9092
debezium.source.schema.history.internal.kafka.topic=pg_schema_history

debezium.source.decimal.handling.mode=string
debezium.source.lob.enabled=true
debezium.source.database.history.skip.unparseable.ddl=true
debezium.source.tombstones.on.delete=false

debezium.sink.type=kafka
debezium.sink.kafka.producer.bootstrap.servers=127.0.0.1:9092
debezium.sink.kafka.producer.key.serializer=org.apache.kafka.common.serialization.StringSerializer
debezium.sink.kafka.producer.value.serializer=org.apache.kafka.common.serialization.StringSerializer

debezium.format.key.schemas.enable=false
debezium.format.value.schemas.enable=false

重启DBZ Server。

检查是否正常

如果DBZ正常，会自动创建表名的队列。
yyx.public.t1

[yinyx@hostyyx bin]$ ./kafka-topics.sh --list --bootstrap-server 127.0.0.1:9092
__consumer_offsets
logminer_schema_history
ora_schema_history
schemahistory
yinyx
yyx
yyx.YINYX.T1
yyx.public.t1
yyx.test.t1
yyx.test.t1_cp
[yinyx@hostyyx bin]$ ./kafka-console-consumer.sh --bootstrap-server 127.0.0.1:9092 --topic yyx.public.t1
{"before":null,"after":{"f1":1,"f2":"aa|111c","f3":1671014854975156},"source":{"version":"2.0.0.Final","connector":"postgresql","name":"yyx","ts_ms":1670986054975,"snapshot":"false","db":"test","sequence":"[\"27170080\",\"27170368\"]","schema":"public","table":"t1","txId":754,"lsn":27170368,"xmin":null},"op":"u","ts_ms":1670986055103,"transaction":null}
{"before":null,"after":{"f1":1,"f2":"aa|111c","f3":1671014884350943},"source":{"version":"2.0.0.Final","connector":"postgresql","name":"yyx","ts_ms":1670986084351,"snapshot":"false","db":"test","sequence":"[\"27171216\",\"27171272\"]","schema":"public","table":"t1","txId":755,"lsn":27171272,"xmin":null},"op":"u","ts_ms":1670986084780,"transaction":null}
{"before":null,"after":{"f1":11,"f2":"xa|113","f3":1670279908000000},"source":{"version":"2.0.0.Final","connector":"postgresql","name":"yyx","ts_ms":1670986134657,"snapshot":"false","db":"test","sequence":"[\"27171416\",\"27171472\"]","schema":"public","table":"t1","txId":756,"lsn":27171472,"xmin":null},"op":"u","ts_ms":1670986134915,"transaction":null}
{"before":{"f1":11,"f2":null,"f3":null},"after":null,"source":{"version":"2.0.0.Final","connector":"postgresql","name":"yyx","ts_ms":1670986150154,"snapshot":"false","db":"test","sequence":"[\"27171616\",\"27171672\"]","schema":"public","table":"t1","txId":757,"lsn":27171672,"xmin":null},"op":"d","ts_ms":1670986150258,"transaction":null}
{"before":null,"after":{"f1":4,"f2":"22|aa","f3":1671014993000000},"source":{"version":"2.0.0.Final","connector":"postgresql","name":"yyx","ts_ms":1670986195671,"snapshot":"false","db":"test","sequence":"[\"27171792\",\"27171848\"]","schema":"public","table":"t1","txId":758,"lsn":27171848,"xmin":null},"op":"c","ts_ms":1670986195753,"transaction":null}
^CProcessed a total of 5 messages

遗留问题，update操作的before居然是null，难道是没有使用decoderbufs插件的原因？