Continuous backups

Every backup on the two pages before this one copies the whole database, however little of it changed. A database of one terabyte therefore sends a terabyte on every backup, whether the day's writes came to 200 MB or to nothing, so taking a backup more frequently multiplies the storage and the bandwidth it uses.

The backups option changes what a backup sends. Sirannon copies the whole database once, then on an interval it sends only the write-ahead log frames written since the previous run, so that same terabyte database sends 200 MB on the day its writes came to 200 MB. The full copy and every change piece taken from it, in order, form a chain, and Sirannon records which runs make up each chain, so you never have to work that out from file names.

This page builds one file, continuous-backup.ts, step by step. You will turn the cycle on, take a turn by hand, read what the cycle is doing, list what the destination holds, and check one stored backup against the record that describes it. The restore page rebuilds a database from the chain this page produces.

rm -rf data && mkdir -p data

What the cycle takes over

A database given the backups option takes checkpointing away from SQLite and does it itself. It has to, because a checkpoint lets SQLite overwrite log frames nothing has captured yet, so the writes those frames held would reach no backup and SQLite would report success all the same. Sirannon therefore opens the writer with wal_autocheckpoint at zero and checkpoints once each capture holds the frames.

Each turn of the cycle takes four steps in this order.

1. Send any capture staged by an earlier turn that the destination refused.
2. Under the writer lock, stage the log frames written since the previous capture.
3. Checkpoint the log under that same lock.
4. Send the staged capture and append its record to the chain.

Staging before checkpointing is what makes the order safe, because a capture that fails stops the checkpoint behind it, so the frames stay in the log and the next turn takes them. Sirannon stages into a directory beside the database file, ./data/orders.db-backup for the database below, so a capture that has yet to reach the destination is still there after a restart.

Three conditions have to hold before a database can capture its log. sirannon.open checks all three, so a database that cannot capture refuses to open at once, with a message naming the condition it broke.

• The database is a file in write-ahead logging mode. An in-memory database and one opened with walMode: false each fail with BACKUP_UNSUPPORTED.
• The driver supplies a backup engine, which the better-sqlite3 and node drivers do.
• The database accepts writes. A read-only database takes no cycle, and backupLocation, backupStatus, backupChain, and captureBackupChanges on it each fail with BACKUP_UNSUPPORTED.

Turn the cycle on and take one turn

Pass backups in DatabaseOptions with the destination the destinations page describes. Opening the database starts the cycle in the background, and its first turn copies the whole database, so nothing waits on that copy.

The destination below keeps its pieces in a Map, which makes this page a single file you can run. Storage you would keep a real backup in goes behind the same three methods.

captureBackupChanges takes a turn immediately, which is what makes the figures below the same on every run.

import { type BackupDestination, type BackupPiece, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`
 
const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
const db = await sirannon.open('orders', './data/orders.db', {
  backups: {
    destination: memory,
    intervalMs: 60_000,
    fullCopyIntervalMs: 24 * 60 * 60 * 1000,
    onError: (err) => console.error('Backup cycle failed:', err.message),
  },
})
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
const capture = await db.captureBackupChanges()
 
console.log(
  JSON.stringify(
    {
      capture: {
        kind: capture?.kind,
        route: capture?.route,
        frameCount: capture?.pageCount,
        pieceCount: capture?.pieceCount,
        bytesWritten: capture?.bytesWritten,
        firstFrame: capture?.position?.firstFrame,
        lastFrame: capture?.position?.lastFrame,
      },
    },
    null,
    2,
  ),
)
 
await sirannon.shutdown()

A change capture reports kind: 'change' and counts the log frames it took in pageCount, since it reads the log and copies no pages. Three commits produce three frames, and a frame is a 24-byte header followed by one 4,096-byte page, which with the log's own 32-byte header comes to the 12,392 bytes the panel prints. route reads 'staged' on every change capture, because a capture reads a local log file and the streamed route applies to a full copy.

position states the stretch of log the piece covers. A restore replays those frames onto the full copy underneath, so the sequence has to be unbroken, and firstFrame and lastFrame are what make a gap visible.

captureBackupChanges returns undefined where a turn wrote nothing, which happens on a node whose replication group backs up somewhere else. The cluster page covers that case.

Read what the cycle is doing

db.backupStatus() answers from memory, so a route or a metrics scrape can call it as frequently as you need. db.backupChain() asks the destination, so it takes a listing and a read for each chain.

import { type BackupDestination, type BackupPiece, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`
 
const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
const db = await sirannon.open('orders', './data/orders.db', {
  backups: {
    destination: memory,
    intervalMs: 60_000,
    fullCopyIntervalMs: 24 * 60 * 60 * 1000,
    onError: (err) => console.error('Backup cycle failed:', err.message),
  },
})
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
const capture = await db.captureBackupChanges()
 
const status = db.backupStatus()
const chains = await db.backupChain()
 
console.log(
  JSON.stringify(
    {
      capture: {
        kind: capture?.kind,
        route: capture?.route,
        frameCount: capture?.pageCount,
        pieceCount: capture?.pieceCount,
        bytesWritten: capture?.bytesWritten,
        firstFrame: capture?.position?.firstFrame,
        lastFrame: capture?.position?.lastFrame,
      },
      status: {
        running: status.running,
        extendsTheSameChain: status.chainId === capture?.chainId,
        lastRunKind: status.lastRun?.kind,
      },
      chain: {
        chains: chains.length,
        base: { kind: chains[0]?.base?.kind, pieceCount: chains[0]?.base?.pieceCount },
        changes: chains[0]?.changes.map((change) => ({
          kind: change.kind,
          sequence: change.sequence,
          frameCount: change.frameCount,
          checkpointed: change.checkpointed,
        })),
      },
    },
    null,
    2,
  ),
)
 
await sirannon.shutdown()

One chain holds one full copy and one change piece, and the change piece extends the chain the full copy began. checkpointed records whether the checkpoint after that capture emptied the log, which reads false where a reader held the checkpoint off.

The panel reads three of the six fields backupStatus states. progress states the counters of a turn under way and is absent between turns, so a caller that triggers a turn without waiting on it polls this to watch a full copy move. lastRun, lastSkip, and lastError each hold the most recent of their kind, and each stays readable after the turn that produced it ends, so a failure is still there to read once the next turn starts. lastError states the code, the message, the moment, the milliseconds from the start of the turn to the failure, the chain that turn was extending, and how far the run had reached.

The names come from the chain identifier and the sequence. A full copy is stored as sirannon-backup-{chainId}-full.db, a change piece as sirannon-backup-{chainId}-000001.wal, the list of chains under sirannon-backup-chain, and one chain's own records under sirannon-backup-chain.{chainId}. namePrefix and chainName change the two fixed parts, which is how two databases share one destination.

Check a stored backup before you need it

A damaged piece would fail a restore only once that restore had already begun. db.verifyBackup(name) reads one stored backup back out of the destination and compares it against the record that describes it, so an operator can find that damage before a restore ever needs the piece.

Sirannon fetches the pieces in index order and folds a SHA-256 over the bytes as it reads them, holding one piece in memory at a time and writing none of them to disk. A check over a full copy of any size therefore needs no local storage.

import { type BackupDestination, type BackupPiece, Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
const stored = new Map<string, Uint8Array>()
const pieceKey = (name: string, index: number): string => `${name}#${index}`
 
const memory: BackupDestination = {
  async writePiece(name, index, bytes) {
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
  },
  async writePieceIfAbsent(name, index, bytes) {
    if (stored.has(pieceKey(name, index))) return false
    stored.set(pieceKey(name, index), Uint8Array.from(bytes))
    return true
  },
  async readPiece(name, index) {
    const bytes = stored.get(pieceKey(name, index))
    if (!bytes) throw new Error(`No piece ${index} of '${name}'`)
    return bytes
  },
  async listPieces(name) {
    const pieces: BackupPiece[] = []
    for (const [key, bytes] of stored) {
      if (key.startsWith(`${name}#`)) {
        pieces.push({ index: Number(key.slice(name.length + 1)), byteLength: bytes.byteLength })
      }
    }
    return pieces
  },
}
 
const driver = betterSqlite3()
const sirannon = new Sirannon({ driver })
 
const db = await sirannon.open('orders', './data/orders.db', {
  backups: {
    destination: memory,
    intervalMs: 60_000,
    fullCopyIntervalMs: 24 * 60 * 60 * 1000,
    onError: (err) => console.error('Backup cycle failed:', err.message),
  },
})
 
await db.execute('CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)')
await db.executeBatch('INSERT INTO orders (customer, total) VALUES (?, ?)', [
  ['Amara Okonkwo', 128.5],
  ['Lucía Fernández', 64],
  ['Yuki Tanaka', 249.99],
])
 
const capture = await db.captureBackupChanges()
 
const status = db.backupStatus()
const chains = await db.backupChain()
 
const base = chains[0]?.base
const verified = base ? await db.verifyBackup(base.name) : undefined
 
console.log(
  JSON.stringify(
    {
      capture: {
        kind: capture?.kind,
        route: capture?.route,
        frameCount: capture?.pageCount,
        pieceCount: capture?.pieceCount,
        bytesWritten: capture?.bytesWritten,
        firstFrame: capture?.position?.firstFrame,
        lastFrame: capture?.position?.lastFrame,
      },
      status: {
        running: status.running,
        extendsTheSameChain: status.chainId === capture?.chainId,
        lastRunKind: status.lastRun?.kind,
      },
      chain: {
        chains: chains.length,
        base: { kind: chains[0]?.base?.kind, pieceCount: chains[0]?.base?.pieceCount },
        changes: chains[0]?.changes.map((change) => ({
          kind: change.kind,
          sequence: change.sequence,
          frameCount: change.frameCount,
          checkpointed: change.checkpointed,
        })),
      },
      verified: {
        kind: verified?.kind,
        pieceCount: verified?.pieceCount,
        bytesRead: verified?.bytesRead,
        digestMatchesTheRecord: verified?.fingerprint === base?.fingerprint,
      },
    },
    null,
    2,
  ),
)
 
await sirannon.shutdown()

A missing piece, a piece past the recorded count, a byte total other than the recorded one, and a digest other than the recorded one each fail with BACKUP_DESTINATION_ERROR. A name no chain record states fails with BACKUP_CHAIN_BROKEN. Where the backup recorded no fingerprint, because fingerprint: false turned it off, the piece listing and the byte count are the whole check and the result states no digest.

How frequently to capture, and when to start again

Two intervals set how much each backup sends and how many writes a failure loses.

intervalMs sets how many recent writes a machine failure leaves in no backup. At the default of one minute, a machine you lose takes up to a minute of writes with it, and the log holds about a minute of writes between one checkpoint and the next. A lower value shrinks both figures and raises the number of pieces your destination holds. A value of zero leaves the cycle taking a turn only when you call captureBackupChanges.

fullCopyIntervalMs sets how long a chain lasts before a fresh full copy starts a new one, and it defaults to 24 hours. A restore replays every piece since the full copy underneath it, so this figure is what bounds how long a restore takes and how many pieces it fetches.

Set onError. A cycle that stops taking turns while writes continue lets the log grow without bound, and that callback is the only place Sirannon reports the failure.

When the log grows and nothing captures it

A destination that refuses every call leaves the cycle holding its chain, its staged capture, and a log that grows with every write. Sirannon keeps all three by default, because keeping them is what lets the chain continue after an outage of any length.

maxUncapturedLogBytes is where you say that the disk matters more than the chain. Sirannon measures the log after any turn that captured nothing, and past that figure it empties the log, drops the staged capture the destination refused, and reports BACKUP_CHAIN_BROKEN through onError. The writes that log held reach no backup, and the next turn that can run starts a fresh chain with a full copy. Sirannon takes that order on purpose, so an operator learns that writes reached no backup before they learn what stopped the turn.

Two more failures end the chain the cycle was extending. BACKUP_LOG_REWOUND means the log restarted before a capture read it, so the writes in the frames it lost are in no backup, and Sirannon starts a fresh chain. Sirannon also reports BACKUP_CHAIN_BROKEN when the list of chains no longer names the chain this cycle holds, which happens when another node's write replaced the record listing it.

What else the cycle changes

Closing the database captures the log a final time, after its writes drain and before its pool closes, so nothing written since the previous turn is lost to an orderly shutdown.

A bulk load on a database with the backups option checkpoints nothing at the end, whatever checkpoint says. A checkpoint there would let SQLite overwrite the frames that load wrote, so the cycle checkpoints once it holds them.

A database that opens onto a chain a previous run left behind takes an ordinary turn at once, so the frames written while the process was down reach the destination ahead of the first interval.