On 10 Dec 2014, at 02:36, Simon Slavin wrote: > > 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.
All the processes would have automatic checkpointing disabled. Just the backup process would perform the checkpoint. >> 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. Thanks for the info, Simon. Regards Nick _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
