PostgreSQL CDC Setup on AWS RDS: Step-by-Step OLake Guide

How to Set Up PostgreSQL CDC on AWS RDS - A Step-by-Step Guide

What and Why's of CDC?

Change Data Capture (CDC) is a method used in databases to track and record changes made to data. Recent trends in the data engineering industry point towards the increasing importance of real-time data processing and the integration of artificial intelligence (AI) in data architecture.

This led to an enormous increase in the need for faster processing and decision-making based on this data. PostgreSQL CDC enables organizations to:

• High-throughput data ELT without putting query pressure on DB.

• Implement real-time data replication with updates, insert & deletes.

CDC is usually stored in append-only logs called WAL (Write-ahead-logs) in Postgres. The above mechanism is called Logical Replication in postgres (Explained below)

This document is intended for those who need to modify their existing database for CDC.

Important

This guide is specifically for RDS PostgreSQL and not for RDS Aurora PostgreSQL.

Understanding Logical Replication

PostgreSQL CDC relies on logical replication, a powerful feature that enables real-time data synchronization. Unlike physical replication (which replicates the entire database at the byte level), logical replication works at the data level by:

• Capturing logical changes: Records inserts, updates, and deletes as they happen
• Publishing changes: Makes these changes available through publications and replication slots
• Streaming data: Sends only the actual data changes, not the entire database blocks
• Supporting selective replication: Allows you to replicate specific tables or schemas

Logical replication uses PostgreSQL's Write-Ahead Log (WAL) to track changes and then publishes them to subscribers in near real-time, making it ideal for CDC implementations.

Prerequisites

PostgreSQL Version Requirement 1

You can only enable logical replication if your PostgreSQL version is 10 or later.

PostgreSQL Version Requirement 2

• Turning on the logical replication (If it's not already on), requires a DB instance restart.
• If you want to use logical replication on a read replica, your PostgreSQL version must be 16 or later.
• Otherwise you need to restart master/primary postgres node

This doc assumes you have already setup the database instance

Access is needed to modify following (please contact your DevOps team who has setup the RDS DB):

• A database parameter group (Default one gets created when you create RDS instance) - Specifies how that database should be configured

• A VPC security group - used to connect to your database running on RDS securely

Create a new Database Parameter Group

AWS RDS already has a default RDS parameter group as given in the below picture, and you won’t be able to edit the parameters from this group.

Hence it is advised to create a new parameter group as suggested below.

1. Navigate to RDS and then to Parameter groups and the click on Create parameter group

2. Enter details such as Name, Description and Database details.

   1. In Engine type, choose Postgres (different from Aurora Postgres)

   2. Choose the required postgres version

3. Click on Create and parameter group will be created.

Modify Database Parameter Group

Some of the parameters need to be changed to configure this database for PostgreSQL CDC.

All of the mentioned parameters can be modified even in an existing parameter group if you are not using a default one.

(Tip: Even in the parameter group, there are a few Non Modifiable types. You can check the Value Type in the details of the parameter group for this information.)

Click edit on the created parameter group and set the following parameters:

• Set rds.logical_replication to 1 in the parameter group.

• Set max_replication_slots to at least 5 (default is 20) so that the internal replication by RDS is not affected. Best practice is to set this to the number of concurrent replication streams you expect for PostgreSQL CDC.

• Set max_wal_senders to at least 7 (default is 20). It’s best to set this value greater than max_replication_slots. A common practice is to set it to max_replication_slots + 2.

• Set wal_sender_timeout to 0 (default is 30000).

Save these changes. Restart the database instance for the changes to be effective.

If you are wondering what you have done, let’s get into details:

• rds.logical_replication: This parameter enables or disables logical replication for a PostgreSQL DB instance. Logical replication is a method used to replicate changes made to a database at a logical level, capturing changes to the data itself, such as inserts, updates, and deletes, and then replicating those changes to another database in near real-time. Note that logical replication is a kind of technique used for PostgreSQL CDC.

• max_replication_slots: This parameter specifies the maximum number of replication slots to be used for WAL replication in PostgreSQL CDC.

• max_wal_senders: This parameter specifies the maximum number of WAL senders or processes that can be connected to the server for streaming replication.

• wal_sender_timeout: This parameter specifies the time (in milliseconds) that the server allows for the WAL sender processes to send data without receiving an acknowledgment before the connection is closed.

Configure above parameter group & VPC Security Group

Everything on RDS runs within virtual private networks, which means we need to configure accessibility to our DB instance from OLake Go. Configure inbound and outbound access routes.

• Navigate to RDS → Databases → Our Postgres DB → Modify (Top right corner)

• Choose the new parameter group created in the parameter group section.

• The VPC security group you have selected, should have connectivity from OLake Go setup.

• In Additional configuration, specify the DB parameter group to use the group we just created for PostgreSQL CDC.

Important

Backup Retention Period: Choose a backup retention period of at least 7 days.

• At the bottom, Continue -> Apply immediately -> Modify DB instance.

• This needs restarting of the DB instance, Depends on if you are configuring on read-replica (only on PG version 16 & above possible) or master node, that has to be restarted.

Check Connectivity

To check if it is properly configured, connect to your database and run this command.

sql

select * from pg_settings where name in ('wal_level', 'rds.logical_replication')

You should see results like below ( settings , on and logical )

Now we could connect to this database using our Postgres root user. However, best practices are to use a dedicated account which has the minimal set of required privileges for CDC. Use this user credentials to connect to the Postgres source

Create a user and grant replication access​

The following commands will create a new user:

sql

CREATE USER <user_name> PASSWORD 'your_password_here';

Now, provide this user with read-only access to relevant schemas and tables. Re-run this command for each schema you expect to replicate data from:

sql

GRANT USAGE ON SCHEMA <schema_name> TO <user_name>;

sql

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;

sql

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

User also need rds_replication role, run the below command for the same:

sql

grant rds_replication to <user_name>;

Create publication and replication slot for CDC

sql

# To check all the replication slot
SELECT * FROM pg_replication_slots;

# Create a publication for all tables (or specific tables) with the name you will use in OLake
CREATE PUBLICATION olake_publication FOR ALL TABLES WITH (publish = 'insert,update,delete,truncate');

# To create a replication slot with pgoutput
SELECT pg_create_logical_replication_slot('<slot_name>','pgoutput'); --> Replace <slot_name> with the name of the slot that you would like to create

# If you want to delete a replication slot
select pg_drop_replication_slot('<slot_name>');

# To check all the publications
SELECT * from pg_publication;

Now you are ready to configure the ETL/ELT sync using OLake Go to dump the data into Apache Iceberg or Parquets for downstream analytics or machine-learning.

This setup can be used for other downstream processing as well, example PG --> Debezium --> Kafka --> Other backend-services consuming the CDC.

FAQs

Q1. How does OLake Go handle PostgreSQL CDC data extraction and what are the performance considerations?

OLake Go uses PostgreSQL's logical replication to capture change data in real-time. The tool connects to your RDS instance using the configured replication slot and extracts INSERT, UPDATE, and DELETE operations. Performance impact is minimal when properly configured - OLake Go only reads WAL (Write-Ahead Log) entries, not the actual data files.

Q2. What are the best practices for configuring OLake Go with PostgreSQL CDC on AWS RDS?

Key best practices include:

• Set rds.logical_replication = 1 and configure appropriate max_replication_slots
• Use dedicated database users with minimal required privileges
• Create replication slots with pgoutput plugin (currently supported by OLake Go)
• Monitor replication lag and adjust max_wal_senders as needed
• Use separate replication slots for different OLake Go jobs to avoid conflicts

Q3. How does OLake Go load PostgreSQL CDC data into a data lakehouse (Apache Iceberg)?

OLake Go processes PostgreSQL CDC data through its EL (Extract-Load) engine, which handles schema evolution and data type conversions as it writes. It supports two destination formats: Apache Iceberg tables and Parquet files on Amazon S3. OLake Go automatically handles orchestration, state management, and logging of pipeline runs, keeping the destination current with near real-time latency.

Q4. What are the common troubleshooting steps for OLake Go PostgreSQL CDC integration?

Common issues and solutions:

• Replication lag: Check max_wal_senders and max_replication_slots settings
• Connection failures: Verify VPC security groups and network connectivity
• Permission errors: Ensure the database user has rds_replication role and proper schema permissions
• Slot conflicts: Use unique replication slot names for different OLake Go jobs
• Data consistency: Monitor OLake Go's checkpoint mechanism to ensure no data loss via 2PC protocol

Q5. How does OLake Go compare to other ETL tools for PostgreSQL CDC workflows?

OLake Go is an EL tool built specifically for replicating databases into open lakehouse formats, which shapes its advantages for PostgreSQL CDC:

• Real-time processing: Native support for streaming CDC data as low as 1 minute latency
• Schema evolution: Automatic handling of table schema changes during replication, without breaking downstream jobs.
• Two native destinations: Apache Iceberg tables and Parquet files on S3, written directly with no intermediate systems.
• Source-level filtering: SQL-style filter conditions applied at ingestion, so only the rows you need are read and replicated.
• No duplicate rows: In Upsert mode, OLake Go deduplicates records using the source table's primary key, so each row appears exactly once in the destination.
• Resumable, stateful syncs: Parallel chunking splits large tables for fast initial loads, and checkpointing lets interrupted syncs resume from the last checkpoint instead of restarting.
• Alerts and notifications: Configurable alerts for sync events and failures on any OLake Go job.

Q6. What monitoring and alerting should be set up for OLake Go PostgreSQL CDC pipelines?

Monitor the pipeline from two sides:

• On the PostgreSQL side, watch replication slot health and lag with pg_replication_slots (check active status and the size of retained WAL), and keep an eye on WAL generation so a lagging slot doesn't fill the disk.
• On the OLake Go side, use OLake Go's alerts and notifications to get notified of sync events and failures. See the alerts and notifications documentation for setup.

Because you are using separate replication slots per job, monitoring each slot individually makes it easy to isolate which pipeline is falling behind.

Q7. Does OLake Go take care of Full-historical snapshot/replication before CDC? How fast is it?

OLake Go has fastest optimised historical load:

• OLake Go has Historical-load + CDC mode for this
• Tables are chunked into smaller pieces to make it parallel and recoverable from failures
• Any new table additions is also taken care of automatically.

For more detailed information about OLake Go's PostgreSQL CDC capabilities, visit the Postgres connector documentation.

Q8. What is the difference between pgoutput and wal2json plugins, and which should I use with OLake Go?

Both plugins receive the same core change information from the WAL - the difference is how they output it:

• pgoutput: Encodes changes in PostgreSQL's native binary logical replication protocol. It is the default and highest-performance option for most CDC workloads and is supported on all managed services (AWS RDS, Aurora PostgreSQL, Google Cloud SQL, Azure).
• wal2json: Converts WAL changes into JSON format, which is easier to parse in any programming language but has higher overhead than pgoutput.

OLake Go uses pgoutput via a publication, which is the recommended approach for both RDS PostgreSQL and Aurora PostgreSQL.

Important: pgoutput does not emit change events for tables that lack a primary key unless REPLICA IDENTITY FULL is explicitly set on those tables. If you are replicating tables without primary keys, run ALTER TABLE <table_name> REPLICA IDENTITY FULL before starting CDC.

Q9. Why should the CDC database user have minimal privileges rather than using the RDS master user?

While you can use the AWS master user account for CDC setup since it already has the rds_superuser and rds_replication roles, best practice is to create a dedicated account with only the minimum required permissions. This limits the blast radius of a potential credential leak.

The dedicated user only needs:

• USAGE on the relevant schemas
• SELECT on the tables being replicated
• The rds_replication role

Nothing more. Always use a purpose-built, least-privilege CDC user rather than a shared superuser account.

Q10. Can you use a single replication slot for multiple OLake Go sync jobs?

This is not recommended. A replication slot acts as an anchor - PostgreSQL keeps all WAL files needed by the slowest consumer on that slot, regardless of how far ahead other consumers have progressed. Using a shared slot across multiple jobs means one slow or stalled job blocks WAL cleanup for all consumers.

Best practice: Each independent OLake Go pipeline should use its own uniquely named replication slot to isolate failures and allow independent progress tracking. If one pipeline stalls, it does not hold back WAL cleanup for the others.

Q11. Does this CDC setup work with RDS Aurora PostgreSQL as well?

Yes. Aurora PostgreSQL supports CDC through PostgreSQL logical decoding using the native pgoutput plugin, and OLake Go has a dedicated Aurora PostgreSQL setup guide.

Logical replication on Aurora is enabled through the cluster parameter group (by setting rds.logical_replication = 1) rather than an instance parameter group, and changes are streamed via pgoutput with a PostgreSQL publication.

Note: The configuration differs from RDS PostgreSQL in a few areas. Aurora uses a cluster parameter group, requires a writer-node reboot to apply the change, and replication slot behavior can differ in a multi-AZ cluster. Follow OLake's Aurora PostgreSQL setup guide rather than the standard RDS PostgreSQL guide to avoid misconfiguration.

OLake Go

Replicate databases, Kafka, and S3 into Apache Iceberg with OLake Go, an open source EL engine built for Iceberg from the ground up.

SignupJoin SlackExplore OLake GitHub

Contact us at hello@olake.io