PostgreSQL on AWS EC2 Instance

When you are developing applications, at the initial stage AWS RDS instance might be expensive. So you may be thinking about running database instance on EC2 instance and later moving onto RDS.

However, AWS RDS comes with many advantages, Important thing is less maintainability. It provides scalability, performance, backup, and point-in-time snapshot and many more.

 In this article, I will be covering how to set up a PostgreSQL instance on EC2 and that can be used similar to RDS.

1). Login into AWS console

2). Go to EC2 dashboard and create security groups with following Inbound rules.

• Custom TCP: 5432, Source: Anywhere (or specific IP for more security)
• SSH TCP:22, Source: Anywhere

3). Provision a new EC2 instance (Type: Ubuntu and depend on load you can start with small family e.g t2.micro) including the above security groups.

4). Once EC2 instance Up and Run, SSH to the instance and execute the following command.

sudo apt-get update && sudo apt-get -y upgrade
sudo apt-get install postgresql postgresql-contrib​

5). You can check PostgreSQL service is status by running following command

service postgresql status

6). We need to change the password of the user postgres (default user), use the following command.

sudo -u postgres psql
postgres=#\password​

7). Now update the configuration for remote access for clients.

#=> Edit pg_hba.conf in vim
sudo nano /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​

8). Restart the PostgreSQL server.

service postgresql restart

9). Now you can connect to the EC2 PostgreSQL using the public DNS provided on port 5432.

10). Use the following command to create User, Database, and grant permission.

$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>;​

Note: If you want to connect the Custom build EC2 PostgreSQL server from your dev machine. You need to have Public IP or Elastic IP assigned to the EC2 instance.

Happy Coding!