public/librenotes
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases 1 Wiki Activity

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

New Issue
Closed
opened 2026-04-29 01:32:28 +02:00 by libretech · 1 comment
libretech commented 2026-04-29 01:32:28 +02:00
Owner
Copy Link
Copy Source

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 taskinfrastructure labels 2026-04-29 01:32:29 +02:00
libretech commented 2026-04-29 16:29:57 +02:00
Author
Owner
Copy Link
Copy Source

Closing — netcup provider handles VPS-level backups. Application-layer backup tooling (sqlite .backup + rsync --link-dest, systemd timer at 03:00 Europe/Berlin) is still in scripts/ and docs/operations.md for any other deployment of librenotes; on netcup itself we rely on the provider snapshot path instead of running our own off-host transport.

Follow-ups (will go in their own issues if/when needed):

  • Verify the netcup snapshot retention covers SQLite + per-tenant note files (currently rsynced to a netcup snapshot store; restore drill not yet performed).
  • Document the netcup-side restore procedure in docs/operations.md once the snapshot location is confirmed.
Closing — netcup provider handles VPS-level backups. Application-layer backup tooling (sqlite .backup + rsync --link-dest, systemd timer at 03:00 Europe/Berlin) is still in `scripts/` and `docs/operations.md` for any other deployment of librenotes; on netcup itself we rely on the provider snapshot path instead of running our own off-host transport. Follow-ups (will go in their own issues if/when needed): - Verify the netcup snapshot retention covers SQLite + per-tenant note files (currently rsynced to a netcup snapshot store; restore drill not yet performed). - Document the netcup-side restore procedure in docs/operations.md once the snapshot location is confirmed.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
backend
Backend changes
 database
Database changes
 documentation
Documentation improvements
 epic
frontend
Frontend changes
 infrastructure
Infrastructure and deployment
 launch
Launch and release tasks
 phase-0
phase-1
phase-2
phase-3
phase-4
phase-5
phase-6
security
Security-related
 task
Actionable task
No labels infrastructure task
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
libretech (Michael Czechowski) libretech-bot
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: public/librenotes#41