Automatic Off-site Backups for your Heroku Postgres Databases

server-room-on-fire

One of the great things about platforms like Heroku is that they generally follow the pattern of immutable infrastructure: on every call to git push heroku master your app contains everything necessary for it to be built and deployed, and if deploying from scratch then everything required will indeed be fetched and all relevant server processes set up, such that your app is deployed and ready in production. Compare that to the case where you have a production server that was hand-configured by someone who left the company a year ago and is now maintained by multiple people, none of whom have a complete picture of what every random cron entry or maintenance script does.

Back to Heroku, where this breaks down perhaps is when add ons such as the Heroku Scheduler are brought in to the mix–it would be nicer perhaps if scheduled tasks could also be specified in the code base and set up on deploy.

The key element missing from this setup however is the database: while the app itself may come close to following immutable deployment principles, the database does not. If everything works as it should, then in theory we have considerable safety afforded to us by transactions in Postgres, the ability to fork databases, rollback migrations, maintain follower databases, the fact that Heroku use redundant WAL logging etc. –but what if all of that failed in an instant, either due to a critical software bug or some unforeseen disaster in Amazon’s data centres?

While enjoying the safety and security that the Heroku platform provides for us, it is also vital that we add at least three more layers of protection to our setup:

  1. Archiving the Postgres backups to S3/Glacier.
  2. Archiving the same backup files to a secure off-site location within our own infrastructure, or at least outside of Amazon’s.
  3. A regularly run, ideally automatic process that will fetch the latest backup, restore it to a fresh database, ensuring that the restore runs without error.

There really is no limit to the number of layers you might want to add to a backup strategy, but I would say that this is a good start for a production application.

In this post I’m going to document one way to implement these additional layers, starting with archival to S3/Glacier, with the remaining layers coming in future posts.

Archiving your Postgres backups to S3/Glacier

A quick search online will bring you to the promising-looking pgbackups-archive project. Set up a few environment variables and you’re done. Unfortunately, due to Heroku deprecating the heroku gem in favour of the Heroku Toolbelt, this project is probably not what you want to use any more, which is a shame as it will have received the most testing out of the options available to us.

We will instead use the newer heroku-database-backups project, which is a standalone Heroku app that we will configure to capture and archive our production database(s). As it is based on the recommended tools of each platform (the Heroku Toolbelt and the Universal Command Line Interface for AWS) then we can perhaps reasonably expect that this approach will not break any time soon, though we would be foolish to rely on that.

Setup

I will assume that you are already using Heroku, and have the Heroku Toolbelt installed on your machine.

S3 Setup

On S3 we will want to create a bucket for the database archives, and an IAM user which will only be used for pushing backups to the bucket. This user will have access to only the one bucket, and will have write access only.

In your S3 console create a new IAM user and copy the access key and secret key to a safe location.

Create a bucket to store the backups in, for the sake of example we will call ours heroku-pgbackups-archive.

Apply a security policy to this user based on the following:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1210229835000",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::heroku-pgbackups-archive/*"
      ]
    }
  ]
}

You now have an IAM user who can use that bucket as a drop box but cannot access or delete the contents.

Heroku Setup

Choose a name for the app; this app will be archiving perhaps several of your apps and so you might want the name to reflect that.

Create the app on Heroku:

$ heroku create database-backup-app-name --buildpack https://github.com/ddollar/heroku-buildpack-multi

Clone the heroku-database-backups project:

$ git clone git@github.com:kbaum/heroku-database-backups.git database-backup-app-name

Push the app to Heroku:

$ cd database-backup-app-name
$ git remote add heroku git@heroku.com:database-backup-app-name.git
$ git push heroku master

Set up the app’s tool belt access:

$ heroku config:add HEROKU_TOOLBELT_API_EMAIL=yourherokulogin@example.com
$ heroku config:add HEROKU_TOOLBELT_API_PASSWORD=`heroku auth:token`

Set up the app’s S3 access credentials with the details of the IAM user and bucket that we created earlier:

$ heroku config:add AWS_ACCESS_KEY_ID="ASDLKJS..."
$ heroku config:add AWS_SECRET_ACCESS_KEY="+sdfOIUSDF..."
$ heroku config:add AWS_DEFAULT_REGION="eu-west-1" # See http://docs.aws.amazon.com/general/latest/gr/rande.html
$ heroku config:add S3_BUCKET_PATH="heroku-pgbackups-archive"

Before we go any further, examine the contents of bin/backup.sh to confirm that there is nothing in there that you wouldn’t want to run against your production environment. At time of writing, as it only contains a call to pgbackups:capture, the worst thing that could happen should just be a performance hit on your database as the backup is being captured. You may wish to conduct any testing either on a follower database, during off-peak times, or even better on a test app.

After checking the script that we are about to run for safety, and with the general disclaimer that you alone are responsible for your database and data, we can run the script:

$ heroku run APP=my-test-app DATABASE=HEROKU_POSTGRESQL_YELLOW_URL /app/bin/backup.sh

In the console output we will see the AWS CLI being downloaded and installed, our database being captured, and then the compressed capture being sent to S3.

If all is well, you may now wish to try this with your production database.

Running this Every Day

We can use Heroku’s Scheduler add-on to facilitate running this command every day:

$ heroku addons:add scheduler
$ heroku addons:open scheduler

In the web interface add a daily scheduled command to run such as:

APP=my-production-app DATABASE=HEROKU_POSTGRESQL_GOLD_URL /app/bin/backup.sh

Note that this is simply the same command as before but without heroku run and pointing at a different app.

Wrapping Up

At this point we should have an automatic process in place which will, once a day, take a backup of our production database and push an archive of it to an S3 bucket.

This post doesn’t contain everything you need to have a complete process in place for database backups, but it has perhaps given you a starting point and at the very least your database archives should be stored now on S3.

Some things that you may wish to now investigate or think about, which I may cover in a future post:

  • Downloading the latest archive produced by pgbackups once a day to other locations outside of Amazon’s infrastructure, e.g. set up a VPS which is itself backed up (I recommend Linode), and/or download the archives to a computer which is secured with full-disk encryption in your office.
  • If this automatic backup process fails, no one will be notified; set up a process by which to verify that the archives are arriving in the S3 bucket.
  • Even if we verify that the files exist for each day, we have not verified that they are valid backups. Once a day, download the latest backup and restore it, watching for warnings/errors and perform data integrity checks.
  • If your database is large or you have many schemas you may wish to set up a follower database, and capture the backups from it by changing the URL to its URL, to avoid additional load on the master database.
  • What if Heroku goes down completely, perhaps due to an extended AWS outage? Consider keeping in place a process by which you can spin up your app on a VPS, with the latest version of the production database loaded, to provide your customers with access to their data in the case of such an outage.
  • To save money, we could use S3′s lifecycle feature to automatically send backup files older than 30 days to Glacier.

Curious about Immutable Infrastructure?

Chad Fowler wrote an interesting piece on this, Trash Your Servers and Burn Your Code: Immutable Infrastructure and Disposable Components.

2 Responses to “Automatic Off-site Backups for your Heroku Postgres Databases”

  1. michael

    How can I have this kind of job fired on an event?

    • Stefan Haflidason

      On Heroku, rake tasks run in their own process a bit like a dyno via `heroku run` so I’m not sure how you would achieve that directly.

      Two things you could perhaps do though:

      1. Build a small system that waits for the event in question and then remotely invokes `heroku run` against the target app.

      2. Set the scheduler to run the backup task every minute, but each time check for a certain event having been fired. If the event has fired, run the backup and then reset the state so that it will wait for the next event. You would want to be _very_ careful not to end up kicking off hundreds of backup processes against your database though!