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
Top comments (0)