Easy Postgres Backup/Restore from Heroku with PGBackups and Rake

Most of my projects lately have been deployed on Heroku. They’ve developed a really nice set of tools to get your Rails (and other) apps from a git repo out into the world. They do really smart things regarding database connections to make things easy to push live. If you follow the standard setup, you’ll be running on Postgres hosted at Heroku.

Often, we want to take data that may be out on a live app (staging or production level) and setup a development machine to have that data. For complex data models and complex data setups, this can be the only way to debug issues that may not have been covered by standard unit/integration tests. With PGBackups, a heroku add-on, and a couple small Rake tasks, this is a snap.

First, make sure you have pgbackups setup for your app:

% heroku addons:add pgbackups

With this enabled, you can immediately grab a snapshot of the database

heroku pgbackups:capture

You can also easily setup auto-weekly or auto-monthly snapshots

heroku addons:add pgbackups:auto-week
heroku addons:add pgbackups:auto-month

To grab the database, you ask for the url

heroku pgbackups:url

which you can then pull down with curl or by some other means. Then use pg_restore to jam it into your local database (if you want).

To make this even easier, I wrote a couple rake tasks which i bring along from project to project. To make it even easier.

Drop this file in your lib/tasks/ directory, update the namespace and development database name and you’re off to the races:

With this in place, you should be able to do:

rake my_app_namespace:db:fetch app=my_heroku_app_name
rake my_app_namespace:restore dbfile=the_dump_file_from_the_previous_command

The other task in there may not suit everyone’s purposes, but if you have sensitive User data, you can use that task to clean it up. Currently it just changes passwords to ‘monkey’. But you could do other things, like set all emails to or remove phone numbers or any other bits that you might not want to be real on your development system.

Now that I’ve done this a couple times, the next step to improve things is to get the script to read the local database.yml to grab the development database name automagically. An easy addition if you want to streamline things even more.

Advertisements

3 thoughts on “Easy Postgres Backup/Restore from Heroku with PGBackups and Rake

  1. Mr Rogers — an excellent post (as usual ;-). One other thing worth mentioning is heroku db:push and db:pull. These commands will push and pull your local dev db into/out of the Heroku app. Of course these commands don’t give the user backups, nor the nice data sanitization that your rake tasks do. But they are useful for quick and dirty copying of data between app instances.

    db:pull [DATABASE_URL] # pull heroku data down into your local database
    db:push [DATABASE_URL] # push local data up to your app

    FYI,
    -Jeremy

    1. True, heroku has those push/pull commands built in. I think the primary benefit of pg_backups and the rake tasks is you get a timestamp-ed snapshot. You can muck around with the app, create/delete records etc, and then easily revert to that snapshot as necessary. With the db:pull, you’d have to take one extra step (granted, it’s a tiny step) to create the snapshot locally after pulling the data.

    2. I was recently reminded of why I didn’t use the db:push/pull to build this feature. If you follow the recommended steps from Heroku, you will no longer be using the heroku gem, but you’ll be using the Heroku toolbelt which no longer includes taps. Taps was the magic db agnostic reader/writer providing that push/pull functionality. You can still install taps, but if you’re development and production instances are all using postgres, why run all your data through a middleman (taps) when no conversion is necessary.

      For reference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s