On Wed, Mar 30, 2011 at 02:53:22PM +0100, Simon Slavin scratched on the wall:
>
> 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.
That's not exactly true. The file is in an unknown state, which may
or may not be corrupt. More to the point, SQLite does its best
to make sure that the database, as a whole (including the main file
and the journal/WAL files), can be recovered back into a known and
non-corrupt state.
> No matter how many components you double for
> fault-tolerance, you still end up with one file written to one hard disk.
Without getting into a big argument over what exactly fault-tolerance
is or isn't, again, this isn't always true. Striped disks and
battery backed write-caches are not all that unusual.
> If it sees anything amiss it does its best to restore the database to
> a fit state before it returns from the _open() call.
And the _open*() call will fail if it cannot restore things. This is
why, for example, you need write permission to open a database
read-only. If a recovery is required, write permissions are needed.
> 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.
Of course, it is almost impossible to copy everything "at the same
time" since this needs to be a fully atomic operation.
If you're using the older journal file, you can simply open an
exclusive transaction. This will lock the database file, allowing it
to be copied with any standard program. You can then rollback the
open transaction and allow things to continue. It's easy, but it
also locks all writes until the copy finishes. That's why the backup
API is there... it allows copies without full locks.
Using WAL, things get a bit more complex. I haven't played with it
enough to offer specific advice, but your general idea of running a
checkpoint to completion, and then holding the lock while things are
copied, should work.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
