Skip to content

Backup and restore

The database is the only state worth backing up: the permanent watch ledger, ratings, dismissals, users, and connections all live in one sqlite file — ~/docker/xique/data/db/xique.db on the host. Everything else is reproducible (the image is in the registry, the quadlets are in the repository, and the imports volume only holds transient upload zips).

Why WAL matters here

The database runs in WAL mode with synchronous = NORMAL: readers and writers work against the main file plus a write-ahead log (xique.db-wal), which is what lets the single web process stay responsive while the worker writes. Two consequences for backups:

  • The -wal file can hold committed transactions that are not yet in the main database file. Copying only xique.db while the app is running can miss recent data.
  • The worker runs PRAGMA wal_checkpoint(TRUNCATE) at the end of every cycle (every few seconds), which folds the WAL back into the main file and empties it. In practice the window of "data only in the WAL" is small — but a backup procedure should not depend on timing.

Backup: stopped (simplest, always consistent)

systemctl --user stop xique-pod
cp ~/docker/xique/data/db/xique.db ~/backups/xique-$(date +%F).db
systemctl --user start xique-pod

The pod is down for the duration of a file copy. This is the recommended routine for a single-instance deployment.

Backup: online (no downtime)

Use sqlite's own backup API, which produces a consistent snapshot of a live WAL-mode database. From the host, with the sqlite3 CLI:

sqlite3 ~/docker/xique/data/db/xique.db \
  ".backup '$HOME/backups/xique-$(date +%F).db'"

No -wal/-shm files are needed alongside the snapshot — the .backup command writes a complete, self-contained database file. This is safe to run while both processes are serving.

Restore

  1. Stop the pod: sh systemctl --user stop xique-pod
  2. Replace the database file (keep the broken one for forensics): sh cp ~/backups/xique-2026-10-17.db ~/docker/xique/data/db/xique.db rm -f ~/docker/xique/data/db/xique.db-wal ~/docker/xique/data/db/xique.db-shm The -wal/-shm cleanup matters: a stale WAL from the failed database must not be replayed onto the restored file.
  3. Start the pod: sh systemctl --user start xique-pod

The worker migrates the restored file forward if it is from an older release; the web process verifies the schema and serves once current.

Verify a backup

A backup is only real if it opens:

sqlite3 ~/backups/xique-2026-10-17.db \
  "PRAGMA integrity_check; SELECT COUNT(*) FROM watch_events;"

integrity_check should answer ok and the count should be in the ballpark you expect. Do this once when you set up your backup routine, not during your first restore.