CDC setup in Postgres

Postgres CDC setup on Linux

1. Need to set the following in postgresql.conf. To find the conf file, go to postgres and run query SHOW config_file. It will give a path to the conf file.

wal_level=logical

max_replication_slots=5

max_wal_senders=5

2. Add the following in the pg_hba.conf conf file. First create the sprinkle user as mentioned in step5 and use that in the below configuration settings. Replace 0.0.0.0 with IPs of sprinkle whitelisted hosts.

host replication [user] 0.0.0.0/0 md5

host replication [user] ::0/0 md5

3. Post this restart Postgres DB

sudo service postgresql restart

4. To check Logical replication is enabled, run the below command with the user who has replica permission. That should give** Logical **as. output if it is enabled.

show wal_level;

5. Login to Postgre console and login with super-user or member of rds-super-user (Hint: \du will give the details of each user). Need to create a sprinkle user or use an existing user which will be used for CDC ingestion. Below permissions need to be granted to that user.GRANT rds_replication TO user_name;

GRANT USAGE ON SCHEMA ‘public’ TO user_name;

GRANT SELECT ON ALL TABLES IN SCHEMA ‘public’ TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA ‘public’ GRANT SELECT ON TABLES user_name;

6. You are required to configure a debezium plugin for reading the WAL from sprinkle. ‘pgoutput’ plugin is recommended for reading from Postgres on Linux.

Now you can create a slot or configure any slot name with the plugins : wal2json or pgoutput. If the slot does not exist, sprinkle will create a slot with the passed name with the user configured.

If you are planning to use wal2json plugin, it needs to be installed on the Postgres machine. Detailed steps available on debezium

7. Need to grant below permissions to the user. For pgoutput plugin it is important to create publication and users must have create permissions for the database.

Grant create permission to the user on schema.

GRANT CREATE ON SCHEMA PUBLIC TO user_name;

Publication must be created for the database(name should be the same).

CREATE PUBLICATION dbz_​publication FOR ALL TABLES;

8. If you are creating a new slot then you can create a slot using pgoutput plugin as below.

SELECT pg_create_logical_replication_slot(slot_name, 'pgoutput');

9. Post this verify that your slot is created and you can access from particular user you had given permission run

select * from pg_replication_slots;

Note: When a slot is used for sprinkle CDC ingestion then it should not be used in any other cases.

‍

CDC setup in AWS Postgres

Note: This CDC setup is allowed only in master instances not in read replica.

1. In configuration change rds.logical_replication to 1.

2. Post this DB reboot should be done.

3. To check Logical replication is enabled, run the below command with the user who has replica permission. That should give Logical as output if it is enabled.

show wal_level;

4. Login to Postgre console and login with super-user or member of rds-super-user (Hint: \du will give the details of each user). Need to create a sprinkle user or use an existing user which will be used for CDC ingestion. Below permissions need to be granted to that user.GRANT rds_replication TO user_name;

GRANT USAGE ON SCHEMA ‘public’ TO user_name;

GRANT SELECT ON ALL TABLES IN SCHEMA ‘public’ TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA ‘public’ GRANT SELECT ON TABLES user_name;

5. You are required to configure a debezium plugin for reading the WAL from sprinkle. ‘wal2json’ plugin is recommended for reading from AWS RDS.

Now you can create a slot or configure any slot name with the plugins : wal2json or pgoutput. If the slot does not exist, sprinkle will create a slot with the passed name with the user configured. rds_replication permission should be sufficient for creating slots.

6. If you are using pgoutput plugin then need to grant below permissions to the user. For pgoutput it is important to create publication and users must have create permissions for the database.

Grant create permission to the user on schema.

GRANT CREATE ON SCHEMA PUBLIC TO user_name;

Publication must be created for the database(name should be the same).

CREATE PUBLICATION dbz_​publication FOR ALL TABLES;

7. If you are creating a new slot then you can create a slot using any of the two plugins.

SELECT pg_create_logical_replication_slot(slot_name, 'wal2json');   

OR

SELECT pg_create_logical_replication_slot(slot_name, 'pgoutput');

But make sure that if you are using pgoutput plugin then grant required permissions as mentioned above in step 5.

Post this verify that your slot is created and you can access from particular user you had given permission run

select * from pg_replication_slots;

Note: When a slot is used for sprinkle CDC ingestion then it should not be used in any other cases.

For more details https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html#AuroraPostgreSQL.Replication.Logical.Configure