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
restarts.
Regards,
Clemens
--
#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],
sqlite3_errmsg(src));
goto error;
}
rc = sqlite3_open_v2(argv[2], &dst, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL);
if (rc != SQLITE_OK) {
fprintf(stderr, "Cannot open %s: %s\n", argv[2],
sqlite3_errmsg(dst));
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",
sqlite3_errmsg(dst));
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));
}
error:
sqlite3_close(dst);
sqlite3_close(src);
return ok ? 0 : 1;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
