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:

shell # 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:

shell # 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 improvement). 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).

shell 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. shell # 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'