How to enable debezium for PostgreSQL

Debezium is an open source distributed platform that turns your existing databases into event streams, so applications can see and respond almost instantly to each committed row-level change in the databases.

This article explains the steps to enable debezium for PostgreSQL.

Step1: Login to the server via SSH as root.

Step2: Install wal2json plugin for PostgreSQL.

In Red Hat/CentOS,

#yum install wal2json<version>

In Debian/Ubuntu,

#apt-get install postgresql-<version>-wal2json

Where,

<version> need to be replaced with PostgreSQL major version.

Step3: Change the wal_level in PostgreSQL configuration.

wal_level = logical

Step4: Restart PostgreSQL service.

Step5: Switch to postgres user.

#sudo su - postgres

Step6: Login to PostgreSQL shell.

#psql

Step7: Create user debezium with some password.

>CREATE ROLE debezium REPLICATION LOGIN PASSWORD 'PASS';

Where PASS should be replaced with the required passsword.

Step8: Grant the user superuser permissions to debezium user.

>ALTER USER debezium WITH SUPERUSER;

Step9: Grant all privileges to debezium on the required database.

>GRANT ALL PRIVILEGES ON DATABASE <db> TO debezium;

Where db need to be replaced with the name of the database.

Step10: Create a replication plugin called wal2json and name it wal2json.

>SELECT * FROM pg_create_logical_replication_slot('wal2json', 'wal2json');

Step11: Make sure that the newly created replication slot is available.

>select * from pg_replication_slots;

Step12: Create a publication for one table.

>CREATE PUBLICATION dbz_publication FOR TABLE <schema>.<table>;

Where schema, table need to be replaced with the corresponding values.

Step13: Add remaining tables to this publication.

>select 'ALTER PUBLICATION dbz_publication ADD TABLE ' || schemaname ||'.'|| tablename || ';' from pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

Please note that new tables for which need to set replication should be added to this publication.

>ALTER PUBLICATION dbz_publication ADD TABLE <schema>.<table>;

Where schema, table need to be replaced with the corresponding values.

Step14: Set replicaton identity to FULL for all tables.

>select 'ALTER TABLE ' || schemaname ||'.'|| tablename || ' REPLICA IDENTITY FULL;' from pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

Note that the debizium user should have read access to all metadata tables. We can verify the same by running the following queries as debizium user.

>SELECT * FROM information_schema.table_constraints; 
>SELECT * FROM information_schema.key_column_usage;

If multiple databases are there, need to create a unique slot for each database and follow the same procedures as above.

That's all…