Backup via file system backup software
I have a webapp on a machine implemented as a persistent process which pools open sqlite database connections without closing them on a WAL-mode database.
The machine's file system is backup daily using file system backup software (namely Borg).
Can I assume the backup software will always snapshot a consistent database state or do I need to use one of the various available means (sqlite3 tool, vacuum, backup API) to periodically make a separate, stable, database file that can be picked up by the backup software ?
(I'd rather not do that for space reasons)
Unless you have taken "special steps" to "snapshot" the filesystem or even specific files, the backup software states in its documentation that it does not do anything to "snapshot" a file or the filesystem and therefore not only may files be internally inconsistent, but that the filesystem is not consistent.
This is in contrast to backup software which operates on a "snapshot" of the filesystem that is maintained consistent from the start of the backup of that filesystem to the end of the backup of that filesystem (this is called "Crash Consistent" in that the filesystem backup is fully consistent as if the machine were powered off suddenly (by flipping off the power switch) and then backing up the filesystem. Crash Consistent backups depend on the ability of the filesystem to "fix itself", and any running software to "fix its files" in the event of power failure.
In practice this is usually done by at some point in time suspending all physical writes to a filesystem so that it may be "backed up" in a stable state. (Microsoft calls this VSS -- Virtual Snap Shot)
Some scheme's will have a method of telling running software to checkpoint itself to a stable state before suspending writes to the physical media in order to ensure that the backup is the equivalent of "Checkpoint Consistent" or "Shutdown Consistent".
If you have not taken any steps to ensure that the backup is "Crash Consistent", "Checkpoint Consistent", or "Shutdown Consistent" then you will need to periodically backup the database to a "dump file" and backup the dump file (since that will be internally consistent as of the time the dump ended).
The SQLite3 library does not contain any code to interface with external consistency managers on any platform, so the best backup that one can hope for is a Crash Consistent backup, however, that is not something that is under the control of either SQLite3 or (apparently) the backup software (Borg).
Note that you can achieve consistent backups by manually quiescing the application before doing the backup and continuing when the backup is complete.
SQLite3 has no internal code to do this either. Your application would have to know how to do that itself.
You cannot backup an open, changing, SQLite database and depend on getting a perfect uncorrupted backup. Because your backup doesn't take a snapshot of the database and its journal file all in the same instant, you will get inconsistencies.
Either close all writing connections to the database while it's being backed up (standard simple way to do it, takes no extra space or time), or use the backup API to take a copy of it, and backup the copy.
You do not need to close the connections.
You do however need to make sure that all write transactions (and read transactions if necessary) are ended and that a checkpoint has processed all journal pages and reset the journal.
Once this state (a full checkpoint) is obtained a backup of the database files will be internally consistent.
You must not permit "write" operations until the backup is complete although you may "read" as much as you like.
Ok that seems a bit too tricky. I was just wondering if the "admin free" aspect of sqlite extended to backups :-)
I'll simply follow the way I usually do with other dbms, which is to dump the db before starting the backup.
So I guess it should be sufficent to add:
rm -f mydb.sqlite3.backup sqlite3 mydb.sqlite3 "VACUUM INTO 'mydb.sqlite3.backup'"
to the backup script.
Thanks for your answers.