Backing Up an Active Database Is Never Just Cp
Copying an active SQLite file is not a backup. Lessons from a light backup script: hot backup, verified atomic tar, and layered pruning.
TL;DR
The script backs up SQLite databases using the official .backup API instead of raw cp, since copying active WAL files can yield corrupt snapshots. Archives are staged, verified with tar, then atomically renamed, with flock preventing overlap. Pruning keeps 14 days plus a floor of 3 newest archives, so failures can't silently destroy valid backups.
My ~/.hermes/backups folder now looks consistent: a tar.gz archive of roughly 90MB every two days, with old ones removed automatically. It looks trivial, but the script behind it, hermes-light-backup.sh, was born from one classic mistake I almost made: treating a copy of an active SQLite database file as a backup. The two are far apart, and the gap only shows up when you need it most.
Why Raw Cp Can Turn Evil
An active SQLite file is not a static file. In WAL mode, writes land in the wal file next to the main database first, so the main file plus wal is one moving unit. Copying both with cp at the wrong moment can produce a mixed snapshot of old and new state. SQLite has an official answer: the Online Backup API, which copies in steps and only locks the source briefly while reading [5]. The result is a snapshot bit-for-bit identical to the database at the start of the copy [5], and even if another writer commits mid-copy, the result is still a consistent snapshot [5].
The script uses the simplest form of that API: sqlite3 file.db .backup target.db. If the sqlite3 binary is missing, it falls back to cp, consciously accepting that the fallback is lower quality. I take that trade-off because the fallback only runs on machines that never got sqlite3 installed.
Verify First, Then Rename
The archive is written to a temporary name ending in .tmp, never straight to the final name. Only after tar -tzf successfully reads the whole archive [7] does the file get renamed to its final name. This small ordering is what keeps the backups folder from ever holding a half-written archive: the atomic rename is the only moment the final name exists.
The process lock uses flock on a separate file descriptor with the non-blocking flag [6]. flock is an advisory lock, meaning other processes are technically free to ignore it [6], but that is enough here: the only processes that respect the lock are other instances of this same script, and that is exactly the overlap we want to prevent. A late cron run does not execute twice; it skips, logs one SKIP locked line, done.
One detail I like: the script writes its log to a separate file, not stdout, matching its silent cron contract. The log is also trimmed, keeping the last 300 lines whenever it passes 500, so the monitoring tool does not slowly become a disk monster itself.
Pruning With a Brake
The auto-delete section was the part I deliberated most. The policy is layered. Backups are kept for 14 days from modify time. Nothing below that age is touched. Then there is a second floor: at least 3 newest archives survive no matter what. The deletable count is also capped, so a prune can never push the total below that floor. And it all runs only after the fresh archive passes verification.
Why so careful? Because backup scripts fail in subtle ways. The schedule can slip for days when a server dies or a partition fills up. A script that prunes without verifying will keep deleting old archives while producing no new valid ones. When you finally need it, the folder is empty or full of junk. A backup without verification plus a prune without a floor is a data-loss recipe that looks sweet on paper.
So the ordering is rigid: lock the process, stage files, copy the database the right way, wrap it into a temporary tar.gz, verify, rename, log, and only then think about deleting. Every step is deliberately boring. It is exactly the boredom that makes me trust this script to run every two days without supervision.