2 ms·
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."
by rmunn 3mo ago
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 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 3mo agoAt 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 https://www.sqlite.org/howtocorrupt.html -- the backup was close()ing the database file descriptor and canceling the SQlite locks.
- discordance 3mo agoGood 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'"