4 ms·
> You can't copy the file of a running, active db receiving updates, that can only result in corruption To push back against "only" -- there is actually one sc
by creatonez 1y ago
> You can't copy the file of a running, active db receiving updates, that can only result in corruption
To push back against "only" -- there is actually one scenario where this works. Copying a file or a subvolume on Btrfs or ZFS can be done atomically, so if it's an ACID database or an LSM tree, in the worst case it will just rollback. Of course, if it's multiple files you have to take care to wrap them in a subvolume so that all of them are copied in the same transaction, simply using `cp --reflink=always` won't do.
Possibly freezing the process with SIGSTOP would yield the same result, but I wouldn't count on that
- lmz 1y agoIt can't be done without fs specific snapshots - otherwise how would it distinguish between a cp/rsync needing consistent reads vs another sqlite client wanting the newest data?
- o11c 1y agoObligatory "LVM still exists and snapshots are easy enough to overprovision for"
- HumanOstrich 1y agoTaking 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;"
- remram 1y agoWhat does "losing transactions" mean? Some transactions will have committed before your backup and some will have committed after and therefore won't be included. I don't see the problem you are trying to solve? Whether a transaction had started and gets transparently rolled back, or you had prevented from starting, what is the difference to you? Either way, you have a point-in-time snapshot, that time is the latest commit before the LVM snapshot. You're discussing this in terms of "safety" and that doesn't seem right to me.
- HumanOstrich 1y agoThis isn't really a personal or controversial take on the issue. There are easier ways to back up a sqlite database, but if you want to use LVM snapshots you need to understand how to do it correctly if you want to have useful backups. Here's a scenario for a note-taking app backed by sqlite: 1. User A is editing a note and the app writes the changes to the database. 2. SQLite is in WAL mode, meaning changes go to a -wal file first. 3. You take the LVM snapshot while SQLite is: - midway through a write (to the WAL or the main db file) - or the WAL hasn’t been checkpointed back into the main DB file 4. The snapshot includes: - a partial write to notes.db - or a notes.db and notes.db-wal that are out of sync Result: The backup is inconsistent. Restoring this snapshot later might: - Cause sqlite3 to throw errors like database disk image is malformed - Lose recent edits - Require manual recovery or loss of WAL contents In order to get a consistent, point-in-time recovery where your database is left in state A and your backup from the LVM snapshot is in state B with _no_ intermediate states (like rolled back transactions or partial writes to the db file), you have to first either: - Tell SQLite to create checkpoint (write the WAL contents to the main DB) and suspend writes - Or, flush and block all writes to the filesystem using fsfreeze Then take the LVM snapshot.
- remram 1y agoYou are wrong, the whole point of the WAL is to make SQLite crash-consistent. Same as the rollback journal. SQLite will safely rollback partial writes. I don't know where you got the idea that in WAL mode, SQLite ditches its consistency guarantees somehow. If you can corrupt a SQLite database by pulling the power or simulating it by taking a block-device snapshot, this is a serious SQLite bug and you should report it. https://sqlite.org/transactional.html https://sqlite.org/transactional.html & https://sqlite.org/atomiccommit.html https://sqlite.org/atomiccommit.html have more details
- ummonk 1y agoI would assume cp uses ioctl (with atomic copies of individual files on filesystems that support CoW like APFS and BTRFS), whereas sqlite probably uses mmap?
- vlovich123 1y agoI was trying to find evidence that reflink copies are atomic and could not and LLMs seem to think they are not. So at best may be a btrfs only feature?
- creatonez 1y agoFrom Linux kernel documentation: https://man7.org/linux/man-pages/man2/ioctl_ficlone.2.html https://man7.org/linux/man-pages/man2/ioctl_ficlone.2.html > Clones are atomic with regards to concurrent writes, so no locks need to be taken to obtain a consistent cloned copy. I'm not aware of any of the filesystems that use it (Btrfs, XFS, Bcachefs, ZFS) that deviate from expected atomic behavior, at least with single files being the atom in question for `FICLONE` operation.
- vlovich123 1y agoThanks! I similarly thought that a file clone would work but I couldn't confirm it.
- lmz 1y agoSo not a "naive" cp, but at least it calls a shared ioctl that is implemented buy multiple filesystems.