How to Back Up a PostgreSQL Database

PostgreSQL provides powerful backup tools for data protection.

Backup with pg_dump

sudo -u postgres pg_dump myapp > myapp_backup.sql

Compressed Backup

sudo -u postgres pg_dump -Fc myapp > myapp_backup.dump

Backup All Databases

sudo -u postgres pg_dumpall > all_databases.sql

Restore

# SQL format
sudo -u postgres psql myapp < myapp_backup.sql

# Custom format
sudo -u postgres pg_restore -d myapp myapp_backup.dump

Scheduled Backups

crontab -e
0 3 * * * sudo -u postgres pg_dump -Fc myapp > /backups/myapp_$(date +\%Y\%m\%d).dump