Backups You Have Actually Restored

A backup is a claim. It says that at some point the data existed somewhere else, in a form that could be brought back. A restore is the evidence. Most teams produce the claim every night and never gather the evidence, so the first real test happens on the worst possible day, with the site down and someone asking how long it will take. The gap between the two is where small teams lose weekends.

What is the difference between a backup and a restore?

A backup is a copy plus a promise about its format. A restore is the act of turning that copy back into a working database, with the schema, the rows, the roles and the permissions the application expects. The first is a job that can report success while producing something useless. The second is the only thing that answers the question you actually care about: can we get back to work?

The failure modes are quiet at capture time. A plain-text dump looks like a backup even when it holds only schema. A data-only dump restores rows into a database that has no tables. PostgreSQL's own documentation says pg_dump covers a single database and leaves cluster-wide objects such as roles and tablespaces to pg_dumpall, so a set without them restores cleanly and then fails on the application's first connection. The same page carries a version rule that produces silent failures: pg_dump cannot dump from a server newer than its own major version. An old client on the backup machine turns the job into an error nobody reads.

Why does a successful backup job prove so little?

Monitoring checks the exit code, file size and age of the newest file. None reads the content. A job that copied half a table because the connection dropped is still a file of plausible size. A snapshot shown as green in a provider console has never been loaded anywhere. The PostgreSQL manual is direct about the limits of this tool too: except in simple cases, pg_dump is generally not the right choice for taking regular backups of production databases.

Providers draw their own boundaries, and they document them. Supabase's backup documentation states that database backups do not include objects stored via the Storage API, that restoring an old backup does not restore objects deleted after that backup, that daily backups do not store passwords for custom roles, and that deleting a project permanently removes its backups. Those are not defects. They are the edge of the word "backup", and most people meet it in an incident.

Where should a backup live?

Somewhere that fails separately from the database. Not another folder on the same disk, another volume on the same machine, or the same cloud account. A copy in object storage inside the account that hosts the database disappears with a credential compromise, a billing suspension or an accidental project deletion. Use a separate account, and for the important data a second provider.

The credentials matter as much as the location. The key that writes backups should not be the one the application uses at runtime, and the application should not be able to delete backups at all. If it is compromised, it should not be able to erase every copy.

The widely repeated 3-2-1 rule says three copies, on two kinds of storage, with one of them off-site. It is a heuristic, not a standard, with no single authoritative source, so treat it as a starting point. For a single developer it translates to a nightly dump to object storage in a separate account, plus a weekly copy to a second provider or an encrypted external disk kept somewhere else. For a small team it means the same, plus the provider's point-in-time recovery if the plan includes it.

Provider retention is not your retention. Amazon RDS keeps automated backups for 0 to 35 days, with a default of one day when the instance is created through the API or CLI and seven days through the console. Supabase keeps 7 days on Pro, 14 on Team and up to 30 on Enterprise. Google Cloud SQL allows 1 to 365 days for standard automated backups, and longer with enhanced backups. All three windows come from the product, not a policy you chose. Two deletion rules decide whether the provider copy is yours at all: RDS deletes automated backups with the instance unless you choose to retain them, while manual snapshots survive; Supabase removes backups when the project is deleted.

How often should backups run, and how long should you keep them?

Define the recovery point objective, how much data you can lose in hours, and the recovery time objective, how long the business can stay down. A nightly dump gives a recovery point of up to 24 hours. Point-in-time recovery shortens it to minutes: Supabase documents WAL files backed up about every two minutes, with a worst-case recovery point of two minutes. If losing a day of orders is unacceptable, a nightly dump alone is the wrong tool.

Retention should outlast the time it takes you to notice a problem, not just the time since yesterday. A bad migration or a silent corruption can go unnoticed for weeks, and by then a seven-day window has already overwritten the last good copy. A workable shape for a small team is 14 daily, 8 weekly and 12 monthly copies, with one kept past a year if invoices or contracts depend on it. Prune on a schedule, but confirm the job kept the copy you would actually restore from.

How do you rehearse a restore on a scratch copy?

Never rehearse by restoring over production, and never rehearse only on the machine that produced the dump. Take the copy you would really reach for, usually the off-site one, and load it into a disposable database.

• Dump in a restorable format. pg_dump -Fc -d app -f app-2026-10-23.dump writes a compressed custom archive that pg_restore can inspect and restore selectively.
• Inspect before restoring. pg_restore -l app-2026-10-23.dump prints the archive's table of contents. If the table you care about is not listed, stop there.
• Create an empty target. createdb -T template0 app_restore_check. pg_restore does not create the database by default, and template0 avoids objects that may have been added to template1.
• Make errors fatal. pg_restore --exit-on-error -d app_restore_check app-2026-10-23.dump. By default pg_restore continues after errors and prints a count at the end, so a run that looks finished can be missing tables.
• Time it. Note the start and the end. That duration is your real recovery time, not the estimate in the runbook.

For a plain-text dump the equivalent is psql -X --set ON_ERROR_STOP=on -d app_restore_check -f app.sql. Without ON_ERROR_STOP, psql keeps going after an SQL error, by design. Restoring from the convenient local copy proves less than restoring from the off-site one, because the off-site copy is the one that survives the event you are preparing for.

On a hosted platform the restore often happens in place: Supabase notes the project is inaccessible during a restore, and downtime grows with database size. Rehearse elsewhere instead, on a scratch project, a local instance or a copy you restored yourself. Our guide to backing up a local database before changing it covers the local side of the same routine.

What should you check after a restore?

• The server accepts connections and the application starts against it with its normal user, not the superuser. Permission failures surface here, before real traffic does.
• Row counts for the handful of tables that matter. Record the counts when the dump is taken, so the restore is compared against a number rather than a feeling.
• A known record. Pick one row you can identify by hand and compare several fields, not just its existence.
• The newest row. The maximum timestamp should be close to the dump time. A restore that is days stale means the backup job has been failing quietly.
• An insert and a delete. Adding a row catches sequences that were not advanced and constraints that no longer match the data.
• One real application query or page. A count proves the data arrived; the application proves it is usable.
• The things outside the database. Uploaded files, object storage, search indexes, queues. Supabase's exclusion of Storage API objects is the documented example, and most providers draw a similar line.

Write the result down as you go. A rehearsal that fails is a success, because it failed on a Tuesday afternoon instead of during an incident.

How do you record a rehearsal?

One line per run, in a file that lives with the project's operations notes rather than in someone's memory: the date of the rehearsal, the timestamp of the backup used, where the copy came from, the dump file name and checksum, the target database, the restore duration, the checks that passed and failed, and who ran it. After three or four runs you have a realistic recovery time and a list of things that break repeatedly, which is the actual output of the exercise.

Put the next rehearsal in the calendar before you finish the current one: quarterly for a small team, monthly if the data changes constantly. Once the manual routine has held for a couple of quarters, automate the boring part. A scheduled job can restore the latest dump into a scratch database, run the counts and the known-record query, and post the result. Automate only what you have already done by hand.

Checklist

• Backups are written somewhere other than the database host, in a separate account or provider.
• The backup credentials cannot be used by the application, and the application cannot delete backups.
• At least one copy is encrypted, and the key is stored outside the backup account.
• Retention is written down, and it outlasts the time it would take you to notice a problem.
• A restore has been performed in the last quarter, from the off-site copy, into a scratch database.
• Row counts, a known record and an application query were checked, and the results were recorded with a date.
• Restore duration is known and compared with the downtime you can tolerate.
• The record names the person who ran the rehearsal and the failures found.

An afternoon self-assessment

Block two hours and do not fix anything until you have written down what you found.

• Find the last backup. Can you say, without opening the provider console, when it ran and what it contains?
• Download it to a machine that is not the database server.
• Start an empty PostgreSQL instance, local or in a container, with a version at least as new as the server that produced the dump.
• Restore it, and time the restore from the first command to the last.
• Run the counts and the known-record query, then try one insert.
• Start the application against it, if only on a laptop, and open the page that reads the most important table.
• Write the record. If a step needed a password you had to ask someone for, that is a finding.

If the whole exercise took less than an hour, you are in good shape. If you could not find the backup, or the restore failed, you have learned the most useful thing you will learn this month, and nobody was down while you learned it. A backup you have restored once is worth more than three you have never touched.