Heroku Postgres Rollback
Last updated February 01, 2023
Table of Contents
Heroku Postgres rollback “rolls back” the state of your database to a previous point.
Rollback doesn’t affect your primary database. It follows the same pattern as fork: rollback provisions a new database that isn’t directly connected to the primary in any way. Like a fork, a rollback takes some time to become available. When the rollback is available, it can be promoted to primary. When the rollback is primary, you can remove the previous add-on.
The rollback period varies by database plan.
If database credentials have been reset on the primary, rollback continues to support going back to a point before credential reset. After the fork is created during the rollback process, a new set of credentials is created.
Creating a Rollback Database
Before you roll back, 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_YELLOW_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
There’s a delay before rollback is available on a new fork after forking and on followers after unfollowing.
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:
Explicit Timestamp - You can specify an explicit time stamp. Use the format
2013-10-22 12:34+00:00. You must include the time zone offset. You can also use a symbolic time zone:
2013-10-22 12:34 US/Pacific.
Interval - You can specify an interval. Use the format
3 days 7 hours 22 minutes. A recovery time must be passed with the
--toflag, and a recovery interval with
--by. At least one must be present, but not both. Rollback isn’t accurate down to the second. Seconds specified in the recovery time or interval are ignored.
The following example shows a full rollback:
$ heroku addons:create heroku-postgresql:standard-0 --rollback HEROKU_POSTGRESQL_YELLOW --to '2013-10-21 15:52+00' --app sushi
The command echoes the target recovery time (and elapsed time) to the command line.
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_SILVER_URL... available
When you’re done with the rollback, deprovision it using
$ 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
Common Use Case: Recovery After a Critical Data Loss
Rollback is a great safety net for critical data loss issues like accidentally dropping an important table. To recover the lost data through rollback:
Create a rollback:
$ heroku addons:create heroku-postgresql:standard-0 --rollback HEROKU_POSTGRESQL_YELLOW --to '2013-10-21 15:52+00' --app sushi Creating heroku-postgresql:standard-0 on ⬢ sushi... $50/month Database will become available after it completes rolling back to 2013-10-21 15:52+0000. Use `heroku pg:wait` to track status. postgresql-adjacent-88888 is being created in the background. The app will restart when complete... Use heroku addons:info postgresql-adjacent-88888 to check creation progress Use heroku addons:docs heroku-postgresql to view documentation
Confirm that the rollback is up to date:
$ heroku pg:wait --app sushi Waiting for database HEROKU_POSTGRESQL_SILVER... available
Promote the rollback as the primary database
$ heroku pg:promote HEROKU_POSTGRESQL_SILVER --app sushi
Re-establish replication on all followers to the new leader (if applicable)
$ heroku addons:create heroku-postgresql:standard-2 --follow HEROKU_POSTGRESQL_SILVER --app sushi Adding heroku-postgresql:standard-2 to sushi... done, v71 ($200/mo) Attached as HEROKU_POSTGRESQL_WHITE Follower will become available for read-only queries when up-to-date Use `heroku pg:wait` to track status