Backing up the database

The race server does not provide a backup mechanism. This page encourages you to consider implementing regular (nightly?) backups, but it can’t tell you how: that depends on how your system is set up.

Similarly, there is not currently a mechanism for partially loading recovered data from a backup. Nonetheless, in the event of a data loss, having a backup is always better than not having a backup.

If you’re hosting the race server yourself, you should investigate what backups are being taken by your tech team (if there is one).

It’s a good idea to take a backup of the SQL database in anticipation of two kinds of event:

• Catastrophic technical failure
• Accidental (or otherwise) loss of student task text data

Catastrophic technical failure

If you’re unlucky and you have a hardware failure or some other event that loses your database, you’ll need a backup to re-populate the database when you rebuild the server. Of course, the more frequently you take the backups, the less likely you will be to have lost much work.

Accidental (or otherwise) loss of student data

If you have set IS_STORING_STUDENT_TASK_TEXTS to Yes (in the “Tasks” group of config settings), then students who have been accumulating their task texts on the server are at risk of losing them, because the interface does allow them to edit (of course) and therefore delete their own texts.

The server does not keep an audit trail of edits to the texts, but if you’re taking nightly backups then it is at least feasible that you could recover lost work from previous days for a distraught student. There’s a utility to support you, but it presupposes you can load the backup into a local copy of a race server in order to extract them. See Recovering texts for details.

Example backup script: with Docker

If you have access to your server (for example, if you’re running it within a virtual machine) then a script like this can be run as a cronjob on that VM to create timestamped nightly zipped backups. It’s executing the PostgreSQL pg_dump command using Docker’s exec command targeted at the database container (in this example, that’s called server-db-1).

Replace the names in the script with values that match your local installation!

#!/bin/bash

# This script is run regularly by a cron job to make backups for the Buggy Race
# Database. Proceed with caution.

# Define the container name
container_name="server-db-1"

# Define the database and backup details
database_username="buggy"
database_name="buggy_race"
backup_dir="/buggy/server/db/backups"
backup_filename="buggy_race_bk_$(date +\%Y\%m\%d).sql.gz"

# Run the command
/usr/bin/docker exec -t $container_name pg_dump -U $database_username $database_name | /usr/bin/gzip > $backup_dir/$backup_filename

Heroku backups

If you’re hosting on Heroku, then currently you can schedule nightly backups of the whole PostgreSQL database. See Heroku’s backup documentation.

You’ll need to know the name of your app (shown as <APP_NAME> in the example commands here). The app name is clearly displayed when you log into your Heroku account on the web interface, but you can also find it with the Heroku command line command heroku apps.

Heroku gives each of its databases an ID — they look something like postrgresql-banana-123 (you can find them through the web interface when you look at your app’s “add-ons”). But if you use DATABASE_URL you probably won’t need it.

Heroku’s command line can helpfully use your app’s database DATABASE_URL setting to identify the database. Note that in the commands, this really is the literal string “DATABASE_URL”, which Heroku will map to the value it’s assigned to your app up at their end.

To schedule nightly backups (e.g., at 3am every night in the UK), do this:

heroku pg:backups:schedule DATABASE_URL --at "03:00 Europe/London" --app <APP_NAME>
Scheduling automatic daily backups of <database> at 03:00 Europe/London... done

To get information on the backups you’ve got:

heroku pg:backups --app <APP_NAME>

To download the latest backup:

heroku pg:backups:download --app <APP_NAME>

The file this pulls down is in PostgreSQL’s binary dump format. You can convert it into SQL text (such as an SQL file) using pg_restore:

pg_restore latest.dump -f - > sql_statements.sql

Be sure to check Heroku’s information about how long backups are retained up on their servers.

If you’re storing personal data in your database (for example, GitHub credentials or even email addresses) you need to handle your local backups just as securely locally as you do up on Heroku. See also the information about privacy considerations on the race server.

Static content backup

If you have a backup of the database, you don’t need to maintain a backup of the static content (which you can find in the published directory on your server) because you can regenerate all of those files from the database. The database backup will include all your config settings, except the DATABASE_URL setting that’s set as an environment variable.

Configuration snapshot

You can save a snapshot of the current configuration settings (in the form of a text file which can be reused as a .env file) — see saving a config snapshot.