how to import data files from s3 to postgresql rds

Question

I am very new to AWS, and Postgresql.

• I have created a Postgresql db (using rds on was)
• I have uploaded several documents to multiple s3 buckets
• I have a EC2 (Amazon Linux 64 bit) running

I tried to use a data pipeline, but nothing seems to be available (template) for Postgres. I can't figure out how to connect to my RDS instance and import/export data from postgres.

I assumed that I could use EC2 to grab from my S3 bucket and import into Postgres in lieu of no data pipeline template being available. If it is possible I have no idea how.. Please advise if possible..

Answer 1

As of now, there is no copy command in RDS Postgresql but there sure is a way to do it on our own.

Here's how you can do it:

1. Install AWS CLI on your EC2 box (it could also be installed by default)

2. Configure the AWS CLI with credentials

3. Download s3 to your local directory using aws s3 sync command as shown below:

aws s3 sync s3://bucket/file.csv /mydirectory/file.csv

4. Use the following command to copy the files into your RDS:

psql -h your_rds.amazonaws.com -U username -d dbname -c '\COPY table FROM ''file.csv'' CSV HEADER'