pg_dump and pg_restore on Mac: Backup, Restore, Verify

Create, inspect, and restore a custom-format backup

pg_dump --format=custom --file=app-2026-08-04.dump \
  "postgresql://backup_user@db.example.com/app"

pg_restore --list app-2026-08-04.dump | less
createdb app_restore_test
pg_restore --exit-on-error --no-owner \
  --dbname=app_restore_test app-2026-08-04.dump

Avoid putting a password directly in shell history. Use an appropriately protected password file, interactive prompt, or secret mechanism.

Choose the archive format deliberately

A plain SQL dump can be reviewed in a text editor and restored with psql. A custom archive is restored with pg_restore and supports listing, filtering, reordering, and parallel restore. Directory format also supports parallel dump and restore, but it creates multiple files.

For a normal application database, custom format is a useful default. Keep the command, PostgreSQL client version, source version, destination, and completion status with the backup record.

Match pg_dump to the server

pg_dump can usually read older server versions, but it refuses to dump a server newer than its own major version. On a Mac with several PostgreSQL installations, verify which binary the shell resolves before starting a long backup.

Check client and server versions

which pg_dump
pg_dump --version
psql "$DATABASE_URL" -Atc "show server_version"

Use a current pg_dump when migrating to a newer major release and read that release's documentation for compatibility details.

Handle roles and ownership

pg_dump exports one database, not every cluster-wide role and tablespace definition. Use pg_dumpall --globals-only when those objects are part of the recovery plan, and protect that output because it can contain role definitions.

For a development restore under a different owner, --no-owner avoids ALTER OWNER commands. Production recovery may need the original roles created first. Do not add --no-acl automatically if grants are part of the behavior you need to recover.

Inspect untrusted archives before restoring

PostgreSQL warns that restoring a dump executes code chosen by users with sufficient privileges in the source database. Treat an archive from an untrusted source like executable input.

List the archive and render it to SQL for review when trust is uncertain. Restore into an isolated environment with limited credentials rather than into a valuable database.

Render an archive to SQL without executing it

pg_restore --file=review.sql app-2026-08-04.dump
less review.sql

Verify recovery, not just archive creation

A zero exit status from pg_dump proves that the export command completed. It does not prove that the file is retained, decryptable, restorable within the recovery window, or complete enough for the application.

Restore into a clean database, review every pg_restore error, run representative counts and constraints, and open the result in a database client. Use the broader PostgreSQL backup comparison to decide where logical dumps fit beside managed snapshots and point-in-time recovery.

Run basic post-restore checks

select current_database(), current_user;
select count(*) from app.orders;
select pg_size_pretty(pg_database_size(current_database()));
analyze;

Replace the sample table with application-specific checks. ANALYZE helps the restored database rebuild useful planner statistics when needed.