The Heroku PGBackups add-on lets you easily capture and manage backups for your shared and dedicated Heroku PostgreSQL databases.

Installing the Add-on

Heroku Command-Line Tool

Make sure that you are running the latest version of the Heroku command-line tool:

$ heroku update

Add-on

Install the add-on:

$ heroku addons:add pgbackups
Adding pgbackups to myapp... done

Add-on Plans

PGBackups has a number of plans, including some that automatically take backups. An up-to-date list of plans is on the PGBackups Add-on page.

Creating a Backup

Capture a backup of your primary database:

$ heroku pgbackups:capture

DATABASE_URL  ----backup--->  b003

Dump... 2.6MB, done
Upload... 2.6MB, done

If you have multiple databases on your application, you can choose which one to backup by specifying at Database ID (DB_ID):

$ heroku pgbackups:capture HEROKU_POSTGRESQL_RED

HEROKU_POSTGRESQL_URL  ----backup--->  b004

Dump ... done
Upload ... done

You can automatically delete your oldest backup when capturing a new one:

 $ heroku pgbackups:capture --expire

Backup Management

You can get a summary list of all your backups:

$ heroku pgbackups
ID   | Backup Time         | Size    | Database             
-----+---------------------+---------+----------------------
b003 | 2010/10/22 15:16.01 |   2.6MB | SHARED_DATABASE_URL  
b004 | 2010/10/22 15:18.12 | 424.7MB | HEROKU_POSTGRESQL_URL

You can create a publicly accessible URL for backups, which is available for 10 minutes. This can be used for migrating or exporting your database:

$ heroku pgbackups:url b004
"http://s3.amazonaws.com/hkpgbackups/app1234567@heroku.com/b004.dump?AWSAccessKeyId=ABCD1234&Expires=1289261668&Signature=3mMBeKISewgEUDT%2FL5mRz4EYS4M%3D"

You can delete a backup. Depending on your plan you will need to delete obsolete backups manually:

$ heroku pgbackups:destroy b003
Backup b003 will be permanently deleted Are you sure (y/N)? y
Backup b003 deleted.

Restoring from a Backup

You can restore a backup into a database. This is a destructive operation: the restore operation will drop existing data and replace it with the contents of the backup. The contents of the database prior to a restore will not be recoverable. To restore backup b007 into the database at DATABASE_URL:

$ heroku pgbackups:restore DATABASE b007

DATABASE_URL  <---restore---  b007
                              DATABASE_URL
                              2010/10/25 21:41.10
                              1.7MB

Download... 1.7MB, done
Restore... 1.7MB, done

Importing From a Backup

If you have an existing PostgreSQL database you’d like to export from elsewhere, dump it in compressed format using the open source pg_dump tool:

$ PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h myhost -U myuser mydb > mydb.dump

Upload it somewhere with an HTTP accessible URL. We recommend using Amazon S3 and S3Fox. Create the file with private access and create a temporary authorized URL for the Heroku import. Then cut and paste that URL into your restore command:

$ heroku pgbackups:restore DATABASE 'http://s3.amazonaws.com/.....mydb.dump?authparameters'

Exporting Via a Backup

If you’d like to move your database out of the Heroku PostgreSQL service, take a backup and then download it using a variety of tools such as curl, wget, or a web browser. For example:

$ curl -o latest.dump `heroku pgbackups:url`

Note that the backup URLs will expire 10 minutes after they are issued.

You can then load this dump into your local database using the pg_restore tool, just as Heroku does when you initiate a restore:

$ pg_restore --verbose --clean --no-acl --no-owner -h myhost -U myuser -d mydb latest.dump

Transfers

You can use pgbackups to transfer data from one app to another, say from your production app to your staging app. To do this, capture a backup on the primary app, then import that backup into the secondary app:

$ heroku pgbackups:capture --app myapp
$ heroku pgbackups:restore DATABASE `heroku pgbackups:url --app myapp` --app myapp-staging

Note that the restore is going into the DATABASE_URL of myapp-staging, but using the url of the latest backup from myapp.