Auto Backup PostgreSQL Database Using pg_dump and cron
This is a simple, no‑frills way to take daily PostgreSQL backups with pg_dump and cron. The script writes timestamped dumps and keeps only the last 7 days.
1. Create a backup script
Create the script file:
vi /path/to/backup_script.sh
Add the content below. Swap the database credentials and backup directory.
#!/bin/bash
# Database credentials
DB_NAME="your_database_name"
DB_USER="your_database_user"
DB_PASSWORD="your_database_password"
# Backup directory
BACKUP_DIR="/path/to/backup/directory"
mkdir -p $BACKUP_DIR
# Date format for backup file
DATE=$(date +%Y-%m-%d_%H-%M-%S)
# Backup file name
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$DATE.sql"
# Export password to avoid prompt
export PGPASSWORD=$DB_PASSWORD
# Perform the backup
pg_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_FILE
# Unset the password
unset PGPASSWORD
# Optional: Delete backups older than 7 days
find $BACKUP_DIR -type f -name "*.sql" -mtime +7 -exec rm {} \;
echo "Backup completed successfully at $DATE"
Make it executable:
chmod +x /path/to/backup_script.sh
2. Schedule the Cron Job
Edit the crontab:
crontab -e
Run it every day at 12:30 AM:
30 0 * * * /path/to/backup_script.sh
That’s it. The backups will land in BACKUP_DIR with timestamps, and older files get pruned automatically.