On Wednesday, 6 March, 2019 04:10, Anton Polonskiy <anton.polons...@gmail.com> wrote:
>Scenario 1: >Multiple processes write to foo.db. >I want to do some periodic snapshots. >What is the best way to do this without interrupting/blocking >writers? >sqlite3 foo.db '.backup snapshot.db' ? This is the scenario that "vacuum into '<filename>';" command was designed to address. It opens a "read" transaction and then copies the database contents to the specified destination db. If the source database is in WAL mode (pragma journal_mode=WAL) then the "vacuum into" command will copy the database including all committed writes up until the time the read transaction (vacuum into command) was initiated. Changes in progress or committed on other connections after the vacuum into is initiated will not be seen or copied. This contrasts with the "backup" interface as the back interface will copy "pages that have been changed" by "restarting the copy". This means that if your interval between updates is less than the time required to make a copy of the database, the backup may never complete... >Scenario 2: >Multiple processes read foo.db (no writes at all). >I need to update foo.db without readers interruption and blocking. >What is the best way to do this? >mv foo-new.db foo.db ? In order to "replace" a database file you must close all connections to the existing database (which means stopping all readers and closing all connections). You then re-open the connections and continue using the new database file after the copy is completed. Is the "foo.db" in Scenario 1 the same "foo.db" in scenario 2 or do they just have the same name by happenstance? Or the same database of different machines or something? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
