Database backup with WAL

#1 · 2025-11-08, 10:42 pm

I haven't seen a problem, but I'm wondering if anyone has attempted to test the RM backup command now that the database is running in WAL mode.

I've read that there is a pragma to make SQLite incorporate all outstanding committed transactions in the write ahead log. I'm just wondering whether running RM backup properly catches all the latest transactions even when Backup is run not on app exit.

Richard

#2 · 2025-11-09, 5:18 pm

Quote from thejerrybryan on 2025-11-09, 5:18 pm
From what I have read, the backup should "just work" under these circumstances.
Let's take a different example to start with. Suppose your computer crashes with RM open and with a WAL file that is not empty. Then suppose you reboot your computer and bring up your favorite SQLite manager and open your RM database. I think one of two things will happen.
When your SQLite manager opens your RM database, the file opening will trigger a checkpoint. The checkpoint will merge the WAL file into the RMTREE file. Any queries or updates you run from your SQLite manager will "just work". The fact that a checkpoint took place will be transparent to your SQLite manager.
When your SQlite manager opens your RM database, the file opening does not trigger a checkpoint. In this case, any queries or updates you run from your SQLite manager will "just work" anyway. Any queries will read from both the RMTREE file and from the WAL file as needed. This process will be handled by the SQLite engine and will be totally transparent both to you as the user and to your SQLite manager. Any updates you apply will be stored in the WAL file. Again, this process will be handled by the SQLite engine and will be totally transparent both to you as the user and to your SQLite manager. When eventually something triggers a checkpoint, your updates that were written transparently to the WAL file will be merged transparently back into the RMTREE file.
If instead, you reboot your computer and open the database with RM, it should function like option #1 above and again "just work". Which is to say that I think a checkpoint will be triggered when RM opens the database. But even if a checkpoint is not triggered by RM opening the database, everything will still "just work". RM will be able to access all its data transparently from both the RMTREE file and the WAL file. The SQLite engine will hide the details from the RM app. Then when RM exits normally, the checkpoint will finally be accomplished, the contents of the WAL file will be merged back into the RMTREE file, and the WAL file will be emptied.
The beauty of the WAL mechanism is that it completely transparent to the app except, of course, that the app can forcibly cause a checkpoint. The checkpoint process is so transparent that an app can even do queries or updates while a checkpoint is in process. So the WAL file can be being added to and emptied out all at the same time.

From what I have read, the backup should "just work" under these circumstances.

Let's take a different example to start with. Suppose your computer crashes with RM open and with a WAL file that is not empty. Then suppose you reboot your computer and bring up your favorite SQLite manager and open your RM database. I think one of two things will happen.

When your SQLite manager opens your RM database, the file opening will trigger a checkpoint. The checkpoint will merge the WAL file into the RMTREE file. Any queries or updates you run from your SQLite manager will "just work". The fact that a checkpoint took place will be transparent to your SQLite manager.
When your SQlite manager opens your RM database, the file opening does not trigger a checkpoint. In this case, any queries or updates you run from your SQLite manager will "just work" anyway. Any queries will read from both the RMTREE file and from the WAL file as needed. This process will be handled by the SQLite engine and will be totally transparent both to you as the user and to your SQLite manager. Any updates you apply will be stored in the WAL file. Again, this process will be handled by the SQLite engine and will be totally transparent both to you as the user and to your SQLite manager. When eventually something triggers a checkpoint, your updates that were written transparently to the WAL file will be merged transparently back into the RMTREE file.

If instead, you reboot your computer and open the database with RM, it should function like option #1 above and again "just work". Which is to say that I think a checkpoint will be triggered when RM opens the database. But even if a checkpoint is not triggered by RM opening the database, everything will still "just work". RM will be able to access all its data transparently from both the RMTREE file and the WAL file. The SQLite engine will hide the details from the RM app. Then when RM exits normally, the checkpoint will finally be accomplished, the contents of the WAL file will be merged back into the RMTREE file, and the WAL file will be emptied.

The beauty of the WAL mechanism is that it completely transparent to the app except, of course, that the app can forcibly cause a checkpoint. The checkpoint process is so transparent that an app can even do queries or updates while a checkpoint is in process. So the WAL file can be being added to and emptied out all at the same time.

#3 · 2025-11-09, 9:56 pm

I guess that I didn't make myself very clear.
What I was concerned about is whether RM does a database wal checkpoint before it zips up the database file.

I can see that RM does not do a

pragma wal_checkpoint(truncate)

when it does a backup (without exiting the app) because the wal file size is not set to 0.
It is probably using one of the other wal_checkpoint options: passive, full or restart.

I got worried after noticing that 11.0.2 won't open a read-only database anymore. I rely on having reference databases set to read-only.

#4 · 2025-11-09, 10:51 pm

I'm not as deep into the inner workings of SQLite as you guys but will contribute observations. RM cannot do a backup/zip creation when the db file is 'open' in an external SQLite manager. If its backup process (which has nothing to do with SQLite) is aware of the file being open by an external SQLite manager, why wouldn't it be aware that the file is 'open' in RM's SQLite engine? If it is 'open' by any instance of SQLite, I think backup would fail to proceed. And the only condition that a WAL file can exist for a db file that RM backup is to operate on is for the file to be 'open' in the same RM's SQLite engine. RM opens and closes the database with each batch of transactions. The WAL is gone when it closes and that's the only time the backup can proceed. If RM crashed and left a WAL extant, the backup procedure is unavailable until RM restarts and reopens the db which processes the WAL to its disappearance before backup is possible or throws a fatal error.

Maybe I made a leap of faith between external and internal but that's my 2 cents.

#5 · 2025-11-10, 9:49 am

Quote from Richard Otter on 2025-11-09, 9:56 pm
...I got worried after noticing that 11.0.2 won't open a read-only database anymore...

Iirc, it never did in the SQLite series of RM versions, unless there was a short exception. It writes something immediately upon or soon after opening, or it used to.

#6 · 2025-11-10, 1:32 pm

Aha! The thrust of my answer was completely wrong. The point I was trying to make (and correctly, I think) is that if you open an RM database using SQLite, it matters not a whit the state of the WAL file. You will get correct results no matter what.

My huge mistake was in assuming that that RM is using SQLite to create its backup file, and of course it is not. As Tom correctly points out, RM is using a zip utility to make the backup file, and the zip utility will be reading the RMTREE file as a bunch of bytes without using SQLite. It therefore will matter greatly whether or not the WAL file has been merged into the RMTREE file at the time the RMTREE file is zipped.

I think what Tom is saying is that RM will have to close the RMTREE file before invoking the zip utility to do the backup, and that closing the RMTREE file will invoke any necessary final SQLite checkpoint to merge the WAL file into the RMTREE file. If so, then all should be well for backups taken from within RM itself.

It's a bit off topic for this thread, but my understanding is that in WAL mode, the RMTREE file itself is pretty safe to be backed up or synced live by things like Dropbox and OneDrive and Carbonite and BackBlaze. But the WAL file and the SHM if anything are even more unsafe being synced or backed up live than the RMTREE file used to be in JOURNAL mode. Of course, the WAL file and the SHM file are in the same folder as the RMTREE file. The overall effect of WAL mode is therefore is to make RM overall less safe than it was before in a live backed up or synced environment.

kevync has reacted to this post.