sqlite-backups

A single-binary background daemon that takes consistent hot-backups of live SQLite databases — without interrupting application reads or writes — using the official SQLite Online Backup API (sqlite3_backup_*), compresses the snapshots (LZ4 / gzip), and streams them to local or S3-compatible storage (AWS S3, MinIO, Cloudflare R2).

Footprint target: < 8 MB RSS idle, < 0.1% CPU (measured ~7 MB / 0.000%).

Status

Step	Deliverable	Status
1	Project structure, Makefile, dependency definitions	✅ done
2	SQLite safe hot-backup engine (Online Backup API) + LZ4/gzip compression	✅ done
3	Cron scheduler + retention manager + local/S3 storage + pipeline	✅ done
4	E2E stress test suite (heavy write load during backup)	✅ done
5	README: systemd + PRoot background service configs	✅ done

Architecture notes live in docs/DESIGN.md.

Quick start

make build            # -> bin/sqlite-backups (requires Go 1.26+, gcc for cgo)
make test             # unit tests
make stress           # E2E stress suite: heavy write load during backup
bin/sqlite-backups -version
bin/sqlite-backups -config config/sqlite-backups.example.toml -check-config

Usage

sqlite-backups -config /etc/sqlite-backups/config.toml   # run the daemon
sqlite-backups -check-config -config <path>              # validate config only
sqlite-backups -once -config <path>                      # single backup cycle
sqlite-backups -version

SIGINT/SIGTERM trigger a graceful shutdown; under systemd the daemon reports readiness via sd_notify.

Configuration

TOML, one live database per daemon instance. See config/sqlite-backups.example.toml for the full reference. Summary:

[database]
path = "/var/lib/myapp/app.db"      # live database (required)
busy_timeout_ms = 5000              # wait for writer locks (default 5000)

[backup]
schedule = "0 3 * * *"              # cron expression (required)
compression = "lz4"                 # lz4 | gzip | none (default lz4)
verify_integrity = true             # PRAGMA integrity_check on each snapshot

[backup.retention]
keep_count = 14                     # keep N newest snapshots
keep_days = 7                       # keep everything younger than N days

[storage.local]                     # exactly one backend: local OR s3
dir = "/var/backups/sqlite"

# [storage.s3]
# endpoint = "s3.amazonaws.com"   # bare host[:port], no scheme; use_ssl=true for https
# bucket = "my-backups"
# ...

Project layout

cmd/sqlite-backups/     daemon entrypoint (flags, signals)
internal/config/        TOML config load/validate
internal/backup/        hot-backup engine  (Online Backup API)
internal/compress/      LZ4/gzip streaming compression
internal/storage/       local + S3 backends
internal/retention/     retention policy + artifact naming
internal/scheduler/     cron scheduler
internal/job/           backup cycle pipeline (snapshot→compress→store→prune)
internal/daemon/        process skeleton, sd_notify
tests/stress/           E2E stress suite (Step 4)
deploy/                 systemd unit + PRoot notes (Step 5)
scripts/footprint.sh    idle RSS/CPU probe (target < 8 MB, < 0.1% CPU)

S3 interoperability

The in-house S3 client (stdlib-only SigV4 + multipart) is verified against rclone/gofakes3, an independent S3 implementation that verifies every request's signature — a 13 MiB two-part multipart upload, list, delete and signed GET round-trips must pass, and unsigned requests must be rejected. This runs as part of make test.

Stress suite

make stress runs the E2E stress suite (tests/stress/, build tag stress): several goroutines hammer a live database with write transactions in both WAL and rollback-journal modes while the full backup pipeline runs repeated cycles. Every artifact is decompressed and must pass PRAGMA integrity_check and a transaction-consistency oracle (two counters that are only ever updated inside a single transaction must always be equal in a snapshot). Retention bounds and temp-file hygiene are asserted too. The suite caught a real bug in Step 4: snapshots of WAL-mode sources used to inherit the WAL header and leak -wal/-shm sidecar files into the temp dir; the engine now rewrites every snapshot to the rollback journal so it is fully standalone.

Deployment

systemd (hardened unit)

deploy/sqlite-backups.service is a hardened unit wired to the daemon's sd_notify readiness (Type=notify, NotifyAccess=main): systemd considers the service started only once the scheduler is running. It ships with ProtectSystem=strict, PrivateTmp, NoNewPrivileges, capability drops and namespace restrictions.

sudo make install                        # binary + example config
sudo install -m 0644 deploy/sqlite-backups.service /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now sqlite-backups
journalctl -u sqlite-backups -f           # logs

Before enabling, edit the unit for your layout:

• User=/Group= — run as the same OS user as the application that owns the live database (the daemon opens it read-only, but a WAL-mode database needs readable -wal/-shm sidecars).
• ReadWritePaths= — the backup destination and the config's temp_dir (defaults shown are /var/backups/sqlite and /var/lib/sqlite-backups/tmp).
• If the database is not under a path that is already read-only, narrow ProtectSystem with ReadOnlyPaths= instead (see the comments in the unit).

PRoot jail (no root, no systemd)

On locked-down hosts or user-space environments without systemd (Android/ Termux, restricted containers), deploy/proot/sqlite-backups-proot.sh runs the daemon inside a PRoot jail: a minimal rootfs containing only the binary and config, with the database and backup directories bind-mounted in. No CAP_SYS_CHROOT or root privileges are required.

./deploy/proot/sqlite-backups-proot.sh --setup /path/to/config.toml

Wrap it in nohup/& or a supervisor and stop it with SIGTERM for a graceful drain. Full instructions: deploy/proot/README.md.

Note: PRoot's ptrace-based syscall interception slows WAL-mode backups noticeably (small databases take seconds instead of milliseconds). Use systemd where available; the jail is for hosts where it is not.

Footprint target

The daemon must run continuously under 8 MB RSS with < 0.1% idle CPU. make footprint probes this directly:

make build && make footprint

License

MIT © 2026 Pastalikek65

Contributing

Contributions are welcome! See CONTRIBUTING.md for the contribution guide, CODE_OF_CONDUCT.md for the code of conduct, and SECURITY.md for how to report vulnerabilities privately.