Deploying a PostgreSQL Server on an AWS EC2

Amazon EC2 is a cost-effective and versatile compute service that, if configured correctly, can fall under the AWS Free Tier. This makes it ideal for projects that do not require significant computational resources.

AWS offers RDS for relational database services, but this service can be expensive and may be overkill for simple applications. Hosting your own PostgreSQL server on an EC2 instance provides a more affordable alternative.

This guide will show you how to set up a PostgreSQL server on an Ubuntu EC2 instance and connect to it using pgAdmin 4.

For this example, I am using a t2.micro Ubuntu instance for no particular reason other than it is very cheap. Note that if your deploying this in a production environment, provision the necessary resources by choosing an appropriate instance.

Setup a key pair if you do not already have one to connect to the instance.

For the network settings, mark both HTTPS and HTTP traffic, as we will need this to connect to our database. (Note that HTTP may not be necessary but I will allow it here.) Create a new security group and configure storage as required; I will only use 10GiB of gp3 storage.

Instance summary:

Before connecting to the instance, we first have to edit some security rules in order to allow us to connect to the database from pgAdmin. Go to the newly created security group’s inbound rules and add a rule as follows:

Next, make sure that the EC2 is running without any errors. I will connect using the browser-based EC2 instance connect, but feel free to use a SSH client as well.

After connecting successfully, we have to first install the necessary packages.

sudo apt update

It is always a good idea to make sure everything is up-to-date.

sudo apt install postgresql postgresql-contrib
sudo apt update
sudo service postgresql status
sudo service postgresql restart

The commands above will install PostgreSQL and start the service. Next, connect to the PostgreSQL server and verify that it works.

sudo -u postgres psql
\conninfo

You should see some output like this:

Next, let's change the password for the user Postgres.

sudo -u postgres psql
\password postgres

You will get to set your database password here.

Now change the Postgres config file to allow connections from any address.

sudo vim /etc/postgresql/14/main/postgresql.conf

In the connections and authentication section, uncomment listen_adresses and change it as follows:

listen_addresses = "*"

This will allow us to connect to this server from any IP address. Save and exit vim after making the necessary changes. Navigating VIM might be troublesome if you’re a beginner, so I’ll link some commands here.

Next, we have to edit the pg_hba.conf

sudo vim /etc/postgresql/14/main/pg_hba.conf

After navigating to the end of the file, add the following 2 lines.

host all all 0.0.0.0/0 md5
host all all ::0/0 md5

These 2 lines allow connections from any IPv4 and IPv6 addresses, respectively. Save and exit as before.

To verify that the server is listening for connections from any IP address, run the commands below.

sudo systemctl restart postgresql
ss -nlt | grep 5432

The output should be as follows:

Nice! Now our Postgres server is set up properly.

Next, let’s connect to this using pgAdmin.

Add a name of your choosing for the name here, and I will name it "AWS”.

For the hostname, add the public IPv4 address of the EC2. Be sure that it’s the public address you select, as it will not work if you select the private one.

Any of the 2 shown above will work.

Next, leave port, username, and maintenance database unchanged. Add the password you created earlier and click save.

Thats it! Now you should be able to query the Postgres server running on the EC2 using pgAdmin 4.