Expose online backup api

[warmwaffles]

This is an interesting page I ran across today and may be beneficial if we can figure out how best to expose the backup api

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

[warmwaffles]

We can utilize the VACUUM INTO but the problem is, if the DBConnection uses it, the connection execution would time out.

[lawik]

Have you looked at Litestream? I wonder if that runs on that API or other parts.
https://litestream.io/

I've been planning on setting up something to make sure Litestream runs to replicate Sqlite data as part of an Elixir application. I think doing what Litestream does, well, is probably challenging enough that I wouldn't bother porting it and would rather use it.

Just thrilled to have streaming backup for Sqlite :)

[warmwaffles]

Yea litestream is cool, but what I was thinking about was something for embedded systems to utilize and backup stuff without being connected to the internet.

I am unsure of litestream's ability to run on micro controllers.

The other use case is just a periodic backup of a system that wakes up for a short period of time.

[lawik]

I would expect litestream to do nicely in that case too (at least Raspberry Pi grade) but it does want something shaped like S3 to shove the file at and that implies an internet connection.

Just wanted to make sure you knew of it. If you have need of the Backup API, go forth :)

[lovebes]

litestream does allow backing up into a file. I know I'm chiming in here a year later - but yeah since one year, Litestream is really picking up some steam (core developer got hired by Fly.io to work on it fulltime!) :) It'd be awesome if exqlite supports this in-house so no additional dependency is added.

[warmwaffles]

@lovebes I don't think there is anything we have to do directly. AFAIK, litestream just watches the write ahead log file to replicate changes. I don't think there is anything directly that we can do here.

[lawik]

I think exposing any existing backup API can be smart. I’d suggest not introducing litestream which is another binary and a whole opinion on how to do backups into the low-level db library here.

There is already a package for running Litestream: https://hex.pm/packages/litestream

[lovebes]

Ah yes, I agree with the approach.

[joeljuca]

Posting here cuz it sounds related to the issue: apparently, SQLite released a new way to run backups, using a rsync-like algo that's probably on-topic here:

https://sqlite.org/rsync.html

[warmwaffles]

@joeljuca I believe that is a standalone binary and not baked into the C interface natively. I'll need to investigate.