The instinct, when you want to back up a small SQLite-backed app, is to copy the database file. cp app.db backup.db, done. On a database that’s actively being written to, that instinct can hand you a backup that’s quietly broken — and you won’t find out until the day you try to restore it.
The fix is one SQL statement, it runs with the server up, and the result is a single clean file you can ship anywhere. Then the second half of the job: get that file off the box, because a backup sitting on the same disk as the original isn’t a backup at all.
What this assumes (and what I tested)
I run this for the blog you’re reading — a small SQLite-backed Go service on Ubuntu 24.04 LTS with systemd, rsync, and ssh, shipping to a second always-on machine on a private network (I use a Tailscale tailnet; any reachable host works the same way).
VACUUM INTO has been in SQLite since 3.27.0 (early 2019), so any current install already has it — check yours with sqlite3 --version. And to be straight about scope: this runs in production. It’s been snapshotting to another box four times a day for a while now, and I check that each snapshot is intact. The one thing I haven’t done is a full restore-from-snapshot drill, and I’ll flag exactly where that gap matters when we get to recovery.
The snapshot, up front
This is the whole trick. With the server running:
sqlite3 app.db "VACUUM INTO 'snapshot.db'"
snapshot.db is now a complete, self-contained copy of the database as of a single consistent instant — no separate log files, no half-written rows. You can gzip it, copy it, hand it to a colleague. (Baking the same call into your app behind a -snapshot flag is nice if you don’t want a sqlite3 binary on the box, but the SQL is identical.)
It’s quick and cheap. On my blog’s database the snapshot finishes in tens of milliseconds with the server live, and gzips down to around 120 KB. And here’s a detail that pays off later: what comes out is a plain database in rollback-journal mode, not WAL — it has no -wal sidecar of its own. That’s what makes restoring it almost trivial, which I’ll come back to.
One catch: VACUUM INTO refuses to overwrite an existing file, so write to a fresh timestamped name each time (snapshot-$(date -u +%Y%m%dT%H%M%SZ).db) rather than reusing one path.
Why cp is the wrong tool
Modern SQLite usually runs in WAL mode (write-ahead logging). Instead of writing changes straight into app.db, it appends them to a sidecar file, app.db-wal, and only later folds those changes back into the main file in a step called a checkpoint. There’s a third file, app.db-shm, that’s shared-memory bookkeeping for readers.
So at any given moment your most recent writes may live only in the -wal file, not in app.db yet. Copy just app.db and you’ve captured a database that’s missing its newest data — internally consistent-looking, but stale. Copy all three files instead, and unless the app is stopped you’re grabbing them at slightly different instants while writes are in flight, which can tear the set into a combination that never actually existed. Either way: a backup that restores to garbage.
If you’re not a database person: cp photographs the building while the furniture is still being carried in. VACUUM INTO waits until the room is settled and photographs that, and it does it without telling anyone to stop working.
A backup on the same disk isn’t a backup
A snapshot next to the original protects you from exactly nothing that matters — a fat-fingered DROP TABLE, sure, but not the failure that actually ends projects: the disk or the box dies and takes both files with it. The snapshot has to land on different hardware.
The shipping half is plain rsync over ssh to another always-on machine:
# DB snapshot -> a second box
rsync -e ssh snapshot-*.db.gz backup-host:/srv/backups/app/db/
# media / uploads, if you have them: mirror, but APPEND-ONLY (no --delete)
rsync -a -e ssh /srv/app/media/ backup-host:/srv/backups/app/media/
That --delete omission is deliberate. For the database you keep several timestamped snapshots and prune the oldest. But for a directory of uploads, leaving --delete off means a file you accidentally remove on the live box still survives in the backup — the mirror only ever adds and updates, never deletes. Recovering a file you didn’t mean to lose beats a perfectly faithful mirror of your mistake.
Automate it with a systemd timer
A backup you have to remember to run is a backup you won’t have. A oneshot service plus a timer makes it boring and automatic:
# /etc/systemd/system/app-backup.timer
[Unit]
Description=Periodic off-host backup
[Timer]
OnCalendar=*-*-* 00/6:10:00 # every 6 hours
Persistent=true # if the box was off at the scheduled time, run at next boot
RandomizedDelaySec=120
[Install]
WantedBy=timers.target
# /etc/systemd/system/app-backup.service
[Unit]
Description=Off-host backup of the app DB + media
After=network-online.target
Wants=network-online.target
[Service]
Type=oneshot
Environment=BACKUP_TARGET=backup-host:/srv/backups/app
ExecStart=/srv/app/deploy/app-backup.sh
Nice=10
IOSchedulingClass=idle # never starve the live service for a backup
Persistent=true is the quietly important line: if the machine was asleep or off at the scheduled moment, the run happens at next boot instead of being silently skipped. IOSchedulingClass=idle keeps the copy from stealing disk from the thing you’re actually serving.
flowchart TB
subgraph LIVE [On the live box - server stays up]
direction TB
db[("app.db (live, WAL mode)")] -->|VACUUM INTO| snap["snapshot.db (consistent, self-contained)"]
snap -->|gzip| gz["snapshot.db.gz"]
media[("media / uploads")]
end
subgraph REMOTE [On a second always-on box]
direction TB
keep["db/ : keep newest N, prune the rest"]
mediabak["media/ : append-only mirror, never deletes"]
end
gz -->|rsync over ssh| keep
media -->|rsync -a, no --delete| mediabak
The gotchas that actually bit
The one-liner is easy. The wrapper script around it is where the sharp edges live, and I’ll be honest about where these came from: most were flagged in code review before the script ever ran for real. I put every change through a few automated reviewers, and on this script they earned their keep — the ones below marked (caught in review) were genuine near-misses, not theoretical nitpicks. A couple I added myself, defensively, while shipping.
rsyncto a path with nohost:writes the “backup” right back onto the origin box.rsync ... /srv/backups/app(no colon, no remote host) is a local copy — it cheerfully “succeeds” while protecting you from nothing. The script validates that the target ishost:path, with both halves present, before it runs. (Caught in review.)- A retention count that isn’t a positive integer can delete every snapshot. The prune is “list newest-first, keep N, remove the rest.” If
Ncomes from config as an empty string or a typo, the arithmetic that’s supposed to mean “keep 14” can collapse to “keep zero” — and the cleanup wipes all your backups. The script refuses to start unless the retain count is a positive integer. (This is the failure mode that turns a backup system into a deletion system.) (Mine — added while shipping, the one I’m most glad is there.) - Back up the directory the app actually uses, not the one you assume. Hardcoding
media/when the app is configured to write todata/media/means you faithfully back up an empty folder. Read the real path from config. (Caught in review — this one would have shipped green while backing up zero media.) - Fail loud on a missing config, never silently. The script won’t run while the destination is still the
CHANGE_ME@HOSTplaceholder — a no-op backup that exits 0 is worse than an error, because it looks like success. (Mine.) - Guard the snapshot source. Point the snapshot at the wrong DB path and SQLite will helpfully create a new empty database there — and you’ll back up nothing, perfectly, on schedule. Check the source file exists first. (Caught in review.)
There were a handful more in the same spirit — quoting around paths so a directory with spaces couldn’t make the prune delete the wrong file, a config-reading pipeline that aborted the whole script early under set -e — the unglamorous shell-safety net you only appreciate the day it saves you.
Every one of these is the same trap: a backup that reports success while protecting you from nothing. That’s worse than no backup — it tells you you’re covered when you aren’t.
Restore — and test it before you need it
Restoring the database is mostly “put the file back,” with one WAL-shaped wrinkle:
sudo systemctl stop app
gunzip -c snapshot-<timestamp>.db.gz > /srv/app/data/app.db
# the snapshot is self-contained — delete any stale sidecars from the old DB:
rm -f /srv/app/data/app.db-wal /srv/app/data/app.db-shm
sudo systemctl start app
Because VACUUM INTO already folded the WAL into the snapshot, the restored file is whole on its own; leaving the old -wal/-shm files next to it would confuse SQLite. Remove them.
Then actually verify the thing — ideally the day you set it up, not the day you need it:
gunzip -c snapshot-<timestamp>.db.gz > /tmp/check.db
sqlite3 /tmp/check.db "PRAGMA integrity_check; SELECT count(*) FROM <a table you know>;"
integrity_check should print ok, and the row count should look like your data. I run exactly this check on every snapshot, so I know the files coming off the timer are intact. What I’ll admit I haven’t done yet is the full dress rehearsal: restoring a real snapshot into a fresh database and bringing the service up on it. That’s the test that actually counts, and it’s the next thing on my own list. Until I’ve done it, the backups are an educated guess — a well-checked one, but a guess.
The honest limits
This is the practical, self-hosted tier, not continuous replication. Worst case you lose whatever changed since the last run — six hours, with this timer. Six divides cleanly into the day, and four snapshots a day is already more than my writing strictly needs: I publish a post or two daily. The sweep also picks up drafts in progress and any reader comments between posts, so it’s not only finished posts at stake. If I’m honest, six hours is a little wishful about how much happens here on a given day — but it’s wishful thinking that costs almost nothing to run, so I’d rather err generous. If losing six hours of writes would ever cost you real money or real pain, that’s your cue to move up to streaming replication (Litestream ships the WAL to object storage for near-zero loss).
This is also where the tailnet quietly earns its place. The live database sits on a cloud VPS; the backup lands on a little NUC at home, on a completely separate network. Those two machines share no LAN, no data center, no city — but on the tailnet they reach each other as if they were one rack apart, so the rsync over ssh just works, and the backup box never needs a public IP or an open port to the internet. That’s real separation: a dead VPS, a disk failure, a fire in one location — none of them take both copies at once.
What it still doesn’t cover is the rarer stuff. One operator account, compromised, could reach both ends, and each side is a single machine rather than a redundant pair. For a personal blog that’s a sensible place to stop — genuinely off-site and off-network, with one person’s blast radius left as the honest, known gap. Know which failures your setup covers, so you don’t lean on it for the ones it doesn’t.
And one thing that’s easy to forget: a backup inherits the sensitivity of what it contains. If your database has user emails, comment IPs, anything private, every off-host copy carries that too. The backup target deserves the same access control as the origin — it’s a second copy of everything sensitive you have, sitting on another machine.
Aside. I’d be overselling it to call this disaster-proofing. The honest reason is smaller: off-host backup was a good excuse to stand up another little service, and I learn by building things. And there’s a quieter truth under that — this blog is mostly for me. If I had to pay a monthly fee for every service it leans on, I’m honestly not sure I’d bother. But self-hosting turns each of those into a snapshot command, an rsync, and a systemd timer — free for an afternoon and some elbow grease — and “an excuse to tinker” is all the justification I need. I’d rather understand a thing than rent it.
# comments (0)
no comments yet — be the first.