logoalt Hacker News

remram • 05/02/2025 • 1 reply • view on HN

What 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.

Replies

HumanOstrich • 05/02/2025

This 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.
➕ show 2 replies