Back Up and Restore PostgreSQL for a Production Application

A completed pg_dump command proves that a process exited; it does not prove that the archive contains every required database, role, extension, or file, or that the application can recover from it. A production backup becomes credible only after a controlled restore and application-level verification.

Logical and physical backups solve different problems

pg_dump creates a consistent logical export of one database while it remains in use. It does not dump roles and tablespaces; pg_dumpall --globals-only can capture cluster-wide objects when you administer the server. Managed services may restrict superuser-owned objects and provide their own backup/PITR system.

Do not treat a filesystem copy of a running PostgreSQL data directory as a valid backup unless it follows PostgreSQL's documented physical-backup method.

Start from recovery objectives

Recovery point objective (RPO) is the maximum acceptable data-loss window. Recovery time objective (RTO) is the maximum acceptable time to restore service. A nightly logical dump can have an RPO approaching 24 hours and may restore too slowly for the required RTO. Point-in-time recovery, replicas, or provider snapshots may be needed in addition—not instead of tested backups.

Inventory every dependency: database data, roles, extensions, encryption keys, object storage, uploaded files, environment configuration, and the application version/schema that can read the restored data.

Create a custom-format dump

Use a PostgreSQL client tool compatible with the server and read the installed version first:

pg_dump --version
psql --version

Create a custom archive. Keep the password out of the command and shell history by using a protected password file, short-lived provider credential, or interactive prompt:

pg_dump \
  --host=db.example.internal \
  --port=5432 \
  --username=backup_user \
  --format=custom \
  --file=appdb-20260812T020000Z.dump \
  appdb

Custom format works with pg_restore, is compressed by default in current PostgreSQL tooling, and supports selective and parallel restore. The backup_user needs enough permission to read every required object. Review warnings and the exit status; redirected output and schedulers must not hide failures.

Avoid putting the real hostname or database name in public examples if infrastructure names are sensitive. Never commit a dump: it can contain customer data, password hashes, tokens, and other secrets.

Capture roles and ownership deliberately

On a self-managed cluster, capture global definitions separately when required:

pg_dumpall \
  --host=db.example.internal \
  --username=backup_admin \
  --globals-only \
  --file=globals-20260812T020000Z.sql

This SQL can contain role definitions and password verifiers, so protect it as strictly as the database. Review it before restore, especially when moving to a managed service where provider-owned roles or tablespaces cannot be recreated.

For application migrations, a portable alternative is to restore without source ownership and assign objects to an existing target role:

createdb --host=restore-db.internal --username=restore_admin restore_appdb
 
pg_restore \
  --host=restore-db.internal \
  --username=restore_admin \
  --dbname=restore_appdb \
  --no-owner \
  --role=app_owner \
  --jobs=4 \
  --exit-on-error \
  appdb-20260812T020000Z.dump

--no-owner avoids commands that set original ownership. --role switches to a pre-created target role after connecting. --jobs applies only to supported archive formats and should be chosen from target CPU, I/O, memory, and connection capacity—not copied blindly. A restore can need extra working space for indexes and constraints.

Restore into a new, isolated database. Do not use destructive --clean or DROP DATABASE against production as a test. Inspect an archive before restoration:

pg_restore --list appdb-20260812T020000Z.dump

Version compatibility

PostgreSQL documents logical dumps as generally reloadable into newer server versions. pg_dump cannot dump from a server newer than its own major version, and loading into an older major version is not guaranteed. Use the newer target version's client tools for an upgrade path, then test extensions, collations, removed features, and application queries.

Physical backups and WAL recovery require a much tighter match with the server version and configuration. Follow the exact major-version or managed-provider recovery guide.

Schedule, retain, encrypt, and move off-server

A production job should:

1. create an archive in restricted temporary storage;
2. verify command exit status and basic archive readability;
3. encrypt it with a managed key when storage does not provide sufficient encryption;
4. upload it to access-controlled off-server storage;
5. verify the remote object size/checksum;
6. delete local temporary data according to policy;
7. apply a documented retention schedule;
8. alert when any stage or expected backup is missing.

Keep encryption keys separate from the backups and rehearse key recovery. Use immutable or object-lock retention where the threat model includes ransomware or compromised administrator credentials. Test removal rules so a bug cannot delete every generation.

The familiar “3-2-1” idea can be a useful prompt, but the real requirements come from RPO, RTO, regulation, data residency, cost, and threat model. A replica is not a backup: accidental deletion can replicate immediately.

Verify the restored application

Archive checksums detect storage corruption but not missing permissions, excluded tables, or an unusable schema. In an isolated network:

• confirm pg_restore completed without ignored errors;
• compare expected schemas, tables, extensions, and approximate/precise counts;
• run integrity and domain checks for critical records;
• connect with the real application role, not only the restore administrator;
• start a compatible application build against the restored database;
• exercise authentication, representative reads/writes, background jobs, and migrations safely;
• confirm referenced object-storage files and encryption keys are also recoverable;
• record duration, manual steps, failures, and the achieved RPO/RTO.

Repeat drills on a schedule and after meaningful schema, provider, encryption, or automation changes. For connection-string safety during recovery, see PostgreSQL Connection URL Explained and PostgreSQL SSL Modes.

Common backup mistakes

• Keeping the only dump on the same server or account as PostgreSQL.
• Backing up one database but forgetting roles, extensions, files, or another database.
• Assuming a zero exit code proves application recoverability.
• Restoring as a superuser but never testing the application's limited role.
• Using a client older than the source server.
• Encrypting archives without a tested method to recover the key.
• Testing restore by overwriting the live database.
• Retaining every backup forever without privacy, cost, or deletion policy.

Verification checklist

• Backup frequency and restore design meet documented RPO and RTO targets.
• Logical, physical/PITR, and external-file coverage match the failure scenarios.
• Roles, ownership, extensions, and provider restrictions have a restore plan.
• Archives are encrypted, access-controlled, monitored, retained, and stored off-server.
• Tool/server major-version compatibility is checked before every migration restore.
• A recent archive restored into isolation and passed application-level tests.
• The runbook records timing, dependencies, contacts, and rollback decisions.

References

Documentation checked on 2026-08-12:

• PostgreSQL SQL dump backup documentation
• PostgreSQL pg_dump
• PostgreSQL pg_restore
• PostgreSQL continuous archiving and point-in-time recovery
• PostgreSQL file-system-level backup