News for hackers. Stuff that matters.

A comment on Using SQLite as storage for web server static content. Parent comment

Re:Using SQLite as storage for web server static content by webstrand on Sunday October 27, 2024 @02:37PM (#41964603)
Sqlite isn't necessarily easier to backup than a filesystem. I've got a fairly large (~60GB) sqlite that I'm somewhat eager to get off of. If I'd stuck with pure filesystem then backing up only the changeset would be trivial, but with sqlite I have to store a new copy of the database.

I've tried various solutions like litestream and even xdelta3 (which generates patches in excess of the actual changeset size), but I haven't found a solution I'm confident in other than backing up complete snapshots.

[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by fanf2 on Sunday October 27, 2024 @02:41PM (#41964637)
You might like the new sqlite3_rsync command https://www.sqlite.org/rsync.html
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by simonw on Sunday October 27, 2024 @03:01PM (#41964760)
Yeah that looks ideal for this exact problem, because it lets you stream a snapshot backup of a SQLite over SSH without needing to first create a duplicate copy using .backup or vacuum. My notes here: https://til.simonwillison.net/sqlite/compile-sqlite3-rsync
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by webstrand on Sunday October 27, 2024 @03:22PM (#41964918)
Maybe that tool just doesn't fit my use-case, but I'm not sure how you'd use it to do incremental backups? I store all of my backups in S3 Glacier for the cheap storage, so there's nothing for me to rsync onto.

I can see how you'd use it for replication though.

[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by simonw on Sunday October 27, 2024 @06:23PM (#41966174)
If you want incremental backups to S3 I recommend Litestream.
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by hedgehog on Sunday October 27, 2024 @07:34PM (#41966553)
What do you do about compaction?
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by webstrand on Monday October 28, 2024 @09:50AM (#41970988)
zstd, though that only shaves off a few GB total.
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by hedgehog on Monday October 28, 2024 @07:28PM (#41977687)
Oh, I mean like with the vacuum command. As the databases get larger it can become unwieldy.
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by webstrand on Tuesday October 29, 2024 @01:02PM (#41986619)
I just tried it, it only recovered a few MB
[ Parent | on HN ]
Re:Using SQLite as storage for web server static content by hedgehog on Tuesday October 29, 2024 @02:32PM (#41987864)
Aha, so not much need. I've always avoided SQLite for larger databases due to the extra space needed to allow compaction, maybe not a real problem in most applications though.
[ Parent | on HN ]