← All posts

July 9, 2026

Hourly SQLite backups to object storage with Kamal

Run SQLite on the same VM as your app and one question follows you around: what happens when the disk dies? You want a copy of the database somewhere else, taken often, restorable in one command.

You don't need continuous replication for this. You could get it with Litestream, but that's another post, one I'll write the day I actually need point-in-time recovery on a SQLite project. An hourly gzipped snapshot in object storage, kept for a week, is enough for most apps and far simpler to reason about. Here's the whole thing: one service, one schedule, two rake tasks.

The Kamal angle: the schedule runs inside Solid Queue, embedded in Puma. So the backup ships with the app on every deploy. No cron on the host, no accessory to manage.

Create a bucket

I use Hetzner Object Storage (S3-compatible, cheap, same provider as the VM). In the Hetzner console, create a bucket and an S3 credential. Note four things:

  • Bucket name, for example antelo
  • Endpoint, which looks like https://nbg1.your-objectstorage.com
  • Access key
  • Secret key

Add the gems

# Gemfile
gem "sqlite3", ">= 2.1"
gem "aws-sdk-s3", require: false # Upload hourly database backups to S3-compatible storage

bundle install

require: false keeps the AWS SDK out of boot; the backup service requires it explicitly.

Store the credentials

bin/rails credentials:edit

storage:
  bucket: antelo
  endpoint: https://nbg1.your-objectstorage.com
  access_key: AK...
  secret_key: ...

The backup service

This is the whole mechanism. It snapshots the live database, verifies the snapshot, uploads it gzipped, and prunes anything older than a week. There is no state outside the bucket: the object key carries the timestamp, so listing the prefix is the catalog, and pruning is deleting old keys.

Two things do the important work:

  • VACUUM INTO produces a consistent, compact copy of the database while the app keeps writing. You get a clean snapshot without stopping traffic or copying the file mid-write.
  • PRAGMA quick_check runs before upload. A snapshot that fails integrity never replaces a good hour, so a corrupt file can't quietly become your only backup.
# app/services/database_backup.rb
require "aws-sdk-s3"
require "tmpdir"
require "zlib"

class DatabaseBackup
  PREFIX = "antelo/backups/".freeze
  KEEP_FOR = 7.days
  KEY_FORMAT = /\A#{PREFIX}production-(\d{4})(\d{2})(\d{2})T(\d{2})(\d{2})(\d{2})Z\.sqlite3\.gz\z/

  class VerificationError < StandardError; end

  class << self
    attr_writer :client, :bucket

    # Snapshot, verify, upload gzipped, prune old backups, return the new key.
    def create!
      key = "#{PREFIX}production-#{Time.current.utc.strftime('%Y%m%dT%H%M%SZ')}.sqlite3.gz"
      Dir.mktmpdir("database-backup-") do |directory|
        snapshot_path = File.join(directory, "snapshot.sqlite3")
        ActiveRecord::Base.connection.execute("VACUUM INTO #{ActiveRecord::Base.connection.quote(snapshot_path)}")
        verify!(snapshot_path)
        gzip_path = gzip(snapshot_path)
        File.open(gzip_path, "rb") { |file| client.put_object(bucket: bucket, key: key, body: file) }
      end
      prune
      key
    end

    # All backups, most recent first: { key:, timestamp:, size: }.
    def list
      objects = client.list_objects_v2(bucket: bucket, prefix: PREFIX).flat_map(&:contents)
      backups = objects.filter_map do |object|
        match = object.key.match(KEY_FORMAT)
        next unless match

        { key: object.key, timestamp: Time.utc(*match.captures.map(&:to_i)), size: object.size }
      end
      backups.sort_by { |backup| backup[:timestamp] }.reverse
    end

    # Download and gunzip a backup into `to`: a plain SQLite file, an exact copy
    # of production at the backup's hour.
    def download(key, to:)
      Dir.mktmpdir("database-backup-") do |directory|
        gzip_path = File.join(directory, "download.sqlite3.gz")
        client.get_object(bucket: bucket, key: key, response_target: gzip_path)
        Zlib::GzipReader.open(gzip_path) { |gzip| File.open(to, "wb") { |file| IO.copy_stream(gzip, file) } }
      end
      to
    end

    def prune
      list.select { |backup| backup[:timestamp] < KEEP_FOR.ago }.each do |backup|
        client.delete_object(bucket: bucket, key: backup[:key])
      end
    end

    private

    def verify!(path)
      database = SQLite3::Database.new(path, readonly: true)
      status = database.get_first_value("PRAGMA quick_check")
      raise VerificationError, "backup snapshot failed verification: #{status}" unless status == "ok"
    ensure
      database&.close
    end

    def gzip(path)
      "#{path}.gz".tap do |gzip_path|
        Zlib::GzipWriter.open(gzip_path) { |gzip| File.open(path, "rb") { |file| IO.copy_stream(file, gzip) } }
      end
    end

    def client
      @client ||= Aws::S3::Client.new(
        endpoint: storage_credentials[:endpoint],
        access_key_id: storage_credentials[:access_key],
        secret_access_key: storage_credentials[:secret_key],
        # The SDK requires a region even though Hetzner derives it from the endpoint.
        region: "nbg1",
        force_path_style: true,
      )
    end

    def bucket
      @bucket ||= storage_credentials[:bucket]
    end

    def storage_credentials
      Rails.application.credentials.storage
    end
  end
end

Schedule it every hour

Solid Queue's recurring jobs run the backup on a schedule. A command entry is enough; no job class needed.

# config/recurring.yml
production:
  backup_database:
    command: "DatabaseBackup.create!"
    schedule: every hour

For that schedule to run on a single-server Kamal setup, Solid Queue has to live inside Puma. Set the flag in your deploy:

# config/deploy.yml
env:
  clear:
    SOLID_QUEUE_IN_PUMA: true

# Persist the database across deploys, otherwise each deploy starts empty.
volumes:
  - "antelo_storage:/rails/storage"

The scheduler runs inside the app, so Kamal ships it on every deploy. Nothing extra runs on the host.

bin/kamal deploy

Or auto deploy with Kamal and GitHub Actions, so a merge to main ships it for you.

Back up and restore by hand

Two rake tasks for manual use. backup:now is clever about where it runs: called on your laptop, it re-runs itself inside the production container through Kamal, so you always snapshot production and never your local database.

# lib/tasks/backup.rake
namespace :backup do
  desc "Create a production database backup now and upload it to S3"
  task now: :environment do
    if Rails.env.production?
      puts DatabaseBackup.create!
    else
      command = [Rails.root.join("bin/kamal").to_s, "app", "exec", "--reuse", "bin/rails backup:now"]
      abort "Backup failed." unless system(*command)
    end
  end

  desc "Restore the local development database from the latest production backup"
  task restore: :environment do
    backups = DatabaseBackup.list
    abort "No backups found in the bucket." if backups.empty?

    backup = backups.first
    database_path = Rails.root.join("storage/development.sqlite3").to_s
    FileUtils.mkdir_p(File.dirname(database_path))

    puts "Restoring #{backup[:key]}..."
    DatabaseBackup.download(backup[:key], to: "#{database_path}.restoring")
    FileUtils.mv("#{database_path}.restoring", database_path)
    # Drop stale WAL/SHM sidecars; they belong to the old file.
    FileUtils.rm_f(["#{database_path}-wal", "#{database_path}-shm"])

    # Rewrite the environment marker Rails embeds, or destructive db tasks refuse
    # to run on the restored file because it is still tagged as production.
    database = SQLite3::Database.new(database_path)
    database.execute("UPDATE ar_internal_metadata SET value='development' WHERE key='environment'")
    database.close

    puts "Done. Development now contains production as of #{backup[:timestamp].strftime('%d/%m/%Y %H:%M UTC')}."
  end
end

Run a backup on demand:

bin/rails backup:now

Pull production down to your laptop to debug against real data:

bin/rails backup:restore

A real disaster restore is the same download into storage/production.sqlite3 inside the container: stop the app, swap the file, start again.

That's the full loop. An hourly snapshot you can trust (verified before it lands), a week of history in object storage, and a one-command restore, all shipped with the app by Kamal.