Backup and Restore

pg_dump

A logical backup: SQL or an archive that recreates objects and data. It runs in one REPEATABLE READ snapshot, so the dump is consistent, and it never blocks readers or writers. It does not include roles, tablespaces, or anything outside one database.

pg_dump -Fc -d prod -f prod.dump                        # custom, compressed
pg_dump -Fd -j 4 -d prod -f prod_dir/                   # directory, parallel
pg_dump -d prod -f prod.sql                             # plain SQL, the default
pg_dump -Fc -Z 9 -d prod -f prod.dump                   # max compression
pg_dump -Fc "postgres://u@host:5432/prod?sslmode=require" -f prod.dump

Use -Fc unless the database is large enough to need -Fd -j. Plain SQL can only be replayed start to finish by psql.

# Schema of one schema, no ownership or grants
pg_dump -d prod -n app --schema-only --no-owner --no-acl -f app_schema.sql

# Two tables with data, one structure only
pg_dump -d prod -Fc -t 'public.orders' -t 'public.order_items' \
        --exclude-table-data='public.audit_log' -f slice.dump

--filter takes one rule per line and beats a long chain of -t flags (17+):

include table public.orders
include table public.order_items
exclude table_data public.audit_log
exclude schema archive

pg_dump -d prod --filter=dump-rules.txt -Fc -f slice.dump

Two things people forget: pg_dump does not dump roles, and a pg_dump from a newer server cannot be restored by an older pg_restore. Always use client tools at least as new as the server.

pg_dumpall for globals

Roles, passwords, tablespaces, and cluster-wide settings live outside any database.

pg_dumpall -g -f globals.sql          # --globals-only: roles + tablespaces
pg_dumpall -r -f roles.sql            # --roles-only
pg_dumpall -f everything.sql          # every database, plain SQL only
pg_dumpall --no-role-passwords -g -f globals_nopw.sql

The correct pair for a full cluster backup you can restore selectively:

pg_dumpall --globals-only -f globals.sql
for db in $(psql -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate"); do
  pg_dump -Fc -d "$db" -f "$db.dump"
done

pg_restore

pg_restore reads -Fc, -Fd, and -Ft archives. A plain .sql dump is not an archive, so it goes through psql instead.

createdb restored
pg_restore -d restored -j 4 prod.dump
pg_restore -d restored --clean --if-exists --no-owner --no-acl prod.dump
pg_restore -d restored -1 prod.dump                # one transaction, all or nothing
pg_restore -C -d postgres prod.dump                # create the database first

-l and -L are the selective-restore mechanism. Dump the list, delete lines, restore what remains:

pg_restore -l prod.dump > toc.txt
grep -v 'TABLE DATA public audit_log' toc.txt > toc_edited.txt
pg_restore -d restored -L toc_edited.txt prod.dump

-j and -1 are mutually exclusive, so choose speed or atomicity. For a plain SQL dump, restore through psql and always set ON_ERROR_STOP, or you get a half-restored database that exits 0:

psql -v ON_ERROR_STOP=1 -1 -d restored -f prod.sql
psql -v ON_ERROR_STOP=1 -d restored -c '\i prod.sql'

Restore speed tips: -j up to the core count, maintenance_work_mem high, and SET synchronous_commit = off for the session. Run ANALYZE afterwards, because a restore leaves no statistics behind on 17 and earlier.

Physical backups with pg_basebackup

A byte-level copy of the whole data directory, which is what you restore from when you need the cluster back rather than one table. Needs a role with REPLICATION and wal_level at least replica.

pg_basebackup -D /backup/base -Ft -z -Xs -P -c fast -U repl -h primary
pg_basebackup -D /var/lib/pg/standby -R -Xs -P -S standby1 -U repl -h primary
pg_verifybackup /backup/base

Always take -Xs. Without WAL alongside, a base backup is not restorable on its own. Incremental backups (17+) are reassembled with pg_combinebackup before starting, never used directly.

WAL archiving and PITR

Continuous archiving turns a base backup plus a WAL stream into restore-to-any-second. On the primary:

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

archive_command must return non-zero on failure, or Postgres believes the segment is safe and recycles it. In practice use a tool that checksums and retries (pgBackRest, WAL-G, Barman) rather than cp.

Recovery, in outline:

1. Stop the server. Move the broken data directory aside, do not delete it.
2. Restore the base backup into an empty data directory.
3. Configure recovery, then create recovery.signal in the data directory.
4. Start the server. It replays WAL to the target, then pauses.
5. Verify, then SELECT pg_wal_replay_resume(); to promote.

# postgresql.conf, then: touch $PGDATA/recovery.signal
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-03-14 15:09:26+00'
recovery_target_action = 'pause'      # pause | promote | shutdown
recovery_target_inclusive = on

Since 12 these live in postgresql.conf, not recovery.conf, and the signal file is recovery.signal or standby.signal.

Logical replication

Not a backup, but the tool for a zero-downtime major-version upgrade and for feeding a reporting replica. Needs wal_level = logical on the publisher.

-- On the publisher
CREATE PUBLICATION app_pub FOR TABLE orders, customers;
CREATE PUBLICATION all_pub FOR ALL TABLES;
CREATE PUBLICATION s_pub  FOR TABLES IN SCHEMA app;         -- 15+
CREATE PUBLICATION f_pub  FOR TABLE orders WHERE (status = 'paid');  -- 15+, row filter
ALTER PUBLICATION app_pub ADD TABLE order_items;
ALTER PUBLICATION app_pub SET (publish = 'insert,update');

-- On the subscriber, after creating matching tables
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=primary dbname=prod user=repl password=... sslmode=require'
  PUBLICATION app_pub
  WITH (copy_data = true, streaming = on, origin = none);

ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION;   -- pick up newly added tables
ALTER SUBSCRIPTION app_sub DISABLE;
DROP SUBSCRIPTION app_sub;                        -- drops the remote slot, do this first

SELECT * FROM pg_publication_tables;
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag_bytes
FROM pg_replication_slots;
SELECT subname, received_lsn, latest_end_lsn, last_msg_receipt_time
FROM pg_stat_subscription;

Every replicated table needs a replica identity, so a table without a primary key needs ALTER TABLE t REPLICA IDENTITY FULL or updates and deletes will error. Sequences, DDL, and large objects are not replicated. An inactive slot retains WAL forever and will fill the disk, so monitor pg_replication_slots and drop what you abandon.

A dump is not a backup

The failure mode is always the same: the dump ran nightly for two years and nobody tried to restore it.

- Restore it on a schedule, automatically, into a throwaway database, and assert on row counts and a checksum of a known table. An untested backup is a hypothesis. - Check the exit code, not just that a file exists. A truncated dump is still a file, and a plain-SQL restore without ON_ERROR_STOP=1 exits 0 after failing. - Back up globals separately. Restoring a database whose roles do not exist fails on every GRANT and every ALTER ... OWNER. - Verify a physical backup with pg_verifybackup, and keep the WAL that the base backup needs. A base backup without its WAL is unusable. - Measure the restore time, not the dump time. A 400 GB single-threaded restore can take a day, and that number is your real recovery objective. - Keep copies off the machine and off the same account, because DROP DATABASE and a compromised credential both reach anything the server can write to.