Litestream and SQLite Backups for Go Applications Explained

A SQLite database is a file, and that makes it easy to believe that backing it up means copying the file. For a database nobody is using, that is true. For a service that is writing to it, the copy can be missing the last few minutes of data, and in the wrong conditions it can be corrupt. This article shows why from Go, with the pure Go driver modernc.org/sqlite v1.50.0 on Go 1.25.6, then covers the two approaches that work: a consistent snapshot with VACUUM INTO, and continuous replication with Litestream.

Litestream was not run here. There was no Litestream binary in the lab behind this series, so its part of this article describes the tool from its documentation and shows the configuration and commands you would use; nothing about its output is claimed from a run. The SQLite parts were run, and their output is quoted. Check Litestream's options against the version you install, because the project has changed its internals between releases.

It belongs to the database cornerstone, and it builds on SQLite WAL mode, since replication depends on it. The wider backup story for a server database is in backing up and restoring Postgres.

Why a file copy is not a backup

package main

import (
	"database/sql"
	"fmt"
	"io"
	"log"
	"os"
	"path/filepath"
	"sync"
	"sync/atomic"
	"time"

	_ "modernc.org/sqlite"
)

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func count(path string) (n int, integrity string) {
	db, err := sql.Open("sqlite", "file:"+path+"?mode=ro")
	must(err)
	defer db.Close()
	if err := db.QueryRow("select count(*) from notes").Scan(&n); err != nil {
		return -1, err.Error()
	}
	must(db.QueryRow("pragma integrity_check").Scan(&integrity))
	return
}

func copyFile(src, dst string) {
	in, err := os.Open(src)
	must(err)
	defer in.Close()
	out, err := os.Create(dst)
	must(err)
	defer out.Close()
	_, err = io.Copy(out, in)
	must(err)
}

func main() {
	dir, err := os.MkdirTemp("", "sqlite647")
	must(err)
	defer os.RemoveAll(dir)
	path := filepath.Join(dir, "app.db")

	db, err := sql.Open("sqlite", "file:"+path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=synchronous(NORMAL)")
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(4)
	_, err = db.Exec("create table notes (id integer primary key, body text not null)")
	must(err)

	// What Litestream needs from the database.
	var mode string
	var timeout int
	must(db.QueryRow("pragma journal_mode").Scan(&mode))
	must(db.QueryRow("pragma busy_timeout").Scan(&timeout))
	fmt.Printf("1. journal_mode=%s busy_timeout=%dms (replication needs WAL, and the application needs a busy timeout)\n", mode, timeout)

	// A writer keeps going while we back up.
	var written atomic.Int64
	stop := make(chan struct{})
	var wg sync.WaitGroup
	wg.Add(1)
	go func() {
		defer wg.Done()
		for {
			select {
			case <-stop:
				return
			default:
			}
			if _, err := db.Exec("insert into notes (body) values (?)", fmt.Sprintf("note %d", written.Load())); err == nil {
				written.Add(1)
			}
		}
	}()
	time.Sleep(400 * time.Millisecond)

	// 2. Copying only the main file misses what still lives in the WAL.
	mainOnly := filepath.Join(dir, "main-only.db")
	copyFile(path, mainOnly)
	atCopy := written.Load()
	n, integrity := count(mainOnly)
	fmt.Printf("2. copying app.db alone while writes continue: the copy holds %d rows (the writer had committed about %d), integrity %s\n", n, atCopy, integrity)

	// 3. VACUUM INTO writes a consistent copy of everything committed at its start, and the writer is not blocked.
	snap := filepath.Join(dir, "snapshot.db")
	before := written.Load()
	start := time.Now()
	_, err = db.Exec("vacuum into ?", snap)
	must(err)
	took := time.Since(start)
	after := written.Load()
	n, integrity = count(snap)
	fmt.Printf("3. VACUUM INTO took %v; the writer committed %d more rows meanwhile; the snapshot holds %d rows (between %d and %d) and its integrity check says %s\n",
		took.Round(time.Millisecond), after-before, n, before, after, integrity)

	close(stop)
	wg.Wait()

	// 4. What the files look like next to each other.
	for _, f := range []string{"app.db", "app.db-wal", "app.db-shm", "snapshot.db"} {
		if st, err := os.Stat(filepath.Join(dir, f)); err == nil {
			fmt.Printf("   %-12s %8.1f KB\n", f, float64(st.Size())/1024)
		}
	}
}

1. journal_mode=wal busy_timeout=5000ms (replication needs WAL, and the application needs a busy timeout)
2. copying app.db alone while writes continue: the copy holds 28745 rows (the writer had committed about 28802), integrity ok
3. VACUUM INTO took 5ms; the writer committed 367 more rows meanwhile; the snapshot holds 29065 rows (between 29038 and 29405) and its integrity check says ok
   app.db          516.0 KB
   app.db-wal     4023.5 KB
   app.db-shm       32.0 KB
   snapshot.db     524.0 KB

The program opens a database in WAL mode, starts a goroutine that inserts rows as fast as it can, and runs two experiments while it does.

Copying the main file. In WAL mode, committed changes go to app.db-wal first and move into app.db at the next checkpoint. The program copies only app.db while the writer runs. The copy held 28,745 rows, while the writer had committed about 28,802. The copy passed integrity_check, and it was only behind: the most recent commits were still in the log. The file sizes at the end explain the scale: the main file was 516 KB and the WAL 4,023.5 KB, so at that moment most of the recent data lived in the log.

That is the gentle failure. A file copy is not atomic either: copying a database while a checkpoint is running can capture pages from different moments, which is a corrupted file that fails its integrity check or, worse, passes it and holds inconsistent data. Never rely on cp, rsync or a volume snapshot of the directory unless the snapshot is atomic for the whole directory and includes the -wal and -shm files, and even then restoring relies on SQLite recovering from the log, which it does well and should be tested.

VACUUM INTO: a consistent snapshot

SQLite has a built in way to write a consistent copy of a live database: VACUUM INTO 'snapshot.db'. It reads the database through an ordinary read transaction, so it sees everything committed at the moment it starts, and writes a compact copy to a new file.

The program ran it while the writer continued. It took 5ms, the writer committed 367 more rows during it (it was not blocked, because a read transaction in WAL mode does not stop writers), and the snapshot held 29,065 rows, a number between what had been committed when it started (29,038) and when it finished (29,405). Its integrity_check said ok. That is what consistent means: a state that really existed, not a mixture.

For a service this is a good, simple backup:

1. Run vacuum into to a temporary file on a schedule.
2. Compress it and upload it to object storage with a timestamped name.
3. Delete old ones by a retention rule.
4. Test the restore: open the snapshot, run integrity_check, run your own sanity query.

The path name must not already exist, and the statement needs the free space of a full copy. It rewrites the whole database, so for a multi gigabyte file it takes a while and a lot of I/O, and the recovery point is the time of the last snapshot. A nightly snapshot means a day of data at risk. SQLite also has an online backup API (sqlite3_backup) that copies in steps, which some drivers expose; VACUUM INTO is simpler from Go.

Litestream: continuous replication

When a day of data is too much to lose, replicate. Litestream is a separate program, written in Go, that runs next to your application and streams the write ahead log of a SQLite database to object storage (S3 and compatible services, Google Cloud Storage, Azure Blob Storage, SFTP or a local path) continuously, typically within a second or so of each commit. It takes periodic snapshots of the whole database and keeps the log segments between them, so it can restore the database to the latest replicated state or to a chosen moment.

A minimal configuration is a YAML file:

dbs:
  - path: /var/lib/myapp/app.db
    replicas:
      - url: s3://my-bucket/myapp

and the commands, per its documentation, are:

litestream replicate -config /etc/litestream.yml
litestream restore -o /var/lib/myapp/app.db s3://my-bucket/myapp

Credentials come from the usual environment variables or instance roles. In a container, the common arrangement is to run Litestream as the entrypoint with -exec, so that it starts your application as a child process, restores the database from the replica if the file is missing at start, and stops when the application stops. That gives a one container deployment that survives losing its disk: on a new host, the database is restored from object storage before the app starts.

What your Go program must do for it

Litestream works by reading the WAL, so the application and the database must cooperate:

• WAL mode is required. Set journal_mode(WAL) in the DSN, as in the WAL article.
• Set a busy_timeout. Litestream takes locks briefly while it works, and a connection that does not wait will occasionally fail with database is locked; see that error.
• Let Litestream control checkpoints. It holds a read lock to keep the WAL segments it has not yet shipped, and it performs checkpoints itself. An application that runs wal_checkpoint(TRUNCATE) on its own can fight with it, so leave automatic checkpoints on and do not truncate by hand.
• Keep one writing machine. Litestream replicates a single database from one place. It is not a way to run a SQLite file on several servers, and two instances writing the same replica corrupt the history.
• Use a local file system. The same restriction as WAL: no network file system.

The last two are the real limit of the design. It gives you durability, with a recovery point of seconds and a restore that you can run on a fresh machine. It does not give you high availability: after a failure, you restore and restart, which takes minutes and has downtime. If you need a standby that takes over, use a server database, or a replicated SQLite system built for it, which is a different category of tool.

Choosing a backup approach

Testing the backup

A backup you have not restored is a hope. Put the restore in a script and run it on a schedule against the latest replica or snapshot: restore to a temporary path, run pragma integrity_check, compare a row count or a checksum of an important table with expected bounds, and alert if any step fails or the data is older than expected. Time the restore, because the number that matters in an incident is how long it takes, which grows with the database and the amount of log to replay. Also practise the failure you fear: delete the data directory of a staging copy, start the container, and check that it restores itself.

The commands used in the lab are documented in the SQLite pages on VACUUM and the backup API, and the replication tool at litestream.io.