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.

Related edition