# 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](https://debezium.io/documentation/reference/postgres-plugins.html) 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](https://debezium.io/documentation/reference/postgres-plugins.html#logical-decoding-output-plugin-installation) 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](https://debezium.io/documentation/reference/postgres-plugins.html) 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