[rmunn]

There's some bad advice in the article:

"Backups are a file copy. [...] you can back up a live SQLite database, under write load, without stopping anything."

This is straight out of section 1.2 of https://www.sqlite.org/howtocorrupt.html. Yes, you can do that, and sometimes you will end up with a valid, non-corrupt backup. But it's timing-dependent: lose the race and you'll end up backing up a partially written transaction, making the backup corrupt. They didn't end up losing that race when they wrote the article, but that doesn't mean it is safe 100% of the time.

The section later on about running "VACUUM INTO backup-$(date +%F).db" is 100% safe, though: SQLite guarantees that you'll get consistent state if you do that.

[luke5441]

At some point I thought I would be clever and just backup the file while no transaction is active (or manual WAL checkpointing and no WAL checkpointing active).

Ran into 2.2. https://www.sqlite.org/howtocorrupt.html -- the backup was close()ing the database file descriptor and canceling the SQlite locks.

[discordance]

Good call.

I use Litestream for offsite backups. SQLite also has a .backup command:

sqlite3 /path/to/db '.backup /path/to/backup'

or

sqlite3 /path/to/db "VACUUM INTO '/path/to/backup'"