On 6 May 2011, at 3:05pm, Jaco Breitenbach wrote: > On 6 May 2011 14:15, Igor Tandetnik <[email protected]> wrote: >> >> >> http://sqlite.org/backup.html >> > > I've also looked at that page. However, that either involves modifying the > main application or writing a separate backup utility application based on > the examples provided.
Writing a backup utility is what I'd expect to be best in most situations. > One thing that I've noticed, is that if the running database that is being > backed up is continuously being written to, then following a call to > sqlite3_sleep(), the backup will automatically be restarted. This seems to > indicate that my backups might never complete. A backup using the backup API must be restarted if there is a change to any database file. This is to ensure that the file is consistent. As you can imagine it's almost impossible to backup a file which is continuously being written to, no matter what method you use. The backup API does its best to take advantage of any short time when an update is not happening. > Would a simple copy of the database, -shm and -wal files result in a > corrupted database if these files are used to restore the system, or would > it simply appear as if SQLite is recovering from a power failure? When the database file is closed properly, there are no -shm or -wal files. The mere fact that these files exist is a sign that the database is probably not in a consistent state, and that some rescue procedures will be needed. SQLite does its best to recover a state between transactions when this happens but obviously this depends on partially-written data in the journal files. > Sure, > there may be a small amount of data loss in doing the copy without a lock, > but that may be acceptable so long as the data that is in the database is > not corrupted. It will work properly only if all those files are copied at the same time. Unfortunately you can't depend on this. For instance, a simple command to your shell to copy those files will copy one file first, then start copying the second file when the first is complete. So you might copy the database file (presumably very long, taking perhaps a few seconds to copy) and then a journal file which corresponds to a few seconds later, by which time several other transactions may have been executed. If SQLite encounters a situation like this it will usually be able to recover gracefully, but if you make this a part of your normal operating procedure, chances are you'll sooner or later get some sort of unpredictable corruption somewhere. There are several ways you can get copies of your data which will be safe and consistent and uncorrupted. (a) Stop your normal application in the normal way. It will close the connections to the database, which will delete any journal files, leaving you with just one database file to copy. Copy it however you want (rsync, copy, backup, whatever) and restart your application. (b) Use the SQLite backup API, which will present you -- as soon as it has had long enough to do the copy safely -- with a copied database file under a different filename or directory. This is specially designed for 24/7 operations, with SQLite sneaking the copy in as soon as it won't interrupt normal business. Usually at 3am in the morning. (c) Freeze your normal applications, which will leave you with the database file and maybe some journal files, but they will all be consistent with one-another. Copy database and any journal files and keep them all together. Unfreeze your normal applications. When SQLite tries to connect to the backups it will notice the journal files and unfinished changes, do whatever needs to be done to restore to a savepoint or transaction boundary, and leave you with a safe and consistent database. (d) The same as (c) but instead of freezing the apps, crash them and restart them once the copies have been made. In this case you chance losing a partly finished transaction even in the normal copy of the database. A lot of work went into making SQLite very good at handling situations (c) and (d), but obviously one of the other two are the 'proper' ways to do things. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
