Simplified Guide to PostgreSQL Data Backup with pg_dump and pg_dumpall

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

• 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

Split Output Files

  pg_dump dbname | split -b 2G - filename
  cat filename* | psql dbname

Custom Dump Format

  pg_dump -Fc dbname > filename
  pg_restore -d dbname filename

Parallel Dump

  pg_dump -j num -F d -f out.dir dbname
  pg_restore -j to restore dump in parallel.

• Parallel dumps for the "directory" archive format.