Skip to content
PLAY VIDEO PLAY VIDEO PLAY VIDEO
By Raphael Campardou

Heroku app, backed by an AWS RDS Postgres database

Amazon recently introduced Postgres engine for AWS RDS.

Finally.

If you are running your app on Heroku like many Rails developers, you might be interested in using Heroku as your app and web server, and using AWS RDS as your database server with a Postgres engine.

Here is a quick run-down on how to achieve this.

Prerequisites

We will assume you already have an application, hosted on Heroku, and an AWS account.

Depending on your application, migrating data is at least a bit tricky. Many things have to be considered, such as the acceptable downtime, the size of the data set, the activity of the app(s) on it…

In this blog post we'll take the easy migration road with a scheduled downtime and manageable data set, and focus on the operational aspects of using AWS RDS.

Still, there are a few precautionary actions to take before migrating:

  • Stop the app accessing the DB
  • Disable the backups
  • Use a single AZ

And after migrating,

  • Turn on backups
  • Turn on multi-AZ
  • Turn on your app :)

And as always, be safe:

  • Backup your data,
  • Verify the integrity of your backups,
  • Test the procedure on a sample data set.

Setting Up

First, we have to create a security group:

Security groups are an important aspect of AWS instances (EC2 and RDS). Basically, it's a set of rules you define to allow access to an instance. It is a lot like defining your firewall. Even better, you can use several options, adding up and allowing a very granular setting. You have many variables to play with, classic firewall variables (IP protocol & type, ports), and more AWS-specific like the source network. This is efficient but it can become complex.

In your AWS console, go to the EC2 dashboard, in the Security Groups tab. Click Create Security Group

  • Name it whatever you want, maybe "RDS-DB", add a description and leave the VPC as the default
  • Select the new group in the list, and in the inbound tab, add a rule
    • Custom TCP rule
    • Port range: 5432,
    • Source 0.0.0.0/0
  • Don't forget to Apply Rule Changes

This will allow any source IP to access your DB instance on port 5432.

Now, go to the RDS dashboard and Launch DB Instance.

  • Select the PostgreSQL engine,
  • If your dataset is large, disable the multi-AZ for now. If you have no data to transfer, you can enable it.
  • On the details page, choose your instance class, set the storage size, choose an identifier, and set the master username and password to whatever suits you.
  • In the config panel, set the db name and choose the security group you just created.
  • As with the AZ setting, disable the backups for now if your data set is large.
  • Review your settings and Launch DB

Migrating The Data Set

Now that you have both the Heroku DB and the RDS one, we're gonna transfer the data.

First by exporting the Heroku:

# get the name of the heroku DB
heroku config
# => something like HEROKU_POSTGRESQL_COLOR

# add the pgbackups addon
heroku addons:add pgbackups

# Export the DB to your heroku
heroku pgbackups:capture <NAME OF THE HEROKU DB>

# retrieve the dump
curl -o latest.dump `heroku pgbackups:url`

# Uncompress it to raw SQL
# the -n public option gets rid of commands we wouldn't have access to on RDS
pg_restore -n public -O latest.dump > latest.sql

And then by importing into RDS:

# Restore the data on the RDS instance
# Replace the < > with your values
psql -f latest.sql --host=<endpoint.of.your.instance> --port=5432 --username=<rdsusername> --password --dbname=<rdsdbname>

Securing connections

Since Heroku won't give you your machine's IP (or range) you can't restrict the source in the security group. This makes the encryption of communications between your servers all the more important. (Well, you could restrict access to the Heroku VPC (098166147350), but it wouldn't be enough of an improvment). Let's see how.

  • Download the cert file from Amazon (look for the link in the SSL chapter): AWS pgSQL guide
  • Copy it in the config/ca dir of your app
  • Commit and push to Heroku
  • Run heroku config:add EXTERNAL_DATABASE_CA='rds-ssl-ca-cert.pem' (assuming you didn't change the name of the cert)

Note: if you still value the security group setting, you can use the Proximo add-on that will funnel traffic through a static IP.

A word of caution: Amazon confirmed that, as of now, it is not possible to force SSL encryption from the DB side. Should the authentication fail with ssl, it will fall back on non-encrypted connection, and since you cannot SSH in either your Heroku instance or your RDS instance, you have no way of verifying (via a tcpdump for instance) that the connection is actually encrypted. Discussing with Amazon, they are aware (of course) of the need and urgency, but didn't risk to share a timeframe.

So let's play with it for now, but let's be careful with our sensitive data.

Making the Switch

To switch, simply change the DATABASE_URL setting on Heroku. This will regenerate the database.yml and reload the app with the new connection.

The parameters at the end must reflect your settings too, especially the name of the cert (you don't have to specify the path, just the name).

heroku config:add DATABASE_URL='postgres://myuser:mypassword@my-instance.bdfsg3dsfg.ap-southeast-2.rds.amazonaws.com:5432/my_db_production?sslca=config/ca/rds-ssl-ca-cert.pem&sslmode=require&encrypt=true'

That's it, your app should now run on an AWS RDS PostgreSQL database.

There is also a heroku add-on to help in making the switch, but it only work for MySQL connections, and honestly, I don't see the added value anyway. You can find additional info at the Heroku Dev Center. The article still has a few interesting details.

Reverting

Should you have second thoughts, you can revert the switch by reapplying the heroku DB url to the DATABASE_URL config.

# get the value of the heroku DB
heroku config

# set the congif with it
heroku config:add DATABASE_URL='postgres://xzwddzjkcmnqbfxy:ULIOnrdYegbzqD56uYiQw1M1@ec2-23-21-101-125.compute-1.amazonaws.com:5432/d10mh4kql8fq6ij'

Latest Articles by Our Team

Our expert team of designers and developers love what the do and enjoy sharing their knowledge with the world.

We Hire Only the Best

reinteractive is Australia’s largest dedicated Ruby on Rails development company. We don’t cut corners and we know what we are doing.

We are an organisation made up of amazing individuals and we take pride in our team. We are 100% remote work enabling us to choose the best talent no matter which part of the country they live in. reinteractive is dedicated to making it a great place for any developer to work.

Free Community Workshops

We created the Ruby on Rails InstallFest and Ruby on Rails Development Hub to help introduce new people to software development and to help existing developers hone their skills. These workshops provide invaluable mentorship to train developers, addressing key skills shortages in the industry. Software development is a great career choice for all ages and these events help you get started and skilled up.

  • Webinars

    Webinars

    Webinars are our online portal for tips, tricks and lessons learned in everything we do. Make the most of this free resource to help you become a better developer.

    Learn more about webinars

  • Installfest

    Installfest

    The Ruby on Rails Installfest includes a full setup of your development environment and step-by-step instructions on how to build your first app hosted on Heroku. Over 1,800 attendees to date and counting.

    Learn more about Installfest

  • Development Hub

    Development Hub

    The Ruby on Rails Development Hub is a monthly event where you will get the chance to spend time with our team and others in the community to improve and hone your Ruby on Rails skills.

    Learn more about Development Hub

Get the “reinteractive Review” Monthly Email