How to install PostgreSQL in AWL EC2 instance, import data and seed data
Published: 2021-08-17 17:58:34

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: 

```javascript
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package postgresql-contrib​
```
  b. do this
```javascript
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
```javascript
postgres=#\password​
```

Update configuration for remote access for clients
```javascript
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):

```javascript
host all all 0.0.0.0/0 md5
```
  c. save & exit out from config

Edit config in vim
```javascript
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
```javascript
sudo /etc/init.d/postgresql restart​
```

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

Create database
```javascript
$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
```javascript
scp -i *.pem -r {foldername} ubuntu@ipaddress:~/yourfolderinUbuntu/
```

How to import csvs into table
```javascript
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:
```javascript
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:
```javascript
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