automate aws_s3.table_import_from_s3

Question

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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html) for aws_s3.table_import_from_s3 however, it does not discuss how to automate this process.

Any help is greatly appreciated 
Thanks

Answer

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

automate aws_s3.table_import_from_s3

Relevant content