Automate off-host backup of SQLite + per-tenant note files #41

New Issue

libretech · 2026-04-29T01:32:28+02:00

libretech commented

2026-04-29 01:32:28 +02:00

Summary

Add automated, off-host backup for librenotes state on netcup. Currently /srv/librenotes/data/ (per-tenant note files) and /srv/librenotes/state/ (librenotes.db SQLite) are bind-mounted volumes with no backup; a host failure or accidental docker compose down -v would lose all user data.

Context

SQLite is in WAL mode, so a hot copy via sqlite3 .dump (or sqlite3 .backup) yields a consistent snapshot without locking writers.
Per-tenant notes are plain files under /srv/librenotes/data/<tenant-uuid>/..., so rsync -a --link-dest for incrementals is sufficient.
Off-host target should NOT live on the same VPS. Options: another VPS, S3-compatible object storage, or rsync.net.

Tasks

Pick off-host target (rsync.net, B2, S3, or another box) — capture decision in this issue
Write scripts/backup.sh (in this repo or in libretech/netcup) that:
- Runs sqlite3 /srv/librenotes/state/librenotes.db ".backup /tmp/librenotes-$(date +%F).db", gzips, ships to off-host target
- rsyncs /srv/librenotes/data/ to a dated directory at the target
- Uses --link-dest against the previous day for cheap incrementals
- Retains 30 dailies + 12 monthlies (rotation script or via target-side policy)
Add a systemd timer (or cron) on netcup to run nightly at 03:00 Europe/Berlin
Test restore on a separate netcup directory: bring up a parallel compose project pointing at restored bind mounts, verify sign-in + notes intact
Document restore procedure in docs/self-hosting.md (or new docs/backup.md)

Acceptance Criteria

A nightly backup runs, completes in < 5 minutes for a small dataset, sends a non-zero exit code on any failure (alerting via cron MAILTO or other)
Last backup is reachable off-host and timestamped
Restore drill succeeds: a fresh container started against the restored data dir lets the test user sign in and see their notes
Restore procedure documented with exact commands

Dependencies

None (deployment is live)

## Summary Add automated, off-host backup for librenotes state on netcup. Currently `/srv/librenotes/data/` (per-tenant note files) and `/srv/librenotes/state/` (`librenotes.db` SQLite) are bind-mounted volumes with no backup; a host failure or accidental `docker compose down -v` would lose all user data. ## Context - SQLite is in WAL mode, so a hot copy via `sqlite3 .dump` (or `sqlite3 .backup`) yields a consistent snapshot without locking writers. - Per-tenant notes are plain files under `/srv/librenotes/data/<tenant-uuid>/...`, so `rsync -a --link-dest` for incrementals is sufficient. - Off-host target should NOT live on the same VPS. Options: another VPS, S3-compatible object storage, or rsync.net. ## Tasks - [ ] Pick off-host target (rsync.net, B2, S3, or another box) — capture decision in this issue - [ ] Write `scripts/backup.sh` (in this repo or in `libretech/netcup`) that: - Runs `sqlite3 /srv/librenotes/state/librenotes.db ".backup /tmp/librenotes-$(date +%F).db"`, gzips, ships to off-host target - rsyncs `/srv/librenotes/data/` to a dated directory at the target - Uses `--link-dest` against the previous day for cheap incrementals - Retains 30 dailies + 12 monthlies (rotation script or via target-side policy) - [ ] Add a systemd timer (or cron) on netcup to run nightly at 03:00 Europe/Berlin - [ ] Test restore on a separate netcup directory: bring up a parallel compose project pointing at restored bind mounts, verify sign-in + notes intact - [ ] Document restore procedure in `docs/self-hosting.md` (or new `docs/backup.md`) ## Acceptance Criteria - [ ] A nightly backup runs, completes in < 5 minutes for a small dataset, sends a non-zero exit code on any failure (alerting via cron MAILTO or other) - [ ] Last backup is reachable off-host and timestamped - [ ] Restore drill succeeds: a fresh container started against the restored data dir lets the test user sign in and see their notes - [ ] Restore procedure documented with exact commands ## Dependencies - None (deployment is live)

libretech added the

task

infrastructure

labels 2026-04-29 01:32:29 +02:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: public/librenotes#41