PostgreSQL Backup Scripts

Technical Architects Zone (TAZ)

PostgreSQL Backup Scripts

Logical Backups, Schema & Table Backups, Cluster Backup, Physical Backup & DBA Utility Commands

Introduction

A reliable PostgreSQL backup strategy is one of the most important responsibilities of a database administrator. Backups help protect databases against accidental data loss, corruption, failed deployments, infrastructure failures, and disaster-recovery scenarios.

PostgreSQL provides several backup approaches. Logical backups can be created with pg_dump and pg_dumpall, while physical backups can be created with utilities such as pg_basebackup. WAL archiving can additionally support Point-in-Time Recovery when properly configured.

Important: A backup command completing successfully does not by itself prove recoverability. Regularly restore backups into a separate PostgreSQL environment and validate the database, application objects, privileges, and recovery process.

1. Custom-Format Database Backup

The PostgreSQL custom archive format provides a flexible logical backup that can later be selectively restored using pg_restore. 

pg_dump -h localhost -p 5432 -U postgres \
-F c \
-f /backup/mydatabase_backup.dump \
mydatabase
  • -h specifies the PostgreSQL server.
  • -p specifies the PostgreSQL port.
  • -U specifies the PostgreSQL user.
  • -F c selects custom archive format.
  • -f specifies the backup output file.

2. Plain SQL Database Backup

A plain-format backup creates a readable SQL script containing commands required to reconstruct the database objects and data included in the dump. 

pg_dump -h localhost -p 5432 -U postgres \
-F p \
-f /backup/mydatabase_backup.sql \
mydatabase
Plain SQL dumps are normally restored using psql, while archive-format backups are normally restored with pg_restore.

3. Parallel Directory-Format Backup

Directory format is useful for larger databases because multiple worker processes can participate in the dump. 

pg_dump -h localhost -p 5432 -U postgres \
-F d \
-j 4 \
-f /backup/mydatabase_directory \
mydatabase
  • -F d selects directory format.
  • -j 4 uses four parallel dump jobs.
  • Choose the number of workers according to available CPU, storage throughput, and workload.

4. Schema-Level Backup

Use a schema-specific dump when only selected application objects need to be exported rather than the entire database. 

pg_dump -h localhost -U postgres \
-d mydatabase \
-n application_schema \
-F c \
-f /backup/application_schema.dump
Important: A schema-specific dump may not include external dependencies required by objects inside the selected schema. Understand cross-schema dependencies before relying on a partial backup.

5. Table-Level Backup

A specific table can be exported with pg_dump when an object-level logical copy is required. 

pg_dump -h localhost -U postgres \
-d mydatabase \
-t public.employee \
-F c \
-f /backup/employee_table.dump
Caution: A table-only dump is not a substitute for a complete database backup. Dependencies, roles, extensions, related tables, and other required objects must be considered separately.

6. Schema-Only Backup

Use --schema-only when the database object definitions are required without table data. 

pg_dump -h localhost -U postgres \
--schema-only \
-f /backup/mydatabase_schema.sql \
mydatabase

7. Data-Only Backup

A data-only dump exports table data without recreating the complete database schema. 

pg_dump -h localhost -U postgres \
--data-only \
-F c \
-f /backup/mydatabase_data.dump \
mydatabase

8. Backup All Databases

pg_dumpall can generate a plain SQL dump covering all databases in a PostgreSQL cluster together with cluster-wide objects. 

pg_dumpall -h localhost -p 5432 -U postgres \
-f /backup/postgresql_cluster.sql
Remember: pg_dumpall produces a plain-text SQL script. For larger environments, DBAs may prefer separate database dumps alongside a dedicated globals backup.

9. Backup PostgreSQL Global Objects

Roles and tablespaces are cluster-wide rather than database-specific objects. Back them up separately when individual databases are protected with pg_dump. 

pg_dumpall -h localhost -p 5432 -U postgres \
--globals-only \
-f /backup/postgresql_globals.sql

10. Physical Backup with pg_basebackup

pg_basebackup creates a physical base backup of a running PostgreSQL cluster. It is commonly used as part of physical replication and recovery designs. 

pg_basebackup \
-h localhost \
-p 5432 \
-U replication_user \
-D /backup/basebackup \
-F p \
-X stream \
-P
  • -D specifies the destination directory.
  • -F p requests plain output format.
  • -X stream streams required WAL while the backup runs.
  • -P displays progress.
Caution: The replication account, pg_hba.conf, network access, permissions, available disk space, WAL retention, and recovery design must be configured correctly before relying on pg_basebackup in production.

11. Inspect and Validate a Logical Backup

For a PostgreSQL archive created by pg_dump, use pg_restore --list to inspect the archive contents. 

pg_restore --list /backup/mydatabase_backup.dump
Very important: Successfully listing the archive contents does not prove that the database can be fully recovered. The strongest operational validation is an actual restore into a separate environment followed by database and application-level checks.

12. Example Production Backup Strategy

There is no single PostgreSQL backup schedule suitable for every production system. The strategy should be driven by business RPO, RTO, database size, recovery architecture, and workload characteristics.

Frequency / Event Action Purpose
Daily Logical Database Backup Provide portable logical recovery copies where appropriate
After role changes Globals Backup Protect roles and other global objects
According to recovery design Physical Base Backup Establish a physical recovery baseline
Continuous WAL Archiving Support PITR where configured
Regularly Test Restore Prove recoverability and recovery procedures
Production recommendation: The actual schedule must be designed from the application's RPO and RTO. Monitor backup failures, backup duration, WAL archiving, storage capacity, retention, off-site copies, and restore-test results.

Production Backup Checklist

  • Define business RPO and RTO requirements.
  • Choose logical, physical, and WAL-based protection according to recovery requirements.
  • Back up PostgreSQL global objects where required.
  • Monitor backup execution failures.
  • Monitor WAL archiving where PITR is used.
  • Monitor backup duration and storage consumption.
  • Define backup retention requirements.
  • Protect backup files from unauthorized access and deletion.
  • Maintain copies outside the primary database infrastructure.
  • Document encryption and key-management requirements.
  • Perform complete restore tests regularly.
  • Measure actual recovery time against the required RTO.
  • Document and rehearse the PostgreSQL disaster-recovery runbook.

Key Takeaways

  • pg_dump provides logical backups of individual PostgreSQL databases.
  • Custom format provides flexible restore capabilities through pg_restore.
  • Directory format supports parallel logical dumps.
  • pg_dumpall can protect all databases and PostgreSQL global objects.
  • Roles and tablespaces require attention when databases are backed up individually.
  • pg_basebackup provides physical base backups of a PostgreSQL cluster.
  • WAL archiving is an important component of PostgreSQL PITR designs.
  • Schema-only, data-only, schema-specific, and table-specific dumps are useful but are not substitutes for a complete recovery strategy.
  • The correct backup design should be based on business RPO and RTO requirements.
  • Actual recovery testing provides stronger assurance than checking whether a backup command simply completed successfully.

Conclusion

PostgreSQL backup planning is more than running pg_dump or pg_basebackup. A production strategy must connect business recovery requirements with the correct logical or physical backup methods, WAL protection, retention, security, monitoring, and restore procedures.

From a DBA perspective, the most important question is not "Did the PostgreSQL backup command succeed?" The more important question is: "Can the database be recovered within the required RPO and RTO?"

A reliable PostgreSQL backup strategy should therefore be validated through actual restore exercises, documented recovery procedures, monitoring, and regular disaster-recovery testing.

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