Setting Up the Debezium PostgreSQL Connector Simplified

Debezium PostgreSQL Connector allows organizations to monitor changes in databases and create triggers for other applications. Although it supports one task, the PostgreSQL connector is widely used for building highly reliable applications due to the flexibility of data processing it offers. Similar to other Debezium connectors, PostgreSQL connectors take snapshots and then determine the changes in the databases. The changes are then sent to Kafka topics, which are consumed by applications.

Upon a complete walkthrough of this article, you will gain a decent understanding of PostgreSQL and Debezium. You will also learn about the working of the Debezium PostgreSQL connector. Read along!

Prerequisites

• Basic understanding of Event Streams.
• Basic understanding of Kafka and ZooKeeper Services

What is Debezium?

Debezium is a Change Data Capture (CDC) tool that uses a log-based method to track database changes. The primary use of Debezium is to monitor such changes and store them to a different destination. In the previous databases, Database Administrators used to have access to such changes, and they used to save the changes to the source control system manually. But now, through Debezium, you can use connectors to keep track of changes and store them in different locations by replication. Replication is nothing but copying or moving the data of one central database to another. However, in Debezium, replication is carried out through connectors as it establishes the connection to its source databases.

What is PostgreSQL?

PostgreSQL is an advanced and Open-Source Object-Relational Database Management System. It is the extension of SQL along with some advanced features. Today, PostgreSQL functions as both relational and non-relational queries. It has helped developers build applications while protecting data integrity — many websites as well as mobile applications leverage PostgreSQL for the flexibility and reliability of digital solutions. 

What are Debezium Connectors?

The main goal of Debezium connectors is to capture changes from the databases and produce events. Therefore, it becomes easier for applications to react to changes very quickly. These connectors then publish changes to Kafka. Kafka is an open-sourced and well-known Event Streaming Platform. When Debezium connectors are deployed to the Kafka cluster, they monitor databases for new changes. After monitoring, they also write events into Kafka.

These events are then independently consumed by the different applications. Kafka acts as a distributed system, ensuring all the connectors are running and configured properly. If any Kafka Connect endpoints in the Kafka cluster go down, the remaining Kafka Connect endpoints will restart the previously running connector on the terminated endpoint. Consequently, it provides high fault tolerance and scalability to applications.

But, it’s possible that not every application needs such type of fault tolerance or scalability. As a result, such applications do not need to rely on external Kafka clusters or Kafka connect services. This is where embedded Debezium connectors are used directly. Whenever there is any data change, the connectors will inform applications rather than the Kafka clusters. 

Getting Started with Debezium PostgreSQL Connector

The Debezium PostgreSQL connector mainly captures the row-level changes in the PostgreSQL databases. Initially, when the Debezium is connected to the PostgreSQL database, the Debezium PostgreSQL connector takes a continuous snapshot of all the schemas. Schemas are the basic framework of the databases. When the snapshots are entirely captured, the connectors notice all the row-level changes like insert, delete and update committed to the PostgreSQL database. The Debezium PostgreSQL connector generates the change data events and sends them to Kafka topics, which applications can consume for further tasks.

However, before connecting the Debezium PostgreSQL connectors, you need to check the compatible version of Debezium with the PostgreSQL connector.

PostgreSQL consists of two different ways to capture changes in databases:

• A Logical Decoding Output Plugin: You need to install this output plugin. It should run first before running the PostgreSQL. The plugins can be decoderbufs, wal2json, or pgoutput. You can leverage one of them for your system. 
• Java Code: Java code reads the changes produced by the logical decoding output plugins. The PostgreSQL JDBC driver uses the streaming replication protocol of PostgreSQL.

PostgreSQL makes use of the WAL (Write-Ahead Log) segments, which consist of the changes to actual data in PostgreSQL. And if the connector stops for any reason, it restarts from the WAL position where it last stopped. 

The most crucial feature that Debezium PostgreSQL provides is security. It is achieved through giving privileges to the users. Instead of providing unauthorized access to the Debezium users, the PostgreSQL connectors provide them with superuser privileges. It means a Debezium replicated user is provided with superuser privileges.

How to use the Debezium PostgreSQL Connector with the PostgreSQL database?

Step 1: Prepare PostgreSQL:

• Enable logical replication by setting wal_level to logical in your postgresql.conf file.
• Configure max_replication_slots and max_wal_senders to appropriate values.
• Restart PostgreSQL to apply these changes.
• Create a replication user with appropriate permissions using the SQL command:

CREATE ROLE debezium WITH REPLICATION LOGIN PASSWORD 'your_password';

Step 2: Install Debezium PostgreSQL Connector:

• Download and install the Debezium PostgreSQL connector plugin in your Kafka Connect setup.
• Ensure Kafka Connect is properly configured and running.

Step 3: Configure the Connector:

• Create a JSON configuration file for the Debezium PostgreSQL connector, specifying connection details, replication settings, and the databases and tables to monitor.
• Example configuration:

{
  "name": "debezium-postgres-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "your_postgres_host",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "your_password",
    "database.dbname": "your_database",
    "database.server.name": "your_server",
    "plugin.name": "pgoutput"
  }
}

Step 4: Deploy the Connector:

• Use Kafka Connect’s REST API to deploy the connector configuration. For example:

curl -X POST -H "Content-Type: application/json" --data @connector-config.json http://localhost:8083/connectors

Conclusion

This article gave us a brief on the working of Debezium PostgreSQL connectors. In Debezium, there are two ways of using connectors. The first one is using the Kafka connect clusters which are considered the high fault-tolerant for applications. And the second one is the embedded Debezium connectors that work the same as the first ones, but instead of sending the change in the data events to the clusters, it directly sends it to applications, making applications the least fault-tolerant compared to the first one. 

FAQ on Debezium Connector for PostgreSQL

Give Hevo Data a try and sign up for a 14-day free trial today. You can also look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of learning about the working of the Debezium PostgreSQL connector. Tell us in the comments below!