3 ms·

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 ne

by lrem 5y ago

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.

cperciva 5y ago
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.
- chasil 5y ago
  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 https://sqlite.org/cli.html
chasil 5y ago
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.
- srcreigh 5y ago
  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 https://www.sqlite.org/sqldiff.html [1]: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html [2]: https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
- simonw 5y ago
  Litestream may be able to do that on top of SQLite: https://litestream.io https://litestream.io See the -timestamp option here: https://litestream.io/reference/restore/ https://litestream.io/reference/restore/
DasIch 5y ago
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.
masklinn 5y ago
> 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.
- lrem 5y ago
  I guess my secret is having almost no writes (the couple things I deployed with SQLite would see a dozen writes per month).
- jks 5y ago
  Litestream does streaming backups using a separate process: https://litestream.io/ https://litestream.io/

by lrem 5y ago

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.

cperciva 5y ago

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.

chasil 5y ago
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 https://sqlite.org/cli.html

chasil 5y ago

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.

srcreigh 5y ago
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 https://www.sqlite.org/sqldiff.html [1]: https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html [2]: https://www.sqlite.org/sessionintro.html https://www.sqlite.org/sessionintro.html
simonw 5y ago
Litestream may be able to do that on top of SQLite: https://litestream.io https://litestream.io See the -timestamp option here: https://litestream.io/reference/restore/ https://litestream.io/reference/restore/

DasIch 5y ago

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.

masklinn 5y ago

> 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.

lrem 5y ago
I guess my secret is having almost no writes (the couple things I deployed with SQLite would see a dozen writes per month).
jks 5y ago
Litestream does streaming backups using a separate process: https://litestream.io/ https://litestream.io/