Back to Glossary

PostgreSQL Backup Tooling

pg_dump

Learn when pg_dump is the right backup tool, where it struggles at scale, and how to combine it with physical backup strategies.

Definition

A logical backup utility that exports a database's schema and/or data to a portable format (SQL, custom, directory).

What pg_dump Is Best At

pg_dump is ideal for logical exports, schema portability, and selective migration workflows where object-level control matters.

It works well for developer backups, environment seeding, and controlled object-level transfers between PostgreSQL environments.

  • Portable logical backups (schema + data)
  • Object-level scope control (database/schema/table)
  • Useful for migration rehearsals and audit workflows

Where pg_dump Alone Falls Short

For large clusters and strict RTO targets, logical backup and restore can be too slow if used as the only DR path.

Production teams usually pair pg_dump with physical backup and WAL-based recovery for incident response resilience.

See pg_restore, pg_basebackup, and backup tooling comparisons.

Format Choices and Restore Impact

The output format directly affects restore flexibility and speed. Custom and directory formats support selective and parallel restore paths, while plain SQL is simpler but less flexible at scale.

  • Plain SQL: simple and human-readable, limited restore flexibility
  • Custom format: good default for selective/parallel restores
  • Directory format: strong choice for very large datasets and parallel jobs

Operational Runbook Guidance

  • Automate dump jobs and integrity checks
  • Encrypt and version backup artifacts
  • Test restores against realistic data size every month
  • Track restore duration as an SLO-linked metric

Related Glossary Topics

pgbench pg_restore pg_basebackup PITR (Point-in-Time Recovery) Physical vs Logical Backup Primary Key Partitioning ACID

Frequently Asked Questions

What is pg_dump used for?
pg_dump creates logical PostgreSQL backups used for migration, auditing, and selective restore workflows.
Is pg_dump enough for disaster recovery?
For small systems it can be acceptable, but most production teams pair it with physical backups and WAL replay for tighter RPO/RTO.
How does pg_dump differ from pg_basebackup?
pg_dump exports logical objects and data; pg_basebackup copies physical cluster state for full-cluster recovery.
Can pg_dump handle very large databases quickly?
It can, but restore duration often becomes the bottleneck. Validate with full-size restore drills, not estimate-based planning.
What is the safest way to use pg_dump in production?
Automate dumps, verify artifacts, encrypt storage, and run repeatable restore drills in isolated environments.