I access an SQLite database via mattn/go-sqlite3. I want to perform a live snapshot of that database. The backup must be saved to an io.Writer instance. Specifically the SaveSnapshot() function for a raft implementation.

Is it possible to use SQLite's online backup system to pipe to this io.Writer instance? Is there a special file or named pipe I can use as the output database? I would be fine running a SELECT statement copying data to my writer, but how to ensure I am seeing a current snapshot while allowing updates to happen while this is taking place?

I access an SQLite database via mattn/go-sqlite3. I want to perform a live snapshot of that database. The backup must be saved to an io.Writer instance. Specifically the SaveSnapshot() function for a raft implementation.

Is it possible to use SQLite's online backup system to pipe to this io.Writer instance? Is there a special file or named pipe I can use as the output database? I would be fine running a SELECT statement copying data to my writer, but how to ensure I am seeing a current snapshot while allowing updates to happen while this is taking place?

• sqlite
• go
• raft

Share Improve this question Follow edited Feb 8 at 3:19 user4157124 3,0021414 gold badges3131 silver badges4646 bronze badges asked Nov 22, 2024 at 15:44 poundifdefpoundifdef 19.4k2828 gold badges100100 silver badges141141 bronze badges 3

• 1 Would backing up to a temporary file and then shoveling its content to that io.Writer work for you? I'm asking because "the backup" functionality is likely to require a random-access destination file. It's possible to dump an SQLite database (grab its source code and see how the .dump shell command is implemented—basically, it's 3 SQL queries) to a stream, but it would dump SQL statements, not "native" backup format. – kostix Commented Nov 22, 2024 at 18:00
• Ideally, I would avoid the temporary file. The purpose of this snapshot is to duplicate data to a new machine, which could be streamed. However, for that to work, it assumes that the backup process does not require random writes. I don't know if that is the case for sqlite's online backups. – poundifdef Commented Nov 23, 2024 at 5:57
• A cursory look at the backup code hints it indeed requires random access to the destination file. So, I guess my initial assessment appears to hold true: you either go with the temporary file which you shovel at your destination once the backup is complete or go with SQL dump, wihch is streamable, and recreate the destination DB from that dump on the receiving end (whatever it is). – kostix Commented Nov 25, 2024 at 7:24

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You could backup to an in-memory database and then serialize it to a buffer.

Using mattn/go-sqlite3, do:

// acquire connections somehow
srcConnection, _ := sql.Open("sqlite3", "/path/to/database.db")
dstConnection, _ := sql.Open("sqlite3", ":memory:")

// get reference to underlying driver connection object
src := srcConnection.(*sqlite3.SQLiteConn)
dst := dstConnection.(*sqlite3.SQLiteConn)

// start a backup operation
//
// see: https://www.sqlite./c3ref/backup_finish.html#sqlite3backupinit
// see: https://pkg.go.dev/github/mattn/go-sqlite3#SQLiteBackup
backup, _ := dst.Backup("main" /* name of the database */, src, "main")
defer backup.Finish()

// stepping copies the pages
//
// see: https://www.sqlite./c3ref/backup_finish.html#sqlite3backupstep
// see: https://pkg.go.dev/github/mattn/go-sqlite3#SQLiteBackup.Step
_, _ = backup.Step(-1 /* number of pages to copy */)

// serialize the database into a buffer
data, _ := dst.Serialize("main")

// copy the buffer into sink
_, _ = io.Copy(sink, bytes.NewReader(data)) // assuming sink is your final io.Writer

Add error handling at each step!