How to Back Up a PostgreSQL Database

I’ve never regretted taking a backup. I have, on more than one occasion, deeply regretted not taking one — or taking one incorrectly and only discovering that when I actually needed to restore it. Backups are one of those things that feel like a chore right up until the moment they save your entire project, and PostgreSQL gives you several solid, well-tested tools for doing it right. Let me walk through the main approaches, when to use each, and the details that actually matter.

Overview: Your Backup Options

PostgreSQL supports two fundamentally different backup strategies:

• Logical backups — pg_dump and pg_dumpall, which export your data as SQL statements or a portable archive format. These are flexible, portable across PostgreSQL versions and even OS platforms, and let you restore selectively.
• Physical backups — file-system-level copies of the actual data directory, taken with pg_basebackup or a filesystem snapshot. These are faster for very large databases and are the foundation of continuous archiving and point-in-time recovery, but they’re tied to the exact PostgreSQL version and architecture they were taken from.

For most projects, pg_dump is where you start. For large, mission-critical databases needing point-in-time recovery, physical backups with WAL archiving are the way to go.

Backing Up with pg_dump

pg_dump backs up a single database. The basic form:

pg_dump -U postgres -d mydb -f mydb_backup.sql

This creates a plain-text SQL file. It’s human-readable, which is nice for small databases, but it doesn’t support parallel restore or selective object restoration the way the other formats do.

Custom Format (Recommended for Most Cases)

pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

-F c produces a compressed, PostgreSQL-specific binary format. This is generally my default choice — it’s smaller than plain SQL, supports parallel restore with pg_restore -j, and lets you restore individual tables or objects rather than the whole thing.

Directory Format (Best for Very Large Databases)

pg_dump -U postgres -d mydb -F d -f mydb_backup_dir -j 4

Directory format splits the backup into multiple files and, unlike custom format, supports parallel dumping with -j, not just parallel restoring. For large databases, this can cut backup time significantly.

Tar Format

pg_dump -U postgres -d mydb -F t -f mydb_backup.tar

Less commonly used today — directory format generally offers the same benefits with more flexibility.

Backing Up the Entire Cluster with pg_dumpall

pg_dump only backs up a single database’s contents. If you need roles, tablespaces, and all databases in the cluster, use pg_dumpall:

pg_dumpall -U postgres -f full_cluster_backup.sql

A common pattern is to combine both: use pg_dumpall --roles-only to capture roles and permissions, and pg_dump in custom format for each individual database’s data:

pg_dumpall -U postgres --roles-only -f roles_backup.sql
pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

This gives you the flexibility of pg_dump‘s restore options while still preserving the role/permission structure pg_dumpall captures.

Backing Up Specific Tables or Schemas

pg_dump -U postgres -d mydb -t employees -f employees_backup.sql

Multiple tables:

pg_dump -U postgres -d mydb -t employees -t departments -f partial_backup.sql

Excluding specific tables (useful for skipping huge log tables you don’t need in the backup):

pg_dump -U postgres -d mydb -T audit_log -f mydb_backup_no_logs.sql

Schema-only or data-only backups:

pg_dump -U postgres -d mydb --schema-only -f schema_only.sql
pg_dump -U postgres -d mydb --data-only -f data_only.sql

Physical Backups with pg_basebackup

For a full, file-system-level copy of your running database cluster:

pg_basebackup -h localhost -U replicator -D /backups/base_backup -Fp -Xs -P

• -Fp — plain format (a direct copy of the data directory structure).
• -Xs — streams WAL alongside the backup so it’s immediately consistent, without needing separate WAL archiving to restore.
• -P — shows progress, genuinely useful for large databases.

This produces a complete, ready-to-use copy of your data directory. It’s version- and platform-specific — you can’t restore a base backup taken on PostgreSQL 16 into a PostgreSQL 15 instance, unlike logical dumps which are more portable.

Continuous Archiving and Point-in-Time Recovery (PITR)

For the strongest recovery guarantees, combine periodic base backups with continuous WAL archiving. In postgresql.conf:

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

This tells PostgreSQL to copy every completed WAL segment to your archive location as it’s generated. Combined with a periodic pg_basebackup, this lets you restore to any point in time between backups, not just the exact moment the last backup was taken — genuinely valuable if you need to recover to “just before” a specific incident (like an accidental mass delete) rather than a fixed backup timestamp.

In production, you’d typically replace the simple cp command with something more robust — shipping to S3, a dedicated backup server, or using a tool like pgBackRest or WAL-G, both of which handle compression, retention policies, and parallel operations far better than a bare cp.

Automating Backups with Cron

A simple daily backup script:

#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres -d mydb -F c -f "$BACKUP_DIR/mydb_$DATE.dump"

# Remove backups older than 14 days
find "$BACKUP_DIR" -name "mydb_*.dump" -mtime +14 -delete

Add it to crontab -e:

0 2 * * * /home/user/scripts/backup_postgres.sh

This runs the backup daily at 2 AM and cleans up backups older than two weeks. For production systems, I’d lean toward a dedicated tool like pgBackRest rather than a hand-rolled script — it handles retention policies, incremental backups, and verification far more robustly.

Encrypting Backups

Since a database dump often contains sensitive data, consider encrypting backups at rest, especially if they’re being stored off-site:

pg_dump -U postgres -d mydb -F c | gpg --symmetric --cipher-algo AES256 -o mydb_backup.dump.gpg

Verifying Backups

A backup you haven’t verified is a hope, not a plan. Periodically test that your backups actually restore cleanly:

createdb -U postgres verify_restore_test
pg_restore -U postgres -d verify_restore_test mydb_backup.dump
psql -U postgres -d verify_restore_test -c "SELECT COUNT(*) FROM employees;"
dropdb -U postgres verify_restore_test

I’d recommend automating this as a scheduled job, not just something you remember to do occasionally — backup verification is exactly the kind of task that’s easy to let slip until it’s too late.

Common Use Cases

• Routine scheduled backups as a core part of any production database’s operational hygiene.
• Pre-migration or pre-upgrade safety nets before making risky schema changes.
• Creating portable copies of a database to seed staging or development environments.
• Compliance requirements mandating regular, verifiable backups with defined retention periods.
• Building the foundation for point-in-time recovery in mission-critical systems.

Troubleshooting Common Issues

Backup file is unexpectedly small — check for errors in the pg_dump output; a failed connection partway through can leave a truncated, seemingly “complete” file that’s actually missing data.

“out of memory” during pg_dump — this can happen with --inserts on very large tables (each row becomes an individual INSERT statement); stick with the default COPY-based format, which is far more memory-efficient.

Backup takes too long / puts too much load on production — consider running pg_dump against a hot standby replica instead of the primary, or schedule backups during low-traffic windows. For very large databases, directory format with -j for parallel dumping helps significantly.

Archive command failing silently — always monitor archive_command for failures; a failed archive command means WAL segments pile up on the primary and, eventually, fill your disk. Set up alerting on this rather than discovering it during an actual restore attempt.

Best Practices

• Follow the 3-2-1 rule: at least 3 copies of your data, on 2 different media types, with 1 copy stored off-site.
• Prefer custom or directory format over plain SQL for anything beyond small, simple databases.
• Automate backups and their retention/cleanup — manual backup processes are the ones that get forgotten.
• Regularly test restores, not just backups — an untested backup is an assumption.
• Encrypt backups containing sensitive data, especially before shipping them off-site.
• For mission-critical systems, combine periodic base backups with continuous WAL archiving to support point-in-time recovery, and consider a dedicated tool like pgBackRest or WAL-G rather than hand-rolled scripts.

Wrapping Up

Backing up PostgreSQL isn’t complicated in principle — pg_dump for most use cases, pg_basebackup and WAL archiving when you need point-in-time recovery — but the details around format choice, automation, and verification are where real-world backup strategies succeed or quietly fail. Set it up once, automate it, and then actually test the restore. That last step is the one people skip, and it’s the one that matters most.