pg_dump: Back Up a PostgreSQL Database
pg_dump -Fc -f db.dump exports a database into a compressed custom-format archive for restore with pg_restore.
pg_dump produces a logical backup of one database. The custom format (-Fc) is the flexible default for CI because pg_restore can then load it selectively and in parallel, unlike a plain SQL script.
What it does
pg_dump reads a single database and writes its schema and data. The output format is chosen with -F: plain SQL (-Fp, the default), custom archive (-Fc), directory (-Fd), or tar (-Ft). Custom and directory formats are restored with pg_restore; plain SQL is replayed with psql.
Common usage
# compressed custom-format archive (restore with pg_restore)
PGPASSWORD=secret pg_dump -h db -U app -Fc -f appdb.dump appdb
# plain SQL script (restore with psql)
pg_dump -h db -U app -Fp -f appdb.sql appdb
# schema only, or a single table
pg_dump -h db -U app --schema-only -f schema.sql appdb
pg_dump -h db -U app -t users -Fc -f users.dump appdbOptions
| Flag | What it does |
|---|---|
| -Fc | Custom compressed archive (use with pg_restore) |
| -Fp | Plain SQL script (default; restore with psql) |
| -Fd | Directory format (supports parallel -j) |
| -f <file> | Write to a file instead of stdout |
| --schema-only / --data-only | Dump structure only or data only |
| -t <table> | Dump only the named table(s) |
| -n <schema> | Dump only the named schema |
In CI
Use -Fc for restorable backups you may load selectively; use -Fp only when a human needs to read the SQL. Run pg_dump as a role that can read every object, or restores will be missing tables. Keep the pg_dump client version at least as new as the server, since an older pg_dump cannot dump a newer server.
Common errors in CI
"pg_dump: error: server version: 16.1; pg_dump version: 15.4 ... aborting because of server version mismatch" means the client is older than the server; install a matching or newer client. "permission denied for table" means the dumping role cannot read an object; dump as the owner or a superuser. "connection refused" means the server is not ready; gate with pg_isready.