Backup PostgreSQL/MySQL Database and Compress

#!/bin/bash

# Configuration for PostgreSQL
DB_TYPE="postgres" # Use "mysql" for MySQL database
PG_DB_NAME="your_postgres_db" # Your PostgreSQL database name
PG_DB_USER="your_postgres_user"
PG_DB_HOST="localhost"
PG_DB_PORT="5432"

# Configuration for MySQL (uncomment and adjust if using MySQL)
# MYSQL_DB_NAME="your_mysql_db"
# MYSQL_DB_USER="your_mysql_user"
# MYSQL_DB_PASS="your_mysql_password"

BACKUP_DIR="/var/backups/databases" # Directory to store backups
TIMESTAMP=$(date +"%Y%m%d%H%M%S")

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

if [ "$DB_TYPE" = "postgres" ]; then
    echo "Performing PostgreSQL database backup for $PG_DB_NAME..."
    export PGPASSWORD="your_postgres_password" # Set password for pg_dump securely
    pg_dump -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U "$PG_DB_USER" -Fc "$PG_DB_NAME" > "$BACKUP_DIR/$PG_DB_NAME-$TIMESTAMP.dump"
    unset PGPASSWORD # Unset password immediately after use
    
    if [ $? -eq 0 ]; then
        echo "PostgreSQL dump created successfully. Compressing..."
        gzip "$BACKUP_DIR/$PG_DB_NAME-$TIMESTAMP.dump"
        echo "Backup saved to $BACKUP_DIR/$PG_DB_NAME-$TIMESTAMP.dump.gz"
    else
        echo "PostgreSQL dump failed."
        exit 1
    fi
elif [ "$DB_TYPE" = "mysql" ]; then
    echo "Performing MySQL database backup for $MYSQL_DB_NAME..."
    mysqldump -u "$MYSQL_DB_USER" -p"$MYSQL_DB_PASS" "$MYSQL_DB_NAME" > "$BACKUP_DIR/$MYSQL_DB_NAME-$TIMESTAMP.sql"
    
    if [ $? -eq 0 ]; then
        echo "MySQL dump created successfully. Compressing..."
        gzip "$BACKUP_DIR/$MYSQL_DB_NAME-$TIMESTAMP.sql"
        echo "Backup saved to $BACKUP_DIR/$MYSQL_DB_NAME-$TIMESTAMP.sql.gz"
    else
        echo "MySQL dump failed."
        exit 1
    fi
else
    echo "Invalid DB_TYPE specified. Use 'postgres' or 'mysql'."
    exit 1
fi

# Optional: Remove old backups (e.g., older than 7 days)
# find "$BACKUP_DIR" -type f -name "*.gz" -mtime +7 -exec rm {} \;
# echo "Old backups cleaned up."