> Have you looked at the ext/session extension ? Yes, but it is a bit more complicated to integrate, and would impose a penalty during execution as far as I understand: there are quite a few intermediate states that would be stored by a changeset, but that do not really need to be preserved at the end of the day.
Those intermediate states could be moved to a secondary DB, but would then lose foreign keys and other integrity constraints.... This is however an extension that I have been looking at, should the need for some form of "live" db replication occur. > I'm sorry. I misunderstood your question and thought you were just duplicating the file using OS calls. The SQLite backup API takes care of all necessary locking and > consistency problems for you. You should be fine. Ok, thanks! > The problem of backing up a changing database is one of the difficult problems in database management. Please don't expect an easy solution. Right now I am tackling it with brute force: a backup usually takes about 4-5 minutes (to a secondary SSD, that offline copy is then uploaded to a rempte server). It is mostly the odd occurences when the backup takes several hours that are problematic. If all else fail, I could also suspend DB writes during backups (suspending DB reads would be more problematic). > 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! Thanks On Tue, Oct 4, 2016 at 4:05 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 4 Oct 2016, at 2:53pm, Eric Grange <zar...@gmail.com> wrote: > > > I am going on the assumption that if something fails during backup, the > > backup itself will be toast anyway, but is that safe otherwise? > > No. You have no locking. You might copy the beginning of the file before > a transaction and the end of the file after it, meaning that pointers at > one part of the file point to things which no longer exist. > > The problem of backing up a changing database is one of the difficult > problems in database management. Please don't expect an easy solution. > > Simon. > _______________________________________________ > 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
