Fisher on SQLite, Part 3: Backing it up to S3

Part 1 toured Fisher as a document database and Part 2 as an event store: the Marten and Polecat programming model on SQLite, running inside your own process with no database server anywhere. The running example is a till, a terminal behind the counter of a coffee shop that keeps selling when the broadband drops.

Three facts from those two matter here. A store is a file, documents and event streams together. One writer per file. And write-ahead logging is on by default, because WAL is what lets the async daemon read while a session writes.

The first makes backup sound solved, and the Fisher whitepaper says so out loud: backup is cp. True in the same way that “stopping is just letting go of the accelerator” is true. Copy the file while nothing is writing and you have a perfect backup. Copy it while the shop is trading and you have something that opens cleanly, passes an integrity check, looks right, and is missing sales.

The third fact is why.

Everything below was run: Fisher 1.0.4 on .NET 10, AWSSDK.S3 4.0.102.4, the upload and restore against MinIO in Docker, and the restore runbook against a real systemd service in a Debian container. Containers are the cheapest way to rehearse a backup path you would rather not first exercise during an incident.

Why cp is not a backup

In WAL mode a committed transaction is durable the moment its frames are in till.db-wal. The main till.db file catches up later, at a checkpoint. Between those two moments, committed data exists only in the sidecar file, and a reader that opens the database sees it because SQLite consults the WAL first.

So cp till.db backup.db copies a file missing every commit since the last checkpoint. It does not error. It produces a database that opens, passes integrity_check, and is short.

A writer loop stores documents continuously while two backups are taken at the same moment, a plain File.Copy of the database and the honest snapshot from the next section:

[cp] wrote 5010 documents while both backups were taken
[cp] db 1,528 KB, -wal 4,683 KB, -shm 32 KB
[cp] naive copy holds 4509 documents
[cp] vacuum into holds 4942 documents
[cp] live store holds 5010 documents

The naive copy silently lost 501 committed movements, and the WAL was three times the size of the main database, which is the shape of a busy store between checkpoints.

For a service writing at a human pace rather than in a tight loop, it is worse than “some”. A till unit recording one sale every 400ms under systemd, after twelve seconds of trading:

-rw-r--r-- 1 root root  61440 till.db
-rw-r--r-- 1 root root  32768 till.db-shm
-rw-r--r-- 1 root root 271952 till.db-wal

naive cp holds: 0

Zero sales. Nothing had triggered a checkpoint yet, so the main file was still just the schema and every movement sat in the WAL. That is the backup you would have taken to the police station after a break-in.

“Copy all three files” is not the fix either. They are three separate copy operations against a database that is still changing, so you get three inconsistent instants stapled together. The fix is to ask SQLite for a copy, not the filesystem.

The right primitive: VACUUM INTO

SQLite has had VACUUM INTO since 3.27 (2019), and it is the whole answer for snapshot backups. It runs in a read transaction, so it sees a single consistent instant including everything in the WAL, and it writes a fresh, defragmented database file:

public static class Snapshot
{
    public static async Task CreateAsync(string connectionString, string destination, CancellationToken token)
    {
        // VACUUM INTO refuses to overwrite: "output file already exists"
        if (File.Exists(destination)) File.Delete(destination);

        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(token);

        await using var command = connection.CreateCommand();
        command.CommandText = "vacuum into $destination";
        command.Parameters.AddWithValue("$destination", destination);
        await command.ExecuteNonQueryAsync(token);
    }
}

Four things about that snippet, all verified rather than assumed:

• The destination is a bound parameter. SQLite parses the filename as an expression, so you do not have to concatenate a path into SQL.
• It refuses to overwrite. An existing destination fails with SQLite Error 1: 'output file already exists', which is why the explicit delete is there. That refusal is a feature: it cannot clobber a database by accident.
• It is a plain SqliteConnection, not Fisher’s. Fisher opens every internal connection through Weasel’s data source and its own PRAGMA profile, and FisherDatabase.OpenConnectionAsync is internal on purpose. For a read-only snapshot on the side, your own connection against store.Options.ConnectionString is right. Do not do this for application reads.
• The output has no sidecars. The snapshot is one self-contained file with no -wal or -shm beside it, which is exactly what you want to hand to an object store.

The till keeps selling while it works. In the test above, 1,904 writes landed during a snapshot of a 790KB database that took 5ms. The one cost is that the WAL cannot be checkpointed past the snapshot’s read point for the duration, so a snapshot of a large database under heavy write load will grow the -wal for that window.

At a more serious size, 200,000 movements in a 61.8MB file with a 14.9MB WAL:

[big] vacuum into: 799ms -> 60.9 MB
[big] integrity_check: 238ms
[big] gzip: 923ms -> 12.9 MB

About two seconds for the whole snapshot, verify and compress pipeline, and 61.8MB of documents leaves as 12.9MB. JSON compresses extremely well, which is a pleasant side effect of a document store.

The alternative: the online backup API

Microsoft.Data.Sqlite also exposes SQLite’s online backup API as SqliteConnection.BackupDatabase, which copies page by page into another open connection:

await using var source = new SqliteConnection(connectionString);
await using var destination = new SqliteConnection($"Data Source={backupPath}");

await source.OpenAsync(token);
await destination.OpenAsync(token);

source.BackupDatabase(destination);

Both produce a valid standalone file with no sidecars. Across several runs of a 6MB database:

	Time	Output
VACUUM INTO	73ms to 99ms	6,288 KB
BackupDatabase	32ms to 54ms	~6,370 KB

BackupDatabase is roughly twice as fast because it copies pages as they are. VACUUM INTO rebuilds the database, which costs the extra time and buys a defragmented file with its free pages reclaimed. At this size both are noise next to the upload; at tens of gigabytes the difference stops being free.

VACUUM INTO also refuses to overwrite an existing file, which I would not give up on a scheduled job. Use BackupDatabase when your destination needs to be a connection rather than a path, including an in-memory database.

Verify the snapshot before you ship it

A backup you have never restored is not a backup, and the cheapest restore test is in the same process. Run SQLite’s own integrity check:

public static async Task VerifyAsync(string path, CancellationToken token)
{
    await using var connection = new SqliteConnection($"Data Source={path};Mode=ReadOnly");
    await connection.OpenAsync(token);

    await using var command = connection.CreateCommand();
    command.CommandText = "pragma integrity_check";

    var result = (string?)await command.ExecuteScalarAsync(token);
    if (result != "ok")
    {
        throw new InvalidOperationException($"Snapshot {path} failed integrity_check: {result}");
    }
}

Go further and open the snapshot with Fisher itself to count documents, which proves the file is a working Fisher store rather than merely well-formed SQLite. If you do, set AutoCreateSchemaObjects to None:

await using var store = DocumentStore.For(opts =>
{
    opts.Connection($"Data Source={snapshotPath}");
    opts.AutoCreateSchemaObjects = AutoCreate.None;   // JasperFx namespace
});

await using var session = store.QuerySession();
var count = await session.Query<StockMovement>().CountAsync();

Not a stylistic preference. The default is CreateOrUpdate, so a verification store configured differently from production will migrate the backup you are verifying. A throwaway DocumentStore.For("Data Source=snapshot.db") dropped both expression indexes out of a snapshot here, because that store had no index configuration and reconciled the schema to match. With AutoCreate.None the same read left the snapshot byte for byte as it was.

Shipping it to S3

Snapshot, verify, compress, upload, delete the staging file. As a hosted service:

public sealed class BackupOptions
{
    public required string Bucket { get; init; }
    public string Prefix { get; init; } = "till";
    public TimeSpan Interval { get; init; } = TimeSpan.FromHours(6);
    public string StagingDirectory { get; init; } = Path.Combine(Path.GetTempPath(), "till-backups");

    // Both are AWS-shaped and not universally supported. See the note below.
    public S3StorageClass? StorageClass { get; init; }
    public ServerSideEncryptionMethod? ServerSideEncryption { get; init; }
}

public sealed class FisherBackupService(
    IDocumentStore store,
    IAmazonS3 s3,
    IOptions<BackupOptions> options,
    TimeProvider clock,
    ILogger<FisherBackupService> logger) : BackgroundService
{
    readonly BackupOptions _options = options.Value;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        using var timer = new PeriodicTimer(_options.Interval);

        do
        {
            try
            {
                await BackupAllAsync(stoppingToken);
            }
            catch (OperationCanceledException) when (stoppingToken.IsCancellationRequested)
            {
                return;
            }
            catch (Exception e)
            {
                // a failed backup must not take the till offline
                logger.LogError(e, "Fisher backup failed");
            }
        } while (await timer.WaitForNextTickAsync(stoppingToken));
    }

    public async Task BackupAllAsync(CancellationToken token)
    {
        Directory.CreateDirectory(_options.StagingDirectory);

        // one file per terminal under database-per-tenant, exactly one otherwise
        foreach (var database in store.Tenancy.AllDatabases())
        {
            await BackupAsync(database.Identifier, database.ConnectionString, token);
        }
    }

    async Task BackupAsync(string identifier, string connectionString, CancellationToken token)
    {
        var stamp = clock.GetUtcNow().ToString("yyyyMMdd'T'HHmmss'Z'");
        var staged = Path.Combine(_options.StagingDirectory, $"{Sanitize(identifier)}-{stamp}.db");

        try
        {
            await Snapshot.CreateAsync(connectionString, staged, token);
            await Snapshot.VerifyAsync(staged, token);

            var compressed = await Snapshot.CompressAsync(staged, token);
            var key = $"{_options.Prefix}/{Sanitize(identifier)}/{stamp}.db.gz";

            var transfer = new TransferUtility(s3);
            await transfer.UploadAsync(new TransferUtilityUploadRequest
            {
                BucketName = _options.Bucket,
                Key = key,
                FilePath = compressed,
                ContentType = "application/gzip",
                StorageClass = _options.StorageClass,
                ServerSideEncryptionMethod = _options.ServerSideEncryption,
                ChecksumAlgorithm = ChecksumAlgorithm.SHA256,
                Metadata =
                {
                    ["fisher-identifier"] = identifier,
                    ["fisher-taken-at"] = stamp,
                    ["fisher-bytes"] = new FileInfo(staged).Length.ToString()
                }
            }, token);

            logger.LogInformation(
                "Backed up {Identifier} to s3://{Bucket}/{Key} ({Bytes} bytes compressed)",
                identifier, _options.Bucket, key, new FileInfo(compressed).Length);

            File.Delete(compressed);
        }
        finally
        {
            if (File.Exists(staged)) File.Delete(staged);
        }
    }

    static string Sanitize(string identifier) =>
        string.Concat(identifier.Select(c => char.IsLetterOrDigit(c) || c is '-' or '_' ? c : '-'));
}

TransferUtility handles multipart uploads, which matters once a compressed store passes a few hundred megabytes. ChecksumAlgorithm.SHA256 makes S3 verify what it received rather than trusting the transfer, and the metadata gives you the uncompressed size and the exact instant without downloading the object.

Those two nullable options exist because of something I found by running this against MinIO rather than AWS. Hardcoding S3StorageClass.StandardInfrequentAccess fails there with Invalid storage class, and ServerSideEncryptionMethod.AES256 fails with Server side encryption specified but KMS is not configured. Both are fine on real S3. On an S3-compatible endpoint, leave them unset and configure encryption on the bucket instead. The SHA256 checksum works everywhere, though reading it back needs ChecksumMode.ENABLED on the HEAD, or the field comes back empty and looks like it was never sent.

End to end against MinIO, one terminal’s store of 5,000 movements:

[s3] live store holds 5000 movements
[s3] object till/Fisher/20260828T173231Z.db.gz 357597 bytes, sha256=WL19eq2ArCaACRMHLYyH5hnUJH1AJyvE1xB34G/wdk0=, uncompressed=1724416
[s3] default staging directory left behind: 0 files
[s3] restored to 20260828T173231Z.db, 1724416 bytes
[s3] restored store holds 5000 movements (expected 5000): MATCH

A 1.7MB store left as 357KB, came back the same size, passed integrity_check, and opened as a Fisher store with every movement present. That is the loop worth having in CI.

Compression is the usual three lines:

public static async Task<string> CompressAsync(string path, CancellationToken token)
{
    var gzPath = path + ".gz";

    await using var source = File.OpenRead(path);
    await using var destination = File.Create(gzPath);
    await using var gzip = new GZipStream(destination, CompressionLevel.SmallestSize);
    await source.CopyToAsync(gzip, token);

    return gzPath;
}

Wiring, including any S3-compatible endpoint (Cloudflare R2, MinIO, Backblaze B2, Wasabi):

services.AddSingleton(TimeProvider.System);

services.AddSingleton<IAmazonS3>(_ => new AmazonS3Client(
    new BasicAWSCredentials(key, secret),
    new AmazonS3Config
    {
        ServiceURL = endpoint,
        ForcePathStyle = true
    }));

services.Configure<BackupOptions>(builder.Configuration.GetSection("Backup"));
services.AddHostedService<FisherBackupService>();

On real AWS, drop the config and use AddAWSService<IAmazonS3>() with the instance role instead of static credentials.

To test the whole path without an AWS account, run MinIO and point at it:

docker run -d --name fisher-minio -p 9000:9000 -p 9001:9001 \
  -e MINIO_ROOT_USER=fisheradmin -e MINIO_ROOT_PASSWORD=fisheradmin \
  quay.io/minio/minio:latest server /data --console-address ":9001"

http://localhost:9000 with ForcePathStyle = true is then a working S3 endpoint, and the console on port 9001 lets you see the objects land.

The bucket side

The code is the easy half. The bucket is where a backup strategy is won or lost:

• Give the till write-only credentials. s3:PutObject and nothing else. A terminal in a shop is a machine strangers stand next to; if it is stolen or compromised, the keys on it must not be able to list, read or delete the chain’s backup history. Restores are a human action with different credentials.
• Turn on versioning, and consider Object Lock in compliance mode for the retention window you need. Versioning survives an overwrite, Object Lock survives an administrator.
• Use lifecycle rules for retention rather than deleting objects from the app: transition to Glacier Instant Retrieval after 30 days, expire after whatever your policy says. The prefix layout above (till/<identifier>/<timestamp>.db.gz) makes those rules easy to scope per terminal.
• Put backups in a different account or at least a different region from the machine being backed up.
• Alert on absence, not on failure. A backup job that stops running produces no error to alert on. Check the age of the newest object under each prefix and page someone when it exceeds two intervals.
• Rehearse the restore on a schedule. The loop below runs in about a second against a container; there is no excuse for its first execution being during an incident.

Cost is not going to be your problem. The 200,000 movement store compressed to 12.9MB. Every six hours for a year, with no lifecycle expiry at all, is about 19GB of Standard-IA per terminal.

Restoring

Downloading and unpacking the newest object is unremarkable:

var listing = await s3.ListObjectsV2Async(new ListObjectsV2Request
{
    BucketName = bucket,
    Prefix = prefix
}, token);

var latest = listing.S3Objects
    .OrderByDescending(x => x.LastModified)
    .FirstOrDefault()
    ?? throw new InvalidOperationException($"No backups under s3://{bucket}/{prefix}");

using (var response = await s3.GetObjectAsync(bucket, latest.Key, token))
{
    await response.WriteResponseStreamToFileAsync(gzPath, false, token);
}

// gunzip into dbPath, then:
await Snapshot.VerifyAsync(dbPath, token);

The step that matters is what you do with the file next, and there is one rule:

Stop the process, put the restored file in place, and delete till.db-wal and till.db-shm.

Skip it and the restore silently is not the backup. A store where 500 movements had been written, killed hard so its WAL survived, then “restored” from a snapshot containing 200 of them by copying the file over the database and leaving the sidecars:

[crash] restored over app.db, leaving -wal present
[crash] sloppy restore reads: 500 docs

SQLite found a WAL beside the database and recovered it onto the file I had just put there. The leftover frames belonged to a related database and produced a plausible answer, which is the worst outcome available: no error, wrong data. Remove the sidecars and the same restore reads 200, which is what the backup contains.

The same thing happens under systemd. Backup taken while the till was trading, service killed with SIGKILL so its WAL survived, backup copied over till.db with the sidecars left in place:

backup contains: 11
sloppy restore reads: 21

A cleanly stopped process leaves no -wal at all, because the last connection to close checkpoints and removes it. Sidecars present at restore time are evidence the process died rather than exited, which is exactly when you must not trust them.

The runbook:

systemctl stop till
rm -f /var/lib/till/till.db /var/lib/till/till.db-wal /var/lib/till/till.db-shm
gunzip -c 20260828T090000Z.db.gz > /var/lib/till/till.db
sqlite3 /var/lib/till/till.db 'pragma integrity_check;'
systemctl start till

Run verbatim against that same container:

== the runbook, counting before the service restarts ==
integrity_check: ok
restored (service stopped): 11 movements, backup had 11
till.db
service after start: active, now 16 movements and trading again

11 restored out of a backup of 11, one file on disk with no sidecars beside it, and the till selling again. Five lines, no vendor tooling, no matching engine version, no pg_restore telling you the target cluster is a minor version behind. That is the upside the whitepaper points at, once you take the snapshot properly.

Multi-tenancy makes this better, not worse

Under database per tenant, each tenant is a file in a directory, so a terminal is a file:

opts.MultiTenantedDatabasesInDirectory("/var/lib/till/terminals");

store.Tenancy.AllDatabases() then hands you every resolved database with a public Identifier and ConnectionString, which is why the backup loop above needed no special casing:

[tenants] till-01 cs='Data Source=.../terminals/till-01.db' -> 60 KB, 1 docs
[tenants] till-02 cs='Data Source=.../terminals/till-02.db' -> 60 KB, 1 docs
[tenants] till-03 cs='Data Source=.../terminals/till-03.db' -> 60 KB, 1 docs

One caveat from that run: the list also includes the store’s default database under the identifier *DEFAULT*, which nothing writes to under this tenancy. Backing it up is harmless at a few KB, but skip it by identifier if an empty object per cycle would annoy you.

In exchange you get a restore granularity a shared database cannot offer. A terminal dies mid-morning and its replacement is restored from this morning’s file while the other tills keep selling. No filtered export, no partial delete, no cross-tenant transaction to reason about. On a shared server that request is a project. Here it is a gunzip.

When snapshots are not enough

Periodic snapshots give you an RPO equal to your interval. Back up every six hours and a bad afternoon costs up to six hours of trading. For plenty of edge and on-premises deployments that is fine, and more than they had before. For a shop, it is the difference between reconciling the till against card settlements and guessing.

When it is not enough, the answer is Litestream, which replicates SQLite continuously to object storage by streaming WAL changes as they happen. It runs as a sidecar process, needs no code changes, and requires exactly the WAL mode Fisher turns on by default. Restores take a timestamp, so you get point-in-time recovery within the retention window rather than back to the last snapshot. Version 0.5, released in late 2025, dropped the CGO dependency and reworked the storage format.

Two things before you reach for it. It refuses to restore over an existing database file, a deliberate guard against overwriting the live store. And it wants to own checkpointing for that file, so do not run it alongside something else with the same ambitions.

	Snapshot to S3	Litestream
RPO	The interval, hours typically	Seconds
Recovery target	The snapshots you took	Any instant in the retention window
Moving parts	A hosted service in your app	A sidecar process to deploy and monitor
Restore	gunzip, then start	litestream restore
Portability	Any S3-compatible bucket	Any S3-compatible bucket

They compose, and for anything with money in it that is what I would run: Litestream for the RPO, plus periodic verified snapshots into a separate write-only bucket for the “someone deleted the replication target” case. The snapshot service above is a hundred lines and does not depend on the sidecar being healthy.

Closing

Removing the database server removes a category of work, and it does. It also moves one item onto your side of the line: nobody is taking a nightly backup on your behalf.

The job is four steps. VACUUM INTO rather than cp, so the WAL is included. integrity_check rather than hope, so a bad snapshot fails at the source instead of during recovery. Gzip and upload with a checksum, into a bucket the terminal can write to but not read or delete. Delete the sidecars on the way back in, or your restore will lie to you.

If you landed here first, Part 1 is the document store itself (identity, sessions, LINQ, the generated-column trick behind duplicated fields, patching and concurrency) and Part 2 is the event store (streams, the projection lifecycles, and the async daemon).

Fisher is on NuGet and the source is at github.com/JasperFX/fisher.