Today I needed to make a backup of my PostgreSQL database on Heroku and restore it locally in order to do some testing. It turns out this is pretty easy.

The Heroku documentation is a good starting point for creating a backup. You might want to start off by creating a new backup (although this step is optional). Then you need to find the backup you want and get the URL for that specific backup.

heroku pgbackups:capture -- Optional step
heroku pgbackups

You should see a list of all your backups here, select the one you want and specify the Id of that backup (in my case it was ‘a022′).

heroku pgbackups:url a022

This will give you a temporary URL where you can download the dump file. Once you have downloaded the dump you can restore it with the following command (obviously fill in your actual user name, database name and the path to your dump file):

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U your_user -d your_db_name your_dump.dump

Happy coding.

Share on: Twitter, Facebook, Google+ or use the Permalink