Backup Fundamentals

What is it? #

There are two fundamentally different ways to back up PostgreSQL, and choosing the wrong one wastes effort.

A logical backup (pg_dump) exports the contents: the SQL statements and data needed to rebuild your database. It is portable across versions and platforms, and you can restore a single table from it.

A physical backup (pg_basebackup) copies the files: the actual data directory, byte for byte. It is faster for large databases, restores as a complete cluster, and — critically — is the foundation for point-in-time recovery.

The decision comes down to size and recovery goals. Small database, want portability and selective restore: logical. Large database, want fast recovery to any moment: physical plus WAL archiving.

And one rule outranks both: a backup you have never restored is not a backup. It is a file you hope is a backup.

Think of it like this #

Two ways to back up a house.

A logical backup is a detailed inventory and instructions: every item, its description, how to rebuild the shelving. Rebuilding takes time, but you can do it in a different house, in a different city, and you can retrieve just the contents of one cupboard.

A physical backup is a photograph of every room, precise to the millimetre. Restoring is fast and exact — but it only works in an identical house, and you cannot extract a single cupboard from it.

Neither is useful if you have never tried rebuilding from them. That is the part people skip.

Simple example #

A 2 GB application database that must be restorable onto a developer's laptop, and occasionally needs one table recovered after a bad deployment. Logical backups fit perfectly.

A 900 GB production database with a requirement to recover to any point within the last week, losing at most a few minutes of data. That needs physical backups plus WAL archiving.

Code #

# ---------- LOGICAL BACKUP: pg_dump ----------

# Plain SQL format — human readable, restored with psql
pg_dump -U postgres -d shop -F p -f shop.sql

# Custom format — COMPRESSED, and allows SELECTIVE restore. Prefer this.
pg_dump -U postgres -d shop -F c -f shop.dump
#   -F c  custom format
#   -F p  plain SQL
#   -F d  directory format (allows PARALLEL dump and restore)
#   -F t  tar

# Directory format with 4 parallel jobs — much faster on big databases
pg_dump -U postgres -d shop -F d -j 4 -f shop_dumpdir/

# ---------- Useful pg_dump options ----------

pg_dump -U postgres -d shop -F c -f shop.dump \
    --no-owner \           # do not record ownership (eases restore elsewhere)
    --no-privileges \      # skip GRANT statements
    --verbose

# Just the schema, no data — useful for creating a matching empty database
pg_dump -U postgres -d shop --schema-only -f schema.sql

# Just the data
pg_dump -U postgres -d shop --data-only -F c -f data.dump

# Only certain tables
pg_dump -U postgres -d shop -t orders -t order_items -F c -f orders.dump

# Everything EXCEPT a huge table you can regenerate
pg_dump -U postgres -d shop --exclude-table-data=audit_log -F c -f shop.dump

# ---------- pg_dumpall: the whole server ----------

# pg_dump backs up ONE database and does NOT include roles or passwords.
# pg_dumpall covers the cluster-wide objects:

pg_dumpall -U postgres --globals-only -f globals.sql
#   roles, passwords, tablespaces  <- EASILY FORGOTTEN
#   Without these, a restore has no users to own or access anything.

pg_dumpall -U postgres -f everything.sql     # all databases + globals (plain SQL only)

# A common, sensible combination:
pg_dumpall --globals-only -f globals.sql          # small, fast
pg_dump -F c -d shop -f shop.dump                 # compressed, selective restore

# ---------- PHYSICAL BACKUP: pg_basebackup ----------

pg_basebackup \
    -h localhost -U replicator \
    -D /backup/base_$(date +%F) \   # destination directory
    -Ft -z \                        # tar format, gzip compressed
    -Xs \                           # stream WAL during the backup
    -P \                            # show progress
    -c fast                          # force an immediate checkpoint

#   -Xs is important: it streams the WAL generated DURING the backup,
#       so the result is self-consistent and restorable on its own.

# Requires a role with REPLICATION permission:
#   CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '...';
# and a pg_hba.conf line allowing "replication" connections.

---------- Choosing between them ----------

                        LOGICAL (pg_dump)      PHYSICAL (pg_basebackup)
Backs up                SQL + data             the data directory files
Size                    smaller (compressed)   full cluster size
Speed on 500 GB         hours                  much faster
Restore granularity     one table, one schema  WHOLE CLUSTER ONLY
Cross-version restore   YES                    NO — same major version
Cross-platform          YES                    NO — same architecture
Restores users/roles    only via pg_dumpall    yes (whole cluster)
Point-in-time recovery  NO                     YES, with WAL archiving
Load on the server      reads every row        file copy, lighter
Good for                < ~100 GB, dev copies, large DBs, fast recovery,
                        migrations, selective  replication, PITR
                        restore

---------- What backups protect against (and what they do not) ----------

A backup protects you from:
    accidental DELETE / DROP / bad migration
    data corruption
    a failed upgrade
    ransomware (IF the backups are offline or immutable)

A backup does NOT protect you from:
    a slow-burning bug that corrupted data weeks ago and was backed up
    faithfully every night since  <- this is why RETENTION matters

REPLICATION IS NOT A BACKUP.
A standby copies every change, including DROP TABLE, within seconds.
It protects against hardware failure, not against mistakes.
You need both.

---------- RPO and RTO: the two numbers that decide everything ----------

RPO — Recovery Point Objective
      "How much data may we lose?"
      Nightly backups only        -> RPO up to 24 hours
      Nightly + WAL archiving     -> RPO of minutes

RTO — Recovery Time Objective
      "How long may recovery take?"
      500 GB logical restore      -> many hours
      500 GB physical restore     -> much faster

Decide these numbers with whoever owns the business risk, THEN design the
backup strategy. Working the other way round produces a strategy nobody
has agreed to and that does not meet anyone's actual requirements.

# ---------- Verifying a backup is real ----------

# A file that exists is not proof. Check it can be READ:
pg_restore --list shop.dump | head -20
# Lists the contents. If this fails, the dump is corrupt.

# Check it is not suspiciously small:
ls -lh shop.dump

# THE ONLY REAL TEST: restore it somewhere and query it.
createdb -U postgres restore_test
pg_restore -U postgres -d restore_test shop.dump
psql -U postgres -d restore_test -c "SELECT count(*) FROM orders;"
dropdb -U postgres restore_test

# Do this on a SCHEDULE, not just when you set the backups up.

How it works #

pg_dump connects as an ordinary client and reads the database inside a single transaction using a consistent snapshot. That means the dump reflects one exact moment even while the database is being written to, and it does not block other users. It does read every row, so it puts real load on the server and takes time proportional to the data.

Because the output describes contents rather than files, a logical dump is portable: dumped from PostgreSQL 14, restored into 16, on a different operating system and CPU architecture. That portability is what makes it the right tool for upgrades and for moving data around.

The custom format (-F c) is compressed and indexed, which is what allows pg_restore to extract a single table from it. Plain SQL (-F p) can only be replayed from beginning to end. Directory format (-F d) adds parallelism, which matters a great deal on large databases.

The most commonly missed detail: pg_dump does not back up roles or passwords. Those live at cluster level, not inside a database. Restoring a dump onto a fresh server without pg_dumpall --globals-only leaves you with tables nobody owns and no users who can connect — usually discovered at the worst possible moment.

pg_basebackup copies the data directory itself. It cannot be selective, because the files only make sense as a complete set. With -Xs it also streams the WAL generated during the copy, which is what makes the result internally consistent: the file copy takes time, the database changes while it runs, and those changes are captured in the streamed WAL.

That property is what makes a physical backup the foundation for point-in-time recovery. Restore the base backup, then replay archived WAL forward to any chosen moment. A logical dump cannot do this — it is a snapshot of one instant with no mechanism to move forward from it.

RPO and RTO are the numbers that should drive the design. If losing a day of data is acceptable, nightly logical dumps may be entirely sufficient. If only minutes may be lost, WAL archiving is required regardless of database size. Agreeing these explicitly prevents both under-engineering and expensive over-engineering.

Real-world use #

The failure that actually happens is not a missing backup — it is a backup that turns out to be unrestorable at the moment it is needed. Empty files from a cron job whose credentials expired. Dumps of the wrong database. A perfect dump of the data with no roles to own it. All of these are common, and all are caught by periodically restoring and querying.

Schedule restore tests. Monthly is reasonable for most systems. Restore into a scratch database, run a few queries that check real row counts, and record that it worked. This is the single highest-value practice in this entire track.

Keep backups off the database server. A backup on the same disk does not survive the disk failing, and a backup on the same machine does not survive that machine being compromised. Copy them to object storage or another host, and for ransomware protection prefer storage with immutability or versioning enabled.

Retention needs thought beyond "keep seven days". A bug that corrupts data slowly may not be noticed for weeks, by which time every retained backup contains the corruption. A common pattern is daily backups for a week, weekly for a month, monthly for a year — enough history to reach back past a slow-burning problem.

Be explicit that replication is not a backup. A standby applies DROP TABLE as faithfully as it applies an INSERT, within seconds. It protects against a server dying, not against a mistake or a bug. Systems need both, and conflating them is a genuinely dangerous assumption.

Finally, write the restore procedure down, as a checklist, somewhere reachable when the database is down. Recovery happens under pressure, often by whoever is available rather than whoever designed it.

Common mistakes #

• Never testing a restore, so the first attempt happens during a real incident.
• Backing up databases with pg_dump but never pg_dumpall --globals-only, losing all roles and passwords.
• Storing backups on the same server or disk as the database they protect.
• Treating a replica as a backup — it copies mistakes like DROP TABLE within seconds.
• Keeping only a few days of backups, so a slow-burning corruption is present in every one of them.

Practice #

Take a custom-format pg_dump of a test database and a separate pg_dumpall --globals-only. Inspect the dump with pg_restore --list to see its contents. Then restore it into a brand-new database, query a table to confirm the row count matches, and drop the test database. Write down the exact commands as a short restore checklist — that document is more valuable than the backup itself.