todor.

My backup was quiet for six weeks

date
read
6 min
in
dev

I was moving the server this blog runs on to a new OS and went through everything on the old one to see what had to come along. In the folder of my backup script I found 44 database dumps. That folder is supposed to be empty. The script dumps the databases every night, uploads them to Google Drive and deletes the local copies as its last step.

The oldest file was from the 29th of September. So for six weeks the script had been starting every night and not finishing, and I had no idea.

What the backup does

It's a small Ruby script I wrote in 2023 and barely touched since. Cron runs it as root at three in the morning:

0 3 * * * /usr/local/rbenv/shims/ruby /root/backitup/backup.rb

For every database it runs pg_dump, gzips the result and uploads it to a folder on Google Drive with the google_drive gem. It logs in as a Google service account, so there's no browser and no token to refresh. After each upload it posts a line with a green tick to a Slack channel. This blog's database is 61 KB gzipped, so I never thought about space.

Six weeks of nothing

There was no log to read. Cron mail on my server goes nowhere, same as in the Cloudflare post. So I ran the script by hand and it told me straight away:

storageQuotaExceeded: The user's Drive storage quota has been exceeded. (Google::Apis::ClientError)

My own Drive has plenty of room, which confused me for a minute. A service account is its own Google user with its own 15 GB. Files it uploads count against that, even when they land in a folder I shared with it from my account. I asked the API what the account was holding:

limit=15.00 GB usage=15.00 GB
todordev_production      files=170   size=0.01 GB

The same account used to back up the Outmuscle Me database too, up to 46 MB a night, and nothing ever deleted an old dump. It took almost three years to fill 15 GB and on the 29th of September the first upload bounced.

Why didn't I notice? The script only talked to Slack when an upload worked. A failed night looked like a quiet channel, and a quiet channel doesn't catch my eye. It also stopped at the first error. The first database in the list failed, so the second one was never dumped and the cleanup at the bottom never ran. That's where the 44 files came from. They were the only alarm I had, and I found them by accident.

The fix

First I deleted the 956 old dumps, which took the account from 15 GB to almost nothing. Then three changes to the script. Dumps older than 30 days get deleted from Drive on every run. A database that fails gets reported to Slack and the others still run. And the script exits with 1 when anything failed, so whatever runs it can tell.

The retention rule is one small method. Dumps are named 2026-11-11-todordev_production.sql.gz, so the date is in the name:

RETENTION_DAYS = 30

# Dumps are named <date>-<database>.sql.gz. A dump is expired once its date
# is more than RETENTION_DAYS before today.
def expired?(filename, today)
  today - Date.strptime(filename[/\A\d{4}-\d{2}-\d{2}(?=-.+\.sql\.gz\z)/].to_s, '%Y-%m-%d') > RETENTION_DAYS
rescue Date::Error
  false
end

Anything without a date in front, or with a date that doesn't exist, is kept. I'd rather leave a stray file on Drive than delete something I didn't write. This is the one part with tests, because it's the part that deletes things:

def test_dump_older_than_the_retention_period_is_expired
  assert expired?('2026-10-11-umai_production.sql.gz', Date.new(2026, 11, 11))
end

def test_dump_exactly_at_the_retention_period_is_kept
  refute expired?('2026-10-12-umai_production.sql.gz', Date.new(2026, 11, 11))
end

def test_file_without_a_date_is_kept
  refute expired?('135924.sql', Date.new(2026, 11, 11))
end

And the rest of the script:

def back_up(databases)
  date = Date.today.strftime '%Y-%m-%d'
  session = GoogleDrive::Session.from_service_account_key File.join(__dir__, 'config', 'key.json')
  folder = session.collection_by_id FOLDERID

  failed = databases.reject do |database|
    filename = "#{date}-#{database}.sql"
    system('pg_dump', database, "--username=#{DBUSER}", "--file=/root/backitup/backups/#{filename}") or raise 'pg_dump failed'
    system('gzip', '--force', "/root/backitup/backups/#{filename}") or raise 'gzip failed'

    folder.upload_from_file "/root/backitup/backups/#{filename}.gz", "#{filename}.gz", convert: false

    puts "Finished backup to #{filename}.gz"
    notify_slack("✅ Backup completed: #{filename}.gz uploaded to Google Drive.")
    true
  rescue StandardError => e
    warn "Backup of #{database} failed: #{e.class}: #{e.message}"
    notify_slack("❌ Backup failed: #{database}: #{e.class}: #{e.message}")
    false
  end

  expired = []
  folder.files { |file| expired << file if expired?(file.title, Date.today) }
  expired.each { |file| file.delete(true) }
  puts "Deleted #{expired.size} expired dumps from Google Drive"

  FileUtils.rm_rf '/root/backitup/backups/.', secure: true
  failed.empty?
end

if $PROGRAM_NAME == __FILE__
  begin
    exit 1 unless back_up(DATABASES)
  rescue StandardError => e
    notify_slack("❌ Backup failed: #{e.class}: #{e.message}")
    raise
  end
end

notify_slack is a plain Net::HTTP post to a Slack webhook. The guard at the bottom is there so the tests can load the file without starting a backup. To see the red cross work I ran it against a database that doesn't exist. Slack got the message and the exit code was 1.

Is the backup a backup

An upload that worked proves a file exists on Drive. It doesn't prove I can restore from it. So I downloaded both dumps again and looked inside:

gunzip -c 2026-11-11-todordev_production.sql.gz | grep -c '^CREATE TABLE'
# 32

gunzip -c 2026-11-11-todordev_production.sql.gz | grep -c 'PostgreSQL database dump complete'
# 1

32 tables, same as the live database, and pg_dump's own closing line is there. A dump that got cut off halfway doesn't have that line. It's not a full restore test, but it's a lot more than I had checked in three years.

A few things I learned along the way

Backticks in Ruby don't care if the command failed. The old script ran pg_dump in backticks, so a failed dump would have uploaded an empty file and posted a green tick. system(...) or raise stops that.

folder.files in the google_drive gem returns one page of results. With a block it walks all pages. With over a thousand files in the folder that's the difference between a retention rule and a rule that deletes whatever happens to be on page one.

file.delete moves a file to the trash and the trash still counts against the quota. file.delete(true) deletes it for good.

gzip refuses to overwrite an existing file. After a failed night the next run on the same day stopped at "already exists" before it got anywhere near Drive. --force fixes that, and so does a cleanup that always runs.

Deleting through the Drive API is one request per file. 956 files took a bit over ten minutes.

The first run with retention deleted every dump older than 30 days. That was all of them, because nothing had been uploaded for six weeks. For a moment Drive held exactly two files, both from that afternoon. If the history had mattered to me I would have wanted to know that before pressing enter.

Summary

  • A nightly Ruby script dumps each Postgres database and uploads it to Google Drive through a service account

  • The service account has its own 15 GB, and when that filled up every upload failed for six weeks

  • The script only reported success, so a failure looked like silence

  • Now it deletes dumps older than 30 days, reports a failed database to Slack and keeps going with the rest

  • After an upload, download the dump and check that it's complete