Thanks for the tool Clemens!
> No, this is what makes the backup restart. With a step size of -1, it
would never restart.
Hmm, given WAL mode, this brings another question: why the sqlite CLI does
not do that but uses 100 pages?
Is using a backup_step N parameter only useful if you want to provide some
progress indicator or pace the backup? (ie. pace I/O bandwidth ?)
Or in other words, given WAL mode, what do we "lose" when using -1 and to
copy everything at once during a backup?
On Tue, Oct 4, 2016 at 5:33 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
