On 30 Mar 2011, at 1:46pm, Mikael wrote: > If yes, we have the solution needed already now - app/OS crashes won't crash > the database, and rsync backups will create working backups even if made > during checkpoints.
But killing the power (or pulling out the hard disk connector) while SQLite is writing to the disk will always cause corruption, no matter what you're doing. No matter how many components you double for fault-tolerance, you still end up with one file written to one hard disk. > If no, I suppose we need to put some kind of file versioning system under > the database file so we can rewind the database file to right before the > last unfinished checkpoint, or we need to use the online backup API to > create intact backups that we then rsync to external media. SQLite has its own rescuing procedure. When you issue _open() SQLite checks for an indication that the database was not closed neatly. For instance, it looks for log files which should exist only if _close() was not called, and checks other signs in the database for half-written transactions. If it sees anything amiss it does its best to restore the database to a fit state before it returns from the _open() call. Do not rsync a database file while it's open and presume that the result will be a neatly-closed database. If you do work out a solution that copies an open database file, then copy all its journal files at the same instant -- possibly best to copy everything from the folder at the same time. That way the reconstruction routines will have a consistent set of files to work from. Better still to use the backup API, as you mentioned. > Though, there doesn't seem to exist any user/application-level > versioning system ready for production, and the online backup API is not > incremental but only for the whole database making backups expensive and > thus we'd do them seldomly. > Possibly I could do all checkpoints from another OS process than the > one where database reads/writes are done, zeroing the impact of app > crashes on database integrity, and leaving OS crashes as the only possible > reason to corrupt the database. I think that internal workings of SQLite mean that most of those procedures won't do much good. You either need to copy the database file while it's closed, or use the backup API. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
