Backup & Restore

Backing up your database is not optional. Data loss can happen due to hardware failures, accidental deletions, or even bugs in your application. PostgreSQL provides robust tools for backing up and restoring your data. This lesson covers the essential backup strategies.

pg_dump — Logical Backups

pg_dump creates a SQL text file that contains everything needed to recreate your database:

-- Backup a single database
pg_dump -U postgres my_database > backup.sql

-- Backup with custom format (compressed, supports parallel restore)
pg_dump -U postgres -Fc my_database > backup.dump

-- Backup only the schema (no data)
pg_dump -U postgres --schema-only my_database > schema.sql

-- Backup specific tables
pg_dump -U postgres -t students -t courses my_database > tables_backup.sql

The custom format (-Fc) is recommended for production backups because it is compressed and supports parallel operations during restore.

pg_restore — Restoring Backups

To restore from a pg_dump backup:

-- From a SQL file
psql -U postgres my_database < backup.sql

-- From a custom format file (with parallel jobs)
pg_restore -U postgres -d my_database -j 4 backup.dump

-- List contents of a backup file
pg_restore -l backup.dump

The -j flag specifies the number of parallel jobs. This can significantly speed up restore times for large databases.

pg_dumpall — Full Cluster Backup

To backup all databases in the cluster:

pg_dumpall -U postgres > full_backup.sql

This includes all databases, roles, and tablespaces. Useful for disaster recovery but creates very large files.

Continuous Archiving (WAL)

For production systems, point-in-time recovery (PITR) is essential. This uses Write-Ahead Logging (WAL) to capture every change:

-- Enable WAL archiving in postgresql.conf
-- archive_mode = on
-- archive_command = 'cp %p /archive/%f'

-- Create a base backup
pg_basebackup -U postgres -D /backup/base -Ft -z -P

With continuous archiving, you can restore your database to any point in time, not just the moment of the backup. This is critical for recovering from accidental data changes.

Automating Backups

Set up a cron job or scheduled task for regular backups:

#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -U postgres -Fc my_database | gzip > /backups/db_$DATE.dump.gz

# Keep only the last 30 days
find /backups -name "*.dump.gz" -mtime +30 -delete

Automate your backups and test your restore process regularly. A backup that has never been tested is not a reliable backup.

Backup Best Practices

Back up regularly — daily minimum, more often for critical data
Store backups offsite — not on the same server
Test your restores — a backup is only good if you can restore from it
Keep multiple backup generations — retain backups for at least 30 days
Monitor backup jobs — make sure they are actually running and succeeding
Encrypt sensitive backups — protect your data at rest