PostgreSQL Backup and Restore Cheat Sheet

pg_dump -h localhost -U postgres -d appdb -F c -f appdb.dump

# Create a compressed custom-format backup archive.

pg_dump -h localhost -U postgres -d appdb -F d -j 4 -f /backups/appdb_dir

# Create a directory-format dump that supports parallel restore.

pg_dump -h localhost -U postgres -d appdb -F p -f appdb.sql

# Create a plain-text SQL dump.

pg_dump -h localhost -U postgres -d appdb --schema-only -f schema.sql

# Export only DDL without row data.

pg_dump -h localhost -U postgres -d appdb --data-only -f data.sql

# Export only table data.

pg_restore -h localhost -U postgres -d appdb appdb.dump

# Restore a custom or directory archive into a database.

pg_restore -h localhost -U postgres -d appdb --clean --if-exists appdb.dump

# Drop objects before recreating them during restore.

psql -h localhost -U postgres -d appdb -f appdb.sql

# Replay a plain-text SQL dump with psql.

pg_dumpall -h localhost -U postgres --globals-only > globals.sql

# Export cluster-wide roles and tablespaces.

pg_dumpall -h localhost -U postgres > cluster.sql

# Dump all databases in one plain SQL file.

pg_basebackup -h primary-db -U replicator -D /backups/base -Fp -Xs -P

# Take a physical base backup suitable for replication or disaster recovery.

pg_verifybackup /backups/base

# Verify integrity of a base backup manifest.

pg_upgrade --check -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/17/data -D /var/lib/postgresql/18/data

# Check whether an in-place major upgrade is feasible.

pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin -d /var/lib/postgresql/17/data -D /var/lib/postgresql/18/data

# Perform an in-place major version upgrade.

vacuumdb --all --analyze-in-stages

# Collect statistics after a fresh restore or upgrade.

pg_dump -h localhost -U postgres -d appdb -t public.users -f users.sql

# Export one table definition and data.

pg_dump -h localhost -U postgres -d appdb -n accounting -f accounting.sql

# Export one schema from a database.

pg_restore -l appdb.dump

# Show archive items without restoring.

pg_restore -h localhost -U postgres -d appdb -t public.users appdb.dump

# Restore only a named relation from an archive.

pg_restore -h localhost -U postgres -d appdb --schema-only appdb.dump

# Restore DDL only from an archive.

pg_restore -h localhost -U postgres -d appdb --data-only appdb.dump

# Restore table data without recreating objects.

pg_restore -h localhost -U postgres -d appdb -j 4 appdb.dump

# Restore using multiple worker jobs when archive format allows it.

COPY public.users TO '/var/lib/postgresql/users.csv' CSV HEADER;

# Write a table to a server-side file.

COPY public.users (user_id, email, created_at)
FROM '/var/lib/postgresql/users.csv' CSV HEADER;

# Load rows from a server-side CSV file.

\copy public.users TO './users.csv' CSV HEADER

# Export via the client machine rather than the database server.

\copy public.users (user_id, email, created_at) FROM './users.csv' CSV HEADER

# Import CSV from the client machine.

COPY (SELECT * FROM public.users) TO PROGRAM 'gzip > /tmp/users.csv.gz' CSV HEADER;

# Stream exported data to a server-side program.

COPY staging_import FROM STDIN WITH (FORMAT csv, HEADER true, FREEZE true);

# Load immutable initial data into a newly created or truncated table.