Debezium, Kafka-connect : updates to postgres are not showing up as messages, only inserts are

Question

I am using the command : # bin/connect-standalone.sh config/connect-standalone.properties config/debezium-config.properties

My debezium-config.properties is :

name=publications-connector
database.hostname=localhost
database.port=5432
database.user=andy
database.password=postgres
database.dbname=postgres
database.server.name=dbserver1
table.whitelist=public.publications

In Postgres, I have a table called publications

When I insert a new record into the publications table, I can see that my consumer shows the new message in json format.

However when I update an existing record into the publications, no new message is published to the topic and hence nothing to consume.

How can I fix this ?

Also, I would like to add another table 'comments' to my database. What changes do I need to make to the debezium-config.properties file or anywhere else to have those messages be published to its own topic ?

I see the following logs from the console : WARN: no values found for table 'public.publications' from update message at 'source_info[server=dbserver1'db='postgres', .... schema=public, table=publications]'; skipping record (io.debezium.connector.postgres.RecordsStreamProducer:333)

Thanks,

Answer 1

After some detailed reading into many debezium topics, i was able to solve this by setting the REPLICA IDENTITY to FULL. Once I did this, on updates I was able to see the update messages and consume them.

Answer 2

I think the reason is this: If a table does not have a primary key, the connector does not emit UPDATE or DELETE events for that table. For a table without a primary key, the connector emits only create events. Typically, a table without a primary key is used for appending messages to the end of the table, which means that UPDATE and DELETE events are not useful. reference resources