How do you back up the SQLite production database of a running Rails 8.1 app, and why is a plain cp of storage/production.sqlite3 unsafe?
answer
- recent commits wait in the -wal file
- copy through SQLite, not the filesystem
- VACUUM INTO a new file
- SQLite3::Backup with step(-1)
- ship it off the server, test restores
basics
~20 sCopy through SQLite itself, with VACUUM INTO or the online backup API the sqlite3 gem exposes as SQLite3::Backup, then ship the copy off the server. A cp of the main file can miss commits still in the -wal file or catch a write halfway.
solid answer
~40 sIn WAL mode, recently committed transactions live in `production.sqlite3-wal` until a checkpoint moves them into the main file, and writes keep landing while `cp` reads. A raw copy of the main file can therefore miss recent invoices or capture a torn mix of pages. Take the snapshot through SQLite so it is transactionally consistent: run `VACUUM INTO '/backups/…sqlite3'` on a connection, or use the gem's `SQLite3::Backup.new(dest, "main", source, "main")` with `step(-1)` and `finish`. Run it from `bin/rails runner` on a schedule, copy the result off the host (the generated deploy config tells you to back the volume up off server), keep the queue database too if pending jobs matter, and restore a copy now and then to prove the backup works.
code
ruby · 8 lines# bin/rails runner script/backup_db.rb
dest = SQLite3::Database.new("/backups/invoices.sqlite3")
ApplicationRecord.with_connection do |conn|
backup = SQLite3::Backup.new(dest, "main", conn.raw_connection, "main")
backup.step(-1)
backup.finish
end
dest.closego deeper
Recall that the SQLite file is all of the data and needs a regular copy taken through SQLite, kept off the server.
Explain why WAL makes a file copy unsafe and how VACUUM INTO or SQLite3::Backup produce a consistent snapshot.
Design the schedule, off-site storage and restore drill, and decide which of the four files must be kept.
Set the recovery objective with the business and decide whether nightly copies or continuous WAL shipping meets it.
## Why the obvious copy is wrong A Rails 8.1 app on SQLite keeps its production data in files under `storage/`, and the adapter runs every connection with `journal_mode` set to WAL. In WAL mode a commit is appended to the **write-ahead log**, `production.sqlite3-wal`, and only later copied into `production.sqlite3` by a **checkpoint**. Meanwhile the app keeps writing. So `cp storage/production.sqlite3 /backups/` has two problems: - **Missing commits.** Everything committed since the last checkpoint is in the `-wal` file, not in the file you copied. - **Torn copies.** `cp` reads the file over time; if a checkpoint writes pages while it reads, the copy can mix pages from before and after, which SQLite may later report as a corrupt database. Copying the `-wal` file as well does not fix it, because the two files are read at different moments. The copy has to be made **through SQLite**, which reads a consistent snapshot inside a transaction. ## Safe ways to take the copy | Method | Where it comes from | Notes | |---|---|---| | `VACUUM INTO 'file'` | a SQL statement on any connection | writes a compacted, consistent copy to a new file | | `SQLite3::Backup` | the sqlite3 gem's binding to SQLite's online backup API | copies page by page; `step(-1)` copies everything in one go | | the `sqlite3` command-line shell | installed in the generated Dockerfile for SQLite apps | its backup command uses the same online backup API | | a volume snapshot | the host or cloud provider | only safe if it captures the database and `-wal` atomically, like a crash | Inside the app, the first two are a few lines run with `bin/rails runner`: ```ruby # script/backup_db.rb path = Rails.root.join("tmp", "backup-#{Time.current.strftime('%Y%m%d%H%M')}.sqlite3") ApplicationRecord.with_connection do |conn| conn.execute("VACUUM INTO #{conn.quote(path.to_s)}") end ``` `VACUUM INTO` cannot run inside an open transaction, so keep it out of `transaction` blocks. The `SQLite3::Backup` route works with two raw `SQLite3::Database` objects: `SQLite3::Backup.new(dest_db, "main", source_db, "main")`, then `step(-1)` (returns `DONE` when every page is copied) and `finish`. ## Making it a real backup For the carpentry firm's single-server invoicing tool, a copy on the same disk protects against a bad migration but not against losing the server. A backup routine needs: 1. **A schedule.** A nightly run, for example as a recurring job, plus a run before risky deploys or migrations. 2. **Off-server storage.** The generated deploy configuration mounts a storage volume and comments that it should be a mounted path "backed up off server". Upload each copy to object storage or another machine, and prune old ones. 3. **Every file that matters.** `production.sqlite3` holds invoices. `production_queue.sqlite3` holds pending and scheduled jobs. `production_cache.sqlite3` can be rebuilt and usually is not worth keeping. 4. **Restore drills.** Copy a backup into a scratch environment, point `database:` at it, boot the app and open a few invoices. A backup nobody has restored is a hope, not a backup. 5. **A recovery objective.** A nightly copy loses up to a day of invoices. If that is too much, take copies more often or stream WAL changes continuously to off-site storage with a replication tool built for SQLite. ## Restoring without making it worse 1. Stop the processes that write: the web server and the job supervisor. 2. Move the current `production.sqlite3` **together with** its `-wal` and `-shm` files aside, so a stale log is not left beside the restored file. 3. Copy the backup in as `production.sqlite3`, owned by the user the app runs as. 4. Start the app and check a few recent invoices against what the business expects to see. ## What a backup is not - **`db/schema.rb` or `db:schema:dump`**: tables, no rows. - **`db:seed`**: sample data you wrote, not the customers' records. - **A git commit of `storage/`**: the generated `.gitignore` excludes it, and a repository is no place for customer data anyway. - **The volume alone**: a persistent volume survives a redeploy, but not a dead disk or a deleted server.
- Which of the four production SQLite files in a Rails 8.1 app would you leave out of backups, and why?Usually `production_cache.sqlite3`: Solid Cache entries can be recomputed, so losing them costs a warm-up, not data. Keep `production.sqlite3` (the business data) and normally `production_queue.sqlite3`, because it holds enqueued and scheduled jobs. The cable file holds short-lived broadcast messages and rarely needs a backup.
- Why is a nightly VACUUM INTO not enough for some businesses, and what would you add?It can lose up to a day of commits if the server dies just before the next run. If the business cannot re-enter a day of invoices, take copies more often or ship WAL changes continuously to off-site storage, and measure how long a restore takes, not only how often you copy.
- Copies production.sqlite3 with cp while the app is writing
- Treats db/schema.rb as a backup of production data
- Keeps the only backup on the same server as the database
- Counts the persistent volume itself as the backup
- Never restores a backup to check that it works