halkyon/deploy/backups.md

# Backups

learn.halkyon.eu backs up its database every hour, encrypted, to a Hetzner Storage Box:
`backup.sh`, started by the systemd timer in `backup/`. The database is all there is to
back up: people's files are in it too (Serverpod's database cloud storage). The other
state of the instance is `deploy/.env`, whose secrets are kept in a password manager,
and Caddy's certificates, which it fetches again.

| | |
|---|---|
| When | every hour, on the hour; a run missed while the server was off happens when it is back |
| What | `pg_dump` of the whole database, stored by [restic](https://restic.net) (encrypted, deduplicated, compressed) |
| Kept | 24 hourly, 7 daily, 4 weekly and 3 monthly snapshots; a deleted account is gone from every backup within about 3 months |
| Where | a sub-account of the Storage Box (SFTP), used for nothing else |
| Protected by | the box's automatic snapshots, daily, which only Hetzner's Console can remove: a taken-over server can delete what restic wrote, but not those |
| Checked | every night at 03:00: forgotten snapshots pruned, the repository's structure read; on Sundays also a random tenth of the data |
| Failures | in the journal only: `systemctl status halkyon-backup`, `journalctl -u halkyon-backup` |

The newest dump also stays on the server, as `~deploy/backups/latest.dump`.

**Without the restic password, the backups can't be read by anyone, you included.** Keep
it, with the box's login, somewhere other than the server.

## What a restore loses

A restore takes the instance back to the time of the backup, up to an hour ago. Devices
then sync with a server that has forgotten their last hour:

- **Someone who used the instance before the backup** loses nothing: their devices still
  have what the server lost, and send it again.
- **Someone who started after the backup** is unknown to the server. At the next start,
  their app finds its session gone, starts a new anonymous account and deletes the old
  one's data from the device, the only copy left. The same happens to a device that
  signed in after the backup, though a GitHub account can be signed into again.
- **An account deleted after the backup** comes back. Delete it again if you know which
  one it was.

## Setting it up

Once, in this order. The commands name who runs them, and where.

### 1. The Storage Box (you, in Hetzner Console)

1. Create a Storage Box (BX11, 1 TB, is plenty) in any location, with a password, which
   goes into your password manager. Turn on **SSH Support** (port 23, which restic uses)
   and **External Reachability**: without it, the box only answers inside Hetzner's
   network. Samba and WebDAV stay off; FTP and SFTP on port 22 can't be turned off.
2. Set up **automatic snapshots**: daily, at a fixed time, with as many kept as the box
   allows (10 on a BX11).
3. Add a **sub-account** for this server: its own directory (e.g. `sahaquiel`), **SSH
   support** and **External reachability** on, Samba and WebDAV off, not read-only
   (restic deletes what it prunes). Note its user name (`uXXXXX-subN`) and password; the
   password is needed once, below.

### 2. restic (an admin with sudo, on the server)

```sh
sudo apt install restic
```

### 3. The repository (deploy, on the server)

As `deploy` (`ssh sahaquiel-deploy` on learn.halkyon.eu). A key without a passphrase,
since nobody is there to type it at 03:00:

```sh
ssh-keygen -t ed25519 -N '' -C 'deploy@sahaquiel: backups' -f ~/.ssh/storagebox
cat >>~/.ssh/config <<'EOF'
Host storagebox
    HostName uXXXXX-subN.your-storagebox.de
    User uXXXXX-subN
    Port 23
    IdentityFile ~/.ssh/storagebox
    IdentitiesOnly yes
EOF
```

Install the key, with the sub-account's password. SSH first shows the box's host key:
accept it only if its ED25519 fingerprint is
`SHA256:XqONwb1S0zuj5A1CDxpOSuD2hnAArV1A3wKY7Z3sdgM`, or, for another key type, the one
[Hetzner lists](https://docs.hetzner.com/storage/storage-box/general/) for it.

```sh
ssh storagebox install-ssh-key <~/.ssh/storagebox.pub
ssh storagebox ls   # must not ask for the password any more
```

Then restic's two files and the repository. The password is generated here; copy it into
your password manager before going on.

```sh
mkdir -p -m 700 ~/.config/restic
echo 'sftp:storagebox:restic' >~/.config/restic/repository
(umask 077 && openssl rand -base64 32 >~/.config/restic/password)
cat ~/.config/restic/password   # into the password manager
export RESTIC_REPOSITORY_FILE=~/.config/restic/repository RESTIC_PASSWORD_FILE=~/.config/restic/password
restic init
~/halkyon-learn/deploy/backup.sh   # the first backup, by hand
```

### 4. The timer (an admin with sudo, on the server)

```sh
sudo cp /home/deploy/halkyon-learn/deploy/backup/halkyon-backup.service \
  /home/deploy/halkyon-learn/deploy/backup/halkyon-backup.timer /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now halkyon-backup.timer
systemctl list-timers halkyon-backup.timer
```

The units name `/home/deploy/halkyon-learn`; another checkout needs them changed.

## Looking at the backups

As `deploy`, with the two variables from step 3 exported:

```sh
restic snapshots                     # every snapshot, the newest last
restic check --read-data-subset=5%   # reads a twentieth of the data back
```

## Restoring

As `deploy`, in `~/halkyon-learn/deploy`, with the two variables from step 3 exported:

```sh
export RESTIC_REPOSITORY_FILE=~/.config/restic/repository RESTIC_PASSWORD_FILE=~/.config/restic/password
```

A restore replaces the whole database, so dump the current one first, if there is one:
the way back from a restore that went wrong.

1. **The dump to restore.** The newest is `~/backups/latest.dump`, if the server still
   has it. Otherwise from the repository (on a new server, set up steps 1–3 first, with
   the restic password from your password manager instead of a new one, and without
   `restic init`):

   ```sh
   restic snapshots
   restic dump <snapshot> /home/deploy/backups/latest.dump >~/backups/restore.dump
   ```

   `latest` in place of a snapshot's ID takes the newest.
2. **Stop the server**, so nobody writes meanwhile:

   ```sh
   docker compose stop server
   ```

3. **Replace the database** with the dump (it drops the database and creates it anew):

   ```sh
   docker compose exec -T postgres \
     pg_restore -U halkyon_learn -d postgres --clean --create <~/backups/restore.dump
   ```

4. **Start the server.** It applies the migrations that came after the dump:

   ```sh
   docker compose up -d
   ```

To check a backup without touching the instance, restore it into a database of its own
next to the real one, look, and drop it:

```sh
docker compose exec -T postgres createdb -U halkyon_learn restore_check
docker compose exec -T postgres pg_restore -U halkyon_learn -d restore_check <~/backups/restore.dump
docker compose exec -T postgres psql -U halkyon_learn -d restore_check -c 'SELECT count(*) FROM studyset'
docker compose exec -T postgres dropdb -U halkyon_learn restore_check
```