Re:Many small queries are efficient in SQLite
by johannes1234321 on Tuesday February 16, 2021 @08:05AM
(#26153460)
Well, backing up by copying doesn't neccissarily result in a consistent state if there are writes to the database. For that you have to use the SQLite `.backup` command (or using the backup API https://sqlite.org/c3ref/backup_finish.html) after which the backup database has to be copied over to backup storage (or backup storage has to be mounted to the production system, which is dangerous)
[ Parent
| on HN ]
Re:Many small queries are efficient in SQLite
by beagle3 on Tuesday February 16, 2021 @12:03PM
(#26156027)
Use "rsync" instead of "cp". After it finishes copying, it will check to see if the file has changed since it started copying, and will restart the copy if so (with a limited number of retries).
If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy.
For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g.
And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.
Re:Many small queries are efficient in SQLite
by johannes1234321 on Wednesday February 17, 2021 @10:46AM
(#26167652)
Yes, rsync is a great toolinnsome cases it might even find which blocks changed and only retransmit those.
However if you have frequent writes I would be careful.