Staging Sqlite Database Files Reliably

I have two SQLite database files: data.db (Production) data.db.tmp (Staging) Both databases are in WAL journaling mode. Additionally, the staging database in in exclusive locking

Solution 1:

Your command expands to:

mv data.db.tmp data.db.tmp-wal data.db data.db-wal

Anyway, multiple file system operations are not atomic.

---

To get rid of both -wal and -shm files, change the journal mode to DELETE; you can change it back later. Once you have a single database file, you can execute an atomic rename to replace it.

Please note that WAL mode optimizes for writing; it would be a better idea to leave the (read-only) production database in rollback journal mode.