Backups and restore

Nightly backups, keeping a copy elsewhere, and restoring one.

What’s backed up

Every night (and before every update) the server writes a backup of the whole database into the backups volume and keeps about 6 days of them. They’re on the same disk as the server, so copy them somewhere else regularly:

docker compose cp waterflow:/backups ./waterflow-backups

The folder also holds purged_accounts.jsonl, the list of accounts deleted for good; a restore needs it too. Backups contain password hashes: keep the copies private.

Restore from Server admin

The admin can put a backup back from Server admin, Restore a backup: choose the .dump file (and, for a backup from another server, its purged_accounts.jsonl), enter your password (and your two-step code when it’s on), tick that everything will be replaced, and Restore. The server checks the file is a Waterflow backup no newer than itself, then restarts and, before anything else, backs up what’s there, loads the backup and deletes again the accounts deleted for good since. If loading fails, it puts back what was there. It takes a minute or two; everyone then signs in again, with the passwords from the backup’s day. Server admin shows how the last restore went. A backup from before the app was renamed (msaving-<time>.dump, from MSaving) restores the same way.

A new server can start from a backup too: on Set up this server, Restore a backup instead, with the setup code from the log.

Files up to 100 MB. On Google Cloud Run a request carries at most 32 MB; a household’s backup is far smaller. Restoring by hand, with commands, is below.

Restore

This replaces everything on the server with the backup. It works the same on the same machine or on a new one (a fresh install first, then these steps). Run it in the folder you cloned, with your copy in ./waterflow-backups:

docker compose stop waterflow
docker compose cp ./waterflow-backups/waterflow-<time>.dump db:/tmp/restore.dump
docker compose exec db dropdb -U postgres --force --maintenance-db=template1 postgres
docker compose exec db createdb -U postgres postgres
docker compose exec db psql -U postgres -c 'alter default privileges revoke execute on functions from public'
docker compose exec db sh -c "pg_restore --list /tmp/restore.dump | grep -v -E ' (SCHEMA|COMMENT) - (SCHEMA )?public ' > /tmp/restore.list && pg_restore -U postgres --no-owner --single-transaction --exit-on-error --use-list=/tmp/restore.list -d postgres /tmp/restore.dump"
docker compose start waterflow
docker compose exec -T waterflow sh -c 'cat >> /backups/purged_accounts.jsonl' < ./waterflow-backups/purged_accounts.jsonl
docker compose exec waterflow python -m app.admin repurge

The last two lines delete again any account that was deleted for good after that backup was taken (the backup still has it). On a new machine the server’s list of those is empty, so the first of them adds yours. Accounts closed or restored after the backup show in the server’s log as account_event lines; redo them by hand if it matters.

With your own database

There’s no db container, so the server’s image runs the restore against ADMIN_DATABASE_URL. It empties the public and waterflow schemas instead of dropping the database (a hosted database may not allow that):

docker compose stop waterflow
docker compose run --rm --no-deps -T waterflow sh -c '
  cat > /tmp/restore.dump &&
  psql "$ADMIN_DATABASE_URL" -q -v ON_ERROR_STOP=1 -c "drop schema if exists waterflow cascade" \
    -c "drop schema public cascade" -c "create schema public" \
    -c "alter default privileges revoke execute on functions from public" &&
  pg_restore --list /tmp/restore.dump | grep -v -E " (SCHEMA|COMMENT) - (SCHEMA )?public " > /tmp/restore.list &&
  pg_restore --no-owner --single-transaction --exit-on-error --use-list=/tmp/restore.list \
    -d "$ADMIN_DATABASE_URL" /tmp/restore.dump' < ./waterflow-backups/waterflow-<time>.dump
docker compose start waterflow

Then the same two purged_accounts.jsonl and repurge lines as above. This empties those two schemas completely: use a database only Waterflow uses.

Tested by hand on a new install (the built-in database, and a separate Postgres 17): sign-in with two-step verification and recovery codes, shared books, transactions and an account deleted after the backup. How backups work: Background jobs.