PostgreSQL Auto Backup
Automatically backs up PostgreSQL databases, compresses and cleans old backups.
Published: March 30, 2024
Detailed Information
This script automatically backs up your PostgreSQL databases, compresses them, and cleans old backups. Works similarly to MySQL backup script but optimized for PostgreSQL.
What Does This Script Do?
This script backs up PostgreSQL databases:
- Backs up all user databases
- Excludes system databases
- Compresses backups with gzip
- Automatically cleans old backups
Why Should You Use It?
PostgreSQL backup protects against data loss:
- Automation: Full automation with cron job
- Disk Savings: Space savings with compression
- Disaster Recovery: Quick restore
How to Use
Step-by-Step Usage Guide
1. Configuration
Edit variables at the beginning of the script:
BACKUP_DIR="/backup/postgresql"
RETENTION_DAYS=72. Run Script
sudo chmod +x pg_backup.sh
sudo ./pg_backup.sh3. Automation
# Daily backup with crontab
0 2 * * * /path/to/pg_backup.shRequirements
Requirements
- PostgreSQL: PostgreSQL must be installed
- pg_dump: PostgreSQL backup tool
- Postgres User Access: Access to databases
Use Cases
Use Cases
1. Daily Automated Backup
Automatically back up your PostgreSQL databases every day.
2. Disaster Recovery
Quickly restore in case of server crash.
Examples
Usage Examples
Example 1: Basic Usage
sudo ./pg_backup.shCode
#!/bin/bash
# PostgreSQL Backup Script
BACKUP_DIR="/backup/postgresql"
PG_USER="postgres"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p "$BACKUP_DIR"
echo "Starting PostgreSQL backup..."
ALL_DBS=$(sudo -u postgres psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false")
DATABASES=$(echo "$ALL_DBS" | grep -v -E "^(postgres|template0|template1)$")
for DB in $DATABASES; do
echo "Backing up: $DB"
BACKUP_FILE="$BACKUP_DIR/${DB}_${DATE}.sql"
sudo -u postgres pg_dump "$DB" > "$BACKUP_FILE"
gzip "$BACKUP_FILE"
echo "✓ Backup completed: ${BACKUP_FILE}.gz"
done
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "Backup process completed!"Usage
sudo chmod +x pg_backup.sh
sudo ./pg_backup.sh
# Automate with crontab
sudo crontab -e
# Add: 0 2 * * * /path/to/pg_backup.shTroubleshooting
Troubleshooting
Problem: "pg_dump: command not found"
Solution: Install PostgreSQL client:
sudo apt-get install postgresql-client