benbjohnson / litestream

Streaming replication for SQLite.
https://litestream.io
Apache License 2.0
11.1k stars 256 forks source link

Continous restore / Restartable restore #571

Open sylr opened 9 months ago

sylr commented 9 months ago

Hi,

First thank you for litestream! I use it to backup sqlite3 databases for read only debug purposes.

I don't know if it is even possible but it would be really nice if we could do multiple restore of the same database without having to remove the local file and download the last snapshot + available WAL history.

Another idea, something like a -continuous flag for the restore command that would trigger a mechanism which would watch for new WAL on the storage and apply them as they appear.

Regards.

slaveofcode commented 6 months ago

Any idea on this?

yanc0 commented 6 months ago

The continuous mode would be hard to do.

Watching from an object storage in not trivial. You could use event notification from Cloud providers (S3, GCS, Azure) but it's very specific to each one. Some Cloud won't ever provide notifications system at all.

Another option is to regularly list objects from S3 and keep track of the state of the current and remote database. Sadly, LIST operations are not cheap, it will incur cost even if there is no write on the primary.

At the end, Litestream should force readonly in "continuous mode", I don't know if this is possible to force other connections to be read only.

Maybe you just need a litestream restore -force to skip the error message cannot restore, output path already exists and run this command inside a cronjob ?