skip to content

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?

level: seniorimportance: should knowfreq 28%

answer

  1. recent commits wait in the -wal file
  2. copy through SQLite, not the filesystem
  3. VACUUM INTO a new file
  4. SQLite3::Backup with step(-1)
  5. ship it off the server, test restores

basics

~20 s

Copy 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 s

In 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
ruby
# 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.close

go deeper

for a junior

Recall that the SQLite file is all of the data and needs a regular copy taken through SQLite, kept off the server.

for a middle

Explain why WAL makes a file copy unsafe and how VACUUM INTO or SQLite3::Backup produce a consistent snapshot.

for a senior

Design the schedule, off-site storage and restore drill, and decide which of the four files must be kept.

for a principal

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