Configuring PostgreSQL Change Data Capture with Debezium

Change Data Capture (CDC) has shifted how we think about database integrations. Instead of hammering your primary relational database with periodic polling queries, CDC captures row-level changes directly from the write-ahead log (WAL) as they occur.

Debezium is an open-source distributed platform that taps directly into PostgreSQL’s logical decoding capabilities. By turning row mutations into event streams, Debezium lets you broadcast creation, update, and deletion events across your microservices or automation engines without adding latency to your application transactions.

In this guide, I will take you step-by-step through configuring PostgreSQL for logical replication, deploying a complete Debezium and Apache Kafka pipeline using Docker Compose, and registering a connector to stream real-time database changes.

What You’ll Need

• Hetzner VPS or Contabo VPS for hosting the infrastructure
• DigitalOcean as an alternative hosting platform
• n8n Cloud or self-hosted n8n for processing change streams
• Namecheap if you need a custom domain for your endpoints

Table of Contents

• Step 1: Configuring PostgreSQL for Logical Replication
• Step 2: Deploying Kafka and Debezium Connector
• Step 3: Registering the Debezium Connector
• Step 4: Consuming CDC Events with Automation Pipelines
• Getting Started

Step 1: Configuring PostgreSQL for Logical Replication

PostgreSQL writes all database transactions to a write-ahead log before committing them to storage. To allow Debezium to read these raw transactional changes, PostgreSQL must be configured to use logical replication. This mode transforms the internal binary logging format into a logical stream of events using a logical decoding plugin such as pgoutput.

If you are provisioning a server on a Hetzner VPS, open your postgresql.conf file (typically located at /etc/postgresql/16/main/postgresql.conf or inside your container volume) and adjust the following parameters:

wal_level = logical
max_wal_senders = 4
max_replication_slots = 4

Here is what these settings do:

• wal_level = logical: Tells PostgreSQL to record the extra metadata required for logical decoding.
• max_wal_senders = 4: Defines the maximum number of concurrent task connections PostgreSQL allows for streaming changes.
• max_replication_slots = 4: Sets the maximum limit for replication slots. Debezium requires one dedicated slot per connector.

After editing postgresql.conf, restart your PostgreSQL daemon to apply the configuration change:

sudo systemctl restart postgresql

Next, create a dedicated database user with the necessary permissions for logical replication and table access. Log into PostgreSQL as the administrative user:

sudo -u postgres psql

Run the following SQL commands to set up a sample database, table, dedicated CDC user, and publication:

CREATE DATABASE app_db;
\c app_db;

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE USER debezium_user WITH REPLICATION PASSWORD 'dbz_secure_password_123';
GRANT CONNECT ON DATABASE app_db TO debezium_user;
GRANT USAGE ON SCHEMA public TO debezium_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO debezium_user;

ALTER TABLE customers REPLICA IDENTITY FULL;

Setting REPLICA IDENTITY FULL on the table ensures that during UPDATE and DELETE operations, PostgreSQL includes the entire previous state of the row in the WAL, not just the primary key. Debezium uses this metadata to populate the before snapshot in your event payloads.

💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.

Step 2: Deploying Kafka and Debezium Connector

Debezium relies on Apache Kafka Connect to run as a cluster worker service, consuming raw database events and outputting them to Kafka topics. We will define a complete multi-container service stack using Docker Compose.

Create a file named docker-compose.yml on your server with the following complete specification:

version: '3.8'

services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.5.0
    container_name: zookeeper
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181
      ZOOKEEPER_TICK_TIME: 2000

  kafka:
    image: confluentinc/cp-kafka:7.5.0
    container_name: kafka
    depends_on:
      - zookeeper
    ports:
      - "9092:9092"
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:29092,PLAINTEXT_HOST://localhost:9092
      KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT
      KAFKA_INTER_BROKER_LISTENER_NAME: PLAINTEXT
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

  postgres:
    image: postgres:16-alpine
    container_name: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres_master_password
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
      - "-c"
      - "max_wal_senders=4"
      - "-c"
      - "max_replication_slots=4"

  debezium:
    image: debezium/connect:2.4
    container_name: debezium
    ports:
      - "8083:8083"
    depends_on:
      - kafka
      - postgres
    environment:
      BOOTSTRAP_SERVERS: kafka:29092
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: my_connect_configs
      OFFSET_STORAGE_TOPIC: my_connect_offsets
      STATUS_STORAGE_TOPIC: my_connect_statuses
      CONFIG_STORAGE_REPLICATION_FACTOR: 1
      OFFSET_STORAGE_REPLICATION_FACTOR: 1
      STATUS_STORAGE_REPLICATION_FACTOR: 1

Bring up the container cluster using Docker Compose in detached mode:

docker compose up -d

Give the cluster about 20 seconds to finish initializing the Kafka brokers and Debezium Kafka Connect REST service. Verify that the Kafka Connect API is accessible by checking its status:

curl -H "Accept:application/json" localhost:8083/

You should receive a JSON output with the service version string, indicating that Kafka Connect is live and listening on port 8083.

Step 3: Registering the Debezium Connector

Now that Kafka Connect is running, we register the PostgreSQL Debezium connector by posting a JSON configuration file to the Kafka Connect REST interface.

Create a local file named pg-source-connector.json containing the exact connector properties:

{
  "name": "postgres-cdc-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",
    "plugin.name": "pgoutput",
    "database.hostname": "postgres",
    "database.port": "5432",
    "database.user": "postgres",
    "database.password": "postgres_master_password",
    "database.dbname": "app_db",
    "database.server.name": "dbserver1",
    "topic.prefix": "cdc",
    "table.include.list": "public.customers",
    "tombstones.on.delete": "false",
    "decimal.handling.mode": "double",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:29092",
    "schema.history.internal.kafka.topic": "schema-changes.inventory"
  }
}

This configuration instructs Debezium to connect to our PostgreSQL container, initialize a logical replication slot using the pgoutput plugin, listen specifically to changes on the public.customers table, and publish structured event messages to the Kafka topic named cdc.public.customers.

Register the connector with a POST request to your Debezium API endpoint:

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

Confirm that the connector is actively running and reading the logical replication stream:

curl -i -X GET http://localhost:8083/connectors/postgres-cdc-connector/status

If everything was deployed cleanly, you will see the task state reported as RUNNING.

Step 4: Consuming CDC Events with Automation Pipelines

When a record is inserted, updated, or deleted, Debezium streams structured event messages containing both schema definition and row payloads.

Let’s test this by inserting a new row into PostgreSQL:

docker exec -it postgres psql -U postgres -d app_db -c "INSERT INTO customers (first_name, last_name, email) VALUES ('Alex', 'Rivers', 'alex.rivers@example.com');"

To see the change event generated by Debezium, inspect the output from the corresponding Kafka topic using the Kafka console consumer:

docker exec -it kafka /usr/bin/kafka-console-consumer --bootstrap-server localhost:9092 --topic cdc.public.customers --from-beginning

The resulting JSON payload looks like this:

{
  "before": null,
  "after": {
    "id": 1,
    "first_name": "Alex",
    "last_name": "Rivers",
    "email": "alex.rivers@example.com",
    "created_at": 1700000000000
  },
  "source": {
    "version": "2.4.0.Final",
    "connector": "postgresql",
    "name": "cdc",
    "ts_ms": 1700000000500,
    "db": "app_db",
    "schema": "public",
    "table": "customers",
    "txId": 578,
    "lsn": 24233728
  },
  "op": "c",
  "ts_ms": 1700000001000
}

Notice the key components of the payload:

• op: Represents the operation type. c stands for Create, u for Update, and d for Delete.
• before: Shows the row attributes before the operation (null during inserts).
• after: Contains the full row data written during the operation.
• source: Contains exact transactional coordinates, including log sequence numbers (LSN) and transaction IDs.

Now, instead of running inefficient batch jobs like we cover in Scheduling Background Python Scripts with Systemd Timers, you can use this real-time stream to power event-driven architectures.

For high-throughput applications, if your database setup experiences heavy concurrent writes or connection limits, combining logical replication with connection management as detailed in Configuring PgBouncer Connection Pooling for PostgreSQL ensures long-term system health without overloading database backends.

Once these CDC change events reach your messaging layer, downstream automation becomes trivial. You can route these messages to send custom notifications or sync user signups instantly to messaging tools, using steps similar to those found in Building a Self Hosted WhatsApp Bot.

Getting Started

To implement PostgreSQL Change Data Capture with Debezium in your infrastructure:

1. Provision a high-performance cloud instance using Hetzner VPS, Contabo VPS, or DigitalOcean.
2. Configure PostgreSQL logical replication parameters in postgresql.conf and restart the service.
3. Deploy Apache Kafka and Debezium Connect using Docker Compose.
4. Register your PostgreSQL CDC connector using the REST API to begin streaming realtime log changes.
5. Stream change streams into workflow systems like n8n Cloud to build reactive, real-time pipelines.

Outsource Your Automation

Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.