Heroku Postgres Rollback

Last updated 06 October 2015

Heroku Postgres rollback allows you to “roll back” the state of your database to a previous point in time, similar to how heroku releases:rollback allows you to roll back to an older deployment of your application.

Rollback does not affect your primary database, but instead follows the same pattern as fork: it provisions a new database that is not directly connected to the primary in any way. Like a fork, a rollback will take some time to become available.

The rollback period available varies by database plan.

Use cases

Rollback is a great safety net in case of a critical data loss issue such as, accidentally dropping an important table. It can also be invaluable for forensics or even one-off analytics runs when some important data had not been captured.

Creating a rollback database

Before you roll back, you must ensure the desired rollback point is available for your database. Different database plans have different rollback availability. To check rollback availability for your current database, use the heroku pg:info command:

$ heroku pg:info --app sushi
=== HEROKU_POSTGRESQL_ROSE_URL (DATABASE_URL)
Plan:        Standard 0
Status:      Available
Data Size:   584.6 MB
Tables:      29
PG Version:  9.2.4
Connections: 8
Fork/Follow: Available
Rollback:    from 2013-10-18 20:00 UTC
Created:     2013-04-18 20:14 UTC
Maintenance: not required

Creating a rollback database uses the same mechanism as creating a follower: provisioning occurs on creation of a new database add-on with the --rollback flag. The --rollback flag can take the config var name of the database on the same app, an argument of the form appname::HEROKU_POSTGRESQL_COLOR, or the full URL of any Heroku Postgres database.

In addition, you must specify the time to roll back to. There are two ways to indicate the desired time: either an explicit timestamp, or a relative time interval. An explicit timestamp should be of the format 2013-10-22 12:34+00:00, including the time zone offset. You may also use a symbolic time zone. For example, 2013-10-22 12:34 US/Pacific. An interval should be of the format 3 days 7 hours 22 minutes. A recovery time must be passed with the --to flag, and a recovery interval with --by. At least one must be present, but not both. Rollback is not accurate down to the second. Seconds specified in the recovery time or interval are ignored.

A full rollback command looks like this:

$ heroku addons:create heroku-postgresql:standard-0 --rollback HEROKU_POSTGRESQL_YELLOW --to '2013-10-21 15:52:52+00' --app sushi

The target recovery time (and how long ago this is) will be echoed in the output of the provisioning command.

Preparing a rollback can take anywhere from several minutes to several hours, depending on the size of your dataset. The heroku pg:wait command shows the provisioning status of any new databases and can be used to determine when the rollback is up-to-date:

$ heroku pg:wait --app sushi
Waiting for database HEROKU_POSTGRESQL_YELLOW_URL... available

Deprovisioning

When you are done with the rollback, deprovision it using heroku addons:destroy:

$ heroku addons:destroy HEROKU_POSTGRESQL_YELLOW --app sushi
    !    WARNING: Destructive Action
    !    This command will affect the app: sushi
    !    To proceed, type "sushi" or re-run this command with --confirm sushi