PostgreSQL Backup: pg_dump, pg_basebackup and PITR Tested (2026)

In most cases, a PostgreSQL backup takes a single command: pg_dump -Fc -f database.dump mydb. It produces a compressed, internally consistent copy of one database, even while users keep writing to it. You restore it with pg_restore. For larger servers, or when you want to rewind to a specific second, the second method comes in: a physical copy with pg_basebackup plus archived WAL files, which is what point-in-time recovery is built on.

That’s the short answer. The long one matters more, because database backups rarely fail loudly. They fail quietly. A backup script reports success and has written a 20-byte file. A restore runs to the end even though ten errors happened along the way. WAL archiving has been failing for hours, and the application doesn’t notice a thing. We reproduced all of this on 3 November 2026 on our own server with PostgreSQL 18.6, a 1.5 GB test database and ten million rows. Every one of these traps could be triggered in a few minutes.

This article shows which backup method fits which job, how to set it up, how to restore a PostgreSQL backup, and how to make sure it actually works when you need it. Every number below comes from our own runs.

Video: PostgreSQL Mechanics walks through the essential backup commands (May 2026). Our measurements and traps below go further.

PostgreSQL backup: the three methods at a glance

PostgreSQL ships with three fundamentally different ways to back up a database. They don’t exclude each other. A properly run production server usually uses two of them.

1. Logical backup with pg_dump. pg_dump reads the database like a regular client and writes out everything needed to rebuild it: table definitions, data, indexes, permissions. The result is either an SQL file or a compressed archive. It works per database, can be restored into a different PostgreSQL version, and it’s what most people mean when they say “PostgreSQL backup”.

2. Physical backup with pg_basebackup. pg_basebackup copies the server’s entire data directory, byte for byte, over the replication protocol. The result is a bootable clone of the whole cluster with all databases. It only runs on the same major version and the same architecture.

3. Continuous archiving (WAL) and point-in-time recovery. PostgreSQL writes every change to the write-ahead log, WAL for short, before anything else. If you keep copying those WAL files somewhere safe, you can take a physical backup and “fast-forward” it to any second after it was taken. That gets you back to 14:31:59, for example, one second before someone ran DELETE FROM orders without a WHERE.

There’s also the option of copying the data directory while the server is stopped, or taking a file system snapshot. That works, but it’s error-prone and not a good idea for most readers. Copying the data directory with a normal file copy while the server is running isn’t a backup, it’s a lottery ticket. We measured the same problem with SQLite, where a copy taken during operation contained 0 instead of 1,000 rows. That’s in SQLite vs. PostgreSQL.

Our test setup

So the numbers make sense: we tested with PostgreSQL 18.6 in the official Docker image on our own server (12 vCPUs, NVMe SSD). We created the test database with pgbench -i -s 100, which gives a 1,503 MB database with 10 million rows in the main table. WAL archiving was on, and so was summarize_wal, so we could test incremental backups too.

After the tests we deleted the containers, the image and all test data again, and only our own. Catch-all commands like docker volume prune also remove other people’s data, which is a lesson we already learned the hard way elsewhere.

One caveat up front: pgbench data is very uniform and therefore compresses unusually well. With real data containing text, JSON and timestamps, your backups will be larger in relative terms. The ratios between methods stay the same, though.

pg_dump: the standard backup for one database

pg_dump has four output formats. We measured all of them against the same database:

What to take away from this:

Use the custom format (-Fc). It’s compressed, you can pick individual tables when restoring, and pg_restore can load it in parallel. A plain SQL file opens in any text editor, but you can only restore it in full, and only with psql.

zstd pays off. Since PostgreSQL 16, pg_dump can compress with zstd. In our test, the backup was faster (2.9 instead of 3.6 seconds) and three times smaller (8 instead of 27 MB) than with the default compression at the same time. With realistic data the gap shrinks, but zstd is almost always the better choice.

Parallel dumps only help with many tables. The directory format with -j 4 wasn’t faster for us, because pgbench is essentially one big table. pg_dump parallelizes per table, not within a table. If your application has dozens of tables of similar size, things look different.

Is pg_dump consistent while the database is in use?

Yes, and we checked this explicitly. pg_dump runs in a single transaction with a fixed snapshot. It doesn’t see anything written after the dump started.

Our test: we ran pgbench with four clients (around 6,500 transactions per second, each one updating an account and a branch and writing a history row) and took a dump in the middle of it. Then we restored the dump into a fresh database and compared the three totals. If the dump were inconsistent, meaning it contained half a transaction, they would differ. Result: all three totals matched exactly, −741,192 each. You don’t have to stop your application to run pg_dump.

What pg_dump still blocks

Consistent doesn’t mean free of side effects. pg_dump holds a lock on every table it backs up for its entire run. That doesn’t affect normal reads and writes, but it does block schema changes. We sent an ALTER TABLE … ADD COLUMN during a running dump: it hung for 4.5 seconds until the dump finished.

For us that’s a few seconds. With a 200 GB database the dump takes an hour, and your deployment’s migration waits an hour. Worse, every other query on that table queues up behind the waiting ALTER TABLE. So don’t schedule nightly dumps in the window where your deployments run.

pg_dumpall: all databases and the roles

pg_dump backs up exactly one database. Users, roles, passwords and tablespaces don’t belong to a database, though, they belong to the whole server, and so they’re not in the dump. That’s one of the most common causes of trouble when restoring.

We reproduced it: created a role shopapp, granted it read access on the tables, backed up the database with pg_dump -Fc and restored it on a fresh PostgreSQL server that didn’t have that role. pg_restore reported:

pg_restore: error: could not execute query: ERROR:  role "shopapp" does not exist
Command was: GRANT SELECT ON TABLE public.pgbench_accounts TO shopapp;
...
pg_restore: warning: errors ignored on restore: 14

All the data was there afterwards, all 10 million rows. But the permissions were missing, and your application, which logs in as shopapp, couldn’t access them. The exit code was 1, so a script with set -e would at least have stopped.

Two fixes, depending on the situation:

# Back up roles and passwords separately (once per server)
pg_dumpall --globals-only > globals.sql

# When restoring, load the roles first
psql -f globals.sql postgres
pg_restore -d shop shop.dump

Or, if you deliberately want to land on a different server with different users:

pg_restore --no-owner --no-privileges -d shop shop.dump

With that, our restore ran without a single error, exit code 0. All objects then belong to the user running the restore, and you have to grant permissions again.

pg_dumpall without options backs up all databases plus roles into one SQL file. That’s convenient but has two drawbacks: it only supports the plain format, so no compression and no selective restore, and the databases are dumped one after the other rather than at a single shared point in time. Our recommendation: pg_dumpall --globals-only for the roles plus one pg_dump -Fc per database.

How to restore a PostgreSQL backup

A backup is only as good as its restore. We loaded each of our three dump variants into an empty database:

All three databases had exactly 10,000,000 rows afterwards. Two things stand out.

First: the restore takes about four times as long as the backup. When backing up, PostgreSQL only reads. When restoring, it has to write all the data, rebuild indexes and check foreign keys. For us that was 3 seconds versus 12. With a 100 GB database, a ten-minute backup turns into a restore of 45 minutes or more. That’s the number you need to know when someone asks how long the shop will be down in an emergency.

Second: restoring in parallel did almost nothing with one large table, for the same reason as with the dump.

Here’s how to restore a PostgreSQL backup from the custom format:

# Create a new, empty database
createdb shop_restore

# Load it, stopping at the first error
pg_restore --exit-on-error -d shop_restore shop.dump

# Restore just a single table
pg_restore -d shop_restore -t orders shop.dump

# Drop existing objects first (careful!)
pg_restore --clean --if-exists -d shop shop.dump

Trap: psql keeps going despite errors

This is probably the nastiest trap with the plain format. We loaded the same SQL dump a second time into the same database, one where the tables already existed. psql reported ten errors like relation "pgbench_accounts" already exists but kept running to the end. Exit code: 0.

A restore script that only checks the exit code treats this as a success. The fix is a single option:

psql -v ON_ERROR_STOP=1 -d shop -f shop.sql

With that, psql stops at the first error and returns exit code 3. If you restore plain SQL dumps, this option belongs in every script. Even better, add --single-transaction, so an error rolls everything back instead of leaving a half-filled database behind.

Trap: an older version can’t read a newer dump

pg_dump can back up older servers, and pg_restore can read older dumps, but not the other way round. We opened our custom dump created with PostgreSQL 18 using pg_restore from PostgreSQL 16:

pg_restore: error: unsupported version (1.16) in file header

This typically happens when your backup server has an older client package installed than your database server, or when you want to go back to the old version after an upgrade. Rule of thumb: for backup and restore, always use the client tools from the newest version involved.

pg_basebackup: the physical clone

pg_basebackup copies the entire data directory, including all databases, roles and settings. Our measurement with the same 1.5 GB database:

Uncompressed, pg_basebackup is the fastest method of all, because it only copies files and doesn’t have to convert anything. With gzip it gets five times slower, because compression runs in a single process. Since PostgreSQL 15 you can pick faster algorithms with --compress=server-zstd or --compress=lz4 and move the work to the server; we didn’t measure that in this run.

Important options you should know:

• -X stream includes the WAL files created during the backup. Without them the backup won’t start.
• -c fast forces an immediate checkpoint. Without it, pg_basebackup waits for the next regular checkpoint, which can take several minutes with nothing apparently happening.
• -P shows progress.

The user needs replication privileges, and pg_hba.conf needs a line for replication. The official Docker images already allow this for local connections.

When pg_basebackup instead of pg_dump? When the database is so large that restoring from a dump takes too long (a physical backup boots straight away, no index rebuilding), when you want point-in-time recovery, or when you want to set up a replica. For a database of a few gigabytes on a single server, pg_dump is usually the simpler and more flexible choice.

Point-in-time recovery: back to a specific second

A nightly dump has a basic problem: if the mistake happens at 4 pm, you lose everything since 3 am. Point-in-time recovery (PITR) closes that gap. The principle: one base backup plus all WAL files since. During recovery, PostgreSQL replays the WAL and stops at exactly the moment you specify.

Step 1: turn on WAL archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

The test ! -f prevents an already archived file from being overwritten, which the official documentation also recommends. archive_mode needs a server restart, the other two only a reload. In practice, the archive doesn’t point to a local folder but to another machine or an object store. That’s what tools like pgBackRest, Barman or WAL-G are built for, more on those below.

Step 2: take a base backup

pg_basebackup -D /bk/base -Ft -z -X stream -c fast

Step 3: the accident

Our test: we wrote a table orders with 50 entries to the database. We noted the time (08:04:47 UTC), waited a second and deleted all orders. Then one more order worth 999 came in, so the accident isn’t the last thing that happened. That’s how it goes in real life, where a mistake is often only noticed after more data has been written.

Step 4: restore to just before the accident

Unpack the base backup into an empty data directory, then add to postgresql.auto.conf (or postgresql.conf):

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-11-03 08:04:47.698259+00'
recovery_target_action = 'promote'

And create an empty file called recovery.signal in the data directory. Then start the server. Our log said:

LOG:  starting point-in-time recovery to 2026-11-03 08:04:47.698259+00
LOG:  recovery stopping before commit of transaction 906, time 2026-11-03 08:04:48.787762+00
LOG:  redo done at 3/253F8F70 ... elapsed: 42.75 s
LOG:  selected new timeline ID: 2
LOG:  database system is ready to accept connections

The query afterwards: 50 orders, highest amount 50. The deleted data was back, and the later order worth 999 was, as intended, not there. PostgreSQL stopped right before the transaction that did the deleting.

The 42.75 seconds are worth noting. The database itself is only 1.5 GB, but our tests had produced roughly 13 GB of WAL since the base backup. So recovery time doesn’t depend on database size, it depends on how much has been written since the last base backup. That’s why PITR always goes together with a regular new base backup, for example every night or every week.

Important: data written after the target time (our 999 order) is gone in the restored server. In real life you therefore usually restore onto a second server and copy only the lost rows back into the running database.

The silent trap: the archive fills up or breaks

Two things we noticed during the test that many guides leave out.

The WAL archive grows with write volume, not with the database. Our database was 1.5 GB, the archive was 17 GB after just under half an hour of testing. Every changed row, every restore, every index change produces WAL. Without a cleanup rule, the archive will eventually fill your disk. Tools like pgBackRest automatically delete WAL files that no longer belong to any retained base backup. With a homemade setup you need pg_archivecleanup.

If archiving fails, the application doesn’t notice. We removed write permissions from the archive folder and generated load for 20 seconds. Result: 33 failed archiving attempts, the pg_wal directory grew from 1.1 to 1.2 GB, and pgbench carried on at an unchanged ~6,600 transactions per second. No error on the client, no warning in the application. PostgreSQL simply holds on to the unarchived WAL files and keeps retrying. Once we restored the permissions, it caught up on its own.

That’s forgiving, but dangerous: if the archive stays broken for days, first, your point-in-time recovery hasn’t been possible for days, and second, at some point the database server’s disk fills up. Then PostgreSQL stops. So monitor the pg_stat_archiver view:

SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

If failed_count keeps rising, or last_archived_time is more than a few minutes old, something is wrong.

Incremental backups since PostgreSQL 17

Since version 17, pg_basebackup can create incremental backups that only contain the data blocks changed since the previous backup. It requires summarize_wal = on.

# Full backup
pg_basebackup -D /bk/full -X stream -c fast

# Later: only the changes
pg_basebackup -D /bk/incr1 -X stream -c fast \
  --incremental=/bk/full/backup_manifest

# To restore, combine into a full backup
pg_combinebackup -o /bk/combined /bk/full /bk/incr1

Our measurement: full backup 1.6 GB. Then we ran pgbench for 15 seconds, about 100,000 transactions. The incremental backup took 1.9 seconds and was 840 MB, a bit more than half the full backup. Combining with pg_combinebackup took 2.6 seconds, the result was 1.6 GB again and verified without errors.

Why so large? pgbench updates randomly spread accounts across the whole table, and a single changed row marks the entire 8 KB block as “changed”. With a typical web application that mostly appends new rows and rarely touches old ones, an incremental backup is much smaller. Under load that writes all over the database, it saves less than you’d hope. Measure it with your real data before you base your storage planning on it.

Checking backups: pg_verifybackup and a real restore test

For physical backups, PostgreSQL ships a verification tool. pg_basebackup writes a backup_manifest with checksums for every file, and pg_verifybackup compares against it:

pg_verifybackup /bk/full
# backup successfully verified

As a test, we changed a single file in the backup, the tiny PG_VERSION. The check caught it immediately:

pg_verifybackup: error: "PG_VERSION" has size 10 on disk but size 3 in the manifest

That helps against bit rot and incomplete transfers. But it doesn’t prove that the backup can be restored and your application runs on it. Only a real restore proves that.

The trap that makes all of this necessary

Here’s the case that gave us the most pause, because it appears exactly like this in countless guides: pg_dump mydb | gzip > backup.sql.gz. We made a typo in the database name (shopp instead of shop):

pg_dump -U postgres shopp | gzip > /bk/wrong.sql.gz
echo $?
# 0
ls -l /bk/wrong.sql.gz
# 20 bytes

pg_dump aborted with an error, but the exit code of a pipe is the exit code of its last command, and gzip successfully compressed nothing. Result: a backup script that reports “success” every night and leaves behind an empty 20-byte file. You find out when you need it.

The fix in Bash is set -o pipefail. With it, the same command returned exit code 1 for us. If you don’t need gzip in a pipe, just use pg_dump -Fc -f file, and there’s no pipe and no problem.

This is exactly why a good backup script doesn’t just check the exit code, but also whether the file has a plausible size and can be read. And at least once a month, a real restore into a test database should run.

A complete backup script for everyday use

Here’s a script that accounts for the traps above. It backs up roles and each database separately, checks the results and cleans up old backups:

#!/bin/bash
# /usr/local/bin/pg-backup.sh
set -euo pipefail

TARGET=/var/backups/postgres
STAMP=$(date +%F_%H%M)
mkdir -p "$TARGET/$STAMP"

# Roles and passwords
sudo -u postgres pg_dumpall --globals-only > "$TARGET/$STAMP/globals.sql"

# Each database separately, custom format with zstd
for DB in $(sudo -u postgres psql -tAc \
  "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'"); do
  sudo -u postgres pg_dump -Fc -Z zstd:3 -f "$TARGET/$STAMP/$DB.dump" "$DB"

  # Sanity check: file readable and not tiny?
  sudo -u postgres pg_restore -l "$TARGET/$STAMP/$DB.dump" > /dev/null
  SIZE=$(stat -c %s "$TARGET/$STAMP/$DB.dump")
  if [ "$SIZE" -lt 10000 ]; then
    echo "WARNING: $DB.dump is only $SIZE bytes" >&2
    exit 1
  fi
done

# Delete everything older than 14 days
find "$TARGET" -mindepth 1 -maxdepth 1 -type d -mmin +$((14*24*60)) -exec rm -rf {} +

Two details. pg_restore -l only reads the dump’s table of contents. It takes a fraction of a second and detects truncated or broken files. And for deletion we use -mmin instead of -mtime, because -mtime +14 actually means “at least 15 full days”. We measured why in our Linux commands list.

The minimum size of 10,000 bytes is a rough floor. Adjust it to your databases, or better, compare with the size of the last backup and raise an alarm when the new one is suddenly much smaller.

Running it automatically

With a cron job:

15 2 * * * flock -n /run/pg-backup.lock /usr/local/bin/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Or, our recommendation, as a systemd timer. Then errors land in the journal, and with OnFailure= you can get notified when it fails. What matters is that someone finds out about a failure. A backup job that fails silently is worse than none at all, because it gives you false confidence.

The monthly restore test

#!/bin/bash
set -euo pipefail
LATEST=$(ls -1d /var/backups/postgres/*/ | tail -1)
sudo -u postgres dropdb --if-exists restore_test
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore --exit-on-error --no-owner -d restore_test "$LATEST/shop.dump"
sudo -u postgres psql -tAc "SELECT count(*) FROM orders" restore_test
sudo -u postgres dropdb restore_test

The last query is deliberately a business one: does the number of orders roughly match production? A restore that runs through but returns an empty table is the case you want to catch. Time it, too. That’s your realistic recovery time.

PostgreSQL backup in Docker

If PostgreSQL runs in a container, the approach is the same, just with docker exec in front. Two variants:

# Create the dump in the container, save it on the host (no -t!)
docker exec my-db pg_dump -U postgres -Fc shop > /backup/shop.dump

# Or write to a mounted directory
docker exec my-db pg_dump -U postgres -Fc -f /backup/shop.dump shop

With the first variant you must not call docker exec with -t. A pseudo-terminal alters line endings, and a binary dump is broken afterwards. With plain SQL this often isn’t noticed straight away.

Copying the folder with the database files (the volume) while it’s running isn’t a backup in Docker either. More on how volumes are structured in What is Docker and our Docker Compose examples.

Video: Chirag Mahto explains point-in-time recovery in PostgreSQL 18 (May 2026).

pgBackRest, Barman, WAL-G: when to use a dedicated tool

For a database of a few gigabytes, the script above is enough, perhaps with an upload to a second location. But once you want point-in-time recovery, a homemade setup quickly gets complicated: shipping WAL files elsewhere, cleaning up old ones, rotating base backups, checksums, encryption, parallel transfers. That’s exactly what specialized tools are for:

We didn’t measure these tools ourselves for this article. Our recommendation is based on how widely they’re used and on the fact that they solve exactly the problems we reproduced by hand above: a growing archive, silently failing archiving, missing verification.

The right backup strategy for your server

Which method fits depends mainly on two questions: how much data can you afford to lose in the worst case (recovery point objective), and how long may recovery take (recovery time objective)?

Hobby project, small app, internal tool: daily pg_dump -Fc plus pg_dumpall --globals-only, kept for 14 days, one copy on another server. Possible loss: up to 24 hours.

Online shop, SaaS with paying customers: nightly base backup plus continuous WAL archiving with pgBackRest to an off-site location. Plus a weekly pg_dump as a version-independent fallback. Possible loss: seconds to a few minutes.

High availability required: additionally a replica (streaming replication) you can fail over to. But careful, a replica is not a backup: an accidental DELETE reaches the replica within milliseconds. Only a backup with point-in-time recovery protects against human error.

And for everyone, the 3-2-1 rule applies: three copies, two different media, one off-site. A backup on the same disk as the database protects against an accidental DROP TABLE, but not against a disk failure, a hacked server or a fire in the data center. We show how to set that up with restic in our 3-2-1 backup strategy. And a RAID doesn’t replace a backup either.

Frequently asked questions

How do I create a PostgreSQL backup?

For a single database, with pg_dump -Fc -f file.dump databasename. That creates a compressed backup in the custom format that’s consistent even while the database is in use. You should also back up roles and passwords with pg_dumpall --globals-only, because they’re not included in the dump.

What’s the difference between pg_dump and pg_dumpall?

pg_dump backs up a single database and supports several formats including compression and selective restore. pg_dumpall backs up all databases on the server plus roles, but only as one big SQL file. The proven combination is pg_dumpall --globals-only for the roles and pg_dump -Fc per database.

What’s the difference between pg_dump and pg_basebackup?

pg_dump is a logical backup: it exports a database’s contents so they can be rebuilt, even in a different PostgreSQL version. pg_basebackup is a physical backup: an exact copy of the entire data directory, only usable with the same major version, but bootable right away and the basis for point-in-time recovery.

How do I restore a PostgreSQL backup?

A custom dump is loaded with pg_restore -d targetdb file.dump, ideally with --exit-on-error. A plain SQL dump with psql -v ON_ERROR_STOP=1 -d targetdb -f file.sql. Without ON_ERROR_STOP, psql reports success even when errors happen, exit code 0 despite ten errors in our test.

Can I use pg_dump while the database is running?

Yes. pg_dump works with a fixed snapshot and delivers a consistent state, even under load. In our test with around 6,500 write transactions per second, all control totals in the backup matched exactly. It does block schema changes like ALTER TABLE until the dump is finished.

How often should I back up PostgreSQL?

As often as you can tolerate losing data. A daily dump means you lose up to a day’s data in the worst case. If that’s too much for you, you need WAL archiving with point-in-time recovery, which brings potential loss down to seconds or minutes.

What is point-in-time recovery?

Point-in-time recovery (PITR) restores a database to any moment in time. It needs a physical base backup and all WAL files archived since. PostgreSQL replays the changes up to the chosen time and then stops. That lets you undo an accidental delete, for example.

Can I restore a backup from a newer PostgreSQL version into an older one?

Not directly. pg_restore from PostgreSQL 16 rejected our dump from version 18 with “unsupported version in file header”. Going down only works via a plain SQL dump and possibly some manual work. Going up, from old to new, works fine if you use the newer version’s tools.

How large is a PostgreSQL backup?

An uncompressed SQL dump is usually somewhat smaller than the database, because indexes are saved as commands rather than data. Compressed, it shrinks a lot: our 1.5 GB test database came out at 933 MB as SQL, 27 MB with gzip and 8 MB with zstd. Real data compresses worse than our very uniform test data.

Is replication a backup?

No. A replica applies every change from the primary within milliseconds, including accidental deletes or encryption by ransomware. It protects against hardware failure, not against mistakes. For that you also need real backups, ideally with point-in-time recovery.

Do I need pgBackRest or is pg_dump enough?

For small and medium databases where losing a day of data is acceptable, pg_dump with a clean script is enough. Once you need point-in-time recovery, large data volumes or backups to object storage, pgBackRest saves you a lot of manual work and sources of error.

Conclusion: the backup is easy, the proof is the work

Creating a PostgreSQL backup is a one-liner. pg_dump -Fc is fast, consistent and exactly right for most projects, and with zstd it’s particularly small. For larger databases and when you want to rewind to the second, pg_basebackup and WAL archiving come in, in practice best handled through pgBackRest.

The real work is elsewhere, and our measurements show it more clearly than any theory: a pipe that reports an empty file as success. A psql that returns exit code 0 despite ten errors. Roles missing from the dump. A WAL archive that hasn’t written anything for hours without the application noticing. None of this produces an error that wakes you up at night.

So: set -o pipefail, ON_ERROR_STOP=1, pg_dumpall --globals-only, monitor pg_stat_archiver, keep a copy off-site. And above all: do a real restore once a month and time it. A backup you’ve never restored isn’t a backup, it’s a hope.