PG Backups

Last Updated: 21 March 2014

backups database pg backups postgres

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 DATABASE_URL.

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 --expire flag:

$ heroku pgbackups:capture --expire

List backups

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.

You can create a publicly accessible backup URL with the pgbackups:url command. This is useful when migrating data between applications, upgrading starter tier plans, and exporting your data.

$ heroku pgbackups:url
"http://s3.amazonaws.com/xkpgbackups/app1234567@heroku.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 pgbackups:url command.

$ heroku pgbackups:url b004

Deleting backups

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 pg:reset first.

Restoring a backup is a destructive operation: the restore operation will drop existing data and replace it with the contents of the backup.

The 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

Import/export

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.

Visibility

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

Use the 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.

Support

All PG Backups support and runtime issues should be submitted via on of the Heroku Support channels.