Hacker News Reader

Comment by alexbezhan | Hacker News Reader

Can I use it to backup SQLite? Like an alternative to Litestream? Are there any negative implications for doing this?

Litestream author here. I haven't personally tried Syncthing on SQLite databases but I suspect it wouldn't work well. Syncthing doesn't have any concept of SQLite's locks so even in the best case scenario where you only have one writer and the replica is read-only, the reader on the replica would have pages changed out from under it by Syncthing and that would appear corrupt to the reader.

One case where you could make it work is if you sync'd across multiple computers but only used one at a time. I mean, it's still dangerous but it seems possible.

What if I do sqlite backup with cron and then sync the backup?

If you're just using the sync'd version as read-only or as a backup then I suspect that's fine. It seems kinda complicated but you should be ok. :)

Syncthing "just" syncs files. Near instantly, which could mean a delay of a few seconds to maybe even a minute. And it won't de-conflict changes beyond saving the "bad" version in a file like `sqlite.db-syncthing-conflict-20230101T043044`. (Not an issue if you only have one host changing the database file)

I like it and IMO it does what it does well. But litestream can "Continuously stream SQLite changes" (pulled from https://litestream.io/, I've never heard of it before). It seems like that's litestream's killer feature, and I think syncthing would leave you wanting.

Not really, it can't resolve a conflict inside the file and can't enforce a distributed lock on changing the file. Ultimately there will be ways for the file to corrupt or get out of sync, especially if multiple writers are present. There might be a way to make things work better with SQLite's more recent WAL mode but I'm not familiar with it.

https://www.sqlite.org/howtocorrupt.html

If not careful, I think you'll hit, particularly 1.2, 1.4.

Found relevant discussion here https://forum.syncthing.net/t/bahaviour-of-st-on-sqlite-file...

Reply on news.ycombinator.com