Skip to content

Snowflake World Tour hits your city

See how leading teams deploy agents at scale. Find a stop near you. Register free.

Register now

Every Postgres backup conversation starts with the same distinction: What are you protecting against? Hardware failure, human error, compliance requirements, and data migration all demand different tools. This guide covers three categories of "backups" — from pg_dump (which isn't really a backup) through physical backups with point-in-time recovery, to fully managed solutions where the platform handles everything for you.

pg_dump: A Copy, Not a Backup

Let's 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 inspect with a text editor.

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

When pg_dump Is the Right Tool

  • Development and staging refreshes — dump production, restore to dev
  • Schema migration testing — dump before a major DDL change, restore if it breaks
  • Cross-version upgrades — logical dumps are the standard way to move between major Postgres versions
  • Partial exports — dump a single table or schema for analysis or sharing
  • Small databases (<50 GB) — where the dump/restore cycle completes in minutes, not hours

Basic Usage

The default output is plain SQL — CREATE and INSERT statements that rebuild your database:

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

For anything beyond toy-size databases, use custom format — it's compressed, supports parallel restore, and lets you selectively 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 enables parallel dumping — multiple tables extracted simultaneously:

# 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.

Don't Forget Globals

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

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

Always keep a recent globals dump alongside your database dumps. Without it, restore will 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 Must Understand

  • Point-in-time snapshot only — you can only recover to the moment the dump started
  • Long-running transaction — holds a transaction open for the entire dump, which can conflict with vacuum and DDL
  • No WAL — cannot replay changes after the dump
  • Slow at scale — databases over 100 GB take hours to dump and restore
  • No cluster-wide state — roles, replication slots, and tablespace info are not included

Automating pg_dump

For small databases where pg_dump is sufficient, automate 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 entire database cluster at the filesystem level — every data file, WAL segment, and configuration file. Unlike pg_dump, physical backups serve as the foundation for point-in-time recovery (PITR), which is your actual protection against human error. Combined with continuous WAL archiving, you can restore to any second within your retention window.

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

pg_basebackup

pg_basebackup is the built-in tool for taking 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 (single compressed archive)
-XsStream WAL during backup (critical for consistency)
-PShow progress
-c fastFast checkpoint — don't wait for scheduled one
-zCompress tar output with gzip

The -Xs flag is critical. Without it, WAL generated during the backup might be recycled before you archive it, leaving an inconsistent backup.

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

Produces base.tar.gz and pg_wal.tar.gz — compact archives ready for object storage.

Verifying Backups

Since PostgreSQL 13, pg_basebackup generates a backup_manifest with checksums for every file:

pg_verifybackup /backups/base_2025_06_15

Silent exit with code 0 = intact. Run this after every backup. An unverified backup is hope, not infrastructure.

Continuous WAL Archiving

Physical backups alone restore to the moment the backup was taken. WAL archiving extends that to any second 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 the archive command indefinitely on failure. A broken archive command will eventually fill pg_wal and halt the server — monitor this.

Point-in-Time Recovery (PITR)

PITR is the reason you take physical backups. A base backup + archived WAL = restore to any second in that window.

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

Recovery workflow (e.g., recovering from 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:
touch /path/to/data_directory/recovery.signal
  1. Start Postgres — it replays WAL through 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

While pg_basebackup and manual WAL archiving work, production environments benefit from dedicated backup tools that handle retention, parallelism, encryption, and verification automatically. pgBackRest is the most widely deployed production backup tool for Postgres. Supports full, differential, and incremental backups with parallel compression, AES-256 encryption, configurable retention, remote backup via SSH or object storage (S3, GCS, Azure), delta restore, and backup from standby to offload I/O from your primary.

Test Your Restores

A backup you've never restored is not a backup — it's a hope. 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 discover your backup process is broken is during an actual incident.

Managed Backups: Let the Platform Handle It

If you're managing WAL archiving, retention, encryption, and restore testing yourself, you're spending engineering time on infrastructure plumbing that managed platforms solve automatically. Managed Postgres services handle backups as a core feature — continuous WAL archiving, automated physical backups, point-in-time recovery, encryption at rest, and retention policies are all included by default.

What Managed Backups Provide

CapabilitySelf-ManagedManaged Service
Physical backupsYou configure pgBackRest/BarmanAutomatic, daily or more frequent
WAL archivingYou set up archive_command + storageBuilt-in, continuous
Point-in-time recoveryYou manage restore workflowAPI call or SQL command
EncryptionYou configure AES keysAutomatic, at rest and in transit
RetentionYou write retention scriptsPolicy-driven, configurable
Restore testingYou schedule and validatePlatform-verified
Cross-region copiesYou build replicationConfiguration option

Snowflake Postgres

Snowflake Postgres includes continuous automated backups with zero configuration:

  • Automatic physical backups — taken daily with no user intervention
  • Continuous WAL archiving — every transaction is archived automatically
  • Point-in-time recovery via fork — create a new instance from any point in your retention window
  • Encrypted and verified — backups are encrypted at rest and integrity-checked automatically
  • High availability — optional cross-zone standby with automated failover

To recover to a specific point in time, you fork the instance:

CREATE POSTGRES INSTANCE my_recovered_db
  FORK_SOURCE = 'my_production_db'
  FORK_POINT_IN_TIME = '2025-06-15 16:36:00';

No manual WAL management, no pgBackRest configuration, no archive monitoring. The platform handles the entire lifecycle.

aside positive With Snowflake Postgres, backups and PITR are included by default. You focus on your application — the platform handles continuous WAL archiving, automated physical backups, encryption, and retention. Recovery is a single SQL command.

Conclusion

Think about backups in three tiers. pg_dump is a copy tool — useful for migrations and dev/staging refreshes, but 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 standard for production. Managed services like Snowflake Postgres handle the entire backup lifecycle automatically — continuous archiving, retention, encryption, and recovery built in from day one.

Whatever you choose: test your restores. Regularly. A backup that's never been restored is just a file you hope works.

Related Resources

PostgreSQL Documentation — Backup and Restore

Official PostgreSQL docs covering all backup methods — logical, physical, and continuous archiving.

Introduction to Postgres Backups — Crunchy Data

Overview of pg_dump vs. pgBackRest vs. managed backups, with guidance on choosing the right approach for your database size.

Snowflake Postgres — Point-in-Time Recovery

How Snowflake Postgres handles forking and PITR with zero manual configuration.

Snowflake Postgres — Managing Instances

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

Snowflake Quickstart — WAL (Write-Ahead Log) Guide

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

Updated 2026-07-08

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