Sqlite3-Rsync (Draft)

OskarS · 2024-09-30T15:37:25.000000Z

Very cool! This seems like an excellent backup utility as part of core SQLite. Great that you can still run write transactions on the origin while it’s in progress, i wonder what the guarantees are there for which in-progress transactions are synced to the replica.

EDIT: ah, missed this part

> REPLICA becomes a copy of a snapshot of ORIGIN as it existed when the sqlite3-rsync command started. If other processes change the content of ORIGIN while this command is running, those changes will be applied to ORIGIN, but they are not transferred to REPLICA Thus, REPLICA ends up as a fully-consistent snapshot of ORIGINAL at an instant in time.

justinclift · 2024-10-09T00:34:12.000000Z

Interestingly, it seems like this utility can create read-only replicas on a different host:

  While sqlite3-rsync is running, REPLICA is read-only.

  Queries can be run against REPLICA while this utility is running, just
  not write transactions.

That seems like it'll be useful in some novel high load situations.

phaedrus · 2024-09-30T18:16:49.000000Z

I wonder if this could be used in a build step to bring a Sqlite DB in a project's "bin/" folder up to date with a source-controlled copy without just overwriting the whole file. Size of the DB file is one issue, but also I'm thinking there could be cases (like having a connection open in Sqlite Studio, or a running instance of the project executable) where a simple copy would fail the build step. (This sqlite3-rsync specifically supports working while other connections are open.)

If so, it solves a real problem I've been having for eight years.