PostgreSQL Dump and Restore: The Complete pg_dump Guide

pg_dump serves as PostgreSQL’s intrinsic utility for exporting a database into a file, which can later be restored on the same server, a different host, or even a newer version of PostgreSQL. This guide meticulously outlines each step involved in the process, from selecting the appropriate output format and configuring connection parameters securely to restoring databases using either psql or pg_restore. It also addresses scaling for larger databases with parallel jobs and executing dumps within Docker containers, complete with copy-paste commands for various scenarios.

What pg_dump Does — and What It Doesn’t Export

pg_dump is designed to export a single database, encapsulating its tables, views, sequences, functions, indexes, triggers, and in-database grants. However, it does not include roles, tablespaces, or any server-level configurations. If the target for restoration is a fresh PostgreSQL server, these global objects must be sourced from pg_dumpall; otherwise, the restoration may fail silently during permission checks.

In our experience with client migration projects, a common oversight occurs when a restore completes without errors, yet application users find themselves unable to connect due to the absence of the role that owns the database on the target server. pg_dump does not address this issue.

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

To capture global objects, execute pg_dumpall --globals-only on the source server prior to your per-database dump. This command generates a plain SQL file containing all CREATE ROLE and CREATE TABLESPACE statements, which you can then pipe through psql on the target server before invoking pg_restore.

According to the official PostgreSQL documentation, pg_dump “makes consistent backups even if the database is being used concurrently,” but this consistency is limited to a single database. Dependencies involving cross-database foreign data wrappers and dblink are exported structurally but will fail during restoration if the referenced database or role is absent on the target server.

For a reliable pg_dump command execution, three binaries must be present and version-matched: pg_dump, pg_restore, and psql. On Debian/Ubuntu servers, these tools are included in the postgresql-client package rather than the postgresql package itself. Therefore, a server running PostgreSQL 16 requires the installation of postgresql-client-16 on the machine performing the dump.

A binary version mismatch is the most frequent cause of obscure restore failures encountered in client migration projects. For instance, a PostgreSQL 14 pg_restore attempting to read a dump from a PostgreSQL 16 server will reject the operation with a version error before processing any tables.

As per the official PostgreSQL documentation, pg_dump necessitates CONNECT and SELECT privileges on every table and sequence within the target database. In practice, a dedicated backup role suffices for single-database dumps, while dumping global objects, roles, and tablespaces via pg_dumpall requires superuser access.

For non-interactive use cases like cron jobs or CI pipelines, it is advisable to store credentials in a .pgpass file instead of passing the -W flag or embedding passwords in scripts. The format for the .pgpass file is hostname:port:dbname:username:password, with permissions set to 0600; PostgreSQL will silently ignore the file if permissions are too permissive.

Dump Your PostgreSQL Database

The quickest method to generate a portable, compressed backup of a PostgreSQL database is as follows:

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

Each flag serves a specific purpose:

• -Fc selects the custom dump format, a compressed binary that pg_restore can read selectively and in parallel.
• -U postgres specifies the role; replace this with your actual superuser or a role with SELECT privileges on all tables.
• -h localhost enforces a TCP connection instead of a Unix socket, mirroring the behavior observed with remote servers.
• mydb is the database name argument, the only required positional parameter accepted by pg_dump.

The exit code is significant, especially in CI environments. A zero indicates that the dump completed without errors; any non-zero value signifies that at least one object was skipped or the connection failed. In automated pipelines, it is essential to verify this 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

Neglecting this step and inadvertently shipping a corrupt backup file to S3 is a common pitfall identified in client migration audits. Teams often inquire whether a zero exit code guarantees a usable file; it does not ensure schema completeness if permissions are lacking, so always follow up with a test restore in a staging environment.

Output formats compared

pg_dump supports four output formats, each influencing the restore tool available, the feasibility of parallel restores, and the disk space occupied by the file.

Flag	Output type	Restore with	Parallel restore (-j)	Compressed by default
(none)	Plain SQL (.sql)	psql	No	No
-Fc	Custom binary (.dump)	pg_restore	Yes	Yes (zlib)
-Fd	Directory of files	pg_restore	Yes	Yes (per-file)
-Ft	Tar archive (.tar)	pg_restore	No	No

The plain SQL format is human-readable and compatible with psql; however, for a 500 MB PostgreSQL database, the uncompressed .sql file typically occupies 600-750 MB on disk. In contrast, the same database exported with -Fc results in a file size of approximately 120-180 MB, depending on data compressibility. This difference is significant in retention policies.

The directory dump format (-Fd) divides each table into its own compressed file within a folder, creating a stack of per-file archives rather than a single monolithic output. This format uniquely supports both parallel dump (pg_dump -Fd -j 4) and parallel restore, making it the optimal choice for databases exceeding roughly 10 GB, where single-threaded restore times become a limiting factor. According to the PostgreSQL documentation on backup formats, the directory format is specifically designed for pg_restore -j parallelism.

Connection parameters for remote servers

To connect to a remote PostgreSQL server, extend the command with explicit host and port parameters:

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 a valuable addition from the outset, as timestamped filenames prevent silent overwrites in cron backup scripts and facilitate straightforward point-in-time recovery audits.

Non-interactive authentication

When executing pg_dump in non-interactive contexts such as cron jobs, Docker exec, or CI, it will hang while waiting for a password prompt. Two methods exist for passing credentials without prompting:

Option 1: .pgpass file

Create ~/.pgpass with one line per connection:

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

Ensure permissions are set to 0600; PostgreSQL will ignore the file 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 method is preferred in persistent environments, as PGPASSWORD exposes the credential in the process list (visible via ps aux). In ephemeral CI environments where the environment is sealed, PGPASSWORD is acceptable, but it should never be committed to version control. Instead, utilize a secrets manager to inject it at runtime and share the configured secret name with your team rather than the actual value.

pg_dump Flags Quick Reference

The following table summarizes the most frequently asked about flags, ranging from dumping a single table to excluding data entirely for schema migrations.

Flag	Purpose	Example
-Fc	Custom dump format, compressed binary; required for selective pg_restore	pg_dump -Fc -U postgres mydb > mydb.dump
-Fd	Directory dump format, writes one file per table into a folder	pg_dump -Fd -U postgres mydb -f mydb_dir/
-t	Dump a single table (or pattern) from the database	pg_dump -Fc -U postgres -t orders mydb > orders.dump
-n	Restrict dump to a named schema	pg_dump -Fc -U postgres -n billing mydb > billing.dump
-s	Schema-only, DDL with no data rows	pg_dump -Fc -s -U postgres mydb > schema.dump
-a	Data-only, rows with no DDL	pg_dump -Fc -a -U postgres mydb > data.dump
–exclude-table	Omit a specific table; accepts wildcards	pg_dump -Fc --exclude-table=audit_logs mydb > mydb.dump
-Z	Set compression level 0-9 (default 6 for -Fc)	pg_dump -Fc -Z 9 -U postgres mydb > mydb.dump
-j	Parallel dump workers, requires -Fd format	pg_dump -Fd -j 4 -U postgres mydb -f mydb_dir/

The -j flag for parallel dumping is exclusive to the -Fd format, as the directory structure allows each worker to write to its own file. Conversely, plain SQL and custom format (-Fc) are inherently single-stream. The same limitation applies to restoration: pg_restore -j 4 necessitates a dump created with -Fd or -Fc, as per the PostgreSQL documentation — plain SQL files lack a parallel restoration path.

For the -t and --exclude-table flags, both accept shell-style wildcards. For example, -t 'order_*' will dump every table whose name begins with order_, which is particularly useful in databases employing a prefix convention for partitioned or tenant-scoped tables.

Restore Your PostgreSQL Dump

The appropriate restoration tool is dictated by the dump format rather than personal preference. Utilize psql for plain SQL .sql files, and employ pg_restore for custom (-Fc), directory (-Fd), and tar formats. A frequent query on platforms like Stack Overflow pertains to this mismatch, so adhering to the format-to-tool guideline is essential before executing any restoration command.

Misidentifying the tool can lead to errors such as pg_restore: error: input file appears to be a text format dump, a quick fix once the rule is understood, yet a frustrating obstacle during migrations.

Psql restore (plain SQL dumps)

Before initiating a restore, ensure the target PostgreSQL database is created, as psql does not perform this action automatically:

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

Next, pipe the dump file directly:

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

The -f flag directs psql to read from a file; alternatively, you can omit it and pipe from stdin (psql mydb < mydb.sql). Both methods are valid. The plain SQL format executes every CREATE, INSERT, and ALTER statement sequentially, resulting in a single-threaded restoration process, which can noticeably slow down the restoration of large databases.

Pg_restore (custom and directory formats)

pg_restore offers selective restoration, format-aware decompression, and, importantly, parallel restoration via the -j flag:

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

Familiarize yourself with these common flags:

Flag	Effect
-j 4	Restore using 4 parallel workers
-Fc	Declare input as custom format
-t orders	Restore only the orders table
-s	Restore schema only, skip data
–clean	Drop objects before recreating them

The -j parallel restoration flag requires either the directory format (-Fd) or custom format (-Fc). The plain SQL format cannot be parallelized due to its single ordered file structure, which lacks a table-of-contents index. The directory format, on the other hand, stores one file per table, providing pg_restore -j with independent units of work to distribute across the worker stack.

In a recent client migration, a 480 GB PostgreSQL 15 database was transferred to a new RDS instance. The single-job restoration took just over 4 hours, while utilizing -j 8 with the directory format reduced the restoration time to 58 minutes.

Dumping and Restoring Inside Docker Containers

Running PostgreSQL within a container does not alter the functionality of pg_dump, pg_restore, or psql; these tools operate identically. However, the method of accessing the running server does change.

Dump from a running container directly to your host filesystem:

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

The redirect occurs on the host, ensuring that the dump file is saved locally without any intermediate copying. If the database requires a password, pass PGPASSWORD inline:

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

Avoid embedding credentials within images; using the -e flag keeps them out of the image layer and prevents them from appearing in shell history when controlled by 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:

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

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

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

The docker cp step is necessary because pg_restore reads from a file path rather than stdin by default. Alternatively, you can use pg_restore -d dbname - to read from stdin if you prefer to bypass the copy step:

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

Version mismatch within containers is a common pitfall. We have encountered restore failures during client migration projects where the host pg_restore binary was PostgreSQL 14, while the container operated on PostgreSQL 16. The dump completed without errors, but the restoration failed with pg_restore: error: unsupported version (1.15) in file header. The solution is always to execute pg_restore from within the container or from a client binary that matches the server version, rather than from the host. As outlined in the PostgreSQL versioning policy, minor versions within the same major version are compatible, but transitioning between major versions necessitates matching tools. This alignment becomes increasingly crucial when managing multiple database environments across your infrastructure.

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

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

It is imperative to check exit codes in any scripted backup. pg_dump returns 0 upon success and a non-zero value upon failure; a cron job that silently ignores a failed dump will leave you without a usable backup file when it is needed most.

How do I dump a PostgreSQL database from the command line?

pg_dump generates a consistent snapshot of a single database to stdout or a file. Execute pg_dump -Fc -U postgres -h localhost dbname > dbname.dump to create a compressed custom-format dump. This command serves as the foundation for any backup or migration; add -v for progress output with larger databases.

How do I restore a PostgreSQL dump file?

The restoration method varies based on the dump format. For a custom-format file, run pg_restore -U postgres -d dbname dbname.dump; for a plain SQL file, utilize psql -U postgres -d dbname -f dbname.sql. Ensure the target database is created first, as pg_restore will not do this automatically.

What is the difference between pg_dump custom format and plain SQL?

The custom format (-Fc) produces a compressed binary archive that supports selective restoration and parallel restoration via the -j flag; in contrast, plain SQL is a text file that psql executes sequentially. According to the PostgreSQL documentation, custom format is the recommended choice for most backup workflows due to its table-of-contents structure, which allows pg_restore to reorder or skip objects during restoration. Plain SQL is best suited for scenarios requiring a human-readable dump or when output needs to be piped directly into another tool.

How do I dump only the schema (no data) with pg_dump?

Utilize the –schema-only flag with pg_dump to export DDL without any rows: pg_dump -Fc --schema-only -U postgres dbname > schema.dump. This is particularly useful for setting up an empty database on a development server before loading a separate data subset. The resulting dump will contain table definitions, indexes, constraints, and sequences, but no COPY or INSERT statements.

How do I dump a single table with pg_dump?

Employ the -t flag to target a specific table: pg_dump -Fc -U postgres -t public.orders dbname > orders.dump. You can repeat the -t flag to include multiple tables in the same dump file. This is particularly important when refreshing a single large table in a live database without performing a full restore.

How do I dump and restore a PostgreSQL database inside Docker?

Execute pg_dump through docker exec, redirecting output to your host: docker exec pg_dump -U postgres -Fc dbname > dbname.dump. To restore, pipe the file back: docker exec -i pg_restore -U postgres -d dbname < dbname.dump. Ensure that the PostgreSQL binary version within the container matches the dump’s version, as a mismatch on the -Fc archive header will abort the restoration.

Does pg_dump export roles and tablespaces?

pg_dump does not export roles or tablespaces, as these are cluster-level objects rather than database-level ones. To dump roles and tablespace definitions separately, use pg_dumpall --globals-only -U postgres > globals.sql, and restore with psql -U postgres -f globals.sql. Ensure that the globals restore occurs before any database restoration to resolve role ownership references correctly.