OnCallReady

Lesson 25.43 · PostgreSQL Operations · 23 min read

Backups: pg_dump, pg_restore and testing restores

In plain words

Taking a backup is like photographing every page of a ledger so you could rebuild it after a fire. Many people take the photos for years and never check them - until the day of the fire, when they discover the camera had no film.

A logical backup is a careful copy of what the ledger says; it can be read anywhere. Testing a backup means actually rebuilding the ledger from the photos, regularly, before you need it.

Why this lesson exists

Every team says it has backups. Far fewer have restored one recently, and that is the only test that counts: the dump that is empty because a pipe hid the error, the backup of the wrong database, the restore that fails because the roles do not exist on the new server, the one that takes nine hours when the business can wait one. This lesson covers logical backups with pg_dump / pg_restore / pg_dumpall, what they do and do not protect against, and how to prove a backup restores. The next lesson adds physical backups and point-in-time recovery.

What you need to know already: roles and ownership (lesson 4), MVCC snapshots and locks (lesson 6), systemd timers (the systemd chapter), set -euo pipefail (the Bash chapter).

The words you need first

pg_dump

$ sudo mkdir -p /var/backups/pg && sudo chown postgres: /var/backups/pg
$ sudo -u postgres pg_dump -Fc -d orders -f /var/backups/pg/orders.dump
$ ls -lh /var/backups/pg/
total 1.1M
-rw-r--r-- 1 postgres postgres 1.1M Sep 22 20:01 orders.dump
$ file /var/backups/pg/orders.dump
/var/backups/pg/orders.dump: PostgreSQL custom database dump - v1.16-0

What pg_dump does while it runs:

The custom format is the one to use: compressed, and pg_restore can list it, pick parts of it, reorder it and restore in parallel. Look inside:

$ sudo -u postgres pg_restore -l /var/backups/pg/orders.dump | head -n 30
;
; Archive created at 2026-09-22 20:01:55 UTC
;     dbname: orders
;     TOC Entries: 36
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 18.3
;     Dumped by pg_dump version: 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)
;
;
; Selected TOC Entries:
;
201; 1259 24631 TABLE public customers orders_owner
202; 1259 24702 TABLE public events orders_owner
203; 1259 24682 TABLE public order_items orders_owner
204; 1259 24661 TABLE public orders orders_owner
205; 1259 24646 TABLE public products orders_owner
206; 1259 24630 SEQUENCE public customers_id_seq orders_owner
207; 1259 24701 SEQUENCE public events_id_seq orders_owner
208; 1259 24660 SEQUENCE public orders_id_seq orders_owner
209; 0 24631 TABLE DATA public customers orders_owner
210; 0 24702 TABLE DATA public events orders_owner
211; 0 24682 TABLE DATA public order_items orders_owner
212; 0 24661 TABLE DATA public orders orders_owner
213; 0 24646 TABLE DATA public products orders_owner
214; 0 24630 SEQUENCE SET public customers_id_seq orders_owner
215; 0 24701 SEQUENCE SET public events_id_seq orders_owner

A table of contents: the header (dump time, version, how many entries), then one line per object - tables, sequences, the data of each table (TABLE DATA), and after the data the constraints, indexes and foreign keys (built after loading, which is much faster than loading into indexed tables).

pg_restore

Never test a restore into the production database. Restore into a scratch database (on a scratch server, ideally) and check it:

$ sudo -u postgres createdb orders_restore
$ sudo -u postgres pg_restore -d orders_restore --no-owner /var/backups/pg/orders.dump
$ sudo -u postgres psql -d orders_restore -c "select (select count(*) from orders) as orders, (select count(*) from customers) as customers, (select max(created_at) from orders) as newest"
 orders | customers |         newest
--------+-----------+------------------------
  40000 |      4000 | 2026-11-06 00:00:00+00
(1 row)

The options you will use:

optioneffect
-d dbrestore into an existing database (without -d it prints SQL)
-C (--create) with -d postgrescreate the database named in the dump first, then restore into it
--clean --if-existsdrop each object before recreating it (restoring over an existing copy)
--no-owner / --no-privileges (-O / -x)skip ALTER ... OWNER / GRANTs - for a scratch copy where the roles do not exist
-j Nparallel restore (custom and directory formats)
-1 (--single-transaction)all or nothing
--exit-on-errorstop at the first error (the default carries on and counts them)
-t table, -n schema, -L listrestore only part of it

By default pg_restore continues after errors and ends with WARNING: errors ignored on restore: N (exit code 1). Read that number. A restore into a server where the roles do not exist shows the most common one:

$ sudo -u postgres createdb orders_restore2
$ sudo -u postgres pg_restore -d orders_restore2 /var/backups/pg/orders.dump 2>&1 | tail -n 4

Errors that look harmless sometimes are not: a failed GRANT means the app cannot read the restored tables. Either restore the globals first, or decide consciously (--no-owner --no-privileges) and re-grant.

Picking pieces

The list is editable: dump it, comment out entries with ;, and restore only what is left - for "restore just yesterday's customers table next to the live one":

$ sudo -u postgres pg_restore -l /var/backups/pg/orders.dump | grep -E 'TABLE( DATA)? public customers' > /tmp/only-customers.list
$ cat /tmp/only-customers.list
201; 1259 24631 TABLE public customers orders_owner
209; 0 24631 TABLE DATA public customers orders_owner
$ sudo -u postgres psql -qc "create database orders_customers"
$ sudo -u postgres pg_restore -d orders_customers --no-owner -L /tmp/only-customers.list /var/backups/pg/orders.dump
$ sudo -u postgres psql -d orders_customers -Atc "select count(*) from customers"
4000

Globals and the whole cluster

$ sudo -u postgres pg_dumpall --globals-only | grep -E '^(CREATE|ALTER) ROLE' | head -n 8
CREATE ROLE orders_app;
ALTER ROLE orders_app WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:OskSySen2mCnjs+bLTozAQ==$zTQgaCRZ+4R1dvIQIorSqVamQ6WsbBAbdThLnC5r5cI=:dDtfC5xyrDVw6soXIMYRD7vJbf1bC/afI7zknDvyclg=';
CREATE ROLE orders_owner;
ALTER ROLE orders_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE orders_ro;
ALTER ROLE orders_ro WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:wxbgRBjfFEGoIjSo7IbIDg==$Rot2Lq5Hx3GrRBtLZp72RVDEbc/Y1cAalHr4Cu8jDjc=:fA5wuLe8WBRLMqKeWlDR7tQmBrHfQS0dtNmZwgVhlV0=';
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;

Passwords come out as SCRAM verifiers (PASSWORD 'SCRAM-SHA-256$4096:...'), so a restored role keeps its password - treat a globals dump as a secret. A complete logical backup of a server is therefore two things: pg_dumpall -g once, plus pg_dump -Fc per database. (pg_dumpall without -g dumps every database too, but only as plain SQL - no parallel or selective restore.)

Plain format and \restrict

$ sudo -u postgres pg_dump -d orders -t products | head -n 12
--
-- PostgreSQL database dump
--

\restrict tswqxp3KA2323pkWjghfbgwxGRG4K6ezXbHBFjOdw44KtYgt2CgdPN7a3uGH5iX

-- Dumped from database version 18.3
-- Dumped by pg_dump version 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

A plain dump is a psql script. Since the August 2025 security releases it starts with \restrict <random key> and ends with \unrestrict <key>: while restricted, psql refuses every meta-command, so a malicious object name in a dump cannot run shell commands through \! when you restore it. Restore plain dumps with psql (psql -X -v ON_ERROR_STOP=1 -d db -f dump.sql), never by pasting them.

Versions

pg_dump can dump servers of the same or older major versions; use the newer client (dumping an 18 server with a 16 pg_dump fails: server version mismatch). A dump restores into the same or a newer major - which is why dump/restore is also a way to upgrade.

Making it a job

A backup script that fails loudly - the details matter:

#!/usr/bin/env bash
set -euo pipefail                       # without pipefail, "pg_dump ... | gzip" hides a failed dump
umask 077
ts=$(date -u +%Y%m%dT%H%MZ)
out=/var/backups/pg/orders-$ts.dump
pg_dump -Fc -d orders -f "$out.partial"
mv "$out.partial" "$out"                # only complete dumps get the real name
pg_restore -l "$out" > /dev/null        # the archive is readable
find /var/backups/pg -name 'orders-*.dump' -mtime +14 -delete

run by a systemd timer as postgres, with its failures alerting (OnFailure=, or the timer's last result in monitoring). Then the part everyone skips: a second job that restores the newest dump into a scratch database every night, runs a few checks (row counts, the newest created_at, an app query) and alerts when they fail.

Where logical dumps stop being enough:

In an interview: "How do you know your PostgreSQL backups work?" - you restore them, regularly and automatically: the newest dump into a scratch database (with the globals from pg_dumpall -g), check row counts and the newest timestamps, alert on failure. And you know what each kind protects: pg_dump = a consistent logical snapshot of one database, portable, slow for big data, RPO = time since the dump; physical backups + archived WAL = any point in time.

What you can do now

Why it helps

Every team says it has backups; far fewer have restored one recently, and the restore is the only test that counts. This lesson covers pg_dump, pg_restore and pg_dumpall: what they lock, what they leave out (the roles), the formats, selective restores, and how a backup job silently produces empty files.

RPO and RTO turn "we have backups" into a requirement you can check, and they decide when logical dumps are not enough.

Commands in this lesson

mkdir pg_dump ls file pg_restore createdb psql cat pg_dumpall printf

FAQ

Does pg_dump stop the application?

No. It reads from one consistent snapshot while the application keeps reading and writing. But it holds an ACCESS SHARE lock on every table it dumps, so a migration's ALTER TABLE waits for the whole dump - and queues the application behind it - and the long snapshot holds back VACUUM. Schedule dumps away from deploys, or dump from a replica.

Why do I need pg_dumpall --globals-only?

pg_dump dumps one database. Roles, their passwords (as SCRAM verifiers) and memberships, and tablespaces belong to the whole cluster and are not in it. Restoring into a fresh server without them gives errors like role does not exist and missing grants. A full logical backup is the globals once plus pg_dump per database.

Which pg_dump format should I use?

Custom (-Fc) for most cases: compressed, and pg_restore can list it, restore only some objects and restore in parallel. Directory (-Fd) when you also want parallel dumping (-j). Plain SQL only for small dumps or when a human needs to read the file; it is restored with psql, not pg_restore.

How do I restore just one table?

Into a scratch database, never over production: pg_restore -t tablename, or pg_restore -l to write the table of contents to a file, keep only the lines you want, and restore with -L. Then copy the rows you need into production. Restoring over the live database replaces today's data with the backup's.

What makes a backup job trustworthy?

It fails loudly: set -euo pipefail so a failed pg_dump in a pipe fails the job, writing to a temporary name and renaming only on success, alerts on job failure and on suspiciously small files. And a second job restores the newest backup into a scratch database every night and checks row counts and the newest timestamps.

In an interview Mid

How do you know your PostgreSQL backups work?

By restoring them, regularly and automatically. A nightly job restores the newest pg_dump archive into a scratch database (after the globals from pg_dumpall --globals-only), checks row counts, the newest timestamps and an application query, and alerts when anything fails. The backup job itself must fail loudly (set -euo pipefail, alerts on failure and on tiny files). And you know what each backup gives you: pg_dump is a consistent logical snapshot of one database with an RPO of the time since the dump; physical base backups with archived WAL give point-in-time recovery. Measure the restore time against the RTO.

Also asked: What is the difference between a logical and a physical backup? · What does pg_dump lock while it runs? · What are RPO and RTO?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.