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

Question

I found the [link ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/postgresql-s3-export.html#aws_s3.export_query_to_s3) 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.

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.

Answer

You should consider Amazon Database Migration Service (DMS). Here is a lab which will help to understand the steps involved.
https://catalog.us-east-1.prod.workshops.aws/v2/workshops/976050cc-0606-4b23-b49f-ca7b8ac4b153/en-US/400/401/430-main-lab

Answer

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.

An alternative could be to [use wal2json plugin with AWS Lambda function and Amazon Kinesis stream](https://aws.amazon.com/blogs/database/stream-changes-from-amazon-rds-for-postgresql-using-amazon-kinesis-data-streams-and-aws-lambda/). 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication).

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

相關內容