PostgreSQL backups: pg_dump, pg_basebackup and point-in-time recovery with WAL archiving

PostgreSQL gives you three backup techniques, each documented in the official manual: SQL dumps, file-system level (base) backups, and continuous archiving of the write-ahead log (WAL). Most small and medium setups start with dumps, add base backups as the data grows, and add WAL archiving when losing a day of changes is no longer acceptable. This guide covers all three with commands you can run on a Debian or Ubuntu server.

Check your version first

The PostgreSQL project supports each major version for five years. As of late October 2026 the supported majors are 18, 17, 16, 15 and 14, and PostgreSQL 14 receives its final release in November 2026. Debian 13 (trixie) packages PostgreSQL 17 and Debian 12 (bookworm) packages PostgreSQL 15. On Debian each cluster keeps its configuration in /etc/postgresql/<version>/<cluster>/ and its data in /var/lib/postgresql/<version>/<cluster>.

pg_lsclusters
sudo -u postgres psql -c 'SELECT version();'

Version matters for backups because pg_dump can dump from servers of its own or older major versions, but refuses to dump from a newer one. Run the client that matches your server, or a newer one.

Logical backups with pg_dump

pg_dump makes a consistent export even while the database is in use, and it does not block readers or writers. Prefer the custom format (-Fc), which is compressed by default and lets pg_restore select and reorder objects, or the directory format (-Fd), which can dump in parallel with -j.

sudo install -d -o postgres -g postgres -m 700 /var/backups/pg
sudo -u postgres pg_dump -Fc -f /var/backups/pg/shop-$(date +%F).dump shop
# large database: directory format with 4 parallel jobs
sudo -u postgres pg_dump -Fd -j 4 -f /var/backups/pg/shop-dir shop

One trap catches many people: pg_dump exports a single database only. Roles and tablespaces are global objects, so a server rebuilt from per-database dumps has no users or passwords unless you also save the globals:

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

To restore, create the roles first, then load the archive with pg_restore. You can list an archive's contents without restoring anything, which is a cheap first check that the file is readable:

sudo -u postgres pg_restore -l /var/backups/pg/shop-2026-10-31.dump | head
sudo -u postgres psql -f /var/backups/pg/globals-2026-10-31.sql
sudo -u postgres createdb shop_restored
sudo -u postgres pg_restore -d shop_restored -j 4 /var/backups/pg/shop-2026-10-31.dump

Dumps are portable to newer PostgreSQL versions, which makes them the standard tool for major upgrades and for copying data between servers. Their weakness is restore time on large databases, since every index is rebuilt.

Physical backups with pg_basebackup

pg_basebackup copies the whole cluster over a replication connection while the server keeps running. It needs a role with the REPLICATION attribute (the postgres superuser has it). By default it streams the WAL generated during the backup alongside the data, so the result can be started on its own:

sudo -u postgres pg_basebackup -D /var/backups/pg/base-$(date +%F) -P
sudo -u postgres pg_verifybackup /var/backups/pg/base-2026-10-31

The server also writes a backup_manifest describing every file, and pg_verifybackup (PostgreSQL 13 and later) checks the backup against it, including whether the WAL needed for recovery is present. The example uses the default plain format because that is what pg_verifybackup reads on every supported version. Tar format (-Ft -z) saves space, but up to PostgreSQL 17 a tar backup has to be extracted before it can be verified; PostgreSQL 18 can check tar backups directly, without the WAL step (-n).

The manual is clear that verification does not include every check a running server performs, so you should still do test restores. PostgreSQL 17 and later can also take incremental base backups with pg_basebackup --incremental and merge them with pg_combinebackup, which relies on WAL summaries kept by the server.

Point-in-time recovery with WAL archiving

A base backup plus every WAL file written since lets you replay the database to any moment, for example one minute before someone dropped a table. Enable archiving in postgresql.conf and restart the cluster:

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

This is the manual's simplest example. The archive must end up off the server, and the command must return a zero exit status only when the file is safely stored, because PostgreSQL recycles WAL files once it gets a zero. A command that fails noisily is fine; one that reports success without copying is how archives silently develop holes. Dedicated tools such as pgBackRest or Barman manage archiving, retention and restores for you and are worth considering for anything beyond a single server.

Recovery follows the procedure in the manual:

1. Stop the server and, if space allows, copy the current data directory somewhere safe.
2. Empty the data directory and restore the base backup into it with the correct ownership.
3. Remove the files in pg_wal/ that came from the backup.
4. Set restore_command and a target such as recovery_target_time in postgresql.conf, and create an empty recovery.signal file in the data directory.
5. Start the server, let it replay the archive, check the data, and only then let users back in.

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-10-31 15:59:00+03'

Date/time and named restore points are the practical recovery targets; the manual notes that transaction IDs are hard to pick accurately.

Test restores, not just backups

Restore your newest dump into a scratch database every week or month, run a few queries your application depends on, and record how long it took. Do the same with a base backup on a separate machine at least occasionally, including a point-in-time recovery to a chosen timestamp. If you are on a managed hosting plan, find out what the platform covers: Skyline's hosting plans, for example, keep nightly backups of websites and databases for 30 days. On a self-managed VPS, everything in this guide is your own job.

What you should do

1. Confirm your major version is supported, and use client tools of the same or a newer major version.
2. Take nightly pg_dump -Fc dumps of each database plus pg_dumpall --globals-only, and copy them off the server.
3. Add pg_basebackup with pg_verifybackup once dumps become slow to restore.
4. If you need recovery to a point in time, set up WAL archiving to off-server storage and make sure the archive command fails loudly.
5. Practise restores on a schedule and write down the time each one took.

Sources

• postgresql.org/support/versioning
• postgresql.org/docs/current/backup.html
• postgresql.org/docs/current/backup-dump.html
• postgresql.org/docs/current/app-pgdump.html
• postgresql.org/docs/current/app-pg-dumpall.html
• postgresql.org/docs/current/app-pgrestore.html
• postgresql.org/docs/current/app-pgbasebackup.html
• postgresql.org/docs/current/app-pgverifybackup.html
• postgresql.org/docs/17/app-pgcombinebackup.html
• postgresql.org/docs/current/continuous-archiving.html
• qa.debian.org/madison.php?package=postgresql-17
• qa.debian.org/madison.php?package=postgresql-15
• manpages.debian.org/trixie/postgresql-common/pg_createcluster.1.en.html
• cloud.alskyline.com/hosting