Auto Backup PostgreSQL Database Using pg_dump and cron

hahnavi | July 28, 2024

1. Create a Backup Script

Create the Script File:

vi /path/to/backup_script.sh

Add the Following Content to the Script:

#!/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 the Script Executable:

chmod +x /path/to/backup_script.sh

2. Schedule the Cron Job

Edit the Cron Tab:

crontab -e

Add the Following Line to Schedule the Backup:

30 0 * * * /path/to/backup_script.sh

This cron job will execute the backup script every day at 12:30 AM.