Clearing out the WAL
(1) By Paul Moore (pf_moore) on 2022-09-01 16:25:58 [link] [source]
I had a database recently that I was doing some large updates to. Unfortunately, part way through the job, it crashed. Of course, the data was fine, but I now had WAL files along with the main .db file, as these hadn't been cleared up after the crash.
The only way I could find to merge the data in the WAL back into the main database was to temporarily take the database out of WAL mode and then put it back. I didn't want to just keep the files around until I next did an update, as I wanted to ship a single file (the partially-loaded data was fine, and the job took ages, so restarting it at the time wasn't practical).
Is there a better way to tidy up the WAL files, or is that a reasonable way to do this? I love WAL mode (not least for the ability to continue querying the database while updates are running) but being able to ship a singe file is important to me.
(2) By Wout Mertens (wmertens) on 2022-09-01 21:15:03 in reply to 1 [link] [source]
It's enough to open and close that database, this will incorporate the WAL files.
I always do sqlite db.sqlite3 .schema, which is sufficient.
(3) By Paul Moore (pf_moore) on 2022-09-02 12:19:27 in reply to 2 [source]
Oh, thanks. I thought I'd tried that and it hadn't worked. I must not have done so - my mistake.
(4) By Stephan (stephancb) on 2022-09-02 13:24:21 in reply to 1 [link] [source]
An alternative is
PRAGMA wal_checkpoint(TRUNCATE);