Safe (atomic) db file snapshot and update

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Safe (atomic) db file snapshot and update

Anton Polonskiy
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' ?

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 ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe (atomic) db file snapshot and update

Chris Locke-3
> Multiple processes write to foo.db.

What method of journaling do you use?  WAL?

> Multiple processes read foo.db (no writes at all).

Do they open a connection, read, then close the connection, or do you open
a connection, read, read, read, read, until the process is terminated, THEN
close the connection?  (ie, is the connection open all the time)


Thanks,
Chris

On Wed, Mar 6, 2019 at 11:33 AM Anton Polonskiy <[hidden email]>
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' ?
>
> 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 ?
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe (atomic) db file snapshot and update

Anton Polonskiy
> What method of journaling do you use?  WAL?

What will be appropriate for my use case?

> Do they open a connection, read, then close the connection, or do you open
a connection, read, read, read, read, until the process is terminated, THEN
close the connection?  (ie, is the connection open all the time)

Transactions are short lived (< 10 sec).

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe (atomic) db file snapshot and update

Keith Medcalf
In reply to this post by Anton Polonskiy

On Wednesday, 6 March, 2019 04:10, Anton Polonskiy <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Safe (atomic) db file snapshot and update

Anton Polonskiy
My main goal - to avoid blocks and interruptions in this two scenarios.

Thanks for "vacuum into" - I will test that.

> 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?

Yes. Same db, different machines.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users