# pg_dump and pg_restore

> Back up and restore scratch databases using plain and custom-format logical dumps.

---

LLMS index: [llms.txt](/en/llms.txt)

---

`pg_dump` is the standard PostgreSQL backup tool. It produces a consistent snapshot of a single database, either as a plain SQL script or as a custom binary archive. Its companion `pg_restore` reads the binary archives back. For full-cluster backups (`pg_dumpall`), per-tablespace data, and point-in-time recovery, PostgreSQL offers separate tools — but `pg_dump` covers the great majority of "back up my database" needs. It is what you reach for first.

## What you'll learn {#learn}

- The two output formats (plain `.sql` and custom `.dump`)
- `pg_dump` and `pg_restore` flags worth memorizing
- Dumping schema only vs data only vs both
- Including/excluding specific tables or schemas
- Restoring in parallel for speed

**Setup.** These are shell commands, not SQL. Run them from your terminal. The source file for this lesson is a thin wrapper that documents the commands.

## Example {#example}

```bash {num="54.1" caption="pg_dump and pg_restore"}
# Plain-text SQL dump (human-readable, runnable with psql)
pg_dump --dbname=postgres --format=plain --file=backup.sql

# Custom-format dump (compressed binary, restorable with pg_restore)
pg_dump --dbname=postgres --format=custom --file=backup.dump

# Schema only (DDL, no data) — useful for reproducing the shape of a database
pg_dump --schema-only --dbname=postgres --file=schema.sql

# Data only (no DDL)
pg_dump --data-only --dbname=postgres --file=data.sql

# Dump only specific tables (or use --exclude-table)
pg_dump --table=fruits --table=orders_example --dbname=postgres --file=subset.sql

# Dump only one schema
pg_dump --schema=app --dbname=postgres --file=app_schema.dump --format=custom

# Restore from a custom-format dump
pg_restore --dbname=postgres --jobs=4 --clean --if-exists backup.dump

# Restore a plain SQL dump
psql --dbname=postgres --file=backup.sql

# Dump every database in the cluster, plus roles and globals
pg_dumpall --file=cluster.sql
```

The **custom format** (`--format=custom` or `-Fc`) is what most teams use. It is compressed by default, supports parallel restore (`--jobs=N`), and lets you selectively restore individual objects (`pg_restore --list backup.dump` to inspect, then `--use-list` to pick what to restore). Plain format is easier to read and grep, but less flexible.

`pg_dump` uses a consistent snapshot and takes `ACCESS SHARE` locks on the tables it dumps. Concurrent data changes do not invalidate that snapshot and ordinary writers can continue. Conflicting DDL may wait or cause a dump to fail; parallel dumps use synchronized snapshots.

`--clean --if-exists` drops objects included in the archive before recreating them; it does not remove every unrelated object or guarantee an error-free restore. Restore into a fresh scratch database for a predictable result. `--jobs=4` can speed up a custom- or directory-format restore.

For production backups, `pg_dump` is part of the strategy but not the whole story. *Continuous archiving* (`archive_mode = on`) plus base backups (`pg_basebackup`) give you point-in-time recovery. Cloud-managed PostgreSQL handles this transparently.

## Run the example {#run}

To run (this is a shell example — see the SQL file for the documented commands):

```text
$ pg_dump --dbname=postgres --format=custom --file=backup.dump
$ pg_restore --dbname=postgres --clean --if-exists --jobs=4 backup.dump
```

## Common pitfalls {#pitfalls}

- Use `pg_dumpall --globals-only` for roles and tablespaces. Neither it nor `pg_dump` backs up replication slots; record and recreate those separately as needed.
- Restoring into a database that already has the schema produces conflicts. Use `--clean --if-exists`, drop and recreate the database, or restore into an empty one.
- A plain SQL dump restored with `psql` does not stop on the first error by default — pass `-v ON_ERROR_STOP=1` to make it fail loud.
- Version compatibility: `pg_dump` cannot dump a server newer than its own major version. Dumps are intended for the same or newer PostgreSQL major version; restoring to an older major is not guaranteed. For upgrades, use the target major's `pg_dump` if it supports the source server.

> [!TIP] Tip
> `pg_dump -t my_table -Fc db | pg_restore -t my_table -d other_db` copies one table from one database to another in a single pipeline — no intermediate file. Handy for moving snapshots around.

> [!NOTE] Try it
> Dump your scratch database, drop a table, then restore from the dump. Run `pg_restore --list backup.dump` to see the manifest of objects. Try `pg_restore --schema-only` to recreate just the structure on a fresh database.

## Downloads {#downloads}

SQL file: [pg-dump-and-restore.sql](/examples/source/pg-dump-and-restore.sql) · [All lesson files](/examples/examples.zip) · [Setup guide](/en/#prerequisites)