CODEWITHSUNDEEP
sqlitebackupdatabase-backups
David Müller
David Müller

Reputation: 5351

SQLite Backup Strategy

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:

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

Upvotes: 2

Views: 7258

Answers (2)

CL.
CL.

Reputation: 180310

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.

Upvotes: 3

pdw
pdw

Reputation: 8901

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.

Upvotes: 4

Related Questions