TECHIES WORLD

For Techs.... Techniques.... Technologies....

AWSLinux

How to enable debezium for PostgreSQL in RDS

admin

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 in RDS.

Step1: Modify the RDS parameter group with the following values.

rds.logical_replication=1
shared_preload_libraries=pg_stat_statements,pg_hint_plan,pgaudit
ssl=0
log_replication_commands=1
log_statement=all

Step2: Connect to RDS instance using psql.

#psql -h <host> -U <username> -p <port>

Here we need to replace host, username and password with the corresponding values.

Step3: Create user debezium with some password.

>CREATE ROLE debezium WITH PASSWORD 'password' CREATEDB CREATEROLE LOGIN;

Where password should be replaced with the required one.

Step4: Grant the user superuser permissions to debezium user.

>GRANT rds_superuser TO debezium;

Step5: 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.

Step6: Create a replication plugin called wal2json and name it wal2json_rds.

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

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

>select * from pg_replication_slots;

Step8: 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.

Step9: 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.

Step10: Set replication 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…

Leave a Reply