how to backup a locked DB
Is there any way to backup a locked DB from the command line utility?
.backup doesn't seem to work, as it complains: Error: database is locked
(2) By David Raymond (dvdraymond) on 2020-04-27 17:29:27 in reply to 1 [link] [source]
Not that I'm aware of, no. If it's locked to the point where you can't read it with the library, then odds are decent that another process may be writing to the file at that moment. And trying to backup a file that's actively being written to is probably a bad call.
Okay that's pretty strange... any other DBMS I can think of allows to do online backups.
You would probably be able to backup while a write is ongoing if you are in the WAL journaling mode. As it allows readers to continue even when a writer is in progress, unlike the default journaling mode.
(5) By David Raymond (dvdraymond) on 2020-04-28 12:31:20 in reply to 3 [source]
With an online DBMS there's a master server process, and your backup request goes to the server process. That server process is the only thing that touches files, and it can coordinate with all the other requests going on, because it has complete overlord power and doesn't have to worry about rogue processes jumping in and trying to do stuff with the files. A server DBMS might use row locking, or table locking, etc. along with various other methods of concurrency control that let it do a consistent backup while others reading and writing requests are going on at the same time.
With SQLite there is no overlord server process to coordinate things. Everyone is accessing the database files directly themselves, not requesting someone else to do it and send them the results. The only lock on a SQLite database is for the entire database. If you're writing, then nobody else is.
WAL mode means that readers and writer (still singular) can coexist, and as mentioned should probably be your first option. WAL mode has some restrictions, notably that all the processes accessing a database have to be on the same computer. If that's ok, then the library should be able to open a read connection at any time to make a backup from.
(Barring of course anyone explicitly opening the database in exclusive mode, like I think Firefox does with its SQLite files when it's running)
Without having a system right now to test it on, I believe that last point is key. If it's got an exclusive lock you'll get that error. If not, the backup system will restart if anyone writes to it but it should proceed.
Sure. And if you do it while the data is being changed you get a corrupt backup.
The technical term is "crash consistent".