PostgreSQL Backups That Actually Restore: pg_dump, pg_basebackup, and the Restore Test Nobody Does

Everyone has a backup strategy until the day they actually need it. I learned this the hard way years ago when a junior colleague ran a migration against the wrong database and we discovered our "nightly backups" had been failing silently for three weeks. The cron job was fine. The disk it wrote to was full. Nobody had ever tried a restore.

That is the thing about PostgreSQL backups: making them is easy, trusting them is where people get hurt. This guide covers the two tools that matter — pg_dump for logical backups and pg_basebackup for full physical copies — plus the restore drill that turns a backup into something you can actually bet on.

The two kinds of backup (and when each one saves you)

Logical backup (pg_dump) exports your data as SQL. It is portable across PostgreSQL versions, you can restore a single table, and the files compress beautifully. This is what you want for most small-to-medium setups.

Physical backup (pg_basebackup) copies the whole data directory, including WAL files. It restores fast and is the foundation of point-in-time recovery, but it is tied to the same major version and you restore everything or nothing. This is what you want when the database is large or when minutes of data loss actually matter.

My rule of thumb: under ~50 GB, pg_dump nightly is enough for most self-hosted apps. Bigger than that, or running something that makes money, add physical backups.

pg_dump: the workhorse

A single-database dump, compressed, with a timestamp in the name:

sudo -u postgres pg_dump -Fc -Z6 nextcloud > /backups/nextcloud-$(date +%F-%H%M).dump

-Fc is the custom format — compressed and, importantly, it lets pg_restore pick out individual tables later. Plain SQL dumps (-Fp) are readable with less but you lose that flexibility.

Do not forget the roles. pg_dump does not include users, passwords, or permissions — those live in the cluster, not the database:

sudo -u postgres pg_dumpall --globals-only > /backups/globals-$(date +%F).sql

Skipping this is the second-most common restore failure I see. The database comes back, the app cannot log in, and everyone stares at pg_hba.conf wondering what changed.

Automate it, but make failure loud

A cron entry is fine, but the job must scream when it breaks:

#!/usr/bin/env bash
set -euo pipefail
DEST=/backups
mkdir -p "$DEST"
sudo -u postgres pg_dump -Fc -Z6 nextcloud > "$DEST/nextcloud-$(date +%F-%H%M).dump"
sudo -u postgres pg_dumpall --globals-only > "$DEST/globals-$(date +%F).sql"
find "$DEST" -name 'nextcloud-*.dump' -mtime +14 -delete

Two details here earn their keep. set -euo pipefail means a failed dump stops the script instead of producing a zero-byte "backup" that looks fine in a file listing. And the find ... -delete line keeps two weeks of dumps so your disk does not silently fill up — which, remember, is exactly how my three weeks of backups died.

pg_basebackup: when you need the whole cluster

For a full physical copy, PostgreSQL needs to be told you are allowed to take one. In postgresql.conf:

wal_level = replica
max_wal_senders = 3

Restart after changing wal_level, then:

sudo -u postgres pg_basebackup -D /backups/cluster-$(date +%F) -Ft -z -P -R

-Ft -z gives you a gzipped tar, -P shows progress, and -R writes the replication config so the copy can also act as a standby. Restoring means stopping PostgreSQL, swapping the data directory with this copy, and starting again — which is why I only reach for this when the database is big enough that replaying SQL would take hours.

The restore drill (the part that actually matters)

Here is my unpopular opinion: an untested backup is not a backup, it is a wish. Once a month, restore somewhere that is not production:

# spin up a scratch cluster or use a test VM, then:
sudo -u postgres createdb restore_test
sudo -u postgres pg_restore -d restore_test /backups/nextcloud-2026-06-13-0300.dump
sudo -u postgres psql restore_test -c 'SELECT count(*) FROM oc_filecache;'

Check three things: the restore command exits cleanly, row counts look sane, and — the step everyone forgets — point a real copy of the app at the restored database and log in. A database can restore perfectly and the app still fails because of a missing role or extension.

Time the restore too. If your database takes 40 minutes to come back, your real recovery time is 40 minutes plus panic. Better to know that number now.

Off-site, or it did not happen

A backup on the same disk as the database protects you from bad SQL, not from dead hardware. Push the dumps somewhere else — another machine, object storage, a USB disk at a friend's house. rsync over SSH is enough to start:

rsync -az /backups/ backupbox:/srv/db-backups/

The full version of this thinking is the 3-2-1 rule: three copies, two different media, one off-site. It sounds like overkill until the first time it saves you.

Quick checklist

• Nightly pg_dump -Fc per database and pg_dumpall --globals-only for roles
• set -euo pipefail in the backup script, plus retention so the disk never fills
• Dumps leave the machine (rsync to another host or object storage)
• Monthly restore drill on a scratch instance, app login included
• If the database is large or downtime is expensive, add pg_basebackup and look at point-in-time recovery

Do the restore drill this weekend. It takes half an hour, and it is the difference between "I think we have backups" and "I know we can recover."