Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 90 ยท Partitioning and Scaling

Backup, Restore and Point-in-Time Recovery

Back up PostgreSQL with pg_dump, pg_restore and base backups, archive WAL for point-in-time recovery, meet pgBackRest, and learn why you must test restores.

A replica protects you from a dead server. It does not protect you from DROP TABLE orders;, because the replica faithfully replays that too. For mistakes, bugs and corruption you need backups, and a backup is only real once you have restored it successfully.

PostgreSQL offers two families. Logical backups (pg_dump) export SQL objects and data. Physical backups (a base backup plus archived WAL) copy the cluster's files and allow point-in-time recovery (PITR): restoring the database to any moment you choose, such as 30 seconds before the bad statement. Backups touch the server's file system and tools, so the PostgreSQL blocks in this lesson are read-only. The runnable examples simulate the recovery logic with a change log and a backup inventory.

Two kinds of backup

Logical pg_dump writes objects and data. Portable, one database at a time.
Physical Copies the cluster's files. Fast to restore, needs the same major version.

Only the physical kind, plus archived WAL, gives point-in-time recovery.

1 / 2

Logical backups with pg_dump

pg_dump connects like any client, takes a consistent snapshot, and writes the definition and data of one database. It does not block writers, because it reads inside a single snapshot transaction (see MVCC: How PostgreSQL Keeps Versions). The -F option selects the format:

Format Flag Restore with Notes
Plain SQL -Fp (default) psql Readable text, easy to edit, no parallel restore
Custom -Fc pg_restore Compressed, selective restore, parallel restore
Directory -Fd pg_restore Also supports parallel dump with -j
Tar -Ft pg_restore Rarely the best choice
Terminal
# One database, custom format
pg_dump -h localhost -U app -Fc -f shop.dump shop

# Large database: directory format with 4 parallel workers
pg_dump -Fd -j 4 -f shop_dir shop

# Plain SQL
pg_dump -Fp shop > shop.sql
psql -d newshop -f shop.sql

Restoring a custom or directory dump uses pg_restore:

Terminal
createdb shop_restored
pg_restore -d shop_restored -j 4 --no-owner shop.dump

# Only one table, or only the schema
pg_restore -d shop_restored -t orders shop.dump
pg_restore --schema-only -f schema.sql shop.dump

# Restore into an existing database and drop objects first
pg_restore -d shop --clean --if-exists shop.dump

pg_dump does not include roles and tablespaces, because they belong to the whole cluster, not to one database. pg_dumpall --globals-only exports them, so a full logical backup is usually one pg_dumpall --globals-only plus one pg_dump per database. Dumps are also useful for upgrades and for copying a database between environments. They are slow on very large databases, and they can only restore to the moment the dump started.

Physical backups and WAL archiving

A base backup copies the cluster's data directory while the server runs:

Terminal
pg_basebackup -h primary.example.com -U replicator -D /backups/base_2026_04_01 -Ft -z -X stream -P

On its own it is a snapshot of one moment. To go to any moment, you also archive the WAL: every completed WAL segment is copied to safe storage as soon as it fills. Remember from Streaming and Logical Replication that the WAL describes every change.

INI
# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/archive/%f && cp %p /mnt/archive/%f'

%p is the path of the segment and %f is its file name. This example is the simple documentation-style command. For production, use a purpose-built tool that fsyncs, verifies and compresses (see pgBackRest below). If the archive command fails, PostgreSQL keeps retrying and the WAL piles up on the primary, so alert on archiving failures.

Starting with PostgreSQL 17, pg_basebackup can also take incremental backups (with summarize_wal = on), and pg_combinebackup merges them with the previous backups into a restorable directory, which shortens backup time on large clusters.

Point-in-time recovery

To recover to a moment, you restore a base backup taken before it, then let PostgreSQL replay archived WAL up to your target:

Terminal
# 1. Stop the server, move the damaged data directory away, restore the base backup
tar -xzf /backups/base_2026_04_01/base.tar.gz -C /var/lib/postgresql/17/main

# 2. Tell the server where the WAL archive is, and where to stop
cat >> /var/lib/postgresql/17/main/postgresql.auto.conf <<'EOF'
restore_command = 'cp /mnt/archive/%f %p'
recovery_target_time = '2026-04-14 09:41:00+00'
recovery_target_action = 'promote'
EOF

# 3. Signal recovery mode and start
touch /var/lib/postgresql/17/main/recovery.signal
pg_ctl -D /var/lib/postgresql/17/main start

PostgreSQL replays WAL until the target, then promotes itself to a normal server. Targets can be a timestamp, a transaction ID, an LSN, or a named restore point created earlier with SELECT pg_create_restore_point('before_migration');. With the default recovery_target_action = pause the server stops at the target so you can inspect the data first and promote when you are happy. Choose a target just before the mistake, since replay includes everything up to and including the target.

The recovery logic is simple enough to model with a table. The base backup holds the state at a certain moment; recovery applies logged changes up to the target time.

SQL Simulating point-in-time recovery

Press Run (or Ctrl + Enter) to see the result here.

If you recover to 09:50 instead, you replay the bad job too. Choosing the target well, and having every change between the backup and the target, is the entire art of PITR. A gap in the WAL archive ends recovery at the gap.

pgBackRest and friends

Hand-written scripts work until the first time they silently stop working. Dedicated tools handle the details:

  • pgBackRest offers full, differential and incremental backups, parallel backup and restore, compression and encryption, backup verification, retention rules, WAL archive management and cloud storage targets.
  • Barman and WAL-G are other widely used options. Managed services (see High Availability and Upgrades) usually offer automated backups and PITR as a feature.

A pgBackRest setup follows the same pattern. Each cluster is a stanza, archive_command points to the tool and backups are scheduled:

Terminal
# postgresql.conf: archive_command = 'pgbackrest --stanza=main archive-push %p'

pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main --type=incr backup
pgbackrest --stanza=main info

# Point-in-time restore
pgbackrest --stanza=main --type=time --target="2026-04-14 09:41:00+00" \
  --target-action=promote restore

Test your restores

An untested backup is a hope, not a backup. Typical failures are an archive command that failed for weeks, missing WAL segments, encryption keys stored only on the dead server, or a restore that takes 14 hours when the business expects 2. Write down your targets:

  • RPO (recovery point objective): how much data you can afford to lose, for example 5 minutes.
  • RTO (recovery time objective): how long recovery may take, for example 1 hour.

Then rehearse. Restore the latest backup to a scratch server on a schedule, run pg_verifybackup on base backups, check that the data is current, run a sanity query, and record how long it took. Also keep backups in a different place (account, region) from the primary.

SQL A backup inventory check

Press Run (or Ctrl + Enter) to see the result here.

Three of four backups have never been restored. You would find out whether they work on the worst possible day.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which backup type lets you restore a database to a chosen moment, such as 09:41?

    Point-in-time recovery restores a base backup and replays archived WAL up to the target. A dump only restores to when it started, and a replica copies mistakes too.
  2. 2. Which pg_dump format supports a parallel dump with -j?

    The directory format (-Fd) writes one file per table and can be dumped in parallel. Custom and directory dumps can both be restored in parallel with pg_restore -j.
  3. 3. Why do you also run pg_dumpall --globals-only?

    pg_dump exports a single database and leaves out cluster-wide objects such as roles. The globals-only dump of pg_dumpall covers them.
  4. 4. What is the most important thing to do with a backup process?

    A backup that has never been restored is unproven. Regular test restores confirm the data is usable and show whether your recovery time objective is realistic.

Summary

  • pg_dump makes logical backups of one database (plain, custom, directory or tar); pg_dumpall --globals-only covers roles and tablespaces.
  • Restore custom and directory dumps with pg_restore, optionally in parallel and for selected objects.
  • A base backup plus continuously archived WAL enables point-in-time recovery with restore_command and a recovery_target_* setting.
  • Pick a target just before the mistake, and alert when WAL archiving fails.
  • pgBackRest, Barman and WAL-G automate backups, retention and verification.
  • Define RPO and RTO, and test restores on a schedule.

Next: High Availability and Upgrades.