How to install PostgreSQL in AWL EC2 instance, import data and seed data

when EC2 has been created and you are inside of the instance

Access into instance: ssh -i *.pem ubuntu@YOURIPADDRESS

Update instance: sudo apt-get update

Upgrade instance: sudo apt-get -y upgrade

download PostgreSQL: sudo apt-get install postgresql postgresql-contrib
a. in case of error message:

Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgresql-contrib

b. do this

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

change your password: sudo -u postgres psql

postgres=#\password

Update configuration for remote access for clients

sudo vim /etc/postgresql/13/main/pg_hba.conf

13 is version of postgresql and it will change once it has new version.

b. Near bottom of file after local rules, add rule (allows remote access):

host all all 0.0.0.0/0 md5

c. save & exit out from config

Edit config in vim

sudo vim /etc/postgresql/13/main/postgresql.conf

b. Change line 59 to listen to external requests
- listen_address='*'
- save & exit out from config

Restart the Postgresql server

sudo /etc/init.d/postgresql restart

Now you may connect to the EC2 postgresql using the public DNS provided and port 5432.

Create database

$sudo su postgres
$psql
postgres=# CREATE DATABASE <database_name>;
postgres=# CREATE USER <user> with encrypted password '<password>';
postgres=# GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user>;

How to import data into EC2 instance

scp -i *.pem -r {foldername} ubuntu@ipaddress:~/yourfolderinUbuntu/

How to import csvs into table

productdb=# \copy products from ‘/home/ubuntu/foldername/filename.csv’ delimiter ‘,’ csv header;
/products/csvs/product.csv: No such file or directory

//if you have null as 'null' or different name you can write
NULL 'null_name' after delimiter

If you are wanting to get repo from github instead of making db, after update and upgrade:

installation:

curl -sL [https://deb.nodesource.com/setup_14.x](https://deb.nodesource.com/setup_14.x) | sudo -E bash
sudo apt-get install -y nodejs
sudo apt-get install git

port-forwarding:

sudo iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 80 -j REDIRECT --to-port 3000

git clone your repo into it

install npm modules, run build scripts, run node

Link for detail instrusction: https://www.shubhamdipt.com/blog/postgresql-on-ec2-ubuntu-in-aws/
Link for error message fix: https://stackoverflow.com/questions/53434849/cannot-install-postgres-on-ubuntu-e-unable-to-locate-package-postgresql
Link for AWS EC2 creation: https://fern-kettle-978.notion.site/Daily-Reflection-8-4-f7bb9770bf70451abfc0dceb171a4b88
Link for seeding data: https://www.enterprisedb.com/postgres-tutorials/how-import-and-export-data-using-csv-files-postgresql

Blog

How to install PostgreSQL in AWL EC2 instance, import data and seed data

Edrick Ee

Join Our Newsletter. No Spam, Only the good stuff.

Related