PostgreSQL Dump and Restore: The Complete pg_dump Guide

What pg_dump Does — and What It Doesn't Export

pg_dump exports a single database: its tables, views, sequences, functions, indexes, triggers, and in-database grants. It does not export roles, tablespaces, or any server-level configuration. If your restore target is a fresh Postgres server, those global objects must come from pg_dumpall, or your restore will fail silently on permission checks.

In our work with client migration projects, the most common surprise is a restore that completes without errors but leaves application users unable to connect, because the role that owns the database never existed on the target server. pg_dump has no answer for that.

What pg_dump exports (per database):

• Tables, views, materialized views
• Sequences and their current values
• Functions, procedures, triggers
• Indexes and constraints
• Column-level and object-level grants within that database

What pg_dump does NOT export:

• Roles and role memberships (login users, group roles)
• Tablespace definitions
• Server-level postgresql.conf or pg_hba.conf settings
• Other databases on the same server

For global objects, run pg_dumpall --globals-only on the source server before your per-database dump. That command produces a plain SQL file containing every CREATE ROLE and CREATE TABLESPACE statement, which you pipe through psql on the target before calling pg_restore.

According to the official PostgreSQL documentation, pg_dump "makes consistent backups even if the database is being used concurrently", but that consistency guarantee is scoped to one database only. Cross-database foreign data wrappers and dblink dependencies are exported structurally but will break at restore time if the referenced database or role does not exist on the target server.

Prerequisites: Tools, Permissions, and Access

Three binaries must be present and version-matched before any pg_dump command will work reliably: pg_dump itself, pg_restore, and psql. On Debian/Ubuntu servers, all three ship in the postgresql-client package, not in postgresql, so a server running PostgreSQL 16 needs postgresql-client-16 installed on the machine running the dump.

Binary version mismatch is the single most common source of cryptic restore failures we see in client migration projects; a PostgreSQL 14 pg_restore reading a dump produced by a PostgreSQL 16 server will refuse with a version error before it processes a single table.

According to the official PostgreSQL documentation, pg_dump requires CONNECT and SELECT privileges on every table and sequence in the target database. In practice, a dedicated backup role works for single-database dumps. Dumping global objects, roles and tablespaces, via pg_dumpall requires superuser access.

For non-interactive use (cron jobs, CI pipelines), store credentials in a .pgpass file rather than passing -W or embedding passwords in scripts. The format is hostname:port:dbname:username:password, with permissions set to 0600, PostgreSQL silently ignores the file if permissions are too open.

Dump Your PostgreSQL Database

The fastest way to produce a portable, compressed backup of a postgres database is:

pg_dump -Fc -U postgres -h localhost -p 5432 mydb > mydb.dump

Each flag earns its place:

• `-Fc` selects the custom dump format, a compressed binary that `pg_restore` can read selectively and in parallel
• `-U postgres` sets the role; substitute your actual superuser or a role with SELECT on all tables
• `-h localhost` forces a TCP connection rather than a Unix socket, which matches the behavior you will see against remote servers
• `mydb` is the dbname argument, the only required positional parameter `pg_dump` accepts

The exit code matters, especially in CI. A zero means the dump completed without errors; any non-zero value means at least one object was skipped or the connection failed. In automated pipelines, always check it explicitly:

pg_dump -Fc -U postgres -h localhost mydb > mydb.dump
if [ $? -ne 0 ]; then
  echo "pg_dump failed, aborting pipeline" >&2
  exit 1
fi

Ignoring this and shipping a corrupt backup file to S3 is the backup antipattern that comes up most often in client migration audits. A common question teams ask when they first set up automated dump restore workflows is whether a zero exit code guarantees a usable file: it does not guarantee schema completeness if permissions are missing, so always follow up with a test restore in staging.

Output formats compared

`pg_dump` supports four output formats. The choice determines which restore tool you can use, whether parallel restore is available, and how much disk the file occupies.

Plain SQL is human-readable and psql-compatible, but on a 500 MB postgres database the uncompressed `.sql` file typically runs 600-750 MB on disk; the same database exported with `-Fc` lands around 120-180 MB depending on data compressibility. That difference compounds in retention policies.

The directory dump format (`-Fd`) splits each table into its own compressed file inside a folder, building a stack of per-file archives rather than one monolithic output. This is the only format that supports both parallel dump (`pg_dump -Fd -j 4`) and parallel restore, making it the right answer for databases above roughly 10 GB where single-threaded restore time becomes a constraint. Per PostgreSQL documentation on backup formats, the directory format is specifically designed for `pg_restore -j` parallelism.

Connection parameters for remote servers

For a remote PostgreSQL server, extend the same command with explicit host and port:

pg_dump -Fc -U appuser -h db.prod.example.com -p 5432 mydb > mydb_$(date +%Y%m%d).dump

The `$(date +%Y%m%d)` suffix is worth building in from day one: timestamped filenames prevent silent overwrites in cron backup scripts and make point-in-time recovery audits straightforward.

Non-interactive authentication

`pg_dump` will hang waiting for a password prompt in any non-interactive context: cron, Docker exec, or CI. There are two ways to pass credentials without the prompt.

Option 1: .pgpass file

Create `~/.pgpass` with one line per connection:

hostname:port:database:username:password
db.prod.example.com:5432:mydb:appuser:s3cr3t

Set permissions to 0600; PostgreSQL ignores the file entirely if it is world-readable:

chmod 0600 ~/.pgpass

Option 2: PGPASSWORD environment variable

PGPASSWORD=s3cr3t pg_dump -Fc -U appuser -h db.prod.example.com mydb > mydb.dump

The `.pgpass` approach is preferable in persistent environments because `PGPASSWORD` exposes the credential in the process list (`ps aux` reveals it). In ephemeral CI environments where the environment is sealed, `PGPASSWORD` is acceptable, but never commit it to version control. Use a secrets manager to inject it at runtime, and share the configured secret name with your team rather than the value itself.

pg_dump Flags Quick Reference

The table below covers the flags asked about most often, from dumping a single table to stripping data entirely for schema migrations.

The -j flag for parallel dump only works with -Fd format because the directory layout gives each worker its own file to write; plain SQL and custom format (-Fc) are single-stream by design. The same constraint applies on the restore side: pg_restore -j 4 requires a dump produced with -Fd or -Fc, per the PostgreSQL documentation — plain SQL files restored through psql have no parallel path at all.

For -t and --exclude-table, both flags accept shell-style wildcards: -t 'order_*' dumps every table whose name starts with order_, which is useful when a database uses a prefix convention for partitioned or tenant-scoped tables.

Restore Your PostgreSQL Dump

The restore tool you need is determined by the dump format, not personal preference. Use psql for plain SQL .sql files; use pg_restore for custom (-Fc), directory (-Fd), and tar formats. A common question on Stack Overflow is exactly this mismatch, so follow the format-to-tool rule below before running any dump restore command.

Getting this wrong produces errors like `pg_restore: error: input file appears to be a text format dump`, a five-second fix once you know the rule, but a frustrating one at 2 a.m. during a migration.

Psql restore (plain SQL dumps)

Before restoring, create the target postgres database, psql does not create it for you:

psql -U postgres -c 'CREATE DATABASE mydb WITH OWNER myuser;'

Then pipe the dump file directly:

psql -U postgres -d mydb -f mydb_20260101.sql

The -f flag reads from a file; omit it and you can pipe from stdin (`psql mydb < mydb.sql`). Both work. The plain SQL format runs every CREATE, INSERT, and ALTER statement sequentially, so restore is single-threaded by design, which is why large databases restore noticeably slower this way.

Pg_restore (custom and directory formats)

pg_restore gives you selective restore, format-aware decompression, and, critically, parallel restore via the -j flag:

pg_restore -U postgres -d mydb -Fc mydb.dump

Common flags worth knowing:

The -j parallel restore flag requires either directory format (-Fd) or custom format (-Fc). Plain SQL cannot be parallelized because it is a single ordered file with no table-of-contents index. The directory format stores one file per table, which is what gives pg_restore -j independent units of work to distribute across the worker stack.

On one recent client migration, a 480 GB PostgreSQL 15 postgres database moving to a new RDS instance, single-job restore took just over 4 hours. With -j 8 and directory format, the same dump restore finished in 58 minutes.

Dumping and Restoring Inside Docker Containers

Running PostgreSQL inside a container doesn't change the tools, pg_dump, pg_restore, and psql all behave identically. What changes is how you reach the running server.

Dump from a running container directly to your host filesystem:

docker exec <container_name> pg_dump -U postgres -Fc dbname > /backups/dbname.dump

The redirect happens on the host, so the dump file lands locally without any intermediate copy step. Pass PGPASSWORD inline if the database requires a password:

docker exec -e PGPASSWORD=secret <container_name> pg_dump -U postgres -Fc dbname > /backups/dbname.dump

Avoid baking credentials into images; the -e flag keeps them out of the image layer and out of shell history when you control the calling script.

Restore a dump file into a container:

For a plain SQL file, pipe it through psql using the container's binary directly:

cat dbname.sql | docker exec -i <container_name> psql -U postgres dbname

For a custom-format dump, copy the file into the container first, then run pg_restore:

docker cp /backups/dbname.dump <container_name>:/tmp/dbname.dump
docker exec <container_name> pg_restore -U postgres -d dbname /tmp/dbname.dump

The docker cp step is necessary because pg_restore reads a file path, not stdin by default. You can also use pg_restore -d dbname - to read from stdin if you prefer to skip the copy:

cat /backups/dbname.dump | docker exec -i <container_name> pg_restore -U postgres -d dbname -

Version mismatch inside containers is a common trap. We've seen restore failures on client migration projects where the host pg_restore binary was PostgreSQL 14 but the container ran PostgreSQL 16. The dump completed without errors, but restore threw `pg_restore: error: unsupported version (1.15) in file header`. The answer is always to run pg_restore from inside the container, or from a client binary that matches the server version, rather than from the host. Per the PostgreSQL versioning policy, minor versions within the same major are compatible, but crossing major versions requires matching tools. This version alignment becomes even more critical when managing multiple database environments across your infrastructure.

For automated container backups, pass the docker exec command directly into a cron job:

0 2 * * * docker exec <container_name> pg_dump -U postgres -Fc dbname > /backups/dbname_$(date +\%Y\%m\%d).dump

Please check exit codes in any scripted backup. pg_dump returns 0 on success and non-zero on failure; a cron job that silently swallows a failed dump will leave you without a usable backup file when you need it most.