7.1 Logical Backups with pg_dump & pg_dumpall

7.1 Logical Backups with pg_dump & pg_dumpall

[!NOTE] Core Design Philosophy: In PostgreSQL, backup strategies are divided into two fundamental paradigms: logical backups and physical backups. A logical backup extracts the human-readable schema definitions (DDL) and raw tuple data (DML) from a database as a stream of SQL commands or structured binary archives. Because logical backups are independent of physical hardware architectures, disk page layouts, and CPU byte ordering (endianness), they serve as the primary tool for version migrations, selective disaster recovery, and cross-platform database transfers.

The primary command-line client tools for logical backups in PostgreSQL are pg_dump (for backing up an individual database) and pg_dumpall (for backing up an entire cluster including cluster-wide global objects). Understanding the locking implications, format characteristics, and limitations of these utilities is essential for both daily administration and the PostgreSQL Associate certification.

---

Scope and Capabilities of pg_dump

pg_dump is an active client utility that connects to a target database through libpq (over a Unix domain socket or TCP/IP connection). It interrogates system catalogs, constructs a frozen transaction snapshot, and serializes the database objects.

Non-Blocking Read Locks and Online Operations

A common misconception is that backing up a production database requires downtime or maintenance windows. pg_dump is designed for 100% online operation:

• ACCESS SHARE Lock Level: When pg_dump processes a table, it acquires an ACCESS SHARE lock on that relation.
• Concurrent Operations Allowed: An ACCESS SHARE lock conflicts only with ACCESS EXCLUSIVE locks. Consequently, application clients can continue executing SELECT, INSERT, UPDATE, and DELETE statements on tables being dumped completely unimpeded!
• Conflicting Operations: The only operations blocked by pg_dump (or that block pg_dump) are disruptive administrative DDL commands that require ACCESS EXCLUSIVE, such as ALTER TABLE, DROP TABLE, TRUNCATE, and VACUUM FULL.

MVCC Snapshot Isolation

How does pg_dump ensure internal data consistency when rows are actively being inserted and modified while the backup runs? pg_dump opens a single transaction under the REPEATABLE READ transaction isolation level (or creates an exported snapshot via pg_export_snapshot()). It views a completely frozen, point-in-time image of the database as of the exact moment the dump transaction began. Later commits by concurrent applications do not alter the data emitted by pg_dump.

# Execute a basic logical backup of a single database
pg_dump -h localhost -U postgres -d production_db -f production_db_backup.sql

---

Output Formats: The Four Format Types

pg_dump provides four distinct output formats, specified using the -F (or --format) option:

-F p | --format=plain      (Plain-text SQL script file - default)
-F c | --format=custom     (Custom binary compressed archive)
-F d | --format=directory  (Multi-file directory archive)
-F t | --format=tar        (Tarball archive format)

1. Plain Text SQL (-F p or -Fp)

• Format Representation: A plain-text ASCII/UTF-8 script file containing raw SQL statements (CREATE TABLE, ALTER TABLE, COPY, CREATE INDEX).
• Default Behavior: If no -F parameter is specified, pg_dump defaults to plain-text SQL.
• Restoration Tool: Can only be restored using psql. The specialized utility pg_restore cannot read or process plain-text SQL files.
• Characteristics: Human-readable and editable with any text editor. However, it cannot be selectively filtered or reordered at restore time without manual file editing, lacks native compression, and cannot be restored in parallel.

2. Custom Archive (-F c or -Fc)

• Format Representation: A proprietary PostgreSQL binary archive format containing an embedded Table of Contents (TOC) header followed by zlib-compressed data streams.
• Restoration Tool: Must be restored using pg_restore.
• Characteristics: Highly flexible and compressed by default (dramatically reducing file footprint). The embedded TOC allows pg_restore to selectively restore individual tables, schemas, or data definitions, and reorder restoration sequences. In modern PostgreSQL, custom archives can be restored in parallel using pg_restore -j.

3. Directory Format (-F d or -Fd)

• Format Representation: Creates a filesystem directory containing a master Table of Contents file (toc.dat) and separate compressed data files for each table and large object.
• Restoration Tool: Must be restored using pg_restore.
• Parallel Execution (-j / --jobs): The directory format is the only format that supports parallel dumping! By specifying -j <number_of_jobs>, pg_dump opens multiple concurrent database connections and dumps tables in parallel, vastly reducing backup duration on multi-core systems.

4. Tar Format (-F t or -Ft)

• Format Representation: A standard Unix tar archive containing individual table data files and a TOC.
• Restoration Tool: Must be restored using pg_restore.
• Limitations: The tar format is uncompressed by default, cannot use compression, does not support parallel dumping (-j), and cannot be restored in parallel with pg_restore -j. Furthermore, individual member files cannot exceed 8GB due to POSIX tar header limits. It is largely considered a legacy format.

---

Essential Command-Line Options and Filters

pg_dump provides fine-grained switches to customize what database objects are extracted:

Common Extraction Flags

• -s / --schema-only: Dumps only object definitions (DDL: tables, views, types, functions, constraints, indexes) without any table rows. Useful for creating test environments or tracking schema revisions in Git.
• -a / --data-only: Dumps only row data (DML: COPY or INSERT commands) without table creation DDL or index structures.
• -t <table> / --table=<table>: Restricts the dump to tables matching a specified pattern (e.g., -t 'sales_*').
• -T <table> / --exclude-table=<table>: Excludes specific tables from the backup.
• -n <schema> / --schema=<schema>: Dumps only objects located in the specified schema (e.g., -n billing).
• -N <schema> / --exclude-schema=<schema>: Excludes all objects in the specified schema.
• -b / --blobs: Includes large objects (BLOBs) in the dump. (Note: large objects are included by default when no table-specific switches are used).
• -v / --verbose: Outputs detailed progress messages and object counts to standard error.
• -Z <0-9> / --compress=<0-9>: Sets the compression level (0 = none, 9 = maximum compression) for supported formats.

# Export only schema DDL in plain text
pg_dump -s -d sales_db -f sales_schema.sql

# Export only data for billing schema using custom format
pg_dump -a -F c -n billing -d sales_db -f billing_data.dump

# Multi-threaded parallel dump of entire database to a directory
pg_dump -F d -j 4 -d enterprise_db -f /var/backups/enterprise_dir

---

Limitations of pg_dump

While pg_dump is remarkably powerful, every database administrator must understand its scope boundaries:

1. Single Database Scope: pg_dump connects to exactly one database at a time. It cannot back up multiple databases in a single invocation.
2. No Cluster-Wide Global Objects: PostgreSQL maintains shared catalog objects that exist at the cluster level rather than within an individual database. pg_dump does NOT dump:
   • Database user accounts and roles (CREATE ROLE, passwords, role attributes).
   • Role group memberships and object grant privileges across databases.
   • Tablespaces (CREATE TABLESPACE definitions).
   • Cluster-wide configuration parameters.

If you restore a pg_dump archive onto a clean PostgreSQL server without first recreating the required roles and tablespaces, the restoration will immediately throw errors when setting object ownership or placing tables into nonexistent tablespaces.

---

Cluster-Wide Backups with pg_dumpall

To capture an entire PostgreSQL instance—including every individual database and all global cluster metadata—PostgreSQL provides pg_dumpall.

# Dump the entire PostgreSQL cluster
pg_dumpall -U postgres -f entire_cluster.sql

How pg_dumpall Operates Internally

pg_dumpall executes a multi-step sequence:

1. Connects to the default postgres or template1 database.
2. Interrogates cluster-wide system catalogs (pg_authid, pg_tablespace, pg_database).
3. Writes SQL DDL to recreate all roles, role memberships, and tablespace paths.
4. Iterates through every active user database in the cluster, internally invoking pg_dump logic for each.

Critical Characteristics of pg_dumpall

• Output Format is ALWAYS Plain-Text SQL: pg_dumpall cannot generate custom (-F c), directory (-F d), or tar (-F t) archives! Its output is strictly a plain-text SQL script.
• Restoration Tool: Because the output is plain-text SQL, pg_dumpall output must always be restored using psql.

Global-Only Extraction (-g / --globals-only)

In enterprise environments, backing up all databases into a single monolithic plain-text SQL file is inefficient and prevents parallel restoration. The standard operational best practice is to separate global definitions from database contents:

# Extract ONLY cluster-wide globals (roles and tablespaces)
pg_dumpall -U postgres --globals-only -f cluster_globals.sql

# Or using the short switch
pg_dumpall -U postgres -g -f cluster_globals.sql

Other specialized global flags include:

• -r / --roles-only: Dumps only roles and their attributes without tablespaces or database definitions.
• -t / --tablespaces-only: Dumps only tablespace definitions.

---

Production Logical Backup Strategy

A robust, scalable logical backup architecture follows a decoupled two-step model:

1. Capture Cluster Globals: Execute pg_dumpall -g to capture all roles, passwords, and tablespace definitions in a compact SQL script.
2. Capture Databases Individually in Parallel: Iterate through individual production databases and dump each using pg_dump -F d -j <workers> or pg_dump -F c. This maximizes CPU utilization, minimizes backup time, and enables selective object restores.

#!/usr/bin/env bash
set -euo pipefail
BACKUP_DIR="/var/backups/postgresql/$(date +%F)"
mkdir -p "$BACKUP_DIR"

# Step 1: Backup cluster globals
pg_dumpall -U postgres -g -f "$BACKUP_DIR/globals.sql"

# Step 2: Backup individual databases using parallel directory format
for db in sales_db inventory_db analytics_db; do
  echo "Dumping database: $db"
  pg_dump -U postgres -F d -j 4 -d "$db" -f "$BACKUP_DIR/${db}_dir"
done

---

Exam Tips and Common Pitfalls

• Exam Trap: Restoring Custom Archives: Remember that psql cannot restore custom (-F c) or directory (-F d) formats; attempting to run psql < backup.dump on a custom archive will fail with binary syntax errors. Custom archives require pg_restore.
• Exam Trap: Parallel Dump Format Support: If an exam question asks which format supports parallel dumping with -j, the only correct answer is the Directory format (-F d). Custom format (-F c) supports parallel restoring via pg_restore -j, but does not support parallel dumping.
• Exam Trap: Missing Roles on Restore: If a restored database complains that role "app_admin" does not exist, it is because pg_dump was used without first running pg_dumpall -g to export cluster roles.
• Exam Trap: Output Format of pg_dumpall: pg_dumpall only outputs plain-text SQL. There is no -F c option for pg_dumpall.