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 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.

Can you speak to the reliability of Time Machine on the Apple File System? In a SQLite application forum, a user argues that it is perfectly safe, describing its function as:

[quote]APFS includes a snapshot mechanism where a snapshot is a read only copy - so not modifiable by any subsequent I/O - of the whole ‘directory structure’ of the system plus the changes to the data since the last snapshot that is created on the system volume. The changes are usually derived from the on-going system journal file of disk changes between the 2 snapshots - or probably more accurately the 2 chosen points in the system journal file.

Note that any data changes to disk are not made in-place but written to free space, so both the old and new copies exist. This happens at the disk block level. As you might guess, snapshots may consume considerable space on the system disk until they are deleted.

Time machine just triggers a snapshot and then copies the read-only copy to it’s backup disk in it’s own time, and in combination with previous snapshots, it has enough information to restore a system disk to the state as of the chosen snapshot.[/quote]

and

[quote] Time Machine doesn’t copy the snapshot as it is being updated. The snapshot is a frozen version of the current data on the system disk that is effectively made read only and cannot be updated - there is no copy at that stage, it is the original existing data. I suspect - but do not know - that the freeze is achieved by picking the current point in the system journal file. Working back through the journal allows the data blocks that are to be included in this snapshot to be selected. Any subsequent file updates get written elsewhere to disk using free space and will be included in the next snapshot. The frozen snapshot that exists logically on the system disk is then copied in no great hurry - for example, it only uses an efficiency core (low performance core) on an Apple silicon Mac - to an external Time Machine volume. [/quote]

Such systems are better than normal live copies, but they still have one bad effect: a backup and restore pair is indistinguishable from the host application crashing at the time of the snapshot and then restarting, from SQLite's perspective.

With WAL, you can expect the journal to be played back when the application opens the restored database, but uncommitted data will be lost.

It's worth noting here that macOS uses SQLite extensively. Try this sometime:

   $ find ~/Library -name \*-wal | grep com.apple

Then watch it scroll!

If I add a "-c" to that grep, I get 4033 here. If I drop the filter for Apple programs, it goes up even further, though how high it goes depends on the set of non-Apple programs running on the machine at the time.

Realize that this counts only open databases, plus databases of programs that have crashed and left the WAL file laying around. You can expect still more databases belonging to programs that closed them gracefully, leaving them in a settled state, without the WAL file. To distinguish those cases, you'd need to filter the list of files through file(1) or similar, since there is no common naming pattern for SQLite databases.

The description of Time Machine is accurate (allowing for some glossing over of technicalities which are difficult to explain) but it doesn't really matter. It doesn't change anything since what Keith wrote above still applies.

If you need a perfect, uncorrupted copy of the database, as it was in a frozen moment in time – i.e. to professional standards – don't try to take a backup while you're making changes to the database. At minimum

1. Don't start the backup until all transactions are closed.
2. Don't open a new transaction until the backup is finished.

However, if you don't insist on a clear understanding of when the data was backed up, and really just want something that looks like a copy taken at roughly the time of the backup, don't worry too much about this. SQLite has automatic recovery of partly-updated databases. If it tries to open a database that has been partly updated with a new transaction it will recover to the point either before or after the transaction. You just can't predict which.

What Simon said, you can very well get a database backup with a partial transactions and it will be function as if that transaction never started.

One easier method though is, while in WAL mode, do a FULL Wal checkpoint. After which you can copy the main db file, this is a pristine snapshot of the db status right after the checkpoint (as long as no other checkpoints are attempted while the file is being copied, you might want to disable auto checkpoint temporarily if it is on right before you do this)

From yours and Warren Young's replies, it seems that one can trust Time Machine's images for the application that is the context in which my question originated (family tree/genealogy).

1. Is there any reduction of that trust for a sqlite application that uses the classic journal, not wal?

2. Is there a Windows equivalent to Time Machine that is similarly "Consistent"? (ref: Keith Medcalf)

The more common question among app users has to do with the risk to a live database being in a cloud-sync folder such as Dropbox. The developer says "don't" while there are users who have done it for years without perceived issue, for backup and/or for working from an alternative computer. I noticed a couple of years ago that MS OneDrive suspends backup of the app's sqlite database as long as the app has it 'open' and it seemed to be exceptional then and may still be now. And such suspension was true for other sqlite database systems. Perhaps this should be a new thread but the fundamentals are the same, I should think.

1. Are there cloud-sync systems in whose local drive folder you would be comfortable having an app's live sqlite database?

Thanks to all who have replied so far.

The developer says "don't" while there are users who have done it for years without perceived issue, ...

Those two fact sets are not in conflict. I could build an airplane with plain steel control cables, then say: "Do not fly this after 3 years without a thorough inspection." Somebody else could say: "I've flown it without trouble for 10 years here in the Sonoran Desert with nary a glance at control cables." Yet another might say: "Hmm, I wonder if this loss of pitch control is related to keeping this thing stored near a Pacific coast beach for the last 8 years. Curse that fellow who boasted of 10 trouble-free years!"

There is a difference between "designed for reliable operation under conditions U and V" and "not designed for reliable operation under conditions other than U and V".

Is there a Windows equivalent to Time Machine that is similarly "Consistent"

Most any Windows backup solution, including the built in one, will use Volume Shadow Copy Service. VSCS is similar to APFS's Snapshot, providing the backup software with a consistent point in time copy of a database file (along with the journal files) regardless of what's going on on the machine during the backup operation.

while there are users who have done it for years without perceived issue

Cool. That doesn't mean my SQLite database on Dropbox wasn't corrupted beyond hope anytime I did a specific action that caused Dropbox to helpfully make a mess of matters.

For a while I used two different PCs to maintain an sqlite database on Dropbox. The worst thing that ever happened was getting a conflicted copy on one or the other when I forgot to shut down the app on one before starting up the other. Figuring out what mods were in which copy was not trivial, but there was no corruption (for the proper definition of corruption--meaning both PCs could read the files, but with inconsistent data).

Gerry

Is there a Windows equivalent to Time Machine that is similarly "Consistent"? (ref: Keith Medcalf)

Yes. Most backup software on Windows can produce "crash consistent" backups using either Windows builtin or a proprietary "write-aside" snapshot system. If the backup software claims that it makes "consistent" backups including of "open files" then it is using some form of snapshot/write-aside.

The builtin backup stuff in Windows (since 2002 or so) does use VSS to create "crash consistent" backups. Adstar (ADSM) uses proprietary (or MS VSS) write-aside to make "crash consistent" backups.

Some software that does online backups (Adstar, Acronis, etc) produces "crash consistent" backups and can even (optionally) tell software that has "hooked into" the VSS layer (such as the OS itself and some applications (Exchange Server, SQL Server, a few others) so that the application can "checkpoint" and "quiesce" while the snapshot is in progress (so that you get "shutdown consistent" backups, which you normally can obtain only be gracefully stopping all applications, shutting down the OS, and doing a backup while the machine is not running).