By using AWS re:Post, you agree to the Terms of Use

Can I use the RDS Postgresql S3 export query to replicate changes from RDS to S3?


I found the link for Postgres to s3 query export, and I wanted to check if I can use this to perform change data capture from RDS to S3.

asked 2 years ago265 views
3 Answers
Accepted Answer

We recommend AWS Database Migration Service (DMS) to replicate changes or CDC from RDS to S3. The PostgreSQL query export copies data into S3, but not for CDC. You can add logic to the RDS to S3 query, which will only get the incremental changes. However, it is better to use DMS as this solution already manages the replication activities for you. DMS uses the pglogical extension, runs a query and writes the output to S3.

answered 2 years ago
reviewed 10 months ago

As explained by @AWS-User-0586262 you can design your own CDC solution which leverages S3 export. You can use triggers to collect changes in a shadow table and then export them to S3 bucket at the end of the day (or any other defined frequency). Another approach could be to use a timestamp field to propagate changes at a defined frequency. Both these approaches will require you to devise your own solution which handles various scenarios and also does exception handling.

An alternative could be to use wal2json plugin with AWS Lambda function and Amazon Kinesis stream. This option requires more effort to build but offers much greater level of flexibility. If you are looking for least effort solution then Amazon Database Migration Service will be a better fit. For both solutions, you need to enable logical replication on RDS PostgreSQL instance.

answered a year ago

You should consider Amazon Database Migration Service (DMS). Here is a lab which will help to understand the steps involved.

answered a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions