PostgreSQL Restore Scripts

Technical Architects Zone (TAZ)

PostgreSQL Restore & Recovery Scripts

Plain SQL Restore, pg_restore, Parallel Recovery, Selective Restore, Globals & Validation

Introduction

A PostgreSQL backup becomes operationally valuable only when it can be restored successfully within the required Recovery Point Objective and Recovery Time Objective. The appropriate restore utility and command depend on the format used to create the backup.

Plain SQL backups are normally restored with psql. Custom, directory, and tar archive backups are restored with pg_restore. Physical recovery uses a different process involving a compatible physical backup, PostgreSQL configuration, WAL records, recovery settings, and, where required, a recovery target.

Important: Test the complete recovery process in a separate non-production environment. Successfully listing or reading a backup file does not prove that the database, roles, privileges, extensions, dependencies, and application can be recovered correctly.

1. Create an Empty Target Database

Before restoring a plain SQL backup or an archive without the create-database option, create the destination database with the required owner, encoding, and locale settings. 

CREATE DATABASE mydatabase_restore
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    TEMPLATE = template0;
Important: Match the required encoding, locale provider, collation, character classification, owner, and tablespace design. These settings may affect application behavior and cannot always be changed easily after database creation.

2. Restore a Plain SQL Backup

A plain-format backup contains SQL statements and is normally restored using the psql command-line utility. 

psql -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
-v ON_ERROR_STOP=1 \
-f /backup/mydatabase_backup.sql
  • -d identifies the target database.
  • -f identifies the plain SQL backup file.
  • ON_ERROR_STOP=1 stops processing when a SQL error occurs.
  • Review the command output and operating-system exit status after completion.
Caution: Without ON_ERROR_STOP, psql may continue processing after some errors. Continuing after an error can leave the destination only partially restored.

3. Restore a Custom-Format Backup

Custom-format backups created with pg_dump -F c are restored using pg_restore. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
  • --verbose displays detailed restore activity.
  • --exit-on-error stops the restore when an error occurs.
  • The destination database must already exist unless --create is used.

4. Create the Database During Restore

The --create option instructs pg_restore to recreate the database recorded in the archive. Connect initially to an existing maintenance database. 

pg_restore -h localhost -p 5432 -U postgres \
--create \
--dbname=postgres \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
The value supplied to --dbname is initially used for the connection. When --create is specified, the database name stored in the archive is used for the database created by the restore.

5. Parallel Restore

Parallel restore can reduce recovery time for suitable custom-format or directory-format archives when sufficient CPU and storage throughput are available. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
-j 4 \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
  • -j 4 uses four concurrent restore jobs.
  • Increase workers only after testing CPU, memory, storage, and connection capacity.
  • Parallel restore can generate significant I/O and WAL activity.
  • More workers do not always produce a faster restore.

6. Restore a Specific Schema

Use the schema filter when only one schema from an archive should be restored. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
-n application_schema \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
Caution: A selective schema restore does not automatically guarantee that all referenced objects and dependencies outside the selected schema are restored.

7. Restore a Specific Table

A table filter can be used to restore a selected table from a supported archive. Schema-qualify the table name to avoid ambiguity. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
-t public.employee \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
Review related sequences, constraints, indexes, triggers, foreign keys, partition objects, functions, permissions, and referenced tables when performing a selective restore.

8. Restore Schema Definitions Only

Use --schema-only to restore object definitions without restoring the associated table data. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
--schema-only \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump

9. Restore Table Data Only

A data-only restore loads table data and sequence values without recreating the complete database schema. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
--data-only \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
Caution: The required tables and supporting schema must already exist and remain compatible with the data contained in the archive.

10. Restore PostgreSQL Global Objects

Roles and tablespaces are cluster-wide objects. A globals backup produced by pg_dumpall --globals-only is restored as a plain SQL script. 

psql -h localhost -p 5432 -U postgres \
-d postgres \
-v ON_ERROR_STOP=1 \
-f /backup/postgresql_globals.sql
Important: Inspect the globals script before running it against an existing cluster. Role names, password metadata, memberships, attributes, and tablespace locations may conflict with objects already present in the destination environment.

11. Clean Existing Objects Before Restore

The clean option issues commands to remove objects before recreating them. Use --if-exists to suppress avoidable errors for objects that do not exist. 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
--clean \
--if-exists \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
Destructive operation: The --clean option can drop existing objects from the target database. Confirm the destination server, port, and database before executing the command. Test the exact command in a non-production environment first.

12. Inspect and Select Backup Contents

Generate a table-of-contents file before a selective restore. The list can be reviewed and edited to control which archive entries are restored.

Generate the Archive List 

pg_restore --list \
/backup/mydatabase_backup.dump \
> /backup/mydatabase_restore.list

Restore Using the Edited List 

pg_restore -h localhost -p 5432 -U postgres \
-d mydatabase_restore \
--use-list=/backup/mydatabase_restore.list \
--verbose \
--exit-on-error \
/backup/mydatabase_backup.dump
Keep an unmodified copy of the generated list. Carefully review dependencies and restore ordering before excluding archive entries.

13. Validate the Restored Database

Restore validation should include database connectivity, object counts, invalid or missing dependencies, row-count reconciliation, ownership, permissions, extension availability, application testing, and operational monitoring.

Check Database Size 

SELECT
    current_database() AS database_name,
    pg_size_pretty(
        pg_database_size(current_database())
    ) AS database_size;

Review Restored Tables 

SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY
    schemaname,
    relname;

Check Installed Extensions 

SELECT
    extname,
    extversion
FROM pg_extension
ORDER BY extname;

Refresh Optimizer Statistics 

ANALYZE VERBOSE;
Estimated row counts from PostgreSQL statistics are not substitutes for exact source-to-target reconciliation. Use exact counts, checksums, business totals, and application-level validation for critical datasets.

14. Point-in-Time Recovery Configuration

Point-in-Time Recovery uses a compatible physical base backup together with the required archived WAL records. The exact procedure depends on the PostgreSQL version, operating system, storage design, archive location, and recovery objective.

The following example shows representative recovery settings. Adapt the restore command, archive location, timestamp, timeline, paths, permissions, and service-management procedure to the actual environment. 

restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-11-04 10:30:00+05:30'
recovery_target_action = 'pause'

For recovery mode, create the required recovery signal file in the restored data directory: 

touch /var/lib/postgresql/data/recovery.signal
Very important: Do not apply a generic PITR procedure directly to production. Preserve the failed environment, confirm the required recovery target, verify the available base backup and WAL continuity, test the procedure, and obtain the required operational approval. An incorrect recovery target or archive configuration can recover the wrong database state.

Recommended Restore Workflow

Step Action Purpose
1 Confirm recovery objective Identify the required database state, RPO, and RTO
2 Identify backup format Select psql, pg_restore, or physical recovery
3 Prepare destination Confirm version, encoding, locale, storage, extensions, and permissions
4 Restore global objects Make required roles and tablespaces available
5 Restore database Recover schema, data, indexes, constraints, and dependent objects
6 Refresh statistics Support appropriate optimizer decisions
7 Validate recovery Verify data, security, integrations, jobs, and application behavior
8 Document results Record duration, errors, evidence, and corrective actions

Production Restore Checklist

  • Confirm the required recovery point and authorized destination.
  • Protect the original failed environment and relevant logs.
  • Confirm the backup type, format, creation time, and source PostgreSQL version.
  • Verify that the destination PostgreSQL version is compatible.
  • Confirm sufficient storage and transaction-log capacity.
  • Confirm required roles, tablespaces, extensions, and libraries.
  • Review ownership and privilege requirements.
  • Use error-stop options and capture the complete restore output.
  • Validate schemas, tables, sequences, indexes, constraints, and functions.
  • Reconcile critical row counts and business totals.
  • Refresh optimizer statistics after restore where required.
  • Validate application connectivity and application-level workflows.
  • Measure actual recovery duration against the required RTO.
  • Document errors, corrective actions, and recovery evidence.
  • Obtain business or application-owner validation before production handover.

Key Takeaways

  • Plain SQL backups are normally restored using psql.
  • Custom, directory, and tar archives are normally restored using pg_restore.
  • Use error-stop options so restore failures are not silently overlooked.
  • Parallel restore can improve recovery time when system resources support concurrent work.
  • Schema-only, data-only, schema-specific, and table-specific restores require careful dependency analysis.
  • PostgreSQL roles and tablespaces are global objects and may require a separate restore.
  • The --clean option is destructive and must be used carefully.
  • An archive list supports controlled and selective restore operations.
  • Physical PITR requires a compatible base backup and continuous availability of the required WAL records.
  • A completed restore command is not enough. Database and application validation must confirm recovery.

Conclusion

PostgreSQL recovery is more than executing a single psql or pg_restore command. A dependable restore process must account for the backup format, PostgreSQL version, database creation settings, global objects, extensions, ownership, dependencies, available infrastructure, and recovery objective.

Selective restores can be useful for controlled object-level recovery, but the DBA must understand dependencies and the limitations of partial recovery. Physical Point-in-Time Recovery requires additional preparation, including compatible base backups, continuous WAL protection, tested recovery settings, and precise recovery targets.

The final measure of success is not simply that the restore utility completed. The restored database must be technically consistent, operationally usable, validated by the application team, and recovered within the required RPO and RTO.

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Comments

Post a Comment

Popular posts from this blog

SQL Server Interview Questions & Answers: 200 Questions from Fundamentals to Architect Level

Image
Technical Architects Zone (TAZ) SQL Server Interview Preparation SQL Server Interview Questions & Answers 200 Questions from Fundamentals to Advanced DBA and Architect-Level Scenarios Introduction Preparing for a SQL Server interview is not only about remembering definitions, commands and feature names. As database professionals move into Senior DBA, Technical Lead and Database Architect roles, interviews increasingly focus on how production problems are investigated, how technical decisions are made and how critical database situations are handled. To make interview preparation more practical, Technical Architects Zone provides a structured collection of 200 SQL Server Interview Questions & Answers . The questions progress from core SQL Server fundamentals to advanced production scenarios covering troubleshooting...
Read more

SQL Server Storage Architecture Explained: How SQL Server Stores Data Internally

Image
  Introduction Have you ever wondered what happens when you insert a row into a SQL Server table? Where does the data actually go? How does SQL Server find free space? How are indexes stored? Why do DBAs talk about Pages, Extents, GAM, SGAM, PFS, and IAM? Understanding SQL Server Storage Architecture is one of the most important skills for any DBA, Database Architect, or Performance Engineer because almost every performance issue eventually leads back to how data is stored internally. In this article, we will simplify SQL Server's internal storage mechanism using practical examples. Why Storage Architecture Matters Understanding storage internals helps you: ✅ Troubleshoot performance issues ✅ Understand index fragmentation ✅ Optimize large tables ✅ Reduce disk I/O ✅ Improve backup and restore strategies ✅ Analyze space usage ✅ Perform advanced DBA troubleshooting The Foundation: Data Pages SQL Server stores all data in Pages . A Page is the smallest unit of storage. Page Size -...
Read more

SQL Server Performance Troubleshooting: Essential Scripts Every Database personnel or Architect Should Have

Image
TECHNICAL ARCHITECTS ZONE SQL Server Performance Troubleshooting A practical collection of SQL Server scripts and diagnostic techniques for investigating CPU, I/O, blocking, waits, memory, TempDB and query-performance issues. Performance issues are among the most common challenges handled by SQL Server DBAs. A typical incident starts with a broad statement such as “the application is slow,” but that statement does not identify whether the underlying problem is blocking, CPU pressure, storage latency, memory pressure, TempDB contention or an inefficient query. Effective troubleshooting begins by collecting evidence from SQL Server before making configuration or application changes. This article provides practical Dynamic Management View queries that can help identify where to begin. TAZ Practical Note Capture evidence ...
Read more