@aaronk6 Should you not mount the storage outside of the container?
@obrhoff Yes, that’s how I do it, but my understanding is that the database file might be inconsistent when I copy or snapshot it while the application running in the container is using it.
@aaronk6
As far as I understand SQLite it's pretty robust. I'd think of restoring it after back-up as a minor risk.
Edit: correction! Back-up-during-write is explicitly listed as a way to corrupt an SQLite database.
Use a file for the volume with the database that allows taking a snapshot maybe? E.g., ZFS?
@kiraso No - please refer to the SQLite docs above:
The corruption may happen when a transaction gets written while a backup is stored, and both the before and the after end up in the same backup file.
A filesystem snapshot with, e.g., ZFS, BTRFS or a block-device snapshot with LVM act at one point in time, before, during or after a transaction.
@tg9541
That's an interesting point.
I'm considering online ZFS snapshot of a database similar to DB process crash, which may stop transactions in the middle, leave some temporary data around, but file system itself remains consistent. If database is expected to survive this, so be it. SQLite seems to be resilient enough.
@aaronk6 @tg9541 @obrhoff
I also missed it on the first reading. It is in section 1.2.
What this means for containers, probably you want to mount another volume just for the latest snapshot, look for a script that implements one of the recommended methods, then use that snapshot file as your backup.
I’d be surprised if there isn’t already a SQLite container image that has this option but I admit I haven’t looked around much