Eric Grange wrote:
> If all else fail, I could also suspend DB writes during backups (suspending
> DB reads would be more problematic).

With WAL, the backup reader does not block writers.

>> Use the backup API, and copy everything in one step.
>> (The restart-on-write feature should not be necessary with WAL.)
> That was what I thought initially, but I can only explain the multi-hours
> backups with it: usually the backup API takes 4-5 minutes. It is just once
> in a while that a very long backup occurs.
>> It calls sqlite3_backup_step() with a size of 100 pages.
> Ok, so I guess the huge cache is overkill with the default CLI!

No, this is what makes the backup restart.  With a step size of -1,
it would never restart.

Use a tool like the one below to do the backup in one step, without


#include <stdio.h>
#include <sqlite3.h>

int main(int argc, char *argv[])
        sqlite3 *src = NULL;
        sqlite3 *dst = NULL;
        sqlite3_backup *backup;
        int rc;
        int ok = 0;

        if (argc != 3) {
                fputs("I want two file names: source, destination\n", stderr);
                goto error;

        rc = sqlite3_open_v2(argv[1], &src, SQLITE_OPEN_READONLY, NULL);
        if (rc != SQLITE_OK) {
                fprintf(stderr, "Cannot open %s: %s\n", argv[1], 
                goto error;

        rc = sqlite3_open_v2(argv[2], &dst, SQLITE_OPEN_READWRITE | 
        if (rc != SQLITE_OK) {
                fprintf(stderr, "Cannot open %s: %s\n", argv[2], 
                goto error;

        sqlite3_exec(src, "PRAGMA busy_timeout=10000", NULL, NULL, NULL);
        sqlite3_exec(dst, "PRAGMA busy_timeout=10000", NULL, NULL, NULL);

        backup = sqlite3_backup_init(dst, "main", src, "main");
        if (backup == NULL) {
                fprintf(stderr, "Cannot initialize backup: %s\n", 
                goto error;

        do {
                rc = sqlite3_backup_step(backup, -1);
        } while (rc == SQLITE_BUSY || rc == SQLITE_LOCKED);

        rc = sqlite3_backup_finish(backup);
        if (rc == SQLITE_OK) {
                ok = 1;
        } else {
                fprintf(stderr, "Backup failed: %s\n", sqlite3_errmsg(dst));


        return ok ? 0 : 1;
sqlite-users mailing list

Reply via email to