Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 37 ยท Postgres and Data

Heroku Postgres Backups and Restore

Capture, schedule, download and restore Heroku Postgres backups, copy data between apps with pg:copy, and learn how retention and recovery objectives guide your plan.

A backup you have never restored is only a hope. Heroku Postgres gives you simple tools to take logical backups, schedule them and restore them, but the work of deciding how often, how long to keep them, and rehearsing a restore is yours. This lesson shows the commands (read-only here, because the CLI cannot run on this site) and uses stand-ins to practice the reasoning.

Two kinds of protection

Heroku Postgres offers two separate mechanisms, and people often confuse them:

  • Logical backups (pg:backups): a snapshot of your data made with pg_dump, stored by Heroku, which you can restore into any database, download, or keep for a while. Available on every tier.
  • Continuous protection (rollback): on Standard tier and above, Heroku also keeps the write-ahead log so you can create a new database as it was at a chosen moment. The followers and rollback lesson covers it.

Essential plans have only the logical backups, and the number you can keep is limited. Check the current plan table for retention.

Taking and scheduling backups

Terminal
heroku pg:backups:capture --app my-app
heroku pg:backups --app my-app
heroku pg:backups:schedule DATABASE_URL --at '02:00 Europe/Berlin' --app my-app
heroku pg:backups:schedules --app my-app
heroku pg:backups:unschedule DATABASE_URL --app my-app

capture takes a backup now and returns an id such as b001. pg:backups lists backups with their ids, sizes and time stamps. schedule creates a daily backup at the time and time zone you give. Backups run against the database, so pick a quiet hour.

Restoring

Terminal
heroku pg:backups:restore b001 DATABASE_URL --app my-app --confirm my-app

Restore replaces the target database with the backup, so the command asks you to type the app name to confirm. A safer routine is to restore into a new database first, check it, and only then promote it:

Terminal
heroku addons:create heroku-postgresql:standard-0 --app my-app
heroku pg:backups:restore b001 HEROKU_POSTGRESQL_AQUA_URL --app my-app
heroku pg:promote HEROKU_POSTGRESQL_AQUA_URL --app my-app

AQUA is an example color name; Heroku gives each extra database a color-based attachment name, and the real one appears in heroku pg:info. pg:promote makes that database the new DATABASE_URL.

flowchart LR
    A[("Production DB")] -->|pg:backups:capture| B["Backup b001"]
    B -->|pg:backups:restore| C[("New DB")]
    C -->|check data| D{"Looks right?"}
    D -->|yes| E["pg:promote"]
    D -->|no| F["Try another backup"]

Restore into a fresh database, verify, then promote.

Downloading and importing

Terminal
heroku pg:backups:download --app my-app
pg_restore --verbose --no-acl --no-owner -d my_local_db latest.dump

The download is a custom-format pg_dump file named latest.dump. Restore it locally with pg_restore. This is handy to reproduce a production bug with real data, but production data is often personal data: only do it if your policy allows, and delete the file afterwards. To push a local database up, upload the dump to a URL Heroku can reach (for example a pre-signed object storage link) and use pg:backups:restore 'https://...' DATABASE_URL.

Copying between databases

pg:copy copies the entire database from one app to another and is the usual way to refresh staging:

Terminal
heroku pg:copy production-app::DATABASE_URL DATABASE_URL --app staging-app --confirm staging-app

The target is overwritten. Do not copy production data into a less protected environment without thinking about privacy. A common compromise is to copy, then run a script that anonymizes emails and names.

Retention: how many backups do you keep?

A schedule with a retention rule keeps the last N daily backups, some weekly ones and a few monthly ones. The stand-in below applies such a rule (an example policy, not a Heroku setting) to a list of dates.

Python Stand-in: which backups does a retention rule keep?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

You should see 13 kept dates (the 7 newest days, the 4 newest Sundays, and the 3 newest first-of-month backups, with overlaps merged). Retention costs storage, so keep what your recovery needs require and no more.

RPO and RTO

Two numbers decide your backup design:

  • RPO (recovery point objective): how much data you can afford to lose, measured in time. A daily backup means an RPO of up to 24 hours.
  • RTO (recovery time objective): how long you can afford to be down while restoring.

The stand-in below turns a backup schedule into the worst-case data loss.

Python Stand-in: worst-case data loss from a schedule

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If the business accepts at most 4 hours of loss, a daily backup is not enough; you need continuous protection or more frequent backups.

Rehearse the restore

Once a quarter, restore the latest backup into a throwaway database and run a few queries: row counts of key tables, the newest order date, a login of a test user. Time the exercise; that is your real RTO. Write the steps in your runbook (production readiness checklist).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does pg:backups:capture do?

    capture takes a logical backup immediately and returns its id; download is a separate command.
  2. 2. Why restore into a new database and then promote it?

    Restoring into a fresh database lets you check the data first; pg:promote then switches DATABASE_URL to it.
  3. 3. A daily backup gives which worst-case data loss?

    If the failure happens just before the next backup, up to a full day of changes is gone; that is the RPO.
  4. 4. What is a risk of pg:copy from production to staging?

    The copy contains real user data, so staging needs equal protection or an anonymizing step.

Summary

  • pg:backups:capture, schedule, restore and download cover the logical backup life cycle on every tier.
  • Restore replaces the target; prefer a fresh database plus pg:promote.
  • pg:copy refreshes another app's database; mind personal data.
  • Pick backup frequency from your RPO, measure your RTO by rehearsing a restore.

Next: Followers, Rollback and Upgrades