Ensuring Consistent SQLite Backups via .dump, VACUUM INTO, and Backup API

Understanding Transaction Isolation and Backup Consistency in SQLite

The challenge of creating consistent backups while minimizing interference with active database operations involves navigating SQLite’s concurrency model, transaction isolation levels, and backup mechanisms. A backup strategy must balance three priorities:

1. Producing a transactionally consistent snapshot of the database.
2. Avoiding blocking concurrent readers/writers during the backup process.
3. Ensuring the backup method integrates with incremental backup workflows.

SQLite provides multiple tools for this purpose—.dump, VACUUM INTO, and the Backup API—each with distinct behaviors under different journaling modes (rollback vs. WAL). Misunderstanding how these tools interact with locks, transactions, and storage engine details leads to incomplete backups, blocked clients, or inconsistent snapshots. This guide dissects the mechanics of each backup method and provides actionable solutions to achieve reliable backups without disrupting live operations.

Critical Factors Impacting Backup Consistency and Concurrency

1. Journaling Mode Dictates Locking and Isolation

SQLite operates in one of two primary journaling modes: rollback journal (default) or Write-Ahead Logging (WAL). These modes fundamentally alter how backups interact with active transactions:

• Rollback Journal Mode:

  • Readers (e.g., .dump or SELECT) acquire a SHARED lock, allowing concurrent reads but blocking writes from committing.
  • Writers require an EXCLUSIVE lock, which cannot be acquired while any SHARED or RESERVED locks are held. A long-running backup in this mode creates writer starvation.
  • .dump uses a SAVEPOINT (equivalent to BEGIN DEFERRED TRANSACTION), which holds a SHARED lock for the duration of the backup.

• WAL Mode:

  • Readers do not block writers, as they read from the database’s last consistent state (via the WAL’s snapshot mechanism).
  • Writers append changes to the WAL file, allowing concurrent reads to proceed without contention.
  • Checkpointing (merging WAL into the main database) may be delayed if long-running readers exist, increasing WAL file size.

2. Backup Method Transactional Guarantees

Each backup method interacts differently with SQLite’s transactional guarantees:

• .dump Command:

  • Executes within a SAVEPOINT, creating a deferred transaction.
  • In rollback mode, this holds a SHARED lock, blocking writers from committing until the .dump completes.
  • In WAL mode, the transaction uses a snapshot, allowing writers to proceed but freezing the WAL’s state until the backup finishes.

• VACUUM INTO:

  • Creates a new database file by reconstructing the entire schema and data.
  • Requires an EXCLUSIVE lock during execution, blocking all other writers and readers.
  • Produces a compacted backup but is unsuitable for high-concurrency environments.

• Backup API:

  • Incrementally copies database pages from source to destination.
  • Operates as a series of short SHARED lock acquisitions in rollback mode, allowing intermittent writer access.
  • In WAL mode, uses snapshots to create a consistent backup without blocking writers.

3. Incremental Backup Efficiency

Text-based backups (e.g., .dump) may not efficiently support incremental backups due to:

• Schema Changes: Table alterations (e.g., ALTER TABLE) rewrite large portions of the dump output.
• Row Ordering: Inserts in arbitrary order cause diff tools to flag false positives.
• Blob Storage: Binary data is serialized as hex strings, bloating dump files.

Resolving Backup Inconsistency and Concurrency Conflicts

Step 1: Choose the Appropriate Journaling Mode

• For Write-Heavy Workloads:
  Use WAL mode to decouple readers from writers:

  PRAGMA journal_mode=WAL;
  

  • Benefits:
    • Backups via .dump or Backup API do not block writers.
    • Writers can commit changes while a backup is in progress.
  • Caveats:
    • Long-running backups delay WAL checkpointing. Monitor WAL size with:

      PRAGMA wal_checkpoint(TRUNCATE);
      

    • Ensure synchronous=NORMAL or FULL to avoid data loss during power failures.

• For Read-Heavy Workloads:
  Use rollback journal mode if write contention is minimal:

  PRAGMA journal_mode=DELETE;
  

  • Risks:
    • .dump backups block writers for the backup duration.
    • Prefer the Backup API for shorter lock intervals.

Step 2: Select a Backup Method Aligned with Workload

• .dump in WAL Mode:

  • Command:

    sqlite3 source.db ".dump" | gzip > backup.sql.gz
    

  • Consistency: Provides a snapshot of the database at the start of the dump.
  • Concurrency: Writers proceed uninterrupted, but checkpointing is paused.

• Backup API (Recommended for Live Systems):

  • Python Example:

    import sqlite3
    
    src = sqlite3.connect('source.db')
    dst = sqlite3.connect('backup.db')
    
    with dst:  
        src.backup(dst, pages=100, progress=lambda status, remaining, total: None)
    

  • Behavior:
    • Copies 100 pages at a time, releasing locks between batches.
    • In WAL mode, uses a snapshot for consistency.
    • Survives source schema changes mid-backup (restarts if necessary).

• VACUUM INTO for Offline Backups:

  • Command:

    VACUUM INTO '/path/to/backup.db';
    

  • Use Case: Scheduled during maintenance windows when exclusive access is possible.

Step 3: Validate Backup Integrity

• Checksum Verification:
  Compare pre-backup and post-restore checksums:

  -- Generate checksum
  PRAGMA integrity_check;
  

• Test Restores:
  Regularly restore backups to a test instance and validate critical queries.

• Monitor for Long Transactions:
  Use sqlite3_sourceid() and sqlite3_txn_state() to identify transactions blocking checkpoints or backups.

Step 4: Optimize Incremental Backups

• Binary Diff Tools:
  Use rsync or bsdiff on the database file directly in WAL mode, as file-level changes are append-only.

• Partial .dump Strategies:
  Export only modified tables using WHERE clauses with row versioning:

  .dump --preserve-row-version
  

  (Note: SQLite’s CLI does not natively support this; implement via application logic.)

Final Recommendations

1. Default to WAL Mode: Enables non-blocking backups and higher concurrency.
2. Use Backup API for Live Systems: Balances consistency with minimal writer disruption.
3. Schedule VACUUM INTO During Off-Peak Hours: For compact, offline backups.
4. Avoid .dump in Rollback Mode: Unless writer blocking is acceptable.
5. Validate All Backups: Automated integrity checks prevent silent corruption.

By aligning journaling modes, backup methods, and validation workflows, SQLite databases can be backed up consistently without sacrificing concurrency or incremental efficiency.