SQLite Backup Strategy

Question

I'm trying to backup my sqlite database from a cronjob that runs in 5 minute intervals. The database is "live", so there are running queries at the time I want to perform the backup.

I want to be sure, that the database is in good shape when I backup it so that I can rely on the backup.

My current strategy (in pseudocode):

function backup() { #try to acquire the lock for 2 seconds, then check the database integrity sqlite3 mydb.sqlite '.timeout 2000' 'PRAGMA integrity_check;' if (integrity is ok and database was not locked) { #perform the backup to backup.sqlite sqlite3 mydb.sqlite '.timeout 2000' '.backup backup.sqlite' if (backup could be performed) { #Check the consistency of the backup database sqlite3 backup.sqlite 'PRAGMA integrity_check;' if (ok) { return true; } } } return false; }

Now, there are some problems with my strategy:

If the live database is locked, I run into problems because I cannot perform the backup then. Maybe a transaction could help there?
If something goes wrong between the PRAGMA integrity_check; and the backup, I'm f*cked.

Any ideas? And by the way, what is the difference between the sqlite3 .backup and a good old cp mydb.sqlite mybackup.sqlite ?

[edit] I'm running nodejs on an embedded system, so if someone suggests the sqlite online backup api with some ruby wrapper - no chance ;(

PRAGMA integrity_check; passes sometimes even though database is bad... I was curious what strategy worked for you? Thank you. — mgh
– mgh, Commented Dec 10, 2022 at 23:49

CL. · Accepted Answer · 2013-04-19 19:20:37Z

If you cannot use the backup API, you must use another mechanism to prevent the database file from being modified while you're copying it.

Start a transaction with BEGIN IMMEDIATE:

After a BEGIN IMMEDIATE, no other database connection will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however.

pdw · Accepted Answer · 2013-04-19 13:49:24Z

If you want to backup while queries are running you need to use the backup API. The documentation has a worked out example of an online backup of a running database (example 2). I don't understand the Ruby reference, you can integrate it in your program or do it as a small C program running besides the real application -- I've done both.

An explicit integrity_check on the backup is overkill. The backup API guarantees that the destination database is consistent and up-to-date. (The flip side of that coin is that if you update the DB too often while a backup is running, the backup might never finish.)

It is possible to use 'cp' to make a backup, but not of a running database. You need to have an exclusive lock for the entire duration of the backup, so it's not really 'live'. You also need to be careful to copy all of sqlite's temp files as well as the main database.

I'd expect the sqlite3 ".backup" command to use the backup API.

Collectives™ on Stack Overflow

SQLite Backup Strategy

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related