Page Comparison

...

Heroku offers us a behind-the-scenes Continuous Protection mechanism for database disaster recovery. This doesn’t involve any actual backup files that we can see or download. It does ensures that, in the event of a disaster, we can roll back the database to a prior state (right before a bunch of files were mistakenly deleted, for instance) using:

heroku addons:create heroku-postgresql:standard-0 --rollback DATABASE_URL --to '2021-06-02 20:20+00' --app scihist-digicoll-production

...

We supplement the above with a regular, 2am, nightly physical database scheduled backup:

heroku pg:backups:schedules --app scihist-digicoll-production

Note that physical (Physical backups are binary files that include dead tuples, bloat, indexes and all structural characteristics of the currently running database.$ file physical.dump
latest.dump: PostgreSQL custom database dump - v1.14-0)

The backups are stored by Heroku and can be listed by running heroku pg:backups.

You can check the metadata on the latest physical backups like this: heroku pg:backups
heroku pg:backups:download a006 will produce a file like:

$ file physical.dump
physical.dump: PostgreSQL custom database dump - v1.14-0

Note that a physical dump can easily be converted to a garden-variety “logical” .sql database file:

...

$ file logical_database_file.sql
logical_database_file.sql: UTF-8 Unicode text, with very long lines

Restoring from a nightly physical backup.

For physical backups retained by Heroku (we retain up to 25) a restore takes about a minute and works like this:

heroku pg:backups:restore --app scihist-digicoll-production

Restoring from If you downloaded a physical backup stored locally involves uploading and have it stored on your local machine, and want to restore from that specific file, you first will need to upload it to s3, creating a signed URL for the dump, and then runningrun:

heroku pg:backups:restore '<SIGNED_URL_IN_S3>' DATABASE_URL # note the (DATABASE_URL is a literal, not a placeholder.)

More details on this process, including how to create a signed s3 URL: https://devcenter.heroku.com/articles/heroku-postgres-import-export#import

...

Given the size of the database in late 2020, the rake task takes 13 seconds, and the upload another 13. The entire job (with the overhead of starting up the dyno and tearing it down) takes a bit under a minute.

...