Atomic SQLite snapshots over rsync without locking writers
Why copying a live WAL database fails and how to stage a clean read-only export.
Copying an active SQLite file directly over rsync is a classic way to produce a
corrupt archive. Even when the main file looks intact, recent transactions may still live in
the accompanying -wal and -shm files, and a mid-copy checkpoint can
tear pages across the transfer.
Stage into a fresh file and atomically replace
Instead of syncing the live write database, have a background maintenance loop periodically
attach the database with ?mode=ro, copy only the columns you need into a temporary
SQLite file in the same export directory, call os.chmod, and atomically swap it
into place with os.replace. Because the reader sees either the previous complete
snapshot or the new one, rsync --checksum never observes a torn file.
Keep the pull account separate
Place the exported snapshot in a dedicated directory owned by the app user with group read-only permissions for the SSH pull account. The remote host needs zero credentials to push data back, and the pull side never touches raw operational tables.
Did this work on your setup, or did you spot a mistake? Send the editor a private note or leave a public note for the next reader.