How to Back Up a Postgres Database — and Prove the Backup Works

Everyone agrees databases need backups. Far fewer people have ever restored one.

That gap is where data loss actually happens. The backup job ran for months and silently wrote empty files. The dumps were on the same disk as the database that failed. The restore worked, but took nine hours nobody had planned for. Or — as in a widely reported 2025 incident where an AI agent deleted a production database — the person in charge was told recovery was impossible when it wasn't, because nobody had checked.

This guide covers how to back up PostgreSQL properly, and, more importantly, how to prove it works.

The three kinds of backup

Type	What it is	Good for	Limitation
Logical dump (`pg_dump`)	The database as SQL or an archive file	Small–medium databases, moving between hosts, single-table restores	Restores only to the moment of the dump; slow for very large databases
Physical backup (`pg_basebackup`)	A copy of the database files	Large databases, fast full restores	Whole-cluster only; same major version to restore
Point-in-time recovery (PITR)	A base backup plus continuous WAL archiving	Restoring to any moment — e.g. one minute before a bad `DELETE`	More setup; needs storage for the WAL stream

If you use a managed database, the provider usually does physical backups and PITR for you. Check what your plan includes: how often, how far back, and whether restores are self-service.

Even then, a periodic logical dump that you control is worth having. It protects you from your provider, your provider's account, and your own mistakes in the provider's dashboard.

Taking a logical backup

pg_dump --format=custom --no-owner --no-acl \
  --file="backup-$(date +%F).dump" "$DATABASE_URL"

--format=custom produces a compressed archive that pg_restore can restore selectively — a single table, or schema only.
--no-owner --no-acl makes the dump portable to a database with different users.
Use a pg_dump version the same as or newer than the server's major version.

For a dump of the whole server, including roles, pg_dumpall exists — but most apps only need their one database.

Automating it

A backup that depends on someone remembering is not a backup. Schedule it — with cron, your host's scheduled jobs, or a CI pipeline:

# Every day at 03:00
0 3 * * * pg_dump --format=custom --no-owner --no-acl \
  --file=/backups/app-$(date +\%F).dump "$DATABASE_URL" \
  && rclone copy /backups remote:app-backups

Then decide retention: a common pattern is daily backups for two weeks, weekly for two months, monthly for a year. Delete older ones automatically, or storage costs will grow forever.

Store backups somewhere else

The backup must survive whatever destroys the database. That means:

Not on the same server or disk.
Ideally not in the same account — a separate storage bucket, with credentials the app doesn't have.
Encrypted, because a backup is a complete copy of your users' data.
Protected from deletion, using object-lock or versioning where your storage supports it. An attacker, or an AI agent with too much access, who can reach the database shouldn't also be able to delete the backups.

The restore drill

This is the part that matters. Do it now, then on a schedule — monthly, or at least quarterly.

1. Create a scratch database (never restore over production to test):

createdb restore_test

2. Restore the most recent backup into it:

pg_restore --no-owner --no-acl --dbname=restore_test backup-2026-09-23.dump

3. Check it's real data, not just a successful exit code:

SELECT count(*) FROM users;
SELECT max(created_at) FROM orders;

Compare those numbers with production. The row counts should be close, and the latest timestamp should be about when the backup was taken. An empty table, or a latest record from three months ago, means your backups have been broken for a while.

4. Time it. Note how long the restore took. That's your real recovery time, and it's usually longer than people assume.

5. Drop the scratch database:

dropdb restore_test

6. Write it down: date, backup used, row counts, duration. A log of successful restores is the only evidence your backups work.

Restoring for real

When something actually goes wrong:

Stop the damage. Pause the app, the job, or the agent that's writing bad data. Revoke credentials if needed.
Don't restore over the damaged database immediately. Restore into a new database first, check it, then switch the app to it. The damaged one may still contain data written after the backup that you'll want to recover.
Pick the right point in time. With PITR, choose a moment just before the incident. With dumps, pick the last backup before it — and accept losing anything written since.
Recover the gap if you can: records created between the backup and the incident may still exist in the damaged database, logs, or your payment provider.
Afterwards, fix the cause, not just the data.

Common ways backups quietly fail

The job fails and nobody's alerted. Alert on failure, and on success not happening.
The dump is empty because DATABASE_URL pointed at the wrong database.
Backups live on the database server, and the server is what failed.
The backup is fine, but nobody knows the storage credentials.
The restore needs an extension (like pgvector) that isn't installed on the target.
Backups exist but retention deleted the one from before the problem started.

Every one of these is caught by a restore drill. None is caught by checking that the backup job "ran."

The minimum

Automated daily backups, plus PITR if losing a day of data would hurt
Stored off-server, encrypted, and protected from deletion
Alerts when a backup fails or doesn't run
A restore drill this quarter, with row counts and duration written down
A short, written recovery procedure someone other than you could follow

EasySpawn provisions a managed PostgreSQL database for every workspace with daily backups included, and on-demand backups on the Team plan. See pricing or join the waitlist.