How to Provision a Cheap PostgreSQL Database in AWS EC2

When RDS is beyond your budget, run Postgres on EC2

Don Restarone
Nov 27, 2020 · 6 min read
a rack of servers
a rack of servers
Photo by Taylor Vick on Unsplash

The Motivation

Caveats / When You Should Opt for RDS

  1. Amazon guarantees 99.99999% up-time for regions, not availability zones. So having a standby replica in a different zone will ensure that even if the primary zone where your master database is hosted experiences an outage, your application will be able to fall back to the standby replica in a different (and presumably unaffected) zone.
  2. Your application does not need to be able to detect database failure and handle fail-over. This is handled by Amazon automatically. They will point your database host URL to the standby database if/when your primary database fails. After the crisis has been resolved, they will automatically catch up the recovered master database and promote it.

Now that we have that out of the way, let's get started. In case you’d prefer to consume this guide in video format:

Set Up the Infrastructure

screenshot: step 1. choose an Amazon machine (AMI) with the choice Ubuntu 20.04 server highlighted
screenshot: step 1. choose an Amazon machine (AMI) with the choice Ubuntu 20.04 server highlighted
Provisioning an Ubuntu server on EC2 (image by author)

The rest of the configuration isn’t that important, but at the tags section, we will create a tag for identifying this DB instance. This will be used when we set up backups later.

screenshot: step 5. add tags with the key set to type and the value set to database
screenshot: step 5. add tags with the key set to type and the value set to database
Tagging the instance on EC2 (image by author)

Next we will configure the security group to allow SSH and database access. I will open up the default Postgres port 5432 in anticipation of running the Postgres service there. For added security, I am limiting access to my public IP address. For production use, you should only allow database connections from the security group of your application server.

screenshot: Step 6. configure security group with settings to access the server via SSH and the Postgres port
screenshot: Step 6. configure security group with settings to access the server via SSH and the Postgres port
Add security groups to access the server via SSH and the Postgres port (image by author)

Once the instance has been launched, take note of the public IP address and public DNS name. We will be using the public IP address to SSH into the server for setting up Postgres. After that, we can use the DNS name to connect to the Postgres server.

screenshot showing the IP address for SSH and the DNS name for connecting to Postgres
screenshot showing the IP address for SSH and the DNS name for connecting to Postgres
We will use the IP address for SSH and the DNS name for connecting to Postgres (image by author)

This will be a good time to pause and add the newly created server to your SSH configuration so you can connect in a more ergonomic way.

after allowing read privileges on the .pem file, you can add it to the SSH configuration (image by author)

Configure Postgres

lines 1 and 2 will refresh the packages and install the latest version of Postgres (image by author)

Let's break down the commands outlined above. The first two lines will refresh the packages in Ubuntu and install the latest version of Postgres.

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

Next we will log in as the default Postgres user (imaginatively named postgres) and create the user roles we will be using. I will simply name my user ubuntu and allow it to login and create databases.

sudo su postgres
psql -U postgres -c "CREATE ROLE ubuntu;"
psql -U postgres -c "ALTER ROLE ubuntu WITH LOGIN;"
psql -U postgres -c "ALTER USER ubuntu CREATEDB;"
psql -U postgres -c "ALTER USER ubuntu WITH PASSWORD 'ubuntu';"

Log out from the postgres user account and go back to your default user by entering the following:

exit

Find the postgresql.conf file (it’s usually in /etc/postgresql/12/main/postgresql.conf). In case you are not sure, you can use a handy Bash command to find it by entering:

sudo find / -name "postgresql.conf"

Open the file with your favourite text editor (yes, mine just so happens to be Nano #sorryNotSorry). We will find the configuration entry listen_addresses and change it from the default setting to ‘*’. This will allow the Postgres server to listen on the DNS name of the EC2 instance.

screenshot showing the configuration entry set to ‘*’
screenshot showing the configuration entry set to ‘*’
If you’re a heathen like me and use Nano, you can use the keyboard shortcut CTRL+W to search (image by author)

After that, find the pg_hba.conf and open it.

sudo nano /etc/postgresql/12/main/pg_hba.conf

We will allow authentication from remote connections by adding the following lines to the end of the file. This will allow connections from any IP address. Please note that in production, it would be a good idea to restrict connections to only the ones you need (a well configured EC2 security group should be sufficient, but for peace of mind you should add the IP address of your application server instead of 0.0.0.0/0)

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

For the new configuration to take effect, we will restart the Postgres daemon by running:

sudo systemctl restart postgresql

Let’s Take It for a Test Drive

screenshot showing the choice to right-click on Servers and select Create -> Server
screenshot showing the choice to right-click on Servers and select Create -> Server
Right-click on Servers and select Create -> Server

Enter the DNS name from the EC2 console and the credentials we set up from before.

screenshot of a dialog box showing the correct DNS name and credentials to connect to the Postgres server
screenshot of a dialog box showing the correct DNS name and credentials to connect to the Postgres server
Connect to the Postgres server (image by author)

If the server is added, great! If you face a timeout error, make sure to double-check your security groups in EC2 and the Postgres configuration we covered before.

I’m going to create a test database and a users table, and insert some dummy data for testing.

screenshot showing dummy test data added
screenshot showing dummy test data added
Add some data to a table (image by author)

Configure Automatic Backups

To do this, go to the EC2 console, select Data Lifecycle Manager, and click on Create Lifecycle Policy.

screenshot showing the words Welcome to Data Lifecycle Manager and the selection Create Lifecycle Policy
screenshot showing the words Welcome to Data Lifecycle Manager and the selection Create Lifecycle Policy
Image by author

Remember that tag we associated with this EC2 server? We will tell DLM to back up targets tagged with the tag Type: Database.

screenshot of Create Lifecycle Policy screen with a dialog box for type showing the word database highlighted
screenshot of Create Lifecycle Policy screen with a dialog box for type showing the word database highlighted
Image by author

Next we will define the backup policy and schedule. I usually opt for a daily backup, and I expire backed up snapshots after a five-day retention period.

screenshot showing policy schedule choices highlighted
screenshot showing policy schedule choices highlighted

It is worth noting that this backup strategy will cause a bit of downtime due to how Amazon handles image creation. Your database server will go down for a few seconds during this backup window.

If this is downtime is unacceptable, you can use pg_dump along with a Bash script and the AWS SDK to glue together a routine that periodically dumps the database and uploads it to S3. This script can be invoked in Unix-like systems using cron. While this is beyond the scope of this article, this gist should get you started:

That’s it for now. Enjoy being a DB admin, and best of luck!

Better Programming

Advice for programmers.

Sign up for The Best of Better Programming

By Better Programming

A weekly newsletter sent every Friday with the best articles we published that week. Code tutorials, advice, career opportunities, and more! Take a look

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Thanks to Zack Shapiro

Don Restarone

Written by

Software engineer & YouTuber from Toronto with a passion for building cool stuff and helping people transition into tech. Founder of https://www.restarone.com/

Better Programming

Advice for programmers.

Don Restarone

Written by

Software engineer & YouTuber from Toronto with a passion for building cool stuff and helping people transition into tech. Founder of https://www.restarone.com/

Better Programming

Advice for programmers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store