SPB Git

Repository Public

BACKUP_RESTORE.md 11.8 KB 170 lines markdown

1# Backup and restore23What `scripts/backup.sh` and `scripts/restore.sh` do, line by line, how to schedule the backup, and how to run a restore drill.45## 1. What is backed up67| Part | Content | Archive |8|---|---|---|9| Database | Everything: sheets, revisions, publications, assets metadata, passkeys (public keys), sessions, audit trail, job history, usage ledger, settings | `$BACKUP_DIR/db/sheet-YYYYmmdd-HHMMSS.dump.enc` |10| Storage | Every file under `STORAGE_ROOT`: uploads **and exports** | `$BACKUP_DIR/storage/sheet-YYYYmmdd-HHMMSS.tar.enc` |1112`tar` excludes only `*.part` (uploads being written) and `.probe` (the health-check file). Exports live in the same key space as uploads and are archived with them.1314Each archive is accompanied by `<archive>.hmac`, an HMAC-SHA256 of the encrypted file keyed with the passphrase.1516## 2. Configuration1718Read from the environment, or from `.env` in the project root for the variables not already set:1920| Variable | Used by | Meaning |21|---|---|---|22| `DATABASE_URL` | backup | Database to dump |23| `STORAGE_ROOT` | backup | Directory to archive |24| `BACKUP_DIR` | backup | Destination; created with mode 0700, files 0600 (`umask 077`) |25| `BACKUP_PASSPHRASE_FILE` | backup, restore | File whose content is the encryption passphrase |26| `BACKUP_KEEP_DAYS` | backup | Retention in days, default 30 |2728### The passphrase file2930```bash31install -d -m 700 ~/.config/sheet32openssl rand -base64 48 > ~/.config/sheet/backup-passphrase33chmod 600 ~/.config/sheet/backup-passphrase34```3536`backup.sh` stops if the file is missing or empty. `openssl` reads the passphrase from the first line of the file.3738**Keep a copy of the passphrase somewhere other than the server and other than the backup destination.** Without it the archives cannot be decrypted; there is no recovery path. Do not put the passphrase file inside `BACKUP_DIR`.3940## 3. What `scripts/backup.sh` does41421. `set -euo pipefail`; changes to the project root; loads the five variables above from `.env` when unset.432. Prepends the Homebrew PostgreSQL 17 and Homebrew `bin` directories to `PATH` (harmless where they do not exist). `pg_dump`, `pg_restore`, `openssl` and `tar` must be on the `PATH`.443. Creates `$BACKUP_DIR/db` and `$BACKUP_DIR/storage`.454. **Database**: `pg_dump --format=custom --no-owner --no-privileges "$DATABASE_URL"` piped into `openssl enc -aes-256-cbc -pbkdf2 -iter 200000 -salt`. Written to a `.tmp` file, then renamed.465. **Verifies the database archive**: decrypts it and runs `pg_restore --list` on the result, then drains the rest of the stream so that the decryption runs to the end. A wrong passphrase or a truncated file fails the run here.476. **Storage**: `tar -C "$STORAGE_ROOT" --exclude='*.part' --exclude='.probe' -cf - .` piped into the same encryption. Written to `.tmp`, then renamed.487. **Verifies the storage archive**: decrypts it, lists it with `tar -tf`, and counts the files.498. **Integrity tag**: for each of the two archives, `openssl dgst -sha256 -hmac <passphrase>` → `<archive>.hmac`.509. **Retention**: deletes `sheet-*.enc` and `sheet-*.hmac` files in both directories whose modification time is older than `BACKUP_KEEP_DAYS` days.5110. Writes `$BACKUP_DIR/last-backup.json`: `{"at": "<UTC time>", "database": "<file>", "storage": "<file>", "files": <count>}`.5211. Prints `backup ok: <dump> (<size>), <tar> (<n> files)`.5354Any failing step aborts the script with a non-zero status; an archive that failed verification is left in place (it was already renamed) and the retention step is not reached.5556Properties worth knowing:5758- Encryption is AES-256-CBC with a key derived by PBKDF2 (200 000 iterations, random salt). CBC gives secrecy, not integrity, so each archive gets a separate HMAC-SHA256 keyed with the same passphrase; `restore.sh` checks it before decrypting and refuses an altered archive.59- The database dump and the storage archive are taken one after the other, not atomically. A file uploaded between the two appears in the archive without a database row; a file deleted between the two is referenced by the dump but absent from the archive. Schedule the backup at a quiet hour.60- `pg_dump` receives the database URL, password included, as a command-line argument, and the HMAC step passes the passphrase to `openssl dgst -hmac` the same way: both are visible to other users of the machine in the process list while they run.61- The dump holds session token hashes, passkey public keys and recovery-code hashes. It holds no API key and no `APP_SECRET`.6263## 4. Scheduling6465The script is meant to run once a day. Nothing in the repository schedules it; that is the operator's job.6667cron:6869```7015 3 * * *  cd /srv/sheet && /bin/bash scripts/backup.sh >> /var/log/sheet-backup.log 2>&171```7273PM2: an app entry with `script: "scripts/backup.sh"`, `interpreter: "bash"`, `autorestart: false`, `cron_restart: "15 3 * * *"` (see [DEPLOYMENT.md](DEPLOYMENT.md)).7475Monitoring: alert when the script exits non-zero, or when `last-backup.json` is older than a day and a half. `GET /api/admin/settings` reports only whether `BACKUP_DIR` is set (`environment.backupDirSet`); the application does not read `last-backup.json`.7677## 5. Retention7879`find … -name 'sheet-*.enc' -mtime +$BACKUP_KEEP_DAYS -delete` (and the same for `sheet-*.hmac`), in `db/` and `storage/`. With the default 30 and one run a day, about 30 pairs are kept. Retention is by file age only: there is no weekly or monthly tier, and no check that a newer valid backup exists before an old one is deleted — though the script only reaches this step after both new archives verified.8081Each storage archive is a full copy. Disk use is roughly `BACKUP_KEEP_DAYS × (size of STORAGE_ROOT + compressed dump)`.8283## 6. What `scripts/restore.sh` does8485```bash86scripts/restore.sh db      <sheet-….dump.enc> <postgres URL of an EMPTY target database>87scripts/restore.sh storage <sheet-….tar.enc>  <target STORAGE_ROOT (empty or new directory)>88```8990- Loads `BACKUP_PASSPHRASE_FILE` from the environment or `.env`.91- **Integrity check first** (`check`): recomputes the HMAC-SHA256 of the archive and compares it with `<archive>.hmac`. A mismatch stops with `INTEGRITY CHECK FAILED … the archive was altered or the passphrase is wrong`. A missing `.hmac` file also stops the restore, unless `FORCE=1` is set (for archives made before the HMAC existed). Keep each `.hmac` next to its archive when copying backups elsewhere.92- `db`: decrypts and pipes into `pg_restore --no-owner --no-privileges --exit-on-error -d <target>`. Objects are created as the role of the target URL. The dump contains `CREATE EXTENSION` statements, so that role must be allowed to create `citext`, `pg_trgm` and `unaccent` (the database owner can, on PostgreSQL 13+), or the extensions must exist already. The target must be empty: `--exit-on-error` stops at the first conflict.93- `storage`: creates the target directory with mode 0700 and extracts the archive into it.94- With no or an unknown first argument it prints the usage and exits 2.9596The script never touches the running installation: it writes only to the targets given on the command line.9798## 7. Restore drill99100Run this on a schedule you can keep (for example monthly), on the server or on another machine with the archives and the passphrase.101102```bash103# 0. choose the pair to test104DB_ARCHIVE=$(ls "$BACKUP_DIR"/db/*.dump.enc | tail -1)105ST_ARCHIVE=$(ls "$BACKUP_DIR"/storage/*.tar.enc | tail -1)106107# 1. restore the database into a scratch database108createdb sheet_drill109bash scripts/restore.sh db "$DB_ARCHIVE" "postgres://<role>@127.0.0.1:5432/sheet_drill"110111# 2. restore the storage into a scratch directory112bash scripts/restore.sh storage "$ST_ARCHIVE" /tmp/sheet-drill-storage113114# 3. compare row counts with production for the tables that matter115for t in sheets sheet_revisions sheet_publications assets passkey_credentials audit_events; do116  echo "$t $(psql -Atc "select count(*) from $t" "$DATABASE_URL") $(psql -Atc "select count(*) from $t" postgres://<role>@127.0.0.1:5432/sheet_drill)"117done118# counts differ only by what happened since the backup was taken119120# 4. every file the restored database knows exists in the restored storage with the recorded checksum121psql -Atc "select storage_key || ' ' || sha256 from assets where deleted_at is null" postgres://<role>@127.0.0.1:5432/sheet_drill |122while read -r key sum; do123  [ "$(shasum -a 256 "/tmp/sheet-drill-storage/$key" | cut -d' ' -f1)" = "$sum" ] || echo "MISMATCH $key"124done125126# 5. (thorough) start the application on the restored copy, on another port127PORT=8799 HOST=127.0.0.1 APP_URL=http://localhost:8799 \128DATABASE_URL=postgres://<role>@127.0.0.1:5432/sheet_drill STORAGE_ROOT=/tmp/sheet-drill-storage \129NODE_ENV=production node_modules/.bin/tsx server.ts130#    then: curl http://localhost:8799/api/health ; open a published sheet at /s/<slug>131132# 6. clean up133dropdb sheet_drill && rm -rf /tmp/sheet-drill-storage134```135136Steps 1–4 are what `scripts/test.sh` automates against the test database after its integration run. On Linux use `sha256sum` in place of `shasum -a 256`.137138Notes for step 5: passkeys are bound to the RP ID, so signing in to the studio on `localhost` with production passkeys does not work — that is expected. Public pages, the public API and `/api/health` are enough to show the data is readable. Do not start a worker against the drill copy unless you want queued jobs to run.139140### Restoring for real1411421. Stop the web and worker processes.1432. Create an empty database (`createdb`), restore the dump into it, restore the storage archive into a new directory.1443. Point `DATABASE_URL` and `STORAGE_ROOT` in `.env` at the restored copies (or swap names).1454. `pnpm migrate` (applies any migration newer than the backup), `pnpm bootstrap`.1465. Start the processes. Sessions that were valid at backup time are valid again; sign in and check the dashboard.1476. Jobs that were queued or running at backup time are in the restored queue and will run. Cancel them first if that is not wanted (see [OPERATIONS.md](OPERATIONS.md)).148149Everything created after the backup is lost.150151## 8. What is not covered152153- **Off-site copies.** The script writes to a local directory — on the same disk as the data unless `BACKUP_DIR` points elsewhere. Copying `BACKUP_DIR` (archives and their `.hmac` files) to another machine or provider is the operator's responsibility; the archives are encrypted and tagged, so they can be stored on an untrusted target — keep the passphrase elsewhere.154- **`.env`**, the passphrase file, `SETUP_URL_FILE`, the proxy configuration and TLS certificates. Keep `.env` and the passphrase in a password manager.155- **The code** and `node_modules`: reinstall from the repository.156- **Point-in-time recovery.** There is no WAL archiving; the granularity is one backup.157- **Deleted uploads.** Deleting an asset removes its file at once; it survives only in archives taken before the deletion.158- **A check from inside the application.** The dashboard has no backup tile.159- **Browsers** installed for exports: reinstall with `playwright-core`.160161## Verified162163Two drills were run on 2026-11-17.164165**Test environment** (`scripts/test.sh`, laptop): backup of a database holding 11 sheets and 10 stored files; restore into an empty database and an empty directory; row counts equal on `sheets`, `sheet_revisions`, `sheet_publications`, `assets`, `passkey_credentials` and `audit_events`; every restored file matches the SHA-256 recorded in `assets`; an archive with one byte appended is refused by `restore.sh` ("INTEGRITY CHECK FAILED").166167**Production node** (`M2M32c`, production passphrase): `scripts/backup.sh` wrote `sheet-20261117-131443.dump.enc` (200 kB) and the storage archive (6 files); both restored into a scratch database and directory: 6 sheets / 6 publications / 6 files on both sides, 0 checksum mismatches. The scratch copies were removed afterwards.168169Still to do by the operator: copy `~/backups/sheet` (and, separately, the passphrase) to another machine on a schedule. The backups currently sit on the same disk as the data.170
Files main
Menu

Theme