How to Set Up PostgreSQL Backups

1. Daily snapshot: pg_dump -Fc -Z9 -f backup.dump dbname (compressed custom format)
2. Physical: pg_basebackup -D /backup/base -Ft -z -P -U replicator
3. Enable WAL archiving in postgresql.conf: archive_mode = on; archive_command = 'aws s3 cp %p s3://bucket/wal/%f'
4. Automate via cron: 0 3 * * * /usr/bin/pg_dump -Fc -f /backup/daily-\$(date +\%Y\%m\%d).dump dbname
5. Upload to S3: AWS CLI or s3cmd for retention + offsite
6. Test restore monthly: pg_restore -d new_db /backup/daily-20260401.dump
7. For PITR: pg_basebackup + apply WAL segments up to target timestamp

• pg_dump for large DBs (100+ GB) — very slow. Use pg_basebackup
• No WAL archiving = data loss possible between snapshots
• Backup not tested — first raw fail at a critical moment
• Hardcoded password in script — security risk. Use .pgpass or IAM role
• S3 without versioning — race on concurrent uploads drops the last

To set up PostgreSQL backups in 2026, use the pg_dump command to create a backup of your database. For a full backup, execute pg_dump -U username -F c -b -v -f /path/to/backup/file.backup dbname. Schedule backups using a cron job, ensuring they run at off-peak hours. Also, consider using tools like pgBackRest or Barman for more advanced backup strategies.

PostgreSQL offers several options for backing up your databases, each suited to different needs and environments. Understanding these options will help you choose the best method for your use case.

1. pg_dump

This command-line utility is the most common way to back up a PostgreSQL database. It allows for full, partial, or incremental backups:

• Full Backup: Use pg_dump -U username -F c -b -v -f /path/to/backup/file.backup dbname for a complete backup in a custom format.
• Schema-Only Backup: To back up just the schema, use pg_dump -U username -s -f /path/to/backup/schema.sql dbname.
• Data-Only Backup: For data only, execute pg_dump -U username -a -f /path/to/backup/data.sql dbname.

2. pg_dumpall

If you want to back up all databases in a PostgreSQL instance, the pg_dumpall command is the way to go. Use it as follows:

pg_dumpall -U username > /path/to/backup/all_databases.sql

This command will dump all databases, roles, and tablespaces, making it a comprehensive solution for full server backups.

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

For enterprise-level applications, you might need point-in-time recovery. This method involves continuous archiving of the WAL (Write Ahead Log) files:

1. Enable WAL archiving by modifying the postgresql.conf file:

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

1. Set the archive_timeout to ensure logs are archived regularly:

archive_timeout = 60s

With this setup, you can restore your database to any point in time by combining the base backup with the archived WAL files.

4. Third-Party Tools

For more complex backup strategies, consider using third-party tools like:

• pgBackRest: Supports full, differential, and incremental backups. It can also handle backup retention policies and compression.
• Barman: Provides backup and recovery management for multiple PostgreSQL servers. It offers features like remote backups and automated recovery procedures.