DEV Community

Caleb Mucheru
Caleb Mucheru

Posted on

Simplified Guide to PostgreSQL Data Backup with pg_dump and pg_dumpall

#postgres

Introduction
In the PostgreSQL ecosystem, ensuring the safety and integrity of your data is paramount. The SQL dump method, facilitated by tools like pg_dump and pg_dumpall, allows you to generate files with SQL commands to recreate databases. Let's explore these tools and their usage for effective data backup.

Key Tools
pg_dump
Used for backing up individual databases.

  • Basic Usage:

     pg_dump dbname > dumpfile

  • Writes the result to standard output, creating a text file.

  • Operates remotely, requiring read access to tables (superuser for entire database).

  • Allows specification of host, port, and user for remote connections.

  • Advantage: Output generally reloadable into newer PostgreSQL versions.

  • Internally consistent dumps; does not block other operations.

pg_dumpall
Backs up entire database clusters, including roles and tablespaces.

  • Basic Usage:

     pg_dumpall > dumpfile

  • Resulting dump can be restored using psql.

  • Requires database superuser access for full restore.

  • Preserves cluster-wide data, but snapshots of different databases are not synchronized.

Restoring Backups
Using psql

  psql dbname < dumpfile
Enter fullscreen mode Exit fullscreen mode
  • Requires manual creation of the database from template0 before execution.
  • Users who own objects or were granted permissions must exist before restoring.
  • Option to run psql with --set ON_ERROR_STOP=on for better error handling.
  • Specify --single-transaction for a fully completed or fully rolled-back restore.

Handling Large Databases
Compressed Dumps

  pg_dump dbname | gzip > filename.gz
  gunzip -c filename.gz | psql dbname
Enter fullscreen mode Exit fullscreen mode

Split Output Files

  pg_dump dbname | split -b 2G - filename
  cat filename* | psql dbname
Enter fullscreen mode Exit fullscreen mode

Custom Dump Format

  pg_dump -Fc dbname > filename
  pg_restore -d dbname filename
Enter fullscreen mode Exit fullscreen mode

Parallel Dump

  pg_dump -j num -F d -f out.dir dbname
  pg_restore -j to restore dump in parallel.
Enter fullscreen mode Exit fullscreen mode
  • Parallel dumps for the "directory" archive format.

Top comments (1)

Collapse
 
grab_chess_309b9c06105b2d profile image
Grab Chess •

A reliable backup strategy is one of the most important parts of managing a PostgreSQL database. pg_dump works well when you need a backup of a specific database, while pg_dumpall is better suited for backing up an entire PostgreSQL instance, including roles and multiple databases. Choosing between them depends on your recovery goals, how often your data changes, and your deployment setup. I like reviewing infrastructure resources from Univik because they explain these concepts in a practical way that's useful for both beginners and experienced database administrators.