How Copy S3 Data Directly into Postgres

If you've read my previous articles on Postgres, you now have the ability to spin up and spin down a Postgres instance from the command line. In this article, I'm going to load data in S3 directly into an AWS Postgres database.

The COPY command is an essential cornerstone to a data warehouse. Once you've spun of the postgres instance using Terraform, connect with your favorite SQL client (mine is PyCharm Professional) and run the following commands to add the S3 Extension.

We will be loading data from the bucket s3://awsdk-book into the a table master.dw.dim_account.

Now follow these steps that will allow Postgres to load from S3.

1. Create S3 Policy

2. Create S3 Role

3. Attach the S3 Policy and Role

4. Add the Role to Postgres

You should now have a direct stream into your database. Run the SQL command in your console to copy the table.

The data from your file will now be in your Postgres database.