If you're using SQLite in Write-Ahead Logging (WAL) mode, a straightforward file copy of the .db file won't suffice for a proper backup. This is because WAL mode utilizes a separate write-ahead log (.wal file) to manage transactions. Until those transactions are checkpointed into the main database file, they exist only in the WAL. Therefore, copying just the .db can result in an inconsistent state. The best method is to use SQLite's .backup() API, which ensures atomic checkpointing, or if you prefer file-level backups, you should copy the .db, .wal, and .shm files together, ideally after checkpointing the WAL first. Many developers, including myself, learned this the hard way, and I'm curious how others manage SQLite backups in production environments.
5 Answers
Litestream is a smart choice because it directly follows the WAL, so you don't deal with the complications of doing manual backups. If you want simplicity and automation, it might be the way to go.
Before we switched to Postgres, we had a routine to back up SQLite using:
```
sqlite3 "PRAGMA wal_checkpoint(FULL);"
sqlite3 "VACUUM;"
sqlite3 "PRAGMA wal_checkpoint(TRUNCATE);"
```
This probably sounds like overkill, but the full checkpoint syncs the WAL data with the .db file, and the VACUUM command really cleans things up. After that, we could safely copy the .db file.
Honestly, for anyone unsure about database backups, it's always best to read the official documentation for your database. A quick search for "[DB name] backup" can save you a lot of headaches down the line.
I learned the hard way, too! I had a cron job set up to copy the .db file nightly, thinking I was good. It wasn't until I tried to restore that I found half my data was missing! Now I just run the sqlite3 .backup command in a little script, and it works like a charm. Litestream is also a great option for continuous backups to S3 if you're looking for something less hands-on.
If you're going to back up your SQLite database, you really need to ensure that all three files (.db, .wal, and .shm) are synchronized. Just copying one without the others can lead to corruption if changes happen during the backup process. A copy-on-write filesystem like btrfs can help by allowing you to take a full snapshot of the partition instead of copying files individually.
For sure! The .backup() API handles all the locking and checkpoint processes automatically, which is super helpful.
Exactly! Having it automated is a game changer for peace of mind.