Postgres and data

pg_dump vs physical backup vs PITR: which to use

2026-11-09 · 10 min read

PostgreSQL gives you three different things that people all call "a backup": a logical dump from pg_dump, a physical copy from pg_basebackup, and point-in-time recovery built from a physical copy plus archived WAL. They protect against different failures, restore in different ways and cost different amounts of effort. Choosing by habit, usually "we run pg_dump nightly", is how teams end up with a backup that cannot answer the question they are asked after an incident.

This guide explains what each one is, what each can and cannot get back, and which to pick for a given database size and tolerance for loss.

The three, in plain terms

A logical dump (pg_dump) reads the database through SQL and writes out the statements, or a custom archive of them, that would recreate it: schema, then data, then indexes and constraints. It takes a consistent snapshot of one database while the server runs. Restoring means running those statements into a server, which re-creates every table and rebuilds every index.

A physical backup (pg_basebackup) copies the files of the whole cluster, every database at once, while the server runs, plus the WAL needed to make that copy consistent. Restoring means putting the files back and starting the server of the same major version. There is no replay of SQL and no index rebuild.

Point-in-time recovery (PITR) is a physical backup plus a continuous archive of WAL. Restoring means copying the base backup back and then replaying archived WAL up to a target you choose: a timestamp, a named restore point, a transaction, or the end of the archive.

The WAL is the difference. Without it, a physical backup restores to the moment it finished. With it, you can stop at 10:14:59, one second before the bad DELETE.

Comparison

Two rows deserve a longer look.

Restore speed. A dump restores by executing SQL. The data is loaded with COPY, but every index and constraint is built afterwards, which on a large database is most of the time. A physical restore copies files, and the only variable is how much WAL has to be replayed. In our own drill, restoring a data directory of 988,707,267 bytes (about 6,000,000 rows) took 42.0 seconds total, of which 36.1 seconds was starting the server and replaying 232 WAL files, and 4.6 seconds unpacking the base backup. A 78 MB database restored in 2.4 seconds. That is a laptop-class container, so read these as shape, not promise. On the dump side, your indexes decide the time, so time a dump restore on your own data.

Version and architecture. A dump is the portable one. If you are going from PostgreSQL 15 to 17, or from an x86 server to an ARM one, a dump restores cleanly. A physical backup must go back onto the same major version, and in practice the same CPU architecture and operating system family, because the on-disk format is not portable across them.

What each one cannot do

pg_dump cannot give you "5 minutes ago". Its recovery points are the times it ran. A nightly dump at 02:00 and a bad migration at 16:00 costs 14 hours of writes.

A physical backup without WAL cannot give you anything in between either. It is the same granularity as a dump, just faster to restore.

PITR cannot protect against losing the archive. If the WAL archive and the base backups live on the same disk as the database, one failed disk takes all of it. The fix is an archive on a different disk or machine, and a separate off-site copy; see a 3-2-1 backup plan for one VPS.

PITR cannot restore "just this table" directly. It restores the whole cluster to a moment. The pattern for a single table is below.

None of them protect you from restoring the wrong thing. A restore that succeeds with stale or partial data is its own failure mode, covered in when the restore succeeded but the data was wrong.

Who needs which

Small database, low stakes: dump is enough

A side project, an internal dashboard, a staging database. A few hundred megabytes, written mostly during working hours. Losing a day is annoying and survivable. Here a nightly pg_dump shipped off the box is the right amount of work, and the restore takes minutes.

# nightly, as the postgres user; -Fc is the custom format that pg_restore reads
pg_dump -Fc -d app -f /var/backups/pg/app-$(date -u +%F).dump
pg_dumpall --globals-only -f /var/backups/pg/globals-$(date -u +%F).sql

Do not forget the second line. pg_dump does not include roles and their passwords, so a restore onto a fresh server without pg_dumpall --globals-only fails on ownership.

A small SaaS: dump plus PITR

A single Postgres holding customers, orders and invoices, from a few gigabytes to a few tens of gigabytes. A bad deploy at 14:20 should cost minutes. Nightly base backup plus WAL archiving with archive_timeout = 60 gives you that. Keep a weekly pg_dump as well, for the reasons in the next section. This is the combination we recommend for a business-critical single server.

A database that has outgrown nightly dumps

At some size a dump no longer fits in the night, and a restore from it no longer fits in your outage budget, because index builds take longer than anyone wants to wait with the site down. That is when physical backups stop being optional. The size where it bites depends on your hardware and your indexes, so measure a restore, not a dump. If restoring the dump into a scratch server takes longer than the downtime you can accept, you are there.

A major version upgrade

Physical backups cannot be restored onto a new major version. For an upgrade you use pg_upgrade or a dump and restore. Plan the upgrade with a fresh dump taken beforehand, and after the upgrade start a new archive and a new base backup; the old WAL belongs to the old major version.

A team that must state its recovery point

If somebody outside engineering asks "how much can we lose?", the answer has to be a number you tested. For a dump it is the interval between dumps. For PITR it is archive_timeout plus shipping time, which you can measure with a drill, as in how much data a lost disk takes.

Choosing by recovery point objective

Rule of thumb: one day of loss is fine, use a dump. Minutes matter, use PITR. Keep a weekly dump either way.

RPO is how much recent data you can afford to lose. This is the simplest way to choose:

Recovery time (RTO) is the second axis. Dump restores scale with data and indexes. Physical restores scale with data volume copied plus WAL replayed, so more frequent base backups mean a faster recovery. If your RTO is "back within an hour" on a database of tens of gigabytes, a physical restore is what fits.

Using PITR and dump together

The two are not rivals. A sensible setup for one VPS looks like this:

• Nightly base backup and continuous WAL archive, for fast and fine-grained recovery of the whole cluster.
• A weekly pg_dump -Fc of each important database, kept longer than the base backups. It survives major upgrades, can be inspected by tools, and is a second, independent kind of copy. If the base backup mechanism breaks in a way nobody noticed, the dump still restores.
• Backups of /etc/postgresql and the application config in the normal file backup. They are not in the base backup on Debian.

Restic backups of SQLite and Postgres covers shipping dumps and archives off the server with encryption and retention. Keep a written procedure for each restore path, as in write a restore runbook.

Restoring one table from PITR

You dropped one table, or deleted half its rows, and the rest of the database is fine. You do not want to roll the whole cluster back and lose everyone else's work since. So you restore a scratch copy of the cluster to just before the mistake, on another port, and pull the table out.

# the scratch instance is already running on port 5433 at the target time (see the restore guide)
psql -h /tmp -p 5433 -d app -c "create schema recovered; alter table public.orders set schema recovered;"
pg_dump -h /tmp -p 5433 -d app -n recovered | psql -d app

The first line moves the table, in the disposable scratch copy, into a schema that cannot collide with the live one. The second dumps that schema and loads it into the live database, where you can compare recovered.orders with public.orders and copy back what you need with an INSERT ... SELECT. The live database never stopped. Restoring Postgres to a timestamp shows how to get the scratch instance running.

Mistakes we see

Calling a base backup without archived WAL "PITR". The name promises a recovery point you choose. Without WAL you have one point.

Only ever testing the dump. Dumps are easy to check by restoring them, and that gives teams confidence that spills over to the other mechanisms. A base backup needs its own restore test, and so does the WAL chain.

Dumping to the same disk. pg_dump -f /var/lib/postgresql/dump.sql on the data volume is a convenience copy, not a backup.

Forgetting pg_dumpall --globals-only. Roles, passwords and ownership live outside any one database. The dump restores tables owned by roles that do not exist.

Assuming pg_dump is a consistent copy of everything. It is consistent per database. Dumping several databases is several snapshots at several moments, and it does not capture cluster-level objects.

Choosing PITR for a dataset that changes constantly and is derived. If you can rebuild the data from another source in minutes, a heavy backup arrangement may be more machinery than you need.

FAQ

Is pg_dump enough for a production database?

It depends on what a day of lost writes would cost you. For a database that is easy to rebuild or changes slowly, a nightly dump copied off the server is a legitimate answer. For anything where recent writes matter, add WAL archiving so you can recover to within minutes, and keep the dump as a second, independent copy.

Can I restore a pg_basebackup onto a newer PostgreSQL version?

No. A physical backup must be started with the same major version that took it, for example 15 to 15. To change major versions, use pg_upgrade or a dump and restore. Plan on starting a new archive and a new base backup after a major upgrade.

Do I need both PITR and pg_dump?

You do not need both, but together they cover each other's weaknesses. PITR gives fine-grained, fast recovery on the same major version. A dump is portable across versions and architectures, and works as an independent copy if a physical backup turns out to be bad. A weekly dump alongside PITR is cheap insurance.

How big does a database need to be before a dump becomes too slow?

There is no fixed size. What matters is the time to restore the dump into a scratch server, since index builds dominate, compared with your downtime budget. Time that restore on your hardware once, and you will know. If it exceeds what you can accept, move to physical backups.

Does PITR replace replication?

No. Archiving recovers you to a recent point, and it always has a gap of up to archive_timeout plus shipping time. A standby with synchronous replication can survive a server loss with no committed data lost, and still needs backups, because replication copies a bad DELETE to the standby just as faithfully.

If you decide PITR is what your server needs, the Postgres PITR Kit is a tested set of bash scripts for the archive, base backups, restore and monitoring on one Debian server.

postgresbackuppitrrecovery