PostgreSQL backup: which method you need and how to test it

PostgreSQL has three ways to make a backup: a logical dump of the data with pg_dump, a copy of the data files with pg_basebackup, and continuous archiving of the write-ahead log (WAL), which is what makes point-in-time recovery possible. They answer different questions: how much data you can afford to lose, whether you need one table back or the whole server, and whether you can reach the server’s files at all. On a managed host you usually cannot, which settles most of it.

# one database, compressed custom format (restore with pg_restore)
pg_dump --format=custom --file=app.dump --dbname="$DATABASE_URL"

# roles and tablespaces, which pg_dump leaves out
pg_dumpall --globals-only --file=globals.sql --dbname="$ADMIN_URL"

# the whole cluster as files, with the WAL needed to make it consistent
pg_basebackup --host=db.internal --username=replicator \
  --pgdata=/backup/base --format=tar --gzip --wal-method=stream

If you only read one line: most application databases are well served by a daily pg_dump copied off the host, plus a regular test restore. Continuous archiving is for when losing up to a day of writes is not acceptable. Every command on this page was run against PostgreSQL 17 before publishing.

Back up a Postgres database with pg_dump

pg_dump connects like any client and writes the database out as SQL or as an archive. The dump comes from a single snapshot, so it is consistent even while the application keeps writing, and it does not block readers or writers. It does take shared locks on the tables it reads, so an ALTER TABLE on one of them waits until the dump finishes.

Use --format=custom. It is compressed, and pg_restore can restore one table from it, reorder it, or run in parallel. A plain .sqldump can only be replayed whole through psql. That, incidentally, is the answer to “psql backup database”: psql does not make backups, it runs them back in.

Two limits to know. pg_dump covers one database, and it does not include roles or tablespaces, which belong to the cluster. And it is a snapshot: restoring it brings back the database as it was when the dump started, and everything written after that is lost. The pg_dump to S3 guide turns this into a scheduled, checked, encrypted upload, and the pg_restore guide covers getting it back.

Roles and the whole cluster: pg_dumpall

pg_dumpall dumps every database in the cluster plus the cluster-wide objects: roles, their memberships, and tablespaces. In practice its most useful form is --globals-only, run next to your per-database pg_dump files, so a restore onto a new server can recreate the roles first and keep ownership and grants intact. Each database in a full pg_dumpall is internally consistent, but the databases are not snapshotted at the same moment.

Reading role passwords needs a superuser, which managed hosts do not hand out. Add --no-role-passwords there and set the passwords again after a restore. The pg_dumpall guide has the examples, the restore steps, and the error every restore prints.

Physical backups with pg_basebackup

pg_basebackup copies the cluster’s data files over a replication connection while the server runs. It always takes the whole cluster; you cannot pick a database. It needs a role with the REPLICATION attribute (or a superuser), a replication line in pg_hba.conf, and enough max_wal_senders:--wal-method=stream opens a second connection to stream the WAL written during the copy, which is what makes the result consistent on its own.

Restoring one is not a command but a file operation: stop a server, replace its data directory with the backup, start it. That is fast for large databases, where replaying a logical dump and rebuilding every index can take hours. The trade-off is granularity: it is all or nothing, and only onto the same major version.

PostgreSQL 17 added incremental base backups. Turn on summarize_wal, then take later backups against the manifest of an earlier one, and combine them before restoring:

# summarize_wal = on in postgresql.conf, then:
pg_basebackup --host=db.internal --username=replicator \
  --pgdata=/backup/full --wal-method=stream
pg_basebackup --host=db.internal --username=replicator \
  --pgdata=/backup/incr1 --wal-method=stream \
  --incremental=/backup/full/backup_manifest

# an incremental backup cannot be used directly; combine it first
pg_combinebackup /backup/full /backup/incr1 --output=/restore/data
pg_verifybackup /restore/data

Continuous archiving and point-in-time recovery

A base backup plus every WAL file written since lets you replay the database to any moment after the base backup: the second before someone ran the wrong DELETE. Turning it on is three settings and a place to put the files:

# postgresql.conf (archive_mode needs a restart)
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'

To recover, restore a base backup into an empty data directory, tell PostgreSQL where the archived WAL is and where to stop, create a recovery.signal file, and start it:

# postgresql.conf of the restored copy
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-09-29 14:59:00+00'
recovery_target_action = 'promote'

We ran exactly this: a base backup, a burst of inserts, a DELETEof the whole table, then recovery to a timestamp just before it. The restored copy had every row back, and its log read “recovery stopping before commit” of the deleting transaction.

In production nobody writes archive_command by hand for long. The physical backup tools (pgBackRest, Barman, WAL-G) handle archiving to object storage, retention, and restores. The one thing they all need is access to the server itself, which is why managed hosts offer their own PITR instead: see the Supabase PITR guide for one example of what that costs.

What managed Postgres already backs up, and what it leaves out

On a managed host, physical backups and PITR are the host’s job. What their backups share is where they live: in the same account as the database. The questions that matter are whether you can take a copy out, and what happens to the backups when the database or the account goes away.

Two patterns stand out. On most of these hosts you cannot take the platform’s backup out as a file. And on several of them, the backups go when the database goes. A pg_dump in a bucket you control is the copy that survives both, which is why it is worth running even on a host with good built-in PITR. For Supabase in particular, the Supabase backup guide covers every option in detail, including the Storage files that no database backup contains.

Where to keep backups

Off the database host, and ideally out of the database’s cloud account, so that one compromised or deleted account does not take both. Encrypted before upload, so a leaked bucket key does not leak the data. With a lifecycle rule that deletes old copies, so the bucket does not grow forever. And written with a key that cannot delete, on a bucket with versioning or object lock turned on: a key that can write can also overwrite an existing file under the same name, so the no-delete key alone does not stop whoever takes over the database server from destroying the backups. The pg_dump to S3 guide covers each of these with S3, R2, or B2.

Test the restore, on a schedule

Every method above can produce a backup that looks fine and does not restore: a dump cut off by a dropped connection, a base backup missing the WAL it needs, an archive that stopped accepting files weeks ago. The only check that covers all of them is restoring the backup somewhere and looking at what came back.

Doing it once after setting up backups proves the setup. Doing it on a schedule proves the backups you have today. For pg_dump files, the restore-check script downloads the newest dump, restores it into a throwaway container, and checks every table came back; the pg_restore guide lists what to check. For physical backups, pg_verifybackup checks a backup against its manifest, which catches damaged files but not a missing WAL archive; a periodic restore to a spare server is still the real test.

Tools that do this for you

pg_dump and a cron job cover a lot. Beyond that, the tools split into physical-backup engines for servers you run (pgBackRest, Barman, WAL-G) and dump schedulers with a web interface (pgBackWeb, Databasus, SimpleBackups), and only a few of them test restores. The Postgres backup tools comparison goes through each.

FAQ