Postgres backups you can actually restore

Here's an uncomfortable question for anyone running Postgres in production: when did you last restore one of your backups?

Not "check that the cron job ran." Not "see that the file landed in the bucket." Actually restore it, into a real database, and query the data.

If the answer is "never," you don't have backups. You have a folder of files you hope are backups. Restore failures are almost never discovered during backups; they're discovered during outages, which is the one moment you can't afford them. The GitLab database incident of 2017 is the canonical story: five backup mechanisms, and when they needed one, none worked.

This guide builds a Postgres backup pipeline where the restore is part of the pipeline, not an afterthought:

1. Dump with the right format
2. Verify the dump is readable
3. Prove it restores (the step everyone skips)
4. Ship it off-site: encrypted, deduplicated, versioned
5. Apply retention automatically
6. Put the whole thing on a schedule

Total time: about 10 minutes. Total cost: free up to 5 GB.

1. Dump with --format=custom

Skip plain-SQL dumps for backup purposes. The custom format is compressed, supports parallel and selective restore, and is what pg_restore is built for:

mkdir -p ./db-dumps
export PGPASSWORD='your_password'   # or better: use ~/.pgpass

pg_dump \
  --host=127.0.0.1 \
  --port=5432 \
  --username=postgres \
  --format=custom \
  --file=./db-dumps/app.dump \
  app_db

Two details that matter more than they look:

• Keep the file path stable. Overwrite ./db-dumps/app.dump on every run. This is what makes deduplicated, incremental uploads possible later: only changed blocks ship.
• Never pass --password on the command line. It leaks into shell history and process lists. PGPASSWORD or ~/.pgpass instead.

Need roles and tablespaces too (they live outside individual databases)? Add a pg_dumpall pass. It produces plain SQL you restore with psql, not pg_restore:

pg_dumpall --host=127.0.0.1 --username=postgres --file=./db-dumps/all.sql

2. Verify the dump is readable

Thirty seconds, catches the dumb failures (truncated files, empty dumps from a bad connection string):

ls -lh ./db-dumps/app.dump
pg_restore --list ./db-dumps/app.dump | head   # prints the archive's table of contents

A non-empty file with a readable TOC is a plausible backup. Plausible isn't proven.

3. Prove it restores

This is the step that separates a backup strategy from a folder of files. Restore into a throwaway database, never over production:

createdb app_db_restore
pg_restore \
  --host=127.0.0.1 \
  --username=postgres \
  --dbname=app_db_restore \
  --clean --if-exists \
  ./db-dumps/app.dump

# Then look at the data. Row counts, latest records, whatever proves it's real:
psql -d app_db_restore -c "SELECT count(*) FROM users;"

Time this step with a stopwatch. That wall-clock number is your real RTO, the minimum downtime you'd eat in a genuine recovery. If it's 40 minutes and your team believes "we can be back in 10," you just learned something worth the whole exercise.

4. Ship it off-site

A verified dump sitting next to the database dies with the database. The classic 3-2-1 rule: three copies, two media, one off-site.

You could aws s3 cp it into a bucket, but a bucket is storage, not backup logic. No versioned snapshots, no integrity verification on restore, no retention policy, no deduplication (you pay full price to store 30 nearly-identical dumps), and encryption is on you.

This is what Backup Data handles. Sign in, claim the free 5 GB workspace, mint an API key scoped backup:write, backup:read, snapshots:read, then:

npm install @lighthouse-web3/baas-js-sdk
export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
export LH_WORKSPACE_ID="your-workspace-uuid"

import { BackupClient } from "@lighthouse-web3/baas-js-sdk";

const client = new BackupClient({
  apiKey: process.env.LH_API_KEY,
  workspaceId: process.env.LH_WORKSPACE_ID,
});

const snapshot = await client.backup(["./db-dumps"], {
  description: "nightly database backup",
  tags: { type: "db-backup", env: "prod", db: "app_db" },
});

console.log(`snapshotId=${snapshot.snapshotId} totalSize=${snapshot.totalSize}`);

(There's a Go SDK with the same surface if that's your stack.)

Each run creates an immutable, point-in-time snapshot. Because uploads are content-addressed and chunked (FastCDC), the nightly dump that's 2% different from yesterday's uploads roughly 2% of the bytes. That's the stable file path from step 1 paying off.

If the dump contains anything sensitive (it does), enable client-side encryption. Data is AES-GCM-encrypted on your machine before upload; the server only ever stores ciphertext:

import { generateKeyfile } from "@lighthouse-web3/baas-js-sdk";

// once:
generateKeyfile("/secure/lh.keyfile", process.env.LH_KEYFILE_PASSPHRASE);

// then on every backup:
const encrypted = await client.backup(["./db-dumps"], {
  description: "nightly-encrypted",
  encryption: {
    keyfilePath: "/secure/lh.keyfile",
    passphrase: process.env.LH_KEYFILE_PASSPHRASE,
  },
});

One warning that deserves bold: you hold the keys. Lose the keyfile and passphrase and the data is unrecoverable. There is no server-side reset. Store the keyfile like you store any root credential.

5. Retention, so storage doesn't grow forever

Nightly snapshots for two years is 700+ snapshots you'll never use. Encode a retention policy, and dry-run it before letting it delete anything:

const keepLatest = 14;
const before = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000).toISOString();

// Preview: what would be deleted?
const preview = await client.pruneSnapshots({ keepLatest, before, dryRun: true });
console.log("would delete:", preview.snapshotIds);

// Then for real:
await client.pruneSnapshots({ keepLatest, before, dryRun: false });

A snapshot is deleted only when it's both outside the newest 14 and older than 30 days. Chunks still referenced by surviving snapshots are kept automatically; deleting a snapshot never corrupts its neighbors.

6. Schedule it, including the restore drill

Wire dump + upload into cron or CI (mint a dedicated API key for the job):

# /etc/cron.d/postgres-backup (nightly at 02:15)
15 2 * * * backup /opt/backup/run-backup.sh >> /var/log/pg-backup.log 2>&1

Where run-backup.sh is the dump command from step 1 followed by node upload.mjs with the backup() call from step 4.

And schedule the drill, because step 3 wasn't a one-time ceremony. Monthly, restore the latest snapshot into a scratch directory and check it:

import { mkdtempSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";

const target = mkdtempSync(join(tmpdir(), "lh-restore-drill-"));
await client.restore(snapshot.snapshotId, target, {
  onProgress: (e) => console.log(`[${e.phase}] ${e.current}/${e.total}`),
});
// then: pg_restore --dbname=app_db_restore ${target}/db-dumps/app.dump

Every restore verifies chunk integrity against expected checksums before reassembly. A silently corrupted backup fails loudly at drill time, not at incident time.

The drill checklist:

• ✅ Restore into a fresh, empty directory. Never over live data
• ✅ Leave checksum verification on
• ✅ Confirm file count and sizes; spot-check actual rows
• ✅ Record wall-clock time (that's your RTO, keep a log of it)
• ✅ Delete the scratch database and directory when done

The pipeline, complete

The difference between this and the pg_dump | aws s3 cp script in most repos isn't the dump. It's that restore, verification, encryption, dedupe, and retention are part of the loop instead of TODOs.

Start with the free tier: backupdata.io has 5 GB free, no card, and the 10-minute quickstart gets you to a first verified snapshot today.