skip to content

Logical Dumps

pg_dump copies a PostgreSQL database from one consistent snapshot as SQL or an archive, and pg_restore replays it in parallel. Interviewers probe formats, global objects and slow restores.

part ofPostgreSQLread it →
on this pageshow

explore

questions

5

What are PostgreSQL pg_dump's four output formats, and which tool restores each of them?

level: juniorimportance: must knowfreq 72%

answer

  1. script versus archive
  2. psql reads one, pg_restore the rest
  3. only one format dumps in parallel
  4. tar: no compression, no -j restore

basics

~10 s

pg_dump writes plain SQL (-Fp), replayed with psql, or one of three archives read by pg_restore: custom (-Fc, one compressed file), directory (-Fd, per-table files, the only parallel-dump format) and tar (-Ft, uncompressed).

solid answer

~50 s

`pg_dump` has four formats. **Plain** (`-Fp`, the default) is an SQL script: you replay it with `psql`, you can read and edit it, but you cannot pick objects from it or restore it in parallel. The other three are archives that only `pg_restore` reads. **Custom** (`-Fc`) is a single file, compressed by default, and supports selective, reordered and parallel restore. **Directory** (`-Fd`) writes a `toc.dat` plus one data file per table and is the only format `pg_dump -j` can write in parallel. **Tar** (`-Ft`) is uncompressed and can be neither restored in parallel nor have its data items reordered. `pg_restore` detects the archive format itself, and without `-d` it turns any archive back into a plain SQL script. In practice, use custom or directory for anything you may restore selectively or quickly, and plain for small dumps you want to read or diff.

code

bash · 15 lines
bash
# One customer database, four formats
pg_dump -Fp -f cust_harbor.sql      cust_harbor
pg_dump -Fc -f cust_harbor.dump     cust_harbor
pg_dump -Fd -j 4 -f cust_harbor.dir cust_harbor
pg_dump -Ft -f cust_harbor.tar      cust_harbor

# Plain: psql replays the script into an empty database
createdb -T template0 cust_harbor_copy
psql -X -d cust_harbor_copy -f cust_harbor.sql

# Archive: pg_restore detects the format (custom or directory for -j)
pg_restore -d cust_harbor_copy2 -j 4 cust_harbor.dump

# Archive back to SQL text, to inspect it
pg_restore -f inspect.sql cust_harbor.dump

go deeper

for a junior

Name the four formats and their flags, and say which tool restores which: psql for plain, pg_restore for custom, directory and tar.

for a middle

Explain why archives allow selective, reordered and parallel restore, why only directory format dumps in parallel, and how default compression differs per format.

for a senior

Pick a format from the restore you will need: custom or directory for fast, partial or staged restores, plain only where a readable script is the goal.

for a principal

Treat the format as a contract with future restores: one archive can serve disaster recovery, staging copies and audits, while a plain script freezes those choices.

## What pg_dump produces `pg_dump` is PostgreSQL's logical export tool for **one database**. It connects like any other client, opens a read-only `REPEATABLE READ` transaction, and writes out the definitions and the rows that this transaction's snapshot sees. The `-F`/`--format` option decides how the result is written. That choice also decides which tool can read it back and what that tool may do with it: - the **script format** (plain) is SQL text, replayed by `psql`; - the **archive formats** (custom, directory, tar) are read only by `pg_restore`, which can select, reorder and parallelise what it restores. ## The four formats side by side | Format | Flag | On disk | Restored with | Compressed by default | Parallel dump | Parallel restore | |---|---|---|---|---|---|---| | plain | `-Fp` (default) | one SQL script | `psql` | no | no | no | | custom | `-Fc` | one archive file | `pg_restore` | yes | no | yes | | directory | `-Fd` | `toc.dat` plus one file per table and large object | `pg_restore` | yes | yes | yes | | tar | `-Ft` | one tar file | `pg_restore` | not supported | no | no | The details behind the table: - **Custom** and **directory** compress each table's data with `gzip` at a moderate level by default. `-Z`/`--compress` picks `gzip`, `lz4`, `zstd` or `none`, optionally with a level. - **Directory** is the only format that several processes can write at once, which is why `pg_dump -j` refuses every other format with `parallel backup only supported by the directory format`. - Extracting a **tar** archive yields a valid directory-format archive. However, tar cannot be compressed, and its table-data items cannot be reordered during a restore. - **Plain** output is uncompressed unless you pass `-Z`, which then compresses the whole file as if it had been piped through `gzip`, `lz4` or `zstd`. ## Restoring each format 1. **Plain**: create an empty database from `template0`, then feed the script to `psql`, for example `createdb -T template0 cust_harbor` and then `psql -X -d cust_harbor -f cust_harbor.sql`. The script runs top to bottom, and the only way to restore part of it is to edit the text. 2. **Custom or directory**: `pg_restore -d cust_harbor cust_harbor.dump`, or the directory's path. `pg_restore` detects the format itself. It can restore only some objects, reorder them through a table-of-contents list, and run several jobs with `-j`. 3. **Tar**: also `pg_restore`, but without parallel jobs. 4. **Any archive back to SQL**: `pg_restore` without `-d` writes an equivalent plain script to a file (`-f`) or to standard output. This is how you read what an archive would execute before you run it. The tools do not cross over. Handing a plain dump to `pg_restore` fails with `input file appears to be a text format dump. Please use psql.`, and handing an archive to `psql` fails because an archive is not SQL text. ## Choosing a format - **Custom**: the usual choice for a single database. It is one file, compressed, and supports everything `pg_restore` can do, including `pg_restore -j`. - **Directory**: when the dump itself must be fast (`pg_dump -j`), or when you want per-table files that ordinary tools can move or re-compress. - **Plain**: for small databases, for schema-only dumps you want to read, diff or commit, and for targets where `pg_restore` is not available. - **Tar**: rarely, when one portable file matters more than compression or parallel restore. ## Options that belong to restore time With a plain dump, options such as `--clean`, `--create` and `--no-owner` are written into the script when `pg_dump` runs, so the script is fixed. When `pg_dump` writes an archive, it ignores those three options. The archive keeps all the information, and you choose them when you call `pg_restore`. This is a large part of why archives are preferred: one dump can serve several different restores. Two more rules interviewers like: - `pg_dump` can dump servers older than itself (back to 9.2), but it refuses to dump a server of a newer major version. - Restoring a dump runs code chosen by the source database's superusers. Inspect a dump from a source you do not trust before you restore it. For an archive, `pg_restore -f` gives you the script to read.

  • Can a plain-format pg_dump file be restored selectively or in parallel?
    No. It is a script that `psql` runs from top to bottom, so selecting objects means editing the text, and there is no parallel mode. `pg_restore` rejects it with `input file appears to be a text format dump. Please use psql.` If you may need selective or parallel restores later, dump in custom or directory format. `pg_restore` can still turn that archive into a plain script whenever you want one.
  • Why do --clean, --create and --no-owner matter at pg_restore time rather than at pg_dump time for archives?
    When `pg_dump` writes an archive, it ignores these options. The archive records every object with its owner, and `pg_restore` decides at restore time whether to emit `DROP`s, a `CREATE DATABASE` or ownership commands. So one custom-format dump can feed both an overwrite of production-like data and a fresh staging copy owned by one account. A plain script fixes those choices when it is written.
  • How does compression differ between the pg_dump formats?
    Custom and directory compress each table's data with gzip by default. `-Z`/`--compress` selects gzip, lz4, zstd or none, plus a level. Plain output is uncompressed unless `-Z` is set, and then the whole file is compressed. Tar does not support compression at all. Files in an uncompressed directory archive can later be compressed with ordinary gzip, lz4 or zstd tools.

  • pg_restore can restore a plain SQL dump if you pass it the file.
  • The custom format supports parallel dumping with pg_dump -j.
  • The tar format is compressed by default, just like the custom format.
  • pg_dump's --clean is applied when writing a custom-format archive.
  • A plain dump is the best format for selective restores of single tables.
open as a page

Copying one PostgreSQL database to staging with pg_dump, why do owner and grant statements fail on restore, and how do you fix that?

level: middleimportance: must knowfreq 61%

basics

~10 s

pg_dump exports one database but not the cluster-wide roles its owners and grants name, so those statements fail on a fresh cluster. Restore the roles first with pg_dumpall --globals-only, or restore with --no-owner --no-privileges.

open as a page

How do pg_dump -j and pg_restore -j parallelise a PostgreSQL dump and restore, and what limits each one?

level: middleimportance: should knowfreq 50%

basics

~20 s

pg_dump -j dumps several tables at once, needs the directory format and opens N+1 connections sharing one snapshot. pg_restore -j loads data and builds indexes and constraints concurrently from a custom or directory archive, never with --single-transaction.

open as a page

Copying a PostgreSQL customer database to staging, how do you skip a huge log table's rows and control what pg_restore restores with --filter, --section and --list?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Dump with --exclude-table-data (or a --filter file line exclude table_data) to keep the log table's definition without rows. Then edit pg_restore -l output and replay it with -L, or restore pre-data, data and post-data sections separately.

open as a page

After restoring a PostgreSQL 18 pg_dump archive into staging, queries plan badly at first; what do pg_dump's --statistics option and a post-restore ANALYZE each cover?

level: seniorimportance: should knowfreq 32%

basics

~20 s

A logical restore loads rows without planner statistics. PostgreSQL 18's pg_dump --statistics, off by default, dumps table and column statistics that pg_restore applies, but not extended statistics, so ANALYZE is still needed for those and after masking.

open as a page