You Don't Have Backups. You Have Backup Files.
Taking backups and having backups are two different things. The gap between them is a restore test you've never run.
Every client I've ever onboarded has told me they have backups. Almost none of them have had backups. What they had were backup files — nightly dumps sitting in an S3 bucket, untouched, untested, quietly rotting. There's a meaningful difference between those two things, and most people don't find out which category they're in until the worst possible moment.
This is the post I wish someone had handed me fifteen years ago, before I spent a Sunday helping a client recover a Postgres database from a corrupted dump file that had been "backing up successfully" for four months.
The Actual Problem
Backup tooling is mature. S3 is cheap. Cron is everywhere. Setting up a nightly pg_dump piped to a compressed file on object storage takes maybe an hour. So teams do that, check the box, and move on. And honestly, the backup part works fine most of the time.
The restore part is where things fall apart.
A backup file is only useful if you can restore from it under pressure, in a reasonable amount of time, to a known-good state. That sounds obvious. But restoring is a different operation than dumping, it involves different permissions, different tooling flags, potentially different Postgres versions, and it takes time you didn't budget for. If you've never actually done it, you don't know how long it takes. You don't know what breaks. You don't know if your dump even loads cleanly.
I've seen dump files that were silently incomplete because the backup user lacked SELECT on a few tables. I've seen gzip streams that were truncated because the disk filled up mid-write and the exit code still came back 0. I've seen restore jobs fail because the target Postgres version was two major versions ahead of the source. None of these failures announced themselves. They were all discovered during an actual incident.
The Automation Gap
Here's the specific gap I want to name: most backup setups have monitoring on the write side (did the job run? did it produce a file?) but no monitoring on the read side (does the file actually restore to a working database?).
Those are fundamentally different signals. Monitoring job success tells you a file exists. It tells you nothing about whether that file is usable.
Closing this gap means automating restore tests on a schedule and treating failures as pages, not log noise. Here's how I do it for NWOS-managed PostgreSQL databases.
A Working Restore Test Script
This is a simplified version of what I actually run. It lives in a Laravel console command that gets scheduled weekly, spun up on a separate instance so it doesn't touch production.
<?php
namespace App\Console\Commands;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Log;
class TestDatabaseRestore extends Command
{
protected $signature = 'db:test-restore';
protected $description = 'Download latest backup and verify it restores cleanly';
public function handle(): int
{
$bucket = config('backup.s3_bucket');
$prefix = config('backup.s3_prefix');
$dbName = 'restore_test_' . date('Ymd_His');
$dumpFile = "/tmp/{$dbName}.sql.gz";
$start = microtime(true);
try {
// Find the most recent backup object
$latest = $this->getLatestBackupKey($bucket, $prefix);
if (!$latest) {
$this->alertFailure('No backup file found in S3');
return Command::FAILURE;
}
$this->info("Found backup: {$latest}");
// Download from S3
$downloadOk = $this->downloadFromS3($bucket, $latest, $dumpFile);
if (!$downloadOk) {
$this->alertFailure('S3 download failed');
return Command::FAILURE;
}
// Create isolated test database
$this->runPsql('postgres', "CREATE DATABASE {$dbName};");
// Attempt restore
$restoreCmd = "gunzip -c {$dumpFile} | psql -U postgres -d {$dbName} 2>&1";
exec($restoreCmd, $output, $exitCode);
if ($exitCode !== 0) {
$this->alertFailure('pg_restore exited non-zero', implode("\n", $output));
return Command::FAILURE;
}
// Spot-check: verify a critical table exists and has rows
$count = $this->runPsqlQuery($dbName, "SELECT COUNT(*) FROM users;");
if ((int)$count < 1) {
$this->alertFailure('Restored database has no users — likely incomplete dump');
return Command::FAILURE;
}
$elapsed = round(microtime(true) - $start, 1);
Log::channel('backup')->info("Restore test passed", [
'backup' => $latest,
'rows' => $count,
'elapsed' => "{$elapsed}s",
]);
$this->info("Restore test passed in {$elapsed}s. Rows in users: {$count}");
return Command::SUCCESS;
} finally {
// Always clean up
@unlink($dumpFile);
$this->runPsql('postgres', "DROP DATABASE IF EXISTS {$dbName};");
}
}
private function alertFailure(string $reason, string $detail = ''): void
{
Log::channel('backup')->critical("Restore test FAILED: {$reason}", [
'detail' => $detail,
]);
// POST to your alerting webhook — PagerDuty, Slack, whatever
Http::post(config('backup.alert_webhook'), [
'text' => "[RESTORE TEST FAILED] {$reason}\n{$detail}",
]);
}
private function getLatestBackupKey(string $bucket, string $prefix): ?string
{
// Using AWS CLI here; you could equally use League/Flysystem S3
$cmd = "aws s3 ls s3://{$bucket}/{$prefix}/ --recursive | sort | tail -n 1 | awk '{print $4}'";
$output = shell_exec($cmd);
return $output ? trim($output) : null;
}
private function downloadFromS3(string $bucket, string $key, string $dest): bool
{
$cmd = "aws s3 cp s3://{$bucket}/{$key} {$dest}";
exec($cmd, $out, $code);
return $code === 0;
}
private function runPsql(string $db, string $sql): void
{
$escaped = escapeshellarg($sql);
shell_exec("psql -U postgres -d {$db} -c {$escaped} 2>&1");
}
private function runPsqlQuery(string $db, string $sql): string
{
$escaped = escapeshellarg($sql);
$result = shell_exec("psql -U postgres -d {$db} -t -A -c {$escaped} 2>&1");
return trim($result ?? '0');
}
}
And in Console/Kernel.php:
$schedule->command('db:test-restore')->weekly()->sundays()->at('03:00');
This runs every Sunday at 3 AM, downloads the most recent production backup, restores it to a throwaway database on the same server, spot-checks that a key table has rows, and sends an alert to Slack (or PagerDuty, or wherever) if any step fails. The throwaway database gets dropped in the finally block regardless of outcome.
Gotchas That Will Bite You
The exit code problem. psql will return 0 even if individual statements failed. If you're piping a plain SQL dump (not using pg_restore with a custom-format dump), errors get written to stderr but the shell exits clean. Always redirect stderr and scan the output, or use --set ON_ERROR_STOP=1 to force a proper failure exit.
gunzip -c dump.sql.gz | psql -U postgres -d restore_test --set ON_ERROR_STOP=1
Version skew. If your production database is Postgres 15 and your restore test box has Postgres 16 installed, custom-format dumps will restore fine. But if it's the other way around — restoring a 16 dump onto a 15 binary — it will fail with an opaque version mismatch error. Pin your Postgres version on restore test infrastructure and keep it in sync with production.
Permissions on the backup user. I burned a client on this. The dump user had been created with read-only access to most schemas, but one schema (added six months later by a contractor) never got the grants. The dump ran fine, the file was there, but the schema was missing entirely from the dump. The spot-check query needs to cover multiple critical tables across multiple schemas, not just one.
Restore time is a number you need to know. When an incident is happening, someone is going to ask "how long until we're back?" You need a real answer. Run the test regularly enough that you have a baseline. My logging records elapsed time on every run so I can look at the trend. If a database grows and restore time doubles, I want to know that on a Sunday at 3 AM, not during an incident on a Wednesday afternoon.
Large databases need a plan. This approach works cleanly up to maybe 20-30 GB before the download and restore time gets painful on a weekly schedule. Past that, I test monthly and use logical replication or PITR for faster recovery paths — but I still test the base backup restore, because that's the thing you fall back to when everything else goes wrong.
When I'd Reach for This
Every production PostgreSQL database I manage gets some version of this. The complexity scales with database size and client risk tolerance — a small e-commerce site gets the script above; a healthcare client with PHI gets a more elaborate setup with encrypted restore targets, additional table-count assertions across every major table, and PagerDuty integration with an on-call rotation.
I'd also run this for any managed database service — RDS, Cloud SQL, PlanetScale's export feature, whatever. Managed doesn't mean tested. AWS can tell you a snapshot exists; it can't tell you the snapshot restores to a working application.
I wouldn't skip this for internal tools or staging databases. Those environments have a habit of becoming load-bearing in ways nobody planned.
When I Wouldn't Bother With This Exact Script
If you're on a platform that gives you true point-in-time recovery with a tested failover SLA backed by money — like RDS Multi-AZ with a published recovery time objective — you can lean on that for the "will it restore" question. But I'd still want a periodic logical restore test to validate data integrity, not just file-level snapshot health. Snapshots can be consistent at the block level and still contain logically corrupt data.
Closing
The difference between "we take backups" and "we have backups" is a tested restore. If you can't answer the question "when did we last successfully restore from backup?" with a specific date and a duration, you don't actually know what you have. Set up the test, schedule it, alert on failure, and log the restore time. That single piece of automation has saved two of my clients from what would have been catastrophic data loss events. It's not glamorous work. Do it anyway.
Need help shipping something like this? Get in touch.