Skip to content

Expedition. Free, on-demand.

Technical tracks for practitioners, outcomes for leaders.

Watch now

Every Postgres backup conversation starts with the same question: what are you protecting against? Hardware failure, human error, compliance requirements, and data migration all call for different tools. This guide covers three kinds of "backups": pg_dump (which isn't really a backup), physical backups with point-in-time recovery, and managed services where the platform handles it for you.

pg_dump: A Copy, Not a Backup

We'll be direct: pg_dump is not a backup tool. It's a data export utility. It produces a snapshot of your database at one moment in time. It doesn't capture WAL, it can't restore to an arbitrary point, and it won't save you from "someone ran DROP TABLE five minutes ago." What it is good for: moving data between environments, extracting a subset of tables, migrating between major Postgres versions, and keeping a portable copy you can read in a text editor.

If pg_dump is your only backup strategy in production, you're accepting that you can only ever recover to the last time your dump ran, and that recovery takes however long it takes to replay all that SQL.

When pg_dump Is the Right Tool

  • Refreshing dev and staging: dump production, restore to dev.
  • Testing schema migrations: dump before a big DDL change, restore if it breaks.
  • Cross-version upgrades: logical dumps are a standard way to move between major Postgres versions.
  • Partial exports: dump a single table or schema for analysis or sharing.
  • Small databases (under 50 GB or so), where a dump and restore finishes in minutes, not hours.

Basic Usage

The default output is plain SQL, a series of CREATE and INSERT statements that rebuild your database:

# Plain SQL dump
pg_dump -h localhost -U postgres mydb > mydb_backup.sql

For anything beyond a toy database, use custom format. It's compressed, supports parallel restore, and lets you restore individual tables:

# Custom format (compressed, parallel-capable)
pg_dump -Fc -h localhost -U postgres mydb -f mydb_backup.dump

Key Options

OptionPurpose
-FcCustom format: compressed, parallel restore
-FdDirectory format: one file per table, parallel dump
-FpPlain SQL text (default)
--schema-onlyStructure only, no data
--data-onlyData only, no DDL
-t tablenameDump a single table
-j NParallel dump (directory format only)
-Z 0-9Compression level

Parallel Dumps for Large Databases

Directory format lets you dump multiple tables at the same time:

# Parallel dump with 4 workers
pg_dump -Fd -j 4 -h localhost -U postgres mydb -f /backups/mydb_dir/

Each worker handles a separate table, so the speedup scales with table count, not table size. One giant table is still one worker.

Don't Forget Globals

pg_dump exports a single database. It does not capture roles, tablespaces, or other cluster-wide objects:

# Dump global objects (roles, tablespaces)
pg_dumpall --globals-only -h localhost -U postgres -f globals.sql

Keep a recent globals dump next to your database dumps. Without it, restores fail on missing roles.

Restoring

For custom or directory format, use pg_restore:

# Full restore
pg_restore -d mydb -h localhost -U postgres mydb_backup.dump

# Parallel restore with 4 workers
pg_restore -j 4 -d mydb -h localhost -U postgres mydb_backup.dump

# Restore a single table
pg_restore -t orders -d mydb -h localhost -U postgres mydb_backup.dump

# List dump contents (useful for selective restore)
pg_restore -l mydb_backup.dump

For plain SQL dumps:

psql -h localhost -U postgres -d mydb -f mydb_backup.sql

Limitations You Need to Know

  • It's a point-in-time snapshot. You can only recover to the moment the dump started.
  • It holds a transaction open for the entire dump, which can hold back vacuum and block some DDL.
  • There's no WAL, so you can't replay changes made after the dump.
  • It's slow at scale. Databases over 100 GB can take hours to dump and restore.
  • Cluster-wide state (roles, replication slots, tablespace info) isn't included.

Automating pg_dump

For small databases where pg_dump is enough, automate it with cron and add retention:

#!/bin/bash
# /usr/local/bin/backup_postgres.sh
BACKUP_DIR="/backups/postgres"
RETENTION_DAYS=7
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

pg_dump -Fc -h localhost -U postgres mydb \
  -f "${BACKUP_DIR}/mydb_${TIMESTAMP}.dump"

# Verify it's not empty
if [ ! -s "${BACKUP_DIR}/mydb_${TIMESTAMP}.dump" ]; then
  echo "ERROR: Backup file is empty" >&2
  exit 1
fi

# Remove old backups
find "${BACKUP_DIR}" -name "*.dump" -mtime +${RETENTION_DAYS} -delete

# crontab: daily at 2 AM
0 2 * * * /usr/local/bin/backup_postgres.sh

Physical Backups: Real Disaster Recovery

Physical backups copy the whole database cluster at the file level: every data file, the WAL needed for consistency, and configuration. Unlike pg_dump, physical backups are the foundation for point-in-time recovery (PITR), which is your actual protection against human error. Add continuous WAL archiving and you can restore to any moment within your retention window.

This is the minimum backup strategy for any production Postgres database.

pg_basebackup

pg_basebackup is the built-in tool for physical backups. It connects to a running server, triggers a checkpoint, and streams a consistent copy of the data directory:

# Physical backup with WAL streaming and progress
pg_basebackup -D /backups/base_2025_06_15 \
  -h localhost -U replicator \
  -Fp -Xs -P -c fast
OptionPurpose
-DDestination directory
-FpPlain format (directory tree)
-FtTar format
-XsStream WAL during backup (needed for a consistent backup)
-PShow progress
-c fastFast checkpoint, don't wait for the scheduled one
-zCompress tar output with gzip

Don't skip -Xs. Without the WAL generated during the backup, you can end up with a backup that won't start.

Tar Format for Portability

# Compressed tar backup, easier to ship to remote storage
pg_basebackup -D /backups/base_2025_06_15 \
  -h localhost -U replicator \
  -Ft -z -Xs -P -c fast

This produces base.tar.gz and pg_wal.tar.gz, compact archives ready for object storage.

Incremental Backups (Postgres 17+)

Postgres 17 added built-in incremental backups. You take a normal full backup, then later backups only copy blocks that changed since a previous backup's manifest. This needs summarize_wal = on on the server so Postgres tracks which blocks changed.

# Full backup
pg_basebackup -D /backups/full -h localhost -U replicator -Xs -c fast

# Later: incremental backup relative to the full backup's manifest
pg_basebackup -D /backups/incr1 -h localhost -U replicator -Xs -c fast \
  --incremental=/backups/full/backup_manifest

An incremental backup can't be started directly. To restore, use pg_combinebackup to merge the chain into a full data directory:

pg_combinebackup /backups/full /backups/incr1 -o /restore/data

For big databases with a small daily change rate, this cuts backup time and storage a lot. Dedicated tools like pgBackRest have had incrementals for years and still handle the retention and storage side better, but it's nice to have this in core.

Verifying Backups

Since Postgres 13, pg_basebackup writes a backup_manifest with checksums for every file:

pg_verifybackup /backups/base_2025_06_15

A silent exit with code 0 means the backup is intact. Run this after every backup. An unverified backup is hope, not infrastructure.

Continuous WAL Archiving

A physical backup on its own restores to the moment the backup was taken. WAL archiving extends that to any point in the archive window:

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

In production, ship WAL to object storage:

archive_command = 'aws s3 cp %p s3://my-backups/wal/%f'

Postgres retries a failing archive command forever, and keeps the WAL until it succeeds. A broken archive command will eventually fill pg_wal and take the server down, so monitor it.

Point-in-Time Recovery (PITR)

PITR is the reason you take physical backups. A base backup plus archived WAL lets you restore to any point in that window.

Base Backup (Monday 2:00 AM)
  + Archived WAL (Monday 2:00 AM to Wednesday 4:37 PM)
  = Restore to any point in that window

Here's the recovery workflow, say for an accidental DROP TABLE at 4:37 PM:

  1. Stop production (or restore to a separate instance).
  2. Restore the most recent base backup to a fresh data directory.
  3. Configure recovery:
# postgresql.conf
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2025-06-15 16:36:00'
recovery_target_action = 'promote'
  1. Create the recovery signal file:
touch /path/to/data_directory/recovery.signal
  1. Start Postgres. It replays WAL up to your target time, then promotes.

Recovery Targets

# By time
recovery_target_time = '2025-06-15 16:36:00'

# By named restore point
recovery_target_name = 'before_migration'

# By LSN
recovery_target_lsn = '0/1A5B3C8'

# By transaction ID
recovery_target_xid = '12345'

Create restore points before risky operations:

SELECT pg_create_restore_point('before_migration');

Backup Tools

pg_basebackup and hand-rolled WAL archiving work, but in production you want a dedicated tool that handles retention, parallelism, encryption, and verification for you. pgBackRest is the most widely deployed one. It does full, differential, and incremental backups with parallel compression, AES-256 encryption, configurable retention, backups to object storage (S3, GCS, Azure) or over SSH, delta restore, and backup from a standby to keep I/O off your primary. Barman and WAL-G are the other common choices.

Test Your Restores

A backup you've never restored is a hope, not a backup. Schedule regular restore tests:

pg_restore -d mydb_restore_test -h localhost -U postgres mydb_backup.dump
psql -d mydb_restore_test -c "SELECT count(*) FROM orders;"

The worst time to find out your backup process is broken is during an incident.

Managed Backups: Let the Platform Handle It

If you're running WAL archiving, retention, encryption, and restore testing yourself, that's engineering time spent on plumbing. Managed Postgres services treat backups as a core feature: continuous WAL archiving, automated physical backups, point-in-time recovery, encryption at rest, and retention are generally included by default.

What Managed Backups Provide

CapabilitySelf-ManagedManaged Service
Physical backupsYou configure pgBackRest/BarmanAutomatic
WAL archivingYou set up archive_command + storageBuilt-in, continuous
Point-in-time recoveryYou manage the restore workflowAPI call or SQL command
EncryptionYou configure keysAutomatic, at rest and in transit
RetentionYou write retention scriptsPolicy-driven
Restore testingYou schedule and validateVaries by provider, still worth doing
Cross-region copiesYou build replicationOften a configuration option

Snowflake Postgres

Snowflake Postgres takes backups and archives WAL for you, with nothing to configure. Point-in-time recovery works by forking: you create a new, fully independent instance from the source as of any point in the 10-day retention window. Under the hood it restores the most recent base backup before your target time and replays WAL up to it, the same process described above.

-- Fork from a specific time
CREATE POSTGRES INSTANCE my_recovered_db
  FORK my_production_db
  AT (TIMESTAMP => '2025-06-15 16:36:00'::TIMESTAMP_NTZ);

-- Or from 30 minutes ago
CREATE POSTGRES INSTANCE my_recovered_db
  FORK my_production_db
  AT (OFFSET => -1800);

The fork keeps the source's Postgres version and credentials. You can pick a different compute size or storage for it, which is handy when you just need to pull a dropped table back out and don't want to pay for a full production-sized copy. Since the fork is separate from the source, you can copy the rows you need back over and then drop it.

High availability (a standby in another availability zone with automatic failover) is a separate option. See the High Availability guide for that side of things.

Conclusion

Think about backups in three tiers. pg_dump is a copy tool. Use it for migrations and dev or staging refreshes, not disaster recovery. Physical backups with continuous WAL archiving (via pgBackRest, Barman, or WAL-G) give you real point-in-time recovery and are the minimum for production. Managed services like Snowflake Postgres run the whole backup lifecycle for you, and recovery becomes a single SQL command.

Whatever you pick, test your restores. Regularly.

Related Resources

PostgreSQL Documentation: Backup and Restore

Official Postgres docs covering logical dumps, physical backups, and continuous archiving.

Introduction to Postgres Backups (Crunchy Data)

Overview of pg_dump vs. pgBackRest vs. managed backups, with guidance on choosing by database size.

Snowflake Postgres: Point-in-Time Recovery

How Snowflake Postgres handles forking and PITR.

Snowflake Postgres: Managing Instances

Instance management, including forking, high availability, and recovery options.

WAL (Write-Ahead Log) Guide

WAL segments, archiving, and their role in backup and recovery.

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances