7.2 Restoring Logical Backups

7.2 Restoring Logical Backups

[!IMPORTANT] Tool Pairing Rule: In PostgreSQL logical backup management, the restoration utility is strictly dictated by the backup format. Plain-text SQL scripts (-F p) must be restored using psql, while custom archives (-F c), directory archives (-F d), and tar archives (-F t) must be restored using pg_restore. Using the incorrect utility will cause immediate syntax or execution failures.

Restoring a logical backup is not merely about executing a command; it requires understanding error propagation, transaction boundaries, parallel dependency scheduling, and object-level filtering. This section covers production restoration workflows for both psql and pg_restore.

---

Restoring Plain-Text SQL Backups with psql

Plain-text logical backups generated by pg_dump -F p or pg_dumpall are standard SQL script files containing DDL, DML, and catalog commands.

# Standard restoration using file flag
psql -h localhost -U postgres -d target_db -f backup.sql

# Alternative restoration using standard input redirection
psql -h localhost -U postgres -d target_db < backup.sql

Critical Error Handling: ON_ERROR_STOP

By default, psql executes SQL scripts in an ignore-error mode: if a statement fails (e.g., a duplicate table error or foreign key violation), psql prints an error message to stderr and blindly continues executing the next statement in the script. In a 50GB restore, a missed table creation failure could result in millions of subsequent COPY statements failing silently, leaving an incomplete and corrupted database.

To enforce strict failure handling, always set ON_ERROR_STOP=1:

# Halt immediately on the first error encountered
psql -v ON_ERROR_STOP=1 -h localhost -U postgres -d target_db -f backup.sql

Atomic Restoration: --single-transaction

Even with ON_ERROR_STOP=1, statements executed prior to the error remain committed in the database. To guarantee complete atomicity—where the entire restore succeeds or rolls back completely—use the --single-transaction (or -1) switch:

# Wrap the entire restoration in a single atomic transaction
psql --single-transaction -v ON_ERROR_STOP=1 -d target_db -f backup.sql

[!WARNING] Limitation of --single-transaction: If the SQL script contains commands that cannot run inside an explicit transaction block (such as VACUUM, CREATE DATABASE, or CREATE INDEX CONCURRENTLY), --single-transaction will fail.

---

Restoring Custom and Directory Archives with pg_restore

pg_restore is a specialized, intelligent restoration engine that parses the internal Table of Contents (TOC) of binary archives (-F c, -F d, -F t).

# Basic restore of a custom archive
pg_restore -h localhost -U postgres -d target_db backup.dump

# Restore from a directory format archive
pg_restore -h localhost -U postgres -d target_db /var/backups/db_dir

Core Operational Options of pg_restore

• -d <dbname> / --dbname=<dbname>: Connects directly to the specified database to execute the restore.
• -C / --create: Creates the target database before restoring into it. When using -C, the connection database specified in -d is typically a maintenance database (like postgres or template1), which connects to issue CREATE DATABASE target_db before switching connections to restore objects.
• -c / --clean: Drops database objects (tables, views, indexes) before recreating them. Crucial when restoring into a pre-existing database to avoid relation already exists errors.
• --if-exists: Used in conjunction with -c to append IF EXISTS to drop statements (e.g., DROP TABLE IF EXISTS), preventing noisy errors if the target objects do not yet exist.
• -O / --no-owner: Prevents pg_restore from setting original table ownership via ALTER ... OWNER TO. All restored objects will be owned by the user running the restore. Indispensable when migrating across environments with different user roles.
• -x / --no-privileges (or --no-acl): Skips restoration of access privileges (GRANT / REVOKE).
• -1 / --single-transaction: Executes the entire restore as a single atomic transaction.

# Clean rebuild of database, connecting via maintenance db
pg_restore -C -c --if-exists -O -d postgres backup.dump

---

Multi-Threaded Parallel Restoration (-j / --jobs)

Restoring a multi-terabyte database sequentially can take many hours, primarily due to single-threaded index creation and table loading. pg_restore solves this through multi-threaded parallel restoration using the -j <number_of_jobs> switch.

# Restore a database using 8 parallel worker connections
pg_restore -j 8 -d target_db backup.dump

The Three-Phase Parallel Restoration Architecture

pg_restore -j implements an intelligent dependency graph to coordinate workers across three distinct phases:

1. Pre-Data Phase (Sequential): Restores schema structures, custom types, sequences, and table definitions. These are executed sequentially by a single connection because subsequent objects depend on them.
2. Data & Index Phase (Parallel):
   • Table data loading is distributed across multiple parallel worker connections via concurrent COPY streams.
   • As soon as a table finishes loading, pg_restore immediately schedules worker threads to build its secondary indexes concurrently. Building 10 indexes across 8 CPU cores is exponentially faster than sequential index builds.
3. Post-Data Phase (Parallel & Dependency-Aware): Once all data is loaded and indexes are built, foreign key constraints, triggers, and validation rules are restored in parallel. Postponing foreign key checks to the post-data phase prevents row insertion ordering deadlocks and avoids continuous constraint verification overhead during ingestion.

[!TIP] Format Requirements for Parallel Restore: Parallel restoration (pg_restore -j) works with Custom archive (-F c) and Directory archive (-F d) formats. It cannot be used with Tar format (-F t) or plain-text SQL files.

---

Selective Restoration and Filtering

In disaster recovery scenarios, you frequently need to restore a single dropped table or a specific schema without restoring the entire multi-gigabyte archive.

Direct Object Filtering Switches

• -t <table> / --table=<table>: Restores only the specified table (including its data and indexes).
• -T <table> / --exclude-table=<table>: Restores all objects except the specified table.
• -n <schema> / --schema=<schema>: Restores only objects within the specified schema.
• -N <schema> / --exclude-schema=<schema>: Excludes the specified schema.
• --section=pre-data|data|post-data: Restores only DDL definitions, only data, or only constraints/indexes.

# Extract and restore ONLY the customer_orders table
pg_restore -d target_db -t customer_orders backup.dump

# Restore ONLY the billing schema
pg_restore -d target_db -n billing backup.dump

---

Surgical Object Management with the Table of Contents (TOC)

Every custom and directory archive includes a structured internal Table of Contents (TOC). pg_restore provides specialized commands to extract, edit, and apply this TOC.

1. Extract the TOC

Generate a human-readable list of every database item (tables, sequences, functions, indexes, constraints) stored inside the archive:

pg_restore -l backup.dump > archive.toc

Inside archive.toc, each line represents an object with an internal dump ID:

; Archive created at 2026-09-06 10:00:00 UTC
;     Database: production_db
214; 1259 16402 TABLE public users postgres
215; 1259 16410 TABLE public orders postgres
3312; 0 16402 TABLE DATA public users postgres
3313; 0 16410 TABLE DATA public orders postgres
3180; 2606 16418 INDEX public idx_orders_customer postgres
3181; 2606 16422 FK CONSTRAINT public fk_orders_user postgres

2. Edit the TOC

Open archive.toc in any text editor:

• Exclude Objects: Add a semicolon (;) at the beginning of a line to comment out objects you wish to skip (e.g., comment out large historical log tables).
• Reorder Execution: Cut and paste lines to change the restoration sequence if needed.

3. Restore Using the Modified TOC (-L)

Instruct pg_restore to execute only the items specified in the edited TOC file:

pg_restore -L archive.toc -d target_db backup.dump

---

Restoring Cluster-Wide Globals from pg_dumpall -g

When rebuilding a cluster or recovering from a major hardware failure, database restoration must follow a strict two-stage sequence:

Stage 1: Restore Globals (psql)  ──>  Stage 2: Restore Databases (pg_restore -j)
  - Creates Roles & Passwords           - Creates Tables & Schemas
  - Creates Tablespaces                 - Assigns Table Ownership to Roles

1. Restore Globals First: Execute the globals SQL script via psql. This creates all roles, permissions, and tablespace paths on the target cluster:

   psql -h localhost -U postgres -f /var/backups/cluster_globals.sql
   

2. Restore Databases Second: Restore individual database archives. Because the roles and tablespaces now exist in the target instance, table ownership assignments and tablespace allocations complete without error:

   pg_restore -C -j 4 -d postgres /var/backups/sales_db.dump
   

---

Exam Tips and Common Pitfalls

• Exam Trap: Restoring Plain Text with pg_restore: pg_restore cannot read plain-text SQL files. Attempting pg_restore backup.sql produces the error pg_restore: error: input file does not appear to be a valid archive. Use psql -f backup.sql.
• Exam Trap: Parallel Restore on Tar Files: If an exam question asks whether pg_restore -j 4 can be run against a .tar backup file, the answer is no. Tar archives do not support parallel restoration.
• Exam Trap: Error Masking in psql: In exam scenarios where a restore encounters failures but reports success, the culprit is the default psql behavior of ignoring errors. The solution is ON_ERROR_STOP=1.