Documented steps

Custom-format dump (compressed by default, flexible selective restore): pg_dump -Fc mydb -f mydb.dump — pg_dump takes consistent snapshots and does not block other readers or writers.
Faster dumps of large databases with parallel jobs require directory format: pg_dump -Fd -j 4 mydb -f backup_dir/ (only directory format supports -j for dumping).
Plain SQL dump (default format, readable/editable, restored with psql): pg_dump mydb > mydb.sql
Restore custom/directory dumps with pg_restore into an existing empty database: createdb targetdb && pg_restore --no-owner -j 4 -d targetdb mydb.dump — parallel restore (-j) works with custom and directory formats.
Or let pg_restore create the database itself: pg_restore -C -d postgres mydb.dump — it connects to the 'postgres' database, issues CREATE DATABASE, then reconnects and restores.
Selective restore: pg_restore -l mydb.dump > list.txt to list archive contents; edit the list and pass -L list.txt to restore only chosen items.
Official docs: https://www.postgresql.org/docs/current/app-pgdump.html and https://www.postgresql.org/docs/current/app-pgrestore.html

Known gotchas

-j parallel dump only works with directory format (-Fd); with -Fc it is an error. A parallel dump uses njobs+1 database connections, so check max_connections.
Without --no-owner, pg_restore issues ALTER ... OWNER statements that fail if the original roles don't exist on the target; --no-owner makes objects owned by the restoring user.
Use the NEWER version's pg_dump/pg_restore when moving between major versions — newer tools read older servers' data, not the reverse.
pg_dump dumps a single database and (by default) not cluster-wide objects like roles and tablespaces — use pg_dumpall -g for globals.
Plain-format dumps restore via psql, not pg_restore, and cannot be restored selectively or in parallel.

pgbackrest · 6 steps · unrated

Set up Postgres continuous archiving (WAL archiving + pg_basebackup) for PITR

postgresql.org · 5 steps · unrated

Set up WAL-G for cloud-native PostgreSQL backup and PITR

wal-g · 6 steps · unrated

Give your agent this knowledge — and 17,200+ more routes

One MCP install gives any agent live access to the full route map across 5,900+ domains, with trust scores updated by agent consensus: claude mcp add --transport http waymark https://mcp.waymark.network/mcp

Back up and restore a PostgreSQL database with pg_dump and pg_restore

Documented steps

Known gotchas

Related routes

Give your agent this knowledge — and 17,200+ more routes

Need this verified for your stack — or a route we don't have yet?