To set up Change Data Capture (CDC) on Postgres you need to enable/configure Logical Replication. Before you start, please keep in mind:
Supported Postgres versions: 9.4.9+ and 9.5.4+
You can only set up CDC for Postgres on the RDS master instance.
The process requires a master instance reboot.
CDC for Postgres requires additional log storage. Though unlikely, failed replication due to a lost connection can cause logs to remain on the server. If logs do accumulate on the server, drop and recreate the replication slot. Notify Alooma if this happens so that we can recover any possible gaps in the data.
In order to replicate DELETE operations, the REPLICA IDENTITY parameter of each table being replicated must be set to DEFAULT, FULL, or BY INDEX (or anything but NOTHING).
You cannot have multiple Postgres CDC inputs referencing the same replication slot.
You can set up CDC for Postgres on RDS in just a few steps:
Go to your Amazon RDS Dashboard. If you already have a Parameter Group, skip to step 4.
In the sidebar, click Parameter Groups and then Create parameter group. Select your Postgres version (postgres9.6 in this example) and provide a name and description:
In the Parameter Groups, for the Parameter Group you created, find "logical_replication" and click Edit Parameters.
Check the box at the bottom and change the value for rds.logical_replication to 1.
Click Save changes.
Go to your Amazon RDS dashboard.
From DB instances, choose your instance.
Click on the Instance Actions drop-down and choose Modify. The Modify DB instance dialog will appear.
Scroll to Database Options and change DB Parameter group to the group you created.
Scroll to the bottom and click Continue to apply your changes.
In the Scheduling of Modifications screen that appears, select Apply immediately.
Click Modify DB Instance.
This will require you to reboot the instance.
From the main RDS page:
In the navigation pane, choose Instances, and then select the your Instance.
From the Instance Actions menu, choose Reboot.
You must create a dedicated replication slot for Alooma. If any other processes use this replication slot, you risk losing data.
Perform the following steps in your master database.
Log into postgresSql as a superuser and, using the test_decoding plugin, create a logical replication slot named: alooma_replication_slot
SELECT pg_create_logical_replication_slot('alooma_replication_slot', 'test_decoding');
Grant the necessary replication permission to your user to enable reading the logical replication slot:
GRANT rds_replication TO <user>;
Grant SELECT permission on all tables in the schema to your user:
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <user>;
Log in as the user and verify you can read from the logical replication slot:
SELECT count(*) FROM pg_logical_slot_peek_changes('alooma_replication_slot', null, null);
And that's it! You're all set to finish connecting to Postgres.