HumanOstrich • 05/01/2025

Taking an LVM snapshot and then copying the sqlite database from that is sufficient to keep it from being corrupted, but you can have incomplete transactions that will be rolled back during crash recovery.

The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata.

To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot.

    PRAGMA wal_checkpoint(FULL);
    BEGIN IMMEDIATE;  -- locks out writers
    .   /* trigger your LVM snapshot here */
    COMMIT;

You can also use fsfreeze to get the same level of safety:

    sudo fsfreeze -f /mnt/data      # (A) flush dirty pages & block writes
    lvcreate -L1G -s -n snap0 /dev/vg0/data
    sudo fsfreeze -u /mnt/data      # (B) thaw, resume writes

Bonus - validate the snapshotted db file with:

    sqlite3 mydb-snapshot.sqlite "PRAGMA integrity_check;"