PostgreSQL backup basics (pg_dump and pg_basebackup)

Topic: Databases core

Back up PostgreSQL with pg_dump for logical backups (single DB or full cluster) or pg_basebackup for physical backups. Use WAL archiving for point-in-time recovery. Use this when setting up backup jobs or when you need to restore a database or cluster.

Intent: How-to

Quick answer

  • Single DB: pg_dump -Fc -f mydb.dump mydb (custom format). Full cluster: pg_dumpall -g for globals only; pg_dump -Fc per database, or use pg_basebackup for a full data directory copy.
  • Physical backup: pg_basebackup -D /backup/pg -Ft -z -P. Requires replication connection; enable wal_level=replica and optionally archive_mode. Use for full cluster restore or standby creation.
  • Restore: pg_restore -d mydb mydb.dump (custom). For SQL dump: psql -d mydb -f mydb.sql. Ensure WAL archiving is configured if you need PITR; test restore regularly.

Prerequisites

Steps

  1. Logical backup with pg_dump

    pg_dump -U postgres -Fc -f /backup/mydb.dump mydb. -Fc is custom format (supports parallel restore, selective restore). Use -Fp for plain SQL. Schedule with cron; retain per policy.

  2. Full cluster and globals

    pg_dumpall -g -f /backup/globals.sql for roles and tablespaces. Then pg_dump each database or use pg_basebackup for a consistent filesystem copy (requires replication settings).

  3. Physical backup with pg_basebackup

    Set wal_level=replica (or higher); allow replication in pg_hba. pg_basebackup -h localhost -D /backup/pg -Ft -z -P. Produces a tar or plain copy; use for standby or full restore.

  4. Restore and verify

    Logical: pg_restore -d mydb -Fc mydb.dump (drop objects first if replacing). Physical: stop Postgres, replace data dir with backup, restore WAL if needed, start. Test restore on a schedule.

Summary

Use pg_dump for logical backups and pg_basebackup for physical backups. Configure WAL archiving for PITR; schedule backups and test restores. Use this to implement and verify database backup strategy.

Prerequisites

Steps

Step 1: Logical backup with pg_dump

Run pg_dump with custom format for one or more databases; schedule and retain per policy.

Step 2: Full cluster and globals

Use pg_dumpall -g for globals; back up each database or use pg_basebackup for a full copy.

Step 3: Physical backup with pg_basebackup

Set wal_level and pg_hba for replication; run pg_basebackup to a directory or tar.

Step 4: Restore and verify

Restore with pg_restore or psql; for physical, replace data dir and replay WAL. Test restores regularly.

Verification

  • Backup files are created and retained; a test restore completes and the database is consistent.

Troubleshooting

pg_basebackup fails — Check wal_level, pg_hba replication allowance, and connection from backup host. Restore conflicts — Drop existing objects or use a new database; ensure backup is from compatible version.

Next steps

Continue to