Rails Postgres Backups with pgBackRest: Point-in-Time Recovery, S3 Storage, and Restore Drills

The Slack message came in at 07:41 on a Sunday. A junior developer had run a rake task in production that was supposed to backfill a column on 40,000 rows. It had run against the wrong table and set deleted_at = Time.current on 812,000 customer orders. The team lead was on a plane. The CEO was awake. The database was still running fine — every one of those rows was quietly hidden by the default scope, and neither pagerduty nor the application knew anything was wrong.

I was on the phone with them by 07:52. By 08:07 we had a copy of the database restored to 07:38 UTC on a fresh instance. By 08:34 we had a diff of the 812,000 affected rows and a script that ran on production and reversed exactly the damage the rake task had done. Nobody lost data. The CEO went back to bed.

That is what Rails Postgres backups with pgBackRest buy you, and it is what nightly pg_dump to S3 does not. After nineteen years of Rails I have watched teams treat backups as a checkbox and disaster recovery as “we have a snapshot on RDS.” Neither survives the Sunday morning phone call. This post is the setup I put on every serious Rails production system: pgBackRest, S3, WAL archiving, and a restore drill you run every month.

Why Nightly pg_dump Is Not a Backup Strategy

pg_dump is a great tool. It is not a backup strategy. Three reasons it fails when you need it:

• RPO is 24 hours. If your dump runs at 03:00 and the incident happens at 20:00, you lose 17 hours of data. Modern SaaS cannot honestly promise a customer they will only lose 17 hours of work.
• Recovery is one point in time. You get whatever the state was at 03:00. You cannot rewind to 07:38 to see the world before the rake task fired. If you want that, you need write-ahead log (WAL) archiving.
• It scales badly. A 40 GB dump takes fifteen minutes to write and forty-five to restore. A 400 GB dump takes hours. At that size pg_dump is not what you want to be reading from at all.

RDS automated backups solve the first problem for you if — and only if — you noticed within the retention window, the region has not gone down, and you can restore to a new instance and cut traffic over inside your RTO. They do not solve “I want a copy of the database on my laptop at 07:38 UTC to run a diff against.”

pgBackRest solves all three. It does full, differential, and incremental backups; archives every WAL segment to S3; and lets you restore the cluster to any second between the oldest backup and the last archived segment. It is what Amazon RDS uses under the hood. It is what any Postgres consultant with a phone number worth calling installs on day one.

Installing pgBackRest for a Rails Postgres Cluster

On Ubuntu 24.04 with Postgres 16, the install is one apt line:

apt-get install -y pgbackrest

Configuration lives in /etc/pgbackrest/pgbackrest.conf. A minimal production config for a single Rails app with S3 storage looks like this:

[global]
repo1-type=s3
repo1-s3-bucket=ttb-postgres-backups
repo1-s3-region=eu-central-1
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com
repo1-s3-key-type=auto
repo1-path=/pgbackrest
repo1-retention-full=4
repo1-retention-diff=6
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=REDACTED_LONG_RANDOM_PASSPHRASE

process-max=4
compress-type=zst
compress-level=3
log-level-console=info
log-level-file=detail
start-fast=y
archive-async=y
spool-path=/var/spool/pgbackrest

[app]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432
pg1-user=postgres

Two things about that config matter more than the rest.

repo1-cipher-pass encrypts every backup and every WAL segment before it leaves the host. The S3 bucket policy is not your last line of defense — pgBackRest’s client-side encryption is. Generate a passphrase with openssl rand -base64 48 and store it in your secrets manager. If you lose it, your backups are unrecoverable, which is the point.

repo1-s3-key-type=auto tells pgBackRest to use the instance’s IAM role (or IMDS) rather than a static access key. Give the EC2 instance an IAM role with s3:GetObject, s3:PutObject, s3:DeleteObject, and s3:ListBucket on the bucket only. No IAM keys in /etc/pgbackrest/pgbackrest.conf, ever.

Turning On WAL Archiving in Postgres

Backups without WAL archiving give you nightly snapshots. WAL archiving is what gives you point-in-time recovery.

In postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=app archive-push %p'
archive_timeout = 60
max_wal_senders = 5
wal_level = replica

archive_timeout = 60 forces Postgres to close a WAL segment at least every 60 seconds even if it is not full. That caps your worst-case RPO at 60 seconds of lost transactions — the trade is a few extra small WAL files per day, which pgBackRest handles cheaply.

Restart Postgres, then initialize the stanza and take the first backup:

sudo -u postgres pgbackrest --stanza=app stanza-create
sudo -u postgres pgbackrest --stanza=app --type=full backup

Check the state:

sudo -u postgres pgbackrest --stanza=app info

You want to see status: ok, at least one full backup, and — critically — an “archive” section showing WAL segments being pushed. If the archive section is empty, Postgres is not calling archive_command correctly, and you have no PITR window. Fix that before you go to bed.

The Backup Schedule I Actually Use

Weekly full, daily differential, and continuous WAL archiving via archive_command. In /etc/cron.d/pgbackrest:

# weekly full backup, Sunday 02:15 UTC
15 2 * * 0  postgres  pgbackrest --stanza=app --type=full backup

# daily differential backup, Mon-Sat 02:15 UTC
15 2 * * 1-6  postgres  pgbackrest --stanza=app --type=diff backup

# hourly expire check to enforce retention
0 * * * *  postgres  pgbackrest --stanza=app expire

On a 100 GB Postgres cluster this produces roughly 30 GB compressed on Sunday, 3-8 GB per weekday differential, and around 15 GB of WAL per day. On S3 Standard-IA that is under $10 a month for four weeks of retention with a 60-second RPO. Nobody has ever regretted spending that money.

Point-in-Time Recovery: The Sunday Morning Restore

This is the scenario that started this post. It is 07:52 UTC on Sunday. A rake task ran at 07:38 that we want to undo. Restore the database to 07:37:30 UTC on a fresh instance:

# on a fresh Postgres 16 host with pgBackRest installed and configured
sudo -u postgres pgbackrest \
  --stanza=app \
  --type=time \
  --target="2026-09-09 07:37:30+00" \
  --delta \
  restore

Then start Postgres:

sudo systemctl start postgresql@16-main

pgBackRest downloads the most recent full and differential backups, applies WAL segments up to 07:37:30, and stops. Postgres comes up in recovery mode, promotes when the target is reached, and you have a read-write copy of the database at exactly the second before the disaster.

From there you export the affected rows to a CSV, ship the CSV to production, and write a targeted UPDATE that reverses the specific damage. You do not cut traffic over to the restored copy. Production has been running normally the whole time; you only need the historical view to compute the diff. Ninety-nine percent of “restore from backup” incidents are actually “give me a read-only snapshot of the past so I can figure out what changed.”

The Rails Rake Tasks I Ship With Every App

I put these in lib/tasks/backups.rake in every Rails app I set up. They wrap pgBackRest in a Rails-native interface so anyone on the team can check the state without SSHing to the database host.

namespace :backups do
  desc "Show pgBackRest info for the primary database"
  task info: :environment do
    output = `ssh postgres@#{db_host} pgbackrest --stanza=app info`
    puts output
    abort "pgBackRest info failed" unless $?.success?
  end

  desc "Verify last backup succeeded within threshold hours (default 26)"
  task :verify, [:hours] => :environment do |_, args|
    hours = (args[:hours] || 26).to_i
    json = `ssh postgres@#{db_host} pgbackrest --stanza=app --output=json info`
    data = JSON.parse(json)
    stop = data.dig(0, "backup", -1, "timestamp", "stop")
    age_hours = (Time.now.to_i - stop) / 3600.0

    if age_hours > hours
      abort "Last backup is #{age_hours.round(1)}h old (threshold #{hours}h)"
    else
      puts "Last backup #{age_hours.round(1)}h old — OK"
    end
  end

  def db_host
    ActiveRecord::Base.connection_db_config.configuration_hash[:host]
  end
end

Wire the backups:verify task into a scheduled job — Solid Queue recurring jobs is fine for this — and have it page you if the last backup is older than 26 hours. Backup jobs fail silently by default. The whole point of alerting is to find out on Tuesday that Sunday’s differential failed, not on the following Sunday morning when you need it.

Restore Drills: The Practice That Separates Real Recovery From Wishful Thinking

Every team I have worked with that had backups configured but no drill has, at least once, discovered during a real incident that the backups did not restore. WAL archiving stopped four months ago. The S3 bucket was in a different region than the encryption key. The passphrase in the secrets manager was a typo. Nobody found out until it mattered.

Book a two-hour slot on the first Monday of every month. Assign it to a rotating on-call engineer. The drill is:

1. Spin up a fresh EC2 instance in a non-production account.
2. Install Postgres 16 and pgBackRest, drop in the encrypted config from your secrets manager.
3. Restore the database using --type=time to a target four hours in the past.
4. Start Postgres, run SELECT count(*) FROM users, SELECT max(created_at) FROM orders, and one production-representative query.
5. Time the whole thing from terraform apply to psql prompt. Write it in a Notion page called “Last Restore Drill.”
6. Destroy the instance.

The metric that matters is time-to-first-query, which is your realistic RTO. Ours is around 22 minutes on 120 GB. A team that has never drilled will discover their real RTO is 8 hours because half of it is figuring out the encryption passphrase.

If you also run Postgres logical replication or monitor with PgHero, drill them the same way. A capability you have not exercised in three months is a capability you do not have.

What I Do Not Do

• No pg_dump to S3 as a primary backup. Fine as a secondary “give me a portable schema+data snapshot” tool, useless as a recovery strategy at real scale.
• No relying only on RDS automated backups. They are good, but they are one region, one account, one AWS billing relationship. Cross-account, encrypted, client-side pgBackRest to S3 in a different region survives things RDS backups do not.
• No skipping WAL archiving. Without it the whole system collapses to nightly snapshots. It is the single most important line in postgresql.conf for any production Rails app.
• No untested restore procedures. A backup that has never been restored is Schrödinger’s backup. Assume it is broken until the drill proves it is not.

Frequently Asked Questions

How much does pgBackRest to S3 actually cost per month?

For a typical Series A Rails app with a 100 GB Postgres cluster, expect around $8-15/month on S3 Standard-IA for four weeks of retention with 60-second WAL archiving. Cost breakdown: one full weekly backup (~30 GB compressed with zstd), six daily differentials (~3-8 GB each), and roughly 15 GB of WAL per day. Enable S3 lifecycle policies to move backups older than 30 days to Glacier Deep Archive if you keep long-term copies for compliance. The S3 bill for backups is almost always the cheapest line item in the entire infrastructure budget — do not let cost be the reason you skip WAL archiving.

Can I use pgBackRest with Amazon RDS or Aurora?

No, and you do not need to. RDS and Aurora both take their own snapshots and archive WAL internally — you get automated backups and PITR out of the box, though only within your retention window (up to 35 days) and only within AWS. If you want cross-account or cross-region encrypted backups outside AWS, use pg_dump to a separate account weekly as a defense-in-depth measure, or run a self-managed logical replica and back that up with pgBackRest. For self-hosted Postgres on EC2, Hetzner, or bare metal, pgBackRest is the correct answer.

What is a realistic RTO and RPO for a Rails Postgres app with pgBackRest?

With archive_timeout = 60 and asynchronous WAL push, your worst-case RPO is roughly 60-90 seconds of lost transactions after a total-host failure — the last WAL segment might not have been pushed to S3 yet. RTO depends on database size and hardware: on a 120 GB cluster with 4 vCPUs and gp3 storage, expect around 20-25 minutes from a cold EC2 instance to a psql prompt on the restored database. On 1 TB, plan for 60-90 minutes. Both numbers assume you have drilled the process. Undrilled, add hours for finding the passphrase and debugging IAM.

Do I need pgBackRest if I already have Postgres streaming replication?

Yes. Streaming replication protects against hardware failure of the primary — it does not protect against logical corruption. If a developer runs UPDATE orders SET deleted_at = now() on 800,000 rows, that statement replicates to every standby in milliseconds. Streaming replication and PITR backups solve different problems and every serious production Rails setup has both. Streaming replication is your high-availability story; pgBackRest with WAL archiving is your disaster recovery and point-in-time undo story.

Need help setting up production-grade Postgres backups, WAL archiving, and restore drills for your Rails app? TTB Software does Rails infrastructure work and Postgres reviews for teams that cannot afford to lose data. Nineteen years of Rails, and I have never lost a byte for a client.