mORMot Open Source

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

litestream - A SQLite replication tool that uses WAL and checkpoints

https://github.com/benbjohnson/litestream
Unfortunately, it has no Windows support yet, but it seems to be on the author's plan.

The way it works looks like a good idea to implement "sqlite replication":

How it works
===
SQLite provides a WAL (write-ahead log) journaling mode which writes pages to a -wal file before eventually being copied over to the original database file. This copying process is known as checkpointing. The WAL file works as a circular buffer so when the WAL reaches a certain size then it restarts from the beginning.

Litestream works by taking over the checkpointing process and controlling when it is restarted to ensure that it copies every new page. Checkpointing is only allowed when there are no read transactions so Litestream maintains a long-running read transaction against each database until it is ready to checkpoint.

The SQLite WAL file is copied to a separate location called the shadow WAL which ensures that it will not be overwritten by SQLite. This shadow WAL acts as a temporary buffer so that replicas can replicate to their destination (e.g. another file path or to S3). The shadow WAL files are removed once they have been fully replicated. You can find the shadow directory as a hidden directory next to your database file. If you database file is named /var/lib/my.db then the shadow directory will be /var/lib/.my.db-litestream.

Litestream groups a snapshot and all subsequent WAL changes into "generations". A generation is started on initial replication of a database and a new generation will be started if litestream detects that the WAL replication is no longer contiguous. This can occur if the litestream process is stopped and another process is allowed to checkpoint the WAL.

Last edited by edwinsn (2021-01-30 06:15:57)

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,682

Website

Re: litestream - A SQLite replication tool that uses WAL and checkpoints

Our ORM master/slave replication features does the same, at ORM level, so is not limited to SQlite3.
Also note that the built-in SQLite3 sessions (which we compile now with our static version of SQlite3) can do this replication, but also off-line synchronization of several peered DBs.
So I don't see the relevance of this project in our context.

Interfacing it with our ORM kernel could be tricky, due to the lock it maintain on the DB.

edwinsn

Member

Registered: 2010-07-02

Posts: 1,218

Re: litestream - A SQLite replication tool that uses WAL and checkpoints

@ab,

IIRC, the mORMot master/slave replication does not intercept raw SQL execution direction to the db?
Also, it cannot be used to sync an old db?
And, the target must have a mORmot server running?

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Junior/RO

Member

Registered: 2011-05-13

Posts: 210

Re: litestream - A SQLite replication tool that uses WAL and checkpoints

I am also looking for a master/slave replication mechanism, but I'm not sure how mORMot will behave in cases of batches or updates made by SQL.

What is the current state of replication in mORMot 1 and mORMot 2?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,682

Website

Re: litestream - A SQLite replication tool that uses WAL and checkpoints

There is the same mechanism involved in mORMot 1 and 2: ORM-level synchronization.
There is no SQL-level replication.