A PostgreSQL backup is only useful when you can restore it under pressure. Running pg_dump and seeing a file appear is not enough: you also need a repeatable format, protected credentials, retention, integrity checks, and a restore drill that proves the data can become a working database again.

This guide builds a practical logical-backup workflow with PostgreSQL's official pg_dump and pg_restore tools. It works well for application databases that need portable backups, selective restores, migrations, or an additional recovery layer alongside infrastructure snapshots.

Understand What pg_dump Protects

pg_dump creates a logical snapshot of one PostgreSQL database. It reads database objects and rows, then writes the SQL or archive instructions required to reconstruct them. The dump is internally consistent even while normal application traffic continues, so you do not need to stop reads and writes for a routine backup.

That does not make it a complete disaster-recovery system. A logical dump normally represents one point in time and one database. Cluster-wide roles and tablespaces are separate, and a dump cannot replay every transaction that happened after it started. If your recovery-point objective requires restoring to a specific second, combine logical backups with physical base backups and WAL archiving.

For many web applications, a sensible layered plan is:

  • daily custom-format logical dumps for portability and object-level recovery;
  • separate backups of cluster globals such as roles;
  • encrypted off-server storage with a defined retention policy;
  • periodic restore drills in an isolated environment;
  • physical backups and WAL archiving when the recovery objectives demand point-in-time recovery.

Choose the Custom Archive Format

Plain SQL dumps are easy to inspect and can be replayed with psql, but PostgreSQL's custom archive format is usually the better production default. Create it with --format=custom or -Fc. A custom archive supports selective restore, reordering, built-in compression, and parallel restore through pg_restore.

pg_dump \
  --host=db.internal \
  --port=5432 \
  --username=backup_user \
  --format=custom \
  --file=appdb_2026-08-24.dump \
  appdb

Use a dedicated backup role with only the permissions it needs. Avoid placing a password directly in the command, shell history, process list, or script. For unattended jobs, PostgreSQL supports a password file such as ~/.pgpass or a file selected through PGPASSFILE. Restrict the file to its owner:

chmod 600 /etc/postgresql/backup.pgpass
export PGPASSFILE=/etc/postgresql/backup.pgpass

A custom dump covers the selected database, but roles and tablespaces belong to the PostgreSQL cluster. Save those separately:

pg_dumpall \
  --host=db.internal \
  --username=backup_user \
  --globals-only \
  --file=cluster_globals_2026-08-24.sql

Build a Fail-Fast Backup Script

A production script should fail visibly, write to a temporary name, validate the archive, and only then promote it to the final filename. The following Bash example uses an explicit destination, UTC timestamps, and conservative permissions:

#!/usr/bin/env bash
set -Eeuo pipefail

backup_dir="/var/backups/postgresql"
database_name="appdb"
database_host="db.internal"
timestamp="$(date -u +%Y%m%dT%H%M%SZ)"
temporary_file="${backup_dir}/.${database_name}_${timestamp}.dump.tmp"
final_file="${backup_dir}/${database_name}_${timestamp}.dump"

umask 077
mkdir -p "$backup_dir"

pg_dump \
  --host="$database_host" \
  --username=backup_user \
  --format=custom \
  --file="$temporary_file" \
  "$database_name"

pg_restore --list "$temporary_file" >/dev/null
mv "$temporary_file" "$final_file"

find "$backup_dir" \
  -type f \
  -name "${database_name}_*.dump" \
  -mtime +14 \
  -delete

printf 'Backup completed: %s\n' "$final_file"

set -Eeuo pipefail stops the script after unexpected failures. The hidden temporary file prevents an incomplete dump from looking valid, while pg_restore --list confirms that PostgreSQL can read the archive catalog. This is a useful structural check, but it is not a substitute for a real restore.

Do not keep the only copy on the same server as the database. Copy completed backups to durable object storage or another protected system, enable encryption, and restrict delete permissions. Record successful uploads in monitoring rather than trusting a silent scheduled command.

Restore into a Clean Test Database

The safest drill restores into a new database, never over production. First create an empty target from template0 so local additions to template1 do not leak into the test:

createdb \
  --host=restore-db.internal \
  --username=restore_operator \
  --template=template0 \
  appdb_restore_test

Then restore the custom archive. --no-owner makes the connecting user own restored objects, which is useful when production roles do not exist in the test environment. --no-privileges skips grant and revoke statements. Keep those flags only if that behavior matches your recovery plan.

pg_restore \
  --host=restore-db.internal \
  --username=restore_operator \
  --dbname=appdb_restore_test \
  --no-owner \
  --no-privileges \
  --exit-on-error \
  --jobs=4 \
  appdb_2026-08-24.dump

Parallel jobs can significantly reduce restore time for a custom archive, especially when it contains many independent tables and indexes. Do not combine --jobs with --single-transaction; choose the behavior that fits your restore requirements and test it before an incident.

If you intentionally restore into a database that already contains objects, --clean --if-exists can drop objects before recreating them. That option is destructive. Resolve the target hostname and database name explicitly, block production destinations in your script, and require a deliberate operator action before using it.

Verify the Restored Database

A successful pg_restore exit code proves that the commands completed, not that the application is healthy. Verification should cover structure, data, permissions, and behavior.

  1. Check the restore log. Treat unexpected warnings and every error as a failed drill.
  2. Compare critical counts. Check important tables, tenant counts, recent transactions, and other domain-specific invariants.
  3. Inspect extensions and functions. Confirm required extensions are installed and application functions execute.
  4. Run application smoke tests. Point a disposable application instance at the restored database and test authentication plus core read/write paths.
  5. Measure recovery time. Record how long download, database creation, restore, and verification take. Compare the total with your recovery-time objective.
psql \
  --host=restore-db.internal \
  --username=restore_operator \
  --dbname=appdb_restore_test \
  --command='SELECT count(*) FROM users;'

psql \
  --host=restore-db.internal \
  --username=restore_operator \
  --dbname=appdb_restore_test \
  --command='SELECT max(created_at) FROM orders;'

After a large restore, run ANALYZE if your restore workflow did not already refresh the statistics needed by the query planner. Then check representative queries with the workflow from our PostgreSQL indexing and EXPLAIN ANALYZE guide.

Avoid Version and Compatibility Surprises

Use a pg_dump client that can communicate with the source server. PostgreSQL documents that pg_dump can dump older server versions, but it refuses to dump from a server newer than its own major version. Standardize the client version in your backup image or host configuration and monitor it during database upgrades.

Logical dumps are designed to move data forward, but major-version upgrades can expose extension, collation, SQL, or application compatibility issues. Always rehearse a restore with the exact target PostgreSQL version and extensions planned for production.

Production Checklist

  • Document recovery-point and recovery-time objectives.
  • Use a dedicated least-privilege backup account.
  • Keep passwords out of commands and logs.
  • Create custom-format dumps and save cluster globals.
  • Validate the archive before promoting or uploading it.
  • Encrypt backups and keep at least one copy off the database host.
  • Alert when a scheduled backup or off-site upload fails.
  • Test restores on a fixed schedule and record their duration.
  • Verify application behavior, not only table existence.
  • Add physical backups and WAL archiving when point-in-time recovery is required.

Final Takeaway

The dependable unit of backup is not a dump file; it is a verified recovery process. A custom-format pg_dump, protected off-site storage, and recurring pg_restore drills give small teams a strong, understandable foundation. Once your recovery objectives become stricter, extend that foundation with physical backups and continuous WAL archiving rather than assuming logical dumps can solve every failure mode.

Official Sources