I am new to architecture role. There is a requirement to read file from S3 and load into postgresql in AWS EC2 instance. As per the role I need to share the deployment model for this. Could anyone guide what details I need to provide?

CodePudding user response：

This will require a few components:

Assuming python3, you will need to get the DataBase credentials to connect- This will be via pyscopg2

In the same script, you will need to connect to the AWS S3 bucket using AWS SDK, read the file into a variable, and simply execute a query in order to update/insert the file into the DB.

you can refer to this article It might provide some insights- AWS Serverless Hit n’ Run

CodePudding user response：

You can use postgres COPY command to do your task. Overview of the command can be found here

Install Postgre SQL on EC2, simple documentation can be found here.

Install and configure AWS CLI in EC2 (if its not there by deafult)

Use AWS S3 sync or AWS S3 cp commmands to download files from S3 to your local directory

Use psql \COPY command to copy the files into your database

Example like below

aws s3 cp s3://mybucket/psql.csv /myfolder/psql.csv
psql -h localhost -U username -d dbname -c '\COPY table FROM ''psql.csv'' CSV HEADER'