...

Heroku database backups

We have three backup/restore mechanisms under Heroku:

1

...

This isn’t technically a backup, but Heroku does offer a convenient way to roll back the database to the way it was before a problem occurred:Rolling back the database to a prior state in Heroku .

...

. Nightly .dump backups

We supplement the above with a regular, 2am, nightly .dump database scheduled backup. These are stored by Heroku in PSQL .dumpformat, and restoring to them is convenient and takes well under a minute. Heroku retains up to 25 of these.use heroku’s built-in postgres backup functionality to make regular backups that are stored in heroku’s system. This is the most convenient backup to restore from, when it is available and meets your needs.

These backups end up stored in postgres -Fc or “dump” format, which postgres says is a compact, fast, and flexible recommended format for postgres backups; but is not human-readable and may be less portable between postgres versions.

• To verify that we have scheduled backups, run heroku pg:backups:schedules --app scihist-digicoll-production, to see that we have a 2AM backup ever night.

• List

...

• what backups exist by running heroku pg:backups

...

Restoring from a nightly .dump backup

For .dump backups retained by Heroku (we retain up to 25) a restore takes about a minute and works like this (to restore to the most recent)

• -a scihist-digicoll-production Note the first section is “backups” (which may scroll off screen), and the first column is a backup ID, such as a189.

• With the backup ID, you can restore production to a past backup (eg id a189), with heroku pg:backups:restore a189 -

...

• a scihist-digicoll-production

...

• • Warning this will overwrite current production data, with the restored backup!

• Maybe instead you want to restore a production backup to staging, to just look at the data, without actually (yet?) restoring to and overwriting current production? You can do this too:

  • heroku pg:backups:restore

...

• • scihist-digicoll-production

...

TODO: distinguish between manual and non-manual backups (see link above).

Downloading a .dump backup file:

heroku pg:backups:download a006 will produce a file like:

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

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

$ pg_restore -f logical_database_file.sql latest.dump.

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

Restoring from a local “.dump” file

If you downloaded a .dump file which is now stored on your local machine, and want to restore from that specific file, you will first need to upload it to s3, create a signed URL for the dump, and finally run:

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

3. Preservation (logical) backups to s3

...

• • :a189 -a scihist-digicoll-staging

For our standard-0 heroku postgres plan, heroku will keep 7 daily backups, and four weeks of one-per-week backups.

You can also download heroku backups to store them in your own location, and then load your local copies into heroku. See Heroku docs for more info.

2. Preservation (logical) backups to s3

We don’t want to rely solely on backups stored inside heroku’s system. We also would like a postgres backup in the more human-readable and transportable plain .sql format, instead of the postgres -Fc .dump format.

We have our own rake task, rake scihist:copy_database_to_s3, which runs on a one-off Heroku dyno, we also run nightly via the heroku scheduler. This uploads a logical (plain vanilla SQL) database to s3task connects to heroku postgres to make an postgres human-readable .sql dump, then uploads it to our s3 chf-hydra-backup bucket, where SyncBackPro then syncs to a local network storage mount (/media/SciHist_Digicoll_Backup), and from there to our tape backups. (SyncBackPro is managed by Chuck and Ponce.)This workflow serves more for preservation than

for disaster recovery: logical .sql files offer portability (they’re UTF8), and are useful in a variety of situations; notably, they can be used to reconstruct the database, even on other machines and other architectures using psql -f db.sqlYou can log into the heroku scheduler add-on via Heroku “resources” tab to verify the copy_database_to_s3 task is scheduled nightly.

• Given the size of the database in late 2020, the entire job (with the overhead of starting up the dyno and tearing it down) takes a bit under a minute. However, if our database grows

...

• an order of magnitude larger and slower to dump/transfer to S3, we may have to reconsider this approach.

The more portable .sql format stored and backed up outside of heroku is motivated primarily for preservation purposes, but it can also serve as a last-ditch or alternative disaster recovery. It can be restored to heroku using the heroku pg:psql command to run arbitrary psql commands on the heroku postgres.

Restoring from a logical (.sql) database dump.

In the unlikely event you have to restore from a logical backup:

• Go to https://s3.console.aws.amazon.com/s3/buckets/chf-hydra-backup?prefix=PGSql%2F&region=us-west-2&showversions=true

• Download the database file you want , and uncompress it.heroku (note the “versions” tab if you want a past version still on S3)

• Uncompress it from the .gz format. On a unix or MacOS command line, that’s gzip -d heroku-scihist-digicoll-backup.sql.gz

• Load it into heroku database: heroku pg:psql --app scihist-digicoll-production < ~/Desktop/digcol_backup.sqlheroku-scihist-digicoll-backup.sql

Note: This will overwrite your database, and won’t warn/prompt you about that fact first! It will run in your terminal and take a bit of time.

3. Heroku postgres “rollback”

Heroku can rollback postgres database to an arbitrary moment in time, based on postgres log files. For our current postgres standard-0 plan, there are four days past of logs kept. See: https://devcenter.heroku.com/articles/heroku-postgres-rollback , and the section “Common Use Case: Recovery After Critical Data Loss”

This is a somewhat more complicated process, and requires some more care to get right, but it is very powerful to be able to go back to any moment in time in the last 4 days!

To do this requires creating a new postgres “rollback” database; switching the app to use it; then deleting the old no-longer in use database. From a terminal with the heroku CLI:

1. heroku addons:create heroku-postgresql:standard-0 --rollback DATABASE_URL --to '2021-06-02 20:20 America/New_York' --app scihist-digicoll-production

2. The site remains up. The new database’s name will be printed to the terminal, and you can see it in the Resources section of the Heroku admin. It might be something like postgresql-curly-07169

3. It might take a few minutes or more for the newly restored database to be ready, you can follow instructions the command gives you to check progress, such as heroku pg:wait

4. Once the rollback database – which has been restored to a past moment in time – is ready, you can switch the app to use that new restored database by using the database name:
   heroku pg:promote postgresql-curly-07169 --app scihist-digicoll-production

5. Make sure you have successfully fixed the problem.

6. Once all is well, don’t forget to get rid of the extra database(s) you are no longer using. Consider leaving this step for the next day; it will only cost a couple dollars over 24 hours.

   1. How do you know which db is the “old” one? Run heroku addons to see all your heroku-postgresql databases; the one currently used by the app is marked as DATABASE. So the other one is the old no longer used one, which also has an AS name.

   2. To remove it run eg heroku addons:destroy HEROKU_POSTGRESQL_YELLOW --app scihist-digicoll-production. Be careful you are removing the correct one!

NOTE: Is it possible to rollback to a past production snapshot, but do it in the staging app first, to see what it looks like without touching production? We need to look into that, it could be a safer way to do it.

Historical notes

Prior to moving off our Ansible-managed servers, we used backup mechanisms that used to be performed by cron jobs installed by Ansible.Backups and Recovery contains a summary of our pre-Heroku backup infrastructure.

...