Restore Heroku Postgres to the Second Before the Bad Migration

Accidental data loss on Heroku Postgres usually shows up as a bad migration that drops a column, a script that deletes the wrong rows, or a deploy that writes bad data. Daily backups get you back to yesterday, but that means losing a full day of work and extended downtime while you rebuild. Point-in-time recovery changes the recovery window from days to seconds, provided the feature is enabled on your plan.

The useful takeaway is simple: with PITR enabled, you can create a new database restored to any second inside the retention window and promote it, instead of overwriting production in place.

What PITR gives you on Heroku Postgres

PITR relies on continuous write-ahead log capture in addition to base backups. Heroku keeps a base backup and the WAL stream for a plan-dependent retention period. That lets you replay changes up to a chosen timestamp.

Eligibility is plan-dependent. PITR is not available on hobby/dev plans and retention length varies by tier. Check the current eligibility and retention for your plan in Heroku Postgres documentation before assuming coverage.

Enabling PITR adds storage overhead for WAL logs and is typically billed as part of the Postgres add-on. It does not change the primary database; it only extends the restore options.

Enable and verify before an incident

Enable PITR from the Heroku Dashboard under Data Management, or via the Heroku CLI from a terminal where you are logged in with collaborator access to the app.

Verify status with:

heroku pg:info --app <app>

Run this from a local terminal with Heroku CLI authenticated. Required permission is read access to the app and the Postgres add-on. Look for the plan name and backup status in the output. If PITR is not listed as enabled, it will not be available for restores.

A practical verification habit is to document the exact restore command for your app while things are calm, including the database name, app name, and how you will repoint the app. Store it in your runbook.

Restore to a timestamp and verify the result

Heroku restores create a new database instance rather than overwriting the existing one. That avoids accidental in-place destruction but means you must clean up the old database and repoint the app to avoid orphaned resources and cost.

A restore is initiated from the Dashboard or CLI targeting a new database name and a timestamp in ISO-8601 format. The timestamp must fall within the PITR retention window for that database; older points cannot be recovered.

After the restore completes, verify point-in-time accuracy by querying data that is known to change around the chosen time. For example, create a test table, insert a row and note the time, delete the row, then restore a copy of the database to a timestamp just before deletion and run a SELECT to confirm the row is present in the restored copy.

Risks to plan for: the restore creates a new database, so you will have two databases running temporarily. Repointing the app causes downtime. The restored database inherits the plan and storage class of the target, which can incur additional charges if left running.

Use PITR as a deployment safety net

The most reliable use is a controlled rollback path for migrations. Before a risky migration, note the current time. If the migration causes data corruption, you can restore a staging copy to a timestamp just before the migration started and compare schemas and row counts.

Limitations to keep in mind. Retention is plan-dependent and typically limited to a few days on standard tiers. Data older than the window cannot be recovered. Enabling PITR adds WAL storage overhead that grows with write volume, so monitor storage usage and costs.

A restore also does not move data back into the original database. You must decide whether to promote the restored database, copy specific tables back, or discard it. Cleaning up the temporary database promptly avoids unexpected charges.

Actionable closing

Enable PITR before you need it, confirm it is active with pg:info, and run a test restore to a new database name with a known timestamp to validate the workflow. Document the restore steps, the timestamp format you use, and the cleanup steps for the temporary database. Add a point-in-time rollback step to your incident runbook so the team can execute it without guessing during an outage.