Study guide
7.1 Logical Backups with pg_dump & pg_dumpall
PostgreSQL Associate Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Exam Overview & PostgreSQL Foundation
1.1 EDB PostgreSQL Associate Certification Overview 1.2 The PostgreSQL Relational Model & Architecture Principles 1.3 Study Strategies, Lab Setup & Exam-Day Execution
2 Chapter 2: PostgreSQL Architecture & Installation
2.1 The PostgreSQL Server Process Model 2.2 Database Clusters & Cluster Initialization (initdb) 2.3 Filesystem Layout, Service Control & Startup/Shutdown 2.4 Server Configuration Parameters
3 Chapter 3: Demonstrating SQL Knowledge
3.1 Relational Data Types & Table Design 3.2 Integrity Constraints & Data Validation 3.3 Querying, Joining & Aggregating Data 3.4 DML Operations, Transaction Control & MVCC Mechanics
4 Chapter 4: PostgreSQL Administrative Tools
4.1 Interactive Command-Line Mastery with psql 4.2 GUI Administration with pgAdmin 4.3 Client & Server Command-Line Utilities 4.4 System Catalogs & Information Schema
5 Chapter 5: Core Database Administration & Security
5.1 Managing Databases, Schemas & Tablespaces 5.2 Roles, Privileges & Object Ownership 5.3 Client Authentication & pg_hba.conf Architecture
6 Chapter 6: Routine Maintenance & Data Operations
6.1 The PostgreSQL Vacuuming Engine & MVCC Space Reclamation 6.2 Analyzing Query Statistics & Reindexing 6.3 High-Performance Data Loading & Data Movement
7 Chapter 7: Backup and Recovery Strategies
7.1 Logical Backups with pg_dump & pg_dumpall 7.2 Restoring Logical Backups 7.3 Physical Base Backups with pg_basebackup 7.4 Write-Ahead Logging & Point-in-Time Recovery
8 Chapter 8: High Availability & Replication
8.1 Physical Streaming Replication Architecture 8.2 Synchronous vs. Asynchronous Replication 8.3 Replication Slots & Standby Management 8.4 Failover, Switchover & High-Availability Design
9 Chapter 9: Monitoring, Diagnostics & Performance Tuning
9.1 Cumulative Statistics & Activity Monitoring 9.2 Query Execution Plans & Index Optimization 9.3 Server Logging Configuration & Autovacuum Tuning 9.4 Server Memory Sizing & Core Performance Parameters
7.1 Logical Backups with pg_dump & pg_dumpall
Key Takeaways Explain This Topic AI
pg_dump creates a consistent logical backup of a single PostgreSQL database while the database remains fully online and accessible, acquiring only ACCESS SHARE locks on dumped tables so concurrent reads and writes (SELECT, INSERT, UPDATE, DELETE) are never blocked.
pg_dump supports four distinct output formats: plain text SQL (-F p), custom archive (-F c), directory format (-F d), and tar archive (-F t), with custom and directory formats enabling compression, selective restores, and object reordering.
The directory format (-F d) is the only dump format that supports multi-threaded parallel dumping (-j jobs), dramatically reducing backup windows on multi-core servers with high-throughput storage arrays.
Crucial extraction flags enable surgical logical filtering, including -s (schema-only DDL), -a (data-only DML), -t (table inclusion), and -n (schema inclusion).
pg_dump operates exclusively at the single-database level and never exports cluster-wide global objects; administrators must use pg_dumpall (or pg_dumpall -g) to capture cluster user roles, passwords, and tablespaces.
Last updated: September 2026
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.
Format Flag File Type Tool to Restore Supports Parallel Dump ( -j )? Supports Parallel Restore ( -j )? Default Compression?
- Plain Text -F p .sql script psql No No None
- Custom -F c .dump binary pg_restore No Yes Yes (zlib)
- Directory -F d Directory pg_restore Yes Yes Yes (zlib)
- Tar -F t .tar file pg_restore No No None
- 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:
Single Database Scope : pg_dump connects to exactly one database at a time. It cannot back up multiple databases in a single invocation.
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:
Connects to the default postgres or template1 database.
Interrogates cluster-wide system catalogs ( pg_authid , pg_tablespace , pg_database ).
Writes SQL DDL to recreate all roles, role memberships, and tablespace paths.
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:
Capture Cluster Globals : Execute pg_dumpall -g to capture all roles, passwords, and tablespace definitions in a compact SQL script.
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 .