...
These are stored in S3, and are backed up within S3 by a process managed by AWS. The backups are then copied to long-term storage by SyncBackPro, which is Windows software running on Promethium managed by Chuck and Ponce (see https://www.2brightsparks.com/syncback/sbpro.html ). This (None of this will not change when we get rid of Ansible.)
Heroku database backups
We have three backup mechanisms under Heroku:
...
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) create a rollback database in under an hour using:
heroku addons:create heroku-postgresql:standard-0 --rollback DATABASE_URL --to '2021-06-02 20:20+00' --app scihist-digicoll-production
A The site remains up, but Heroku creates a new rollback database will be created, and its in the background, representing the state of the DB at 2021-06-02 20:20+00. Its name will be printed to stdout e.g. postgresql-curly-82727 in this example.Restore , and you can see it in the Resources section of the Heroku admin, or by listing databases on the command line.
Once it’s ready, you can restore to it like this:
heroku pg:promote postgresql-curly-82727 --app scihist-digicoll-production
...
Once all is well, don’t forget to get rid of the extra database(s) you are no longer using – otherwise . Otherwise, you’ll continue getting charged for it. heroku pg:info --app scihist-digicoll-production will show it (or them). Obviously, be Be really careful when doing this.
...
Details are at https://devcenter.heroku.com/articles/heroku-postgres-rollback .
For our database, this performs the rollback in under an hour. (The site remains up and usable while the rollback is being prepared and executed.)
2. Nightly physical backups
We supplement the above with a regular, 2am, nightly physical database scheduled backup. These are stored by Heroku, and restoring to them is very fast and convenientconvenient and takes well under a minute. Heroku retains up to 25 of these.
heroku pg:backups:schedules --app scihist-digicoll-production
(Physical backups are binary files that include dead tuples, bloat, indexes and all structural characteristics of the currently running database.)
The List 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
Restoring from a nightly physical backup
...
In the unlikely event you have to restore from a logical backup, because you can’t use the continuous protection rollback, and all the dumps managed by Heroku are somehow unavailable: download the logical backup you want to use from s3, and run::
Download the database file you want
heroku pg:psql --app scihist
...
-digicoll
...
-production <
...
~/Desktop/digcol_backup.sql
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.
...