How to stream PostgreSQL changes to Amazon S3 with AWS Fargate

The useful part

How to stream PostgreSQL changes to Amazon S3 with AWS Fargate | AWS Database Blog Skip to Main Content AWS Database Blog. How to stream PostgreSQL changes to Amazon S3 with AWS Fargate In this post, we show you how to build a fully managed, event-driven change data capture (CDC) pipeline. You deploy the entire pipeline with a single AWS CloudFormation template, and it can run in private subnets with no internet gateway without exposing resources to the public internet.

How it works

• Many teams need their database changes in a data lake, an auditing system, or a downstream API without waiting for nightly batch jobs.
• The source database (Amazon RDS for PostgreSQL or Aurora PostgreSQL) writes changes to its WAL.
• AWS Lambda recently introduced durable functions (for fault-tolerant workflows with checkpointing) and Lambda MicroVMs (stateful sandboxes with up to 8-hour runtimes).
• However, Lambda MicroVMs auto-suspend when idle and resume on demand, which would disconnect the replication slot during quiet periods.
• WAL streaming works best with a truly persistent connection that never drops.

What to take from it

For testing or low-volume workloads, you can skip VPC endpoints by using subnets with internet access (set CreateVpcEndpoints=false). If your subnets have internet access, set CreateVpcEndpoints=false when deploying. S3 storage: depends on data volume, $0.023 per GB for S3 Standard storage.

Example or evidence

• A container build tool (Docker or Finch) to build and push the CDC reader image to Amazon Elastic Container Registry (Amazon ECR).
• It builds on PostgreSQL's native Write-Ahead Log (WAL) and logical replication capabilities to take change data outside of PostgreSQL entirely, landing it in S3 where you can query it with Amazon Athena,...
• Solution overview The pipeline uses AWS Fargate, Amazon EventBridge, Amazon Simple Queue Service (Amazon SQS), and AWS Lambda.
• A CDC reader container on Fargate polls the replication slot every few seconds, grabs new changes, and publishes them as events to EventBridge.

Details worth keeping

Polling the database on a timer misses changes and adds load. A replication slot and publication define what to capture. EventBridge receives events on a custom bus and routes them to an Amazon SQS queue.