PostgreSQL Avtomatik Yedəkləmə

#!/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!"

sudo chmod +x pg_backup.sh
sudo ./pg_backup.sh

# Automate with crontab
sudo crontab -e
# Add: 0 2 * * * /path/to/pg_backup.sh