PostgreSQL Backup and Restore: A Tested Runbook

This is an invented scenario, but the arithmetic is real. A small team runs a Postgres database with one PostgreSQL backup: a pg_dump at 02:00 every night. On Friday 25 September a deploy script runs an UPDATE with no WHERE clause at 16:42. Every customer row now has the same plan name. Somebody notices at 16:50.

With only the nightly dump, the best restore is the 02:00 state. That is 16:42 minus 02:00, or 14 hours 42 minutes of orders, sign-ups and edits gone. With WAL archiving on top of a base backup, the target is 16:41, a minute before the mistake, and you lose at most a minute of work.

The rest of this post builds both, so you can see exactly what each one buys you.

People say "backup" for two very different things. A dump is a logical export of one database: SQL or an archive that recreates your tables and rows. A base backup is a physical copy of the whole cluster's files. Point-in-time recovery needs the second kind, plus a continuous stream of write-ahead log (WAL) files.

Neither PostgreSQL backup replaces the other. A dump is portable and easy to inspect, which makes it good for migrations and small databases. Base backups plus WAL are what save you on Friday afternoon. For the deeper walk-through of the second kind, see Postgres point-in-time recovery.

1. 1Dump in custom formatRun pg_dump with -Fc. The custom format is compressed by default and lets pg_restore pick individual tables later. For a big database use the directory format, -Fd, which is the only one that allows parallel dumps with -j.
2. 2Move the file off the machineA dump on the same disk as the database survives nothing that matters. Copy it to object storage or another host.
3. 3Dump roles separatelypg_dump does not include roles or tablespaces. Run pg_dumpall with --globals-only, or you will restore tables owned by users that do not exist.
4. 4Restore into an empty targetUse pg_restore with --create and -d postgres to make the database, or point -d at one you made. Add --clean only when you mean to drop existing objects first.
5. 5Analyse and checkRun ANALYZE. The docs say optimiser statistics are not fully carried over, so the planner starts blind. Then compare row counts on your busiest tables.

pg_dump -Fc -f /backups/app-2026-09-25.dump "$DATABASE_URL"
pg_dumpall --globals-only -f /backups/globals.sql
pg_restore --create -d postgres /backups/app-2026-09-25.dump

One practical point. A dump loads into a newer major version, and that makes it your upgrade path too. PostgreSQL 14 stops getting fixes on 12 November 2026, so a restore into a newer version is a job many teams have coming.

A base backup is taken with pg_basebackup, which the docs describe as taking the backup "without affecting other clients of the database". Use tar format with gzip, ask for progress, and stream the WAL it needs so the backup stands alone.

pg_basebackup -h db.internal -U replicator -D /backups/base-2026-09-25 -Ft -z -P -X stream
pg_verifybackup /backups/base-2026-09-25

pg_verifybackup is useful but modest. It checks files and checksums against the backup manifest. It does not check that the databases work or hold the right data. Keep that limit in mind when we get to testing.

Then switch on continuous archiving. The default wal_level is already replica, which is enough. You need archive_mode on and an archive_command that copies each finished WAL segment somewhere durable.

archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

The reason is in the next sentence of the docs. On a zero result, PostgreSQL assumes the file is archived and will remove or recycle it. A command that returns zero after a failed upload quietly throws your history away.

Back to Friday. The team stops the app, then restores the base backup into a fresh data directory. They set where to stop and where to fetch WAL from, and create an empty recovery.signal file.

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-09-25 16:41:00+00'
recovery_target_action = 'promote'

• Keep a copy of the broken data directory first, if you have space. You may want the rows written after 16:41.
• Remove old files from pg_wal in the restored directory, and make sure the files belong to the postgres user, not root.
• Start the server. It replays archived WAL up to the target.
• The default action at the target is pause, so a check is possible before you commit. Setting promote, as above, lets it open for writes straight away.
• Inspect the data before letting users back in. If it is wrong, go back and pick a new target.

Two limits from the docs are worth memorising. The target must fall after the end of the base backup, so a backup cannot recover to a time while it was still running. And when recovery ends, PostgreSQL starts a new timeline, so later WAL does not overwrite the old.

You restore it. On a schedule, into a scratch database, with a check that fails loudly. A backup that exits with status zero has told you only that a file exists.

#!/usr/bin/env bash
set -euo pipefail
LATEST=$(ls -t /backups/*.dump | head -1)
createdb restore_check
pg_restore --exit-on-error -d restore_check "$LATEST"
ROWS=$(psql -Atc "SELECT count(*) FROM orders" restore_check)
dropdb restore_check
test "$ROWS" -gt 0 || { echo "restore produced an empty orders table"; exit 1; }

Time the restore as well. The number you need in an incident is how long you are down, and you only learn it by doing the restore. Write it in the runbook next to the command.

There is one more gap a scratch restore can expose. The pg_dump docs say it only dumps a single database, and that global objects such as roles and tablespaces need pg_dumpall. Restore a dump onto a fresh server and every table owner may be missing, so the load fails or the app cannot log in. Dump the globals with pg_dumpall alongside your nightly dump and keep the two files together. Then your weekly check tests the roles as well as the rows.

Do the same for base backups: start a throwaway server from the latest one, replay WAL to a recent time, and query it. If you would rather not run any of this yourself, Supabase compared with Neon sets two managed options side by side.

Helm7 databases run Postgres 18. Every PostgreSQL backup on Builder is daily and kept for 7 days. Pro and Scale add point-in-time recovery. You can also take a backup on demand with the database_backup operation, which keeps it for 7 days. Our terms say we take backups and test restoring them, and that you should still keep your own.

Restores are careful by design. The database_restore operation takes a backup id or a point in time, replaces the current contents, and refuses to run without confirm set to true. It can restore into a new database alongside the old one, and that is the safer first move when you are not sure. Restores are API and MCP operations today, with no CLI command. The quickstart shows how to create a database, and pricing lists what each plan keeps.