AWS RDS might be expensive at initial stage however EC2 is much more relatively cheaper. At the same time, RDS comes with several advantages and less maintenance. In this blog, we will cover how to set up a PostgreSQL instance in EC2 such that it can be used similar to RDS.
The steps are as follows:
- Log in to your AWS account.
- Go to EC2 dashboard and create security group with following inbound rules:
- Custom TCP: 5432, Source: Anywhere (or specific IP for more security)
- SSH TCP:22, Source: Anywhere
- Initiate an EC2 instance (Ubuntu, for.eg. t2.medium) and include the created security group.
- Once created, SSH into the instance (take help of the Connect button in the EC2 dashboard).
- Run the following commands:
sudo apt-get update && sudo apt-get -y upgrade
sudo apt-get install postgresql postgresql-contrib
- Now PostgreSQL has been initiated, however it wil only listen to localhost, so we need to edit the configuration of the PostgreSQL.
- But first we need to change the password of the user postgres (default user)
sudo -u postgres psql
postgres=#\password
- Now update configuration for remote access for clients.
# Edit pg_hba.conf in vim
sudo vim /etc/postgresql/10/main/pg_hba.conf
# Near bottom of file after local rules, add rule (allows remote access):
host all all 0.0.0.0/0 md5
# save file
# Edit config in vim
sudo vim /etc/postgresql/10/main/postgresql.conf
# Change line 59 to listen to external requests:
listen_address='*'
# save file
- The last step is to restart the PostgreSQL server.
# Restart postgres 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>;
Have fun :)