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 ;(

Answer 1

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.