Re:SQLite is not a toy database
by nayuki on Thursday March 25, 2021 @01:28PM
(#26582984)
I don't think you can do naive file copy backups even in WAL mode. WAL will checkpoint the log into the main database file from time to time. You need to use the SQLite backup API to let you make a backup while blocking any potential checkpointing.
[ Parent
| on HN ]
Re:SQLite is not a toy database
by formerly_proven on Thursday March 25, 2021 @05:01PM
(#26585325)
Sorry, I was unclear. I meant the "copy a few pages then sleep for some time to release the database lock" part. With WAL mode you should just be able to copy as fast as you can without disturbing other readers or the writer. SQLite also has a similar SQL built in, VACUUM INTO. That's nice because the backup API just copies all pages (iirc).