Re: [sqlite] Backup and integrity check questions
> Rowan Worth Thu, 26 Jul 2018 22:02:50 -0700 > > On 26 July 2018 at 05:56, Rune Torgersen wrote: > > > The databases have been opened with two connections (one for reads, one > > for writes), and use the following options: > > sqlite3_busy_timeout(mDbConn, 500); > > sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0); > > > > Surely this effectively reduces your number of connections to one? No, both connections are from within the same application, and have seemed to work just fine for about 10 years now... > > PRAGMA quick_check is faster, but not as exhaustive as integrity_check. > > There's more efficient ways to copy a DB than the backup api, but they > involve either downtime as Simon said, or some risk in correctly managing > locks. The application has uptime sometimes measured in years, so closing it down to backups are not feasible. > It's not clear whether you have another process calling sqlite3_backup_* or > that is done by the application itself. The latter can be more efficient as > sqlite will not have to restart the backup if the application updates the > DB (any writes to a DB are automatically propagated to in-progress backups > within the same process). backup is done from a separate thread within same process. Same with the integrity check Rune Torgersen Innovative Systems LLC ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Backup and integrity check questions
We have an application that has multiple sqlite3 databases (30-40) open with exclusive locking. Every night we do a database backup and a database integrity check. The backup is done using sqlite3_backup_*. The check is done using a "PRAGMA integrity_check;" Currently we allow reads/writes while doing both. Management wants to know if we're doing it correctly, or if there are faster/easier ways to do backup (and check). Also wants to know if a backup done using the live backup API gives us an exact copy (with any possible corruption) or if the backup is regenerated. The checks are done to detect some issues we have had with corrupted databases (we think we fixed the issue, but are running the checks to make sure). The databases have been opened with two connections (one for reads, one for writes), and use the following options: sqlite3_busy_timeout(mDbConn, 500); sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0); sqlite3_exec(mDbConn, "PRAGMA synchronous = NORMAL;", 0, 0, 0); sqlite3_exec(mDbConn, "PRAGMA journal_mode = TRUNCATE;", 0, 0, 0); sqlite3_busy_timeout(mReadDbConn, 500); sqlite3_exec(mReadDbConn, "PRAGMA read_uncommitted = 1;", 0, 0, 0); Thanks, Rune Torgersen Innovative Systems LLC. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
