On 10 Dec 2014, at 12:30am, Nick <maill...@css-uk.net> wrote: > That's interesting Simon I didn't expect the database not to be trustworthy.
The database will be trustworthy at any instant. Your copy of it will be corrupt because the file will be changing while you are copying it. > In WAL mode I thought the database file is only written to when > checkpointing. Have I misunderstood this journaling mode? How do you intend to prevent your other processes from checkpointing while you take the backup ? You can disable checkpointing for your own connection to the database but not for the connections other processes have. > Again I may have misunderstood the docs around the Backup API, does it not > start again from the beginning copying pages if another process writes to the > database during the process? In practice could it successfully backup a 2GB > database that is being written to once a second? Not if the writing never stopped. But there's no way to take a copy of a file which is constantly being rewritten. rsync can't do it either. How can anything copy a file which is constantly being modified ? You can BEGIN EXCLUSIVE and then END once your backup is finished. That should prevent other processes writing to the file. You will have to deal with what happens if your BEGIN EXCLUSIVE times out, and you will have to put long timeouts in your other processes so they can handle the file being locked long enough for the entire copy to be taken. That's the only way I can think of to do it. And yes, it will prevent writing to the database while it's being copied. On the other hand, there's a different way to clone a database: log the changes. When something issues an INSERT/DELETE/UPDATE command, execute the command but also append a copy of that command to a text file somewhere. When you want to bring your backup copy up-to-date, take a copy of the log file, then execute all the commands in it to your out-of-date copy. You need a method of zeroing out the log file, or knowing where you got to on your last backup. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
