PostgreSQL Backup and Restore with pg_dump

By Khimananda Oli | Last reviewed: August 2026

Database failures rarely announce themselves before corrupting data or halting services. Mastering PostgreSQL backup and restore with pg_dump is the single most critical safety skill for any engineer managing Postgres in production, whether on AWS RDS, Azure Database for PostgreSQL, or self-hosted VPS infrastructure. This guide covers the exact flags, formats, and validation workflows I use daily to ensure recoverability, moving beyond basic tutorials to address real-world constraints like large datasets, compliance requirements, and zero-downtime restoration.

Before running any backup command against a live system, understand that your strategy must align with your broader backup and disaster recovery strategy on the cloud. A backup you cannot restore is merely a liability during an audit or outage. The following sections break down the operational reality of pg_dump, focusing on decisions that impact Recovery Time Objectives (RTO) and Recovery Point Objectives (RPO).

How do you perform PostgreSQL backup and restore with pg_dump correctly?

The default plain-text SQL output (-Fp) is fine for small development databases but fails in production environments. For any system where downtime matters or datasets exceed a few gigabytes, the custom format (-Fc) is mandatory. It stores data in a compressed, table-of-contents-aware binary structure that enables parallel restoration and selective object recovery.

Essential backup command for production

pg_dump -U postgres \
  -h db-primary.internal \
  -Fc \
  -j 4 \
  -Z 6 \
  -v \
  --file=/backups/prod_$(date +%Y%m%d_%H%M%S).dump \
  myapp_production

• -Fc: Custom format. Enables pg_restore features like parallel jobs and selective restores.
• -j 4: Parallel dump. Dumps up to 4 tables simultaneously. Only works with directory format (-Fd) in older versions, but modern pg_dump supports it with custom format for large objects. Verify your version; for true parallel custom dumps, some teams prefer -Fd to a directory.
• -Z 6: Compression level (0–9). Level 6 balances speed and size. For archival to S3 or R2, level 9 saves bandwidth at the cost of CPU.
• -v: Verbose output. Critical for logging in automated scripts to confirm completion.

Restore command with safety checks

pg_restore -U postgres \
  -h db-staging.internal \
  -d myapp_staging \
  -j 4 \
  --clean \
  --if-exists \
  --no-owner \
  -v \
  /backups/prod_20260811_020000.dump

The --clean --if-exists flags drop existing objects before recreating them, preventing "already exists" errors during staging refreshes. Use --no-owner when restoring to environments where the original roles don't exist—a common scenario when migrating between cloud providers or from on-prem to managed Postgres.

When should you use custom format versus plain SQL in pg_dump?

This decision affects everything from backup window duration to restore flexibility. In practice, I default to custom format unless there's a specific reason not to.

A common mistake is using plain SQL for compliance archives because auditors ask for "readable backups." Instead, provide the pg_restore --list output as documentation and store the actual backup in custom format. This satisfies audit requirements while preserving operational flexibility. If you're building automated SOC 2 compliance evidence collection, script the list generation alongside the dump.

How do you automate pg_dump backups without breaking production?

Automation isn't just about scheduling; it's about making failures visible and ensuring backups are actually usable. I've seen too many teams discover their nightly cron job has been silently failing for months only when they need to restore.

1. Wrap pg_dump in a script with exit-code handling. Never call pg_dump directly from cron. Use a wrapper that logs output, checks exit codes, and alerts on failure.
2. Verify every backup. Run pg_restore --list immediately after dump completion. If it fails, the backup is corrupt. Alert immediately.
3. Test restores weekly. Automate restoration to a disposable staging instance. Measure restore time. This validates both the backup and your RTO assumptions.
4. Rotate and offload. Keep 7 days locally, 30 days in object storage (S3/R2), and 1 year in cold archive. Use lifecycle policies, not manual cleanup.
5. Encrypt at rest. For compliance (SOC 2, ISO 27001), encrypt backups before leaving the database host. Use gpg or cloud KMS integration.

#!/bin/bash
# /opt/scripts/pg_backup.sh
set -euo pipefail

BACKUP_DIR="/backups/postgres"
DB_NAME="myapp_production"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"

pg_dump -U postgres -Fc -Z 6 -v --file="${BACKUP_FILE}" "${DB_NAME}" 2>&1 | logger -t pg_backup

if ! pg_restore --list "${BACKUP_FILE}" >/dev/null 2>&1; then
  echo "ALERT: Backup verification failed for ${BACKUP_FILE}" | mail -s "PG Backup FAILED" [email protected]
  exit 1
fi

aws s3 cp "${BACKUP_FILE}" "s3://company-db-backups/${DB_NAME}/" --sse aws:kms
rm "${BACKUP_FILE}"
echo "Backup completed and verified: ${BACKUP_FILE}"

This pattern ensures every backup is validated before local cleanup. For teams managing infrastructure as code, consider integrating backup verification into your CI pipeline as described in build verification and quality gates in CI—treat backup integrity like code quality.

What are common pg_dump mistakes that cause restore failures?

In 15 years of managing Postgres, I've catalogued recurring failure modes. Avoid these:

• Ignoring encoding mismatches. Always specify --encoding=UTF8 explicitly. Restoring a UTF8 dump into a LATIN1 database (or vice versa) causes silent data corruption or outright failure.
• Forgetting global objects. pg_dump backs up a single database. Roles, tablespaces, and cluster-wide settings require pg_dumpall --globals-only. Run this separately and restore globals before individual databases.
• Using --single-transaction incorrectly. This wraps the entire restore in one transaction. For large databases, this can exhaust shared memory or WAL space. Use --exit-on-error instead for safer partial restores.
• Neglecting extension dependencies. If your app uses PostGIS, pgvector, or other extensions, ensure the target server has matching versions installed before restore. pg_dump includes CREATE EXTENSION statements, but the binaries must exist.
• Assuming version compatibility. While pg_dump from newer versions can read older databases, the reverse isn't true. Always use a pg_dump version equal to or newer than the source. For cross-major-version migrations, test thoroughly.

How does pg_dump compare to physical backups for disaster recovery?

pg_dump provides logical backups. They're portable and flexible but slower to restore than physical backups (like pg_basebackup or continuous archiving with WAL-G). Your DR strategy should include both.

Use pg_dump for application-level recovery (accidentally dropped table, schema migration rollback, dev environment seeding). Use physical backups for infrastructure-level disasters (server failure, region outage, corruption). Most production systems need both: daily pg_dump for granular recovery and continuous WAL archiving for PITR. If you're evaluating managed services, understand that AWS RDS automated snapshots are physical backups—they won't help you restore a single table quickly. That's where pg_dump remains indispensable.

Implementing Reliable PostgreSQL Backup and Restore with pg_dump

Reliable PostgreSQL backup and restore with pg_dump requires treating backups as tested artifacts, not scheduled tasks. Start today by auditing your current setup: verify your last three backups can actually restore, measure the restore time, and document the procedure. Then implement the wrapper script pattern above, add verification steps, and schedule weekly restore tests. If your team needs help designing a compliant, automated backup strategy that survives audits and outages alike, reach out to discuss your infrastructure.