Store PostgreSQL data Changes in Kafka topics by running locally on windows.

This blog guides you through the process of capturing and storing dynamic PostgreSQL database changes in Kafka topics, enabling efficient and scalable data synchronization.

👉 To install Kafka on Windows, refer to this: 🔗 https://www.geeksforgeeks.org/how-to-install-and-run-apache-kafka-on-windows/

Download PostgreSQL (version should be 10.0+)
To capture data from PostgreSQL, first you need to enable logical decoding by executing the following command:

ALTER SYSTEM SET wal_level = logical;

Restart the PostgreSQL from the services app on windows after that verify that the wal_level configuration is set to logical by executing the following command:

SHOW wal_level;

Install Debezium PostgreSQL Connector

Create a plugins folder and one more folder inside plugins postgres-debezium-connector in the Kafka directory.

Download PostgreSQL Connector from here: https://debezium.io/releases/2.3/

Extract the downloaded zip file and put all .jar files into C:\kafka\plugins\postgres-debezium-connector

also create postgres-connector.json file in C:\kafka\plugins\postgres-debezium-connector

Modified this file according to your PostgreSQL configuration.

{
    "name": "postgres-debezium-connector",
    "config": {
        "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
        "database.hostname": "localhost",
        "database.port": "5432",
        "database.user": "postgres",
        "database.password": "1234",
        "database.dbname": "MartenDB",
        "table.include.list": "event_store.mt_events",
        "column.include.list": "event_store.mt_events.data",
        "plugin.name": "pgoutput",
        "topic.prefix": "debezium",
        "slot.name": "debezium_replication_slot"
    }
}

open C:\kafka\config\connect-distributed.properties and set plugin.path to

plugin.path=C:/kafka/plugins

Steps for running PostgreSQL Connector

Run Zookeeper:

.\kafka\bin\windows\zookeeper-server-start.bat .\kafka\config\zookeeper.properties

Run Kafka Server:

.\kafka\bin\windows\kafka-server-start.bat .\kafka\config\server.properties

Run Kafka Connector:

.\kafka\bin\windows\connect-distributed.bat .\kafka\config\connect-distributed.properties

Send Post Request to running connector with PostgreSQL Configuration file:

curl -X POST -H "Content-Type: application/json" --data @.\kafka\plugins\postgres-debezium-connector\postgres-connector.json http://localhost:8083/connectors

Now our connector is working and ready to capture data from a particular table that we mentioned on the properties file

To check that our connector is working properly check its status by entering this URL in the browser:

http://localhost:8083/connectors/postgres-debezium-connector/status

change something on that table and check the kafka topics are storing the message by running this command:

 .\kafka\bin\windows\kafka-console-consumer.bat --bootstrap-server localhost:9092 --from-beginning --property print.key=true --topic debezium.event_store.mt_events

OR you can use tools like kafka magic to handle kafka topics: kafka magic