Backup & Restore (pg_dump)

PostgreSQL gives you two broad families of backup: logical backups, which export data as a set of SQL statements or a portable dump file, and physical backups, which copy the actual on-disk files. Most day-to-day backup needs are served by logical backups with pg_dump.

pg_dump: Backing Up a Single Database

pg_dump exports one database (or a selected subset of its objects) as a consistent snapshot, even while other clients continue reading and writing to it.

pg_dump-examples.sh

Bash

# Plain SQL dump of an entire database, written to a file
pg_dump -U myuser -d storefront -f storefront_backup.sql

# Custom-format dump (compressed, required for pg_restore's advanced options)
pg_dump -U myuser -d storefront -Fc -f storefront_backup.dump

# Dump only a specific table
pg_dump -U myuser -d storefront -t orders -Fc -f orders_only.dump

Output Formats

Format	Flag	Notes
Plain SQL text	`-Fp` (default)	Human-readable SQL script. Restore with `psql`. Cannot be restored selectively or in parallel.
Custom format	`-Fc`	Compressed, PostgreSQL-specific binary format. Must be restored with `pg_restore`. Supports selective restore of individual tables/objects.
Directory format	`-Fd`	One file per table inside a directory. Restore with `pg_restore`. Supports parallel restore (`-j`), fastest option for large databases.

pg_dumpall: Cluster-Wide Backups

pg_dump only covers one database's objects and data — it does not include roles, tablespaces, or other cluster-wide objects that live outside any single database. pg_dumpall captures the whole cluster, which matters if you're restoring onto a brand-new server rather than into an existing one.

Bash

pg_dumpall -U myuser -f full_cluster_backup.sql

pg_restore

pg_restore-examples.sh

Bash

# Restore a custom or directory-format dump into an existing (empty) database
pg_restore -U myuser -d storefront storefront_backup.dump

# Restore in parallel using 4 workers — only available for custom/directory format
pg_restore -U myuser -d storefront -j 4 storefront_backup.dump

# Plain SQL dumps are restored with psql instead, not pg_restore:
psql -U myuser -d storefront -f storefront_backup.sql

Physical Backups

For full-cluster backups intended to support point-in-time recovery or as the basis for a standby server, PostgreSQL offers physical backups via pg_basebackup, which copies the raw data directory rather than exporting logical SQL. This is the foundation used for setting up replication — see the Replication & High Availability page for more.

Tip

A backup you have never restored is a guess, not a backup. Regular, automated backups are only half the job — periodically practicing an actual restore onto a scratch server is what tells you whether your backup strategy really works when you need it. Treat this as non-negotiable for any production database.