Import Postgres data into RDS using S3 and aws_s3

Question:

I’m having a hard time importing data from S3 into an RDS postgres instance. According to the docs, you can use this syntax:

So, in pgAdmin, I did this:

I also tried it with an explicit NULL for the last parameter.

The error message I get is:

I checked the server logs and there was no further information.

I have triple-checked the correctness of all the parameters. How do I make this work?

UPDATE:

I can confirm that I can do an s3.getObject() in the Java aws sdk using these same credentials.

Answer:

The main issue here is that you need to 1) add a IAM role to the RDS instance to access the S3 bucket and 2) add an S3 endpoint to the VPC where the RDS instance run in order to allow communications.

This is the procedure I followed to make it work, using AWS cli commands in a shell (take care of value properly the environmental variables involved), hope it can help:

  1. Create the IAM role:

  1. Create the IAM policy that will be attached to the IAM role:

  1. Attach the policy:

  1. Add the role to a specific instance – this step need to be repeated for every new instance:

  1. Create the VPC endpoint for the S3 service:

The route table id related to the VPC where the endpoint is created can be retrieved through the command

Leave a Reply