Hacker News Reader

Comment by oefrha | Hacker News Reader

A few sites of mine are backed by SQLite databases replicated daily from another source. Right now I’m rsyncing them, but sending an even increasing number of gigabytes a day isn’t great for the long term. Anyone aware of some sort of checkpointing and incremental sync solution for SQLite? Doesn’t need to be real time, and shouldn’t be real time, really. This one isn’t what I’m looking for unfortunately, the “sign up for a free account” part is also pretty crazy.

View on HN

Verneuil https://github.com/backtrace-labs/verneuil might work for you. Its replication client can use a persistent chunk cache (no fsync, so doesn't survive reboots), and there's a CLI utility to reconstitute a sqlite db file (`verneuilctl restore`). It also tries to do the right thing by writing to a temporary file before renaming it over the old db file.

On the write-side, you can load the verneuil VFS as a runtime extension. There's a similar gotcha with reboots, where the replication state is persisted but not fsync-ed, so you get a full refresh on reboots. The S3 storage is content addressed though, so you "only" pay for useless bandwidth and API calls, not storage.

The SQLite Session extension[1] is one option for shipping row-level change sets between databases. It may be more efficient than rsync and you can do it in batches.

Can you talk more about your use case? I'm the author of LiteFS which does real-time replication of databases. It doesn't sound like it's a fit for your use case but I'm always interested in hearing about what people are doing.

[1]: https://www.sqlite.org/sessionintro.html

At this point, why not switch to using a real database like mariadb or postgresql? These days you don't even have to run it yourself, every cloud provider offers it as a service and can handle backups, patching, replication for you

You may want rqlite?

Or Litestream.

rqlite creator here. rqlite is not simply a SQLite replication technology[1], so probably won't meet your needs. Maybe litestream.

[1] https://rqlite.io/docs/faq/#can-i-use-rqlite-to-replicate-my...

Reply on news.ycombinator.com