Table of Contents
The PG Backups add-on lets you easily capture and manage backups for your Heroku Postgres databases.
Though all Heroku Postgres databases are managed and backed up on your behalf, it is still wise to capture regular backups yourself. This will protect you against errant operations and other mistakes that otherwise propagate to Heroku Postgres’ real-time backup system.
Provisioning the add-on
PG Backups can be attached to a Heroku application via the CLI:
A list of all plans available can be found here.
$ heroku addons:add pgbackups -----> Adding pgbackups to my app... done, v18 (free)
The default plan only supports manual backups, whereas the plans that start with
auto will periodically take backups for you automatically (on a schedule defined by the plan).
Creating a backup
Backups are compressed. The size of a backup will be less than the size of your database.
By default, PG Backups operates against your primary database (located at the
DATABASE_URL config var location).
$ heroku pgbackups:capture HEROKU_POSTGRESQL_BLACK (DATABASE_URL) ----backup---> b251 Capturing... done Storing... done
If you have multiple databases on your application, you can choose which one to backup by specifying the database name:
$ heroku pgbackups:capture HEROKU_POSTGRESQL_PINK HEROKU_POSTGRESQL_PINK ----backup---> b252 Capturing... done Storing... done
Automatically scheduled backups always run against
Note that capturing a backup does add some load on your database for the duration of the backup. How this impacts your application will vary with the size of your database and the nature of the app. Consider taking backups on a follower if there is a significant impact from running them on the master.
Plans are limited in the number of manual backups that can be retained at any point in time. To capture a new backup while automatically deleting the oldest manual backup use the
$ heroku pgbackups:capture --expire
To view a list of capture backups use the
heroku pgbackups command.
$ heroku pgbackups ID | Backup Time | Size | Database -----+---------------------+-------+------------------------ a226 | 2012/02/22 20:02.19 | 5.3KB | DATABASE_URL a227 | 2012/02/23 20:02.19 | 5.3KB | DATABASE_URL b251 | 2012/02/24 16:08.02 | 5.3KB | HEROKU_POSTGRESQL_BLACK b252 | 2012/02/24 16:08.53 | 5.3KB | HEROKU_POSTGRESQL_PINK
Manually taken backups’ ids start with a ‘b’, while automatically taken backups (if your plan automatically captures backups) start with an ‘a’.
The backup ID is notable as it's used when downloading, deleting or restoring a backup.
Downloading a backup
While public, the URL is not guessable and expires after a short duration in order to keep your data secure.
$ heroku pgbackups:url "http://email@example.com/b004.dump?AWSAccessKeyId=ABCD1234&Expires=1289261668&Signature=3mMBeKISewgEUDT%2FL5mRz4EYS4M%3D"
By default the URL will reference the most recent backup. To access another backup pass the backup ID to the
$ heroku pgbackups:url b004
You can manually delete a backup to remove obsolete backups or make room for new captures. Use the backup ID to specify which backup to remove.
$ heroku pgbackups:destroy b003 Backup b003 will be permanently deleted Are you sure (y/N)? y Backup b003 deleted.
Restoring from backup
Tables not included in the backup will generally be unaffected; if you would like to start from scratch on an already-populated database, we recommend running
Restoring a backup is a destructive operation: the restore operation will drop existing data and replace it with the contents of the backup.
pgbackups:restore command accepts the database to restore to backup to and the backup ID to restore.
The contents of the database prior to a restore will not be recoverable.
$ heroku pgbackups:restore HEROKU_POSTGRESQL_BLACK b251 HEROKU_POSTGRESQL_BLACK (DATABASE_URL) <---restore--- b251 HEROKU_POSTGRESQL_BLACK 2012/02/24 16:08.02 5.3KB
PG Backups is also useful when exporting your remote Heroku Postgres data to another database outside of Heroku or importing a
pg_dump from another source into your Heroku Postgres database.
Read about importing and exporting with external sources in the Importing and Exporting Heroku Postgres Databases article.
You can identify connections made by PG Backups to your database by running the following command from your terminal.
$ echo "select * from pg_stat_activity where application_name = 'pgbackups'" | heroku pg:psql
This is useful if you need to see if a backup/restore is happening, blocking a migration, etc.
Migrating between plans
heroku addons:upgrade command to migrate to a new plan.
$ heroku addons:upgrade pgbackups:auto-month -----> Upgrading pgbackups:auto-month to myapp... done, v18 (free) Your plan has been updated to: pgbackups:auto-month
Removing the add-on
PG Backups can be removed via the CLI.
This will destroy all associated data and cannot be undone!
$ heroku addons:remove pgbackups -----> Removing pgbackups from myapp... done, v20 (free)
Before removing PG Backups, consider downloading and storing your most recent backup elsewhere in case you need to recover the data.
All PG Backups support and runtime issues should be submitted via on of the Heroku Support channels.