automate aws_s3.table_import_from_s3

0

I am trying to automate importing S3 data into RDS PostgreSQL using the aws_s3.table_import_from_s3 function in postgres. I have the policy and role created for postgres to have access to the bucket and the function created in postgres. Ideally I would like for the function to execute whenever a new object is created in the bucket, but I am stuck on how to go about setting that up.

Do I need to set up SNS so that postgres is notified when a new object is created?

I have read the documentation for aws_s3.table_import_from_s3 however, it does not discuss how to automate this process.

Any help is greatly appreciated Thanks

1 Answer
0

Hi There,

I understand you are trying to automate importing S3 data into RDS PostgreSQL

It is recommend to use AWS Database Migration Service (DMS) to replicate changes[1] or CDC from RDS to S3. It is better to use DMS as this solution already manages the replication activities for you. DMS uses the pglogical extension.

An alternative could be to use wal2json plugin with AWS Lambda function and Amazon Kinesis stream.[2] 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.

Here is a lab which will help to understand the steps involved. [3]

[1] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.S3.html

[2] https://aws.amazon.com/blogs/database/stream-changes-from-amazon-rds-for-postgresql-using-amazon-kinesis-data-streams-and-aws-lambda/

[3] https://catalog.us-east-1.prod.workshops.aws/v2/workshops/976050cc-0606-4b23-b49f-ca7b8ac4b153/en-US/400/401/430-main-lab

Mfanelo
answered 2 years 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