...
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 .
2. Nightly
...
.dump backups
We supplement the above with a regular, 2am, nightly physical .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.
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.)
List List .dump backups by running heroku pg:backups.
Restoring from a nightly
...
.dump backup
For physical .dump 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
Downloading a
...
.dump backup file:
heroku pg:backups:download a006 will produce a file like:
$ file physicallatest.dumpphysicallatest.dump: PostgreSQL custom database dump - v1.14-0.
Note that a physical .dump file can be converted to a garden-variety “logical” .sql database file:
$ pg_restore -f logical_database_file.sql physicallatest.dump.
$ file logical_database_file.sqllogical_database_file.sql: UTF-8 Unicode text, with very long lines
Restoring from a
...
local “.dump” file
If you downloaded a physical backup .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:
...
Finally, we maintain a rake task, rake scihist:copy_database_to_s3, which runs on a one-off Heroku dyno, via the scheduler. This uploads a logical (plain vanilla SQL) database to s3, where SyncBackPro then syncs to a local network storage mount (/media/SciHist_Digicoll_Backup), and from there to our tape (this process, again, backups. (SyncBackPro is managed by Chuck and Ponce.)
...
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 much larger (20GB or more) we will probably have to get rid of these frequent logical backups.SyncBackPro on Promethium (managed by Chuck and Ponce) finally copies the S3 file to a local network storage mount (/media/SciHist_Digicoll_Backup), and that gets backed up to tape.
Restoring from a logical (.sql) database dump.
...