Physical base backup vs logical dump for 4‑hour RTO on 500 GB Rails/PostgreSQL

Answer first

Use physical base backup with PITR (pg_basebackup + WAL archiving). It is the only option here that fits the 4-hour RTO: restoring the 500 GB data directory takes roughly 45 minutes, leaving ample budget for WAL replay, startup, and verification. A logical restore (pg_dump/pg_restore) at 6–8 hours on ~8 cores blows the budget before you even start application checks, and adding workers does not help because catalog lock contention plateaus around 8 parallel jobs.

Why physical wins for this case

Physical restore is a file copy plus WAL replay — it scales with disk throughput, not with the number of tables, indexes, or constraints that a logical restore must rebuild serially-ish. The trade-off is real but acceptable here: the DR target must run the same PostgreSQL major version on a compatible OS/architecture, and pg_control must match. If your DR environment is heterogeneous (different PG major, different OS), physical restore is off the table and you would need to either replicate the production environment for DR or accept the longer logical restore and renegotiate the RTO.

Blob integrity: what each method does and does not cover

Neither backup method touches ActiveStorage blobs:

• S3/GCS blobs survive independently of the database. After either restore, the risk is orphaned or missing references, not blob corruption. Verify with an existence sweep: ActiveStorage::Blob.find_each { |b| raise unless b.service.exists? }. This is O(n) API calls — run it as a background job, not a gate on declaring recovery complete, or it will eat your RTO margin.
• Local-disk blobs are invisible to both pg_basebackup and pg_dump. You need a separate rsync --checksum or filesystem snapshot (LVM/ZFS) of the storage directory, taken consistently with the DB backup window, and verified the same way after restore. Skipping this breaks blob integrity regardless of which DB method you pick.

Sidecar manifest without custom tooling

Yes — pg_basebackup has this built in. Run it with --manifest-checksums=SHA256 (the default algorithm in current versions) and it writes a backup_manifest file listing every file in the backup with its checksum. After copying the backup to the restore target, verify it with:

pg_validatebackup /path/to/backup

This is stock PostgreSQL tooling — no custom scripts needed. Logical dumps have no equivalent; pg_dump's custom-format TOC lists objects but does not checksum data, so integrity assurance there rests on pg_restore exiting cleanly plus application-level checks.

Mandatory verification per method

Physical (pg_basebackup + PITR)

1. Run pg_validatebackup against the manifest before starting anything.
2. If the cluster was initialized with data_checksums=on, run pg_verify_checksums on the restored data directory while the server is stopped.
3. Start PostgreSQL, confirm WAL replay reached the target point (check the logs for "consistent recovery state reached" / replay completion).
4. Run bin/rails db:migrate:status to confirm schema matches the app version you are bringing up.
5. Kick off the ActiveStorage existence sweep (background) and verify local-disk attachments via rsync checksum comparison if applicable.

Logical (pg_dump/pg_restore)

1. Confirm pg_restore completed without errors (use --exit-on-error or review the error list with -l).
2. Row-count audits against known totals for critical tables.
3. Validate constraints: SELECT conname, conrelid::regclass FROM pg_constraint WHERE NOT convalidated; and re-validate any stragglers.
4. Reindex/ANALYZE as needed, then the same migration-status and blob-existence checks as above.

Bottom line

Physical backup + PITR meets the RTO with ~3 hours of slack; logical restore fails the RTO on its own. Use backup_manifest + pg_validatebackup for DB-file integrity, handle ActiveStorage separately (existence sweep for cloud blobs, checksummed rsync/snapshot for local blobs), and pin your DR target to the same PostgreSQL major version and platform. One caveat worth confirming before committing: whether your DR site can guarantee that platform match — if it cannot, the recommendation flips and the RTO conversation has to reopen.