News for hackers. Stuff that matters.
Re:Consider SQLite by lrem on Wednesday December 29, 2021 @02:04PM (#29728586)
I don't know what backup tools you have in mind... But since a SQLite database is a single file (modulo write ahead journal and whatnot), making whatever you need is trivial.
Re:Consider SQLite by masklinn on Wednesday December 29, 2021 @02:13PM (#29728707)
> modulo write ahead journal and whatnot

That's like saying immortality is trivial modulo death and whatnot. If you don't integrate with sqlite's locking system, you can easily "backup" a file mid-write, corrupted and unusable.

That's why sqlite has a built-in backup command.

Re:Consider SQLite by jks on Wednesday December 29, 2021 @10:12PM (#29733100)
Litestream does streaming backups using a separate process: https://litestream.io/
Re:Consider SQLite by lrem on Wednesday December 29, 2021 @03:49PM (#29729831)
I guess my secret is having almost no writes (the couple things I deployed with SQLite would see a dozen writes per month).
Re:Consider SQLite by cperciva on Wednesday December 29, 2021 @02:10PM (#29728673)
Be careful! If you copy a database file while it is being modified, you may end up with an inconsistent view of the database depending on the order in which different parts of the file are modified.

There are ways around this (filesystem snapshots and potentially file locking) but it's not trivial and failing to handle this correctly is a very good way to build a system which passes tests under low load (when the file-copying operation will win all the relevant races) but fail catastrophically under heavy load.

Re:Consider SQLite by chasil on Wednesday December 29, 2021 @02:15PM (#29728748)
The sqlite3 utility can be used to create a transaction-consistent backup of a live database. This interface is the only way that a backup should be taken, either from the utility or the C API.

    .backup ?DB? FILE        Backup DB (default "main") to FILE
https://sqlite.org/cli.html
Re:Consider SQLite by DasIch on Wednesday December 29, 2021 @02:12PM (#29728703)
The state of the art is that you click a few buttons in the UI of the cloud provider of your choice. That takes a few seconds.

What you are proposing may be trivial but in comparison it might just as well be rocket science. Doing more work to use a simpler database that is harder to setup and has less features is not exactly compelling.

Re:Consider SQLite by chasil on Wednesday December 29, 2021 @02:11PM (#29728692)
In Oracle, I can do this:

    RECOVER DATABASE UNTIL TIME '2021-10-01 02:00:00' USING BACKUP CONTROLFILE;
SQLite does not implement such a feature.
Re:Consider SQLite by srcreigh on Wednesday December 29, 2021 @02:20PM (#29728821)
There is a diff tool [0], and an online backup tool [1]. That kinda thing should be pretty easy to cobble together. You'll just need ~3x disk space to store 2 backups at an interval, diff them and discard the older backup, then apply backward diffs on your most recent backup to achieve a specific backup time.

EDIT: or maybe the Session Extension [2]

[0]: https://www.sqlite.org/sqldiff.html

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

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

Re:Consider SQLite by simonw on Wednesday December 29, 2021 @06:47PM (#29731643)
Litestream may be able to do that on top of SQLite: https://litestream.io

See the -timestamp option here: https://litestream.io/reference/restore/