Install Postgresql on Ubuntu EC2 Server instead of using RDS

The goal of today’s post is to go through the following agenda.

1. Setup EC2 Server and install Postgresql Server
2. Configure EC2 ports to make Postgres accessible via the internet.

The biggest challenge is to not only just install the PostgreSQL instance, but also make the Postgres server available through the internet, or simply put make it available publicly. The steps below will help you to get this up and running in 10mins tops.

First, go to the AWS console and spin up an EC2 Instance as follows.

Click Launch Instance as shown below.

Select Ubuntu AMI to get started. For the purpose of this training, I am using the free tier, no bells and whistles.

During the setup, when you get to the Security Group settings, simply add the PostgreSQL port 5432 to be accessible from anywhere as shown below. Note, this setting can also be added after the instance is created.

You can connect to the instance through SSH and do the following steps next:

sudo apt update

Once the libraries of repositories is updated, you can move on to install PostgreSQL on the ec2 instance. While writing this post, we are currently at version 12 of the PostgreSQL server. You can always visit the link to get information about the latest version.

Linux downloads (Ubuntu)

To install PostgreSQL on EC2 instance do the following:

sudo apt-get -y install postgresql

Once the installation is complete, the next step is to create a password for the default user “Postgres”

ubuntu@ip-172-31-19-221:~$ sudo su postgres
postgres@ip-172-31-19-211:/home/ubuntu$ psql
postgres=# ALTER USER postgres password 'mypassword';
# To quit psql and exit postgres user:
postgres=# \q
postgres@ip-172-31-19-211:/home/ubuntu$ exit

Please use a strong password instead of “mypassword”. Once the new password is set. You will receive a confirmation as a response “ALTER ROLE” on the terminal.

At this point, you have installed and configured an instance on an EC2 Server, next, we just need to configure few more permissions to ensure that the PostgreSQL instance is accessible publicly through any IP address.

Next, navigate to the following folder

ubuntu@ip-172-31-19-211:~$ cd /etc/postgresql/10/main/
ubuntu@ip-172-31-19-211:/etc/postgresql/10/main$ ls

You will see two files as follows:

• pg_hba.conf
• postgresql.conf

Using vim or any editor please add the following line in the pg_hba.conf file. Please note, the following line should be the first line in the file.

host       all        all              0.0.0.0/0          md5

Once you add the, next open the PostgreSQL.conf file and find the “listen_addresses=’localhost’”. Enable this line as shown below and replace the localhost with “*”.

Lastly, restart the server using the following:

sudo service postgresql restart

This concludes our initial setup of the PostgreSQL server on EC2 instance. You can now use the public IP address of the instance to connect directly to PostgreSQL server that we just installed.

Next, I will soon write about, how to do the following for self managed PostgreSQL server:

1. Creating regular data backups and recovery process
2. How to assign static IP to the server and attach a domain or a sub-domain name to the server.

Please feel free to reach me with any questions in the comments section below, or simply email me at rupeshdesai82@gmail.com

Here is a full YouTube video of the EC2/PostgreSQL setup: