replicate_postgres_db.sh

#!/bin/bash

CONFIG_FILE="/tmp/pg_script_config.tmp"

# Function to check if a command is available
command_exists() {
    command -v "$1" >/dev/null 2>&1
}

# Function to delete the config file
cleanup() {
    rm -f "$CONFIG_FILE"
}

# Function to load configuration from the file
load_config() {
    if [ -f "$CONFIG_FILE" ]; then
        while IFS='=' read -r key value; do
            case $key in
                "SOURCE_DB_HOST") SOURCE_DB_HOST="${value:-$SOURCE_DB_HOST}" ;;
                "SOURCE_DB_NAME") SOURCE_DB_NAME="${value:-$SOURCE_DB_NAME}" ;;
                "SOURCE_DB_SCHEMA") SOURCE_DB_SCHEMA="${value:-$SOURCE_DB_SCHEMA}" ;;
                "SOURCE_DB_USER") SOURCE_DB_USER="${value:-$SOURCE_DB_USER}" ;;
                "SOURCE_PASSWORD") SOURCE_PASSWORD="${value:-$SOURCE_PASSWORD}" ;;
                "TARGET_DB_HOST") TARGET_DB_HOST="${value:-$TARGET_DB_HOST}" ;;
                "TARGET_DB_NAME") TARGET_DB_NAME="${value:-$TARGET_DB_NAME}" ;;
                "TARGET_DB_SCHEMA") TARGET_DB_SCHEMA="${value:-$TARGET_DB_SCHEMA}" ;;
                "TARGET_DB_USER") TARGET_DB_USER="${value:-$TARGET_DB_USER}" ;;
                "TARGET_PASSWORD") TARGET_PASSWORD="${value:-$TARGET_PASSWORD}" ;;
            esac
        done < "$CONFIG_FILE"
    fi
}

# Function to prompt the user whether to load the config
prompt_load_config() {
    if [ -f "$CONFIG_FILE" ]; then
        read -p "A configuration file already exists. Do you want to load it? (y/n): " LOAD_CONFIG
        if [ "$LOAD_CONFIG" = "y" ] || [ "$LOAD_CONFIG" = "Y" ]; then
            load_config
        fi
    fi
}

# Function to prompt the user for any missing values and save to config
prompt_for_missing_values() {
    read -p "Enter source database host [default: $SOURCE_DB_HOST]: " USER_SOURCE_DB_HOST
    SOURCE_DB_HOST=${USER_SOURCE_DB_HOST:-$SOURCE_DB_HOST}
    echo "SOURCE_DB_HOST=$SOURCE_DB_HOST" >> "$CONFIG_FILE"

    read -p "Enter source database name [default: $SOURCE_DB_NAME]: " USER_SOURCE_DB_NAME
    SOURCE_DB_NAME=${USER_SOURCE_DB_NAME:-$SOURCE_DB_NAME}
    echo "SOURCE_DB_NAME=$SOURCE_DB_NAME" >> "$CONFIG_FILE"

    read -p "Enter source database schema [default: $SOURCE_DB_SCHEMA]: " USER_SOURCE_DB_SCHEMA
    SOURCE_DB_SCHEMA=${USER_SOURCE_DB_SCHEMA:-$SOURCE_DB_SCHEMA}
    echo "SOURCE_DB_SCHEMA=$SOURCE_DB_SCHEMA" >> "$CONFIG_FILE"

    read -p "Enter source database user [default: $SOURCE_DB_USER]: " USER_SOURCE_DB_USER
    SOURCE_DB_USER=${USER_SOURCE_DB_USER:-$SOURCE_DB_USER}
    echo "SOURCE_DB_USER=$SOURCE_DB_USER" >> "$CONFIG_FILE"

    read -p "Enter source database password: " SOURCE_PASSWORD
    echo "SOURCE_PASSWORD=$SOURCE_PASSWORD" >> "$CONFIG_FILE"

    echo # New line for better formatting

    read -p "Enter target database host [default: $TARGET_DB_HOST]: " USER_TARGET_DB_HOST
    TARGET_DB_HOST=${USER_TARGET_DB_HOST:-$TARGET_DB_HOST}
    echo "TARGET_DB_HOST=$TARGET_DB_HOST" >> "$CONFIG_FILE"

    read -p "Enter target database name [default: $TARGET_DB_NAME]: " USER_TARGET_DB_NAME
    TARGET_DB_NAME=${USER_TARGET_DB_NAME:-$TARGET_DB_NAME}
    echo "TARGET_DB_NAME=$TARGET_DB_NAME" >> "$CONFIG_FILE"

    read -p "Enter target database schema [default: $TARGET_DB_SCHEMA]: " USER_TARGET_DB_SCHEMA
    TARGET_DB_SCHEMA=${USER_TARGET_DB_SCHEMA:-$TARGET_DB_SCHEMA}
    echo "TARGET_DB_SCHEMA=$TARGET_DB_SCHEMA" >> "$CONFIG_FILE"

    read -p "Enter target database user [default: $TARGET_DB_USER]: " USER_TARGET_DB_USER
    TARGET_DB_USER=${USER_TARGET_DB_USER:-$TARGET_DB_USER}
    echo "TARGET_DB_USER=$TARGET_DB_USER" >> "$CONFIG_FILE"

    read -p "Enter target database password: " TARGET_PASSWORD
    echo "TARGET_PASSWORD=$TARGET_PASSWORD" >> "$CONFIG_FILE"

    echo # New line for better formatting
}


# Function to check for existing backup files and prompt the user
check_existing_backups() {
    BACKUP_FILES=$(ls BACKUP-*.backup.sql 2>/dev/null)
    if [ -z "$BACKUP_FILES" ]; then
        echo "No previous backup files found."
        return 1
    fi

    echo "Existing backup files:"
    i=1
    for FILE in $BACKUP_FILES; do
        echo "$i. $FILE"
        i=$((i + 1))
    done

    read -p "Do you want to use an existing backup file? Enter the number (1-$i) or press Enter to create a new backup: " CHOICE

    if [ -n "$CHOICE" ] && [ "$CHOICE" -ge 1 ] && [ "$CHOICE" -le $i ]; then
        FILE_NAME=$(echo "$BACKUP_FILES" | sed -n "${CHOICE}p")
        return 0
    else
        return 1
    fi
}

###################################
# MAIN FLOW 
###################################

# Check if pg_dump and pg_restore are installed
if ! command_exists pg_dump || ! command_exists pg_restore; then
    echo "Error: pg_dump or pg_restore is not installed. Please install PostgreSQL client tools first."
    echo "On Debian/Ubuntu, you can run: sudo apt-get install postgresql-client"
    echo "On Red Hat/Fedora, you can run: sudo dnf install postgresql"
    exit 1
fi

# Prompt the user whether to load the config file
prompt_load_config

# Prompt for missing values and save to config
prompt_for_missing_values

# Check for existing backup files and prompt the user
if check_existing_backups; then
    echo "Using existing backup file: $FILE_NAME"
else
    # Save inputs to config file
    TIMESTAMP=$(date "+%Y-%b-%d-%H-%M")
    FILE_NAME="BACKUP-$TIMESTAMP.backup.sql"
    echo "Saving backup to $FILE_NAME"

    # Set the PGPASSWORD environment variable
    export PGPASSWORD="$SOURCE_PASSWORD"

    ## BACKUP DATABASE
    pg_dump \
    --host="$SOURCE_DB_HOST" \
    --port=5432 \
    --username="$SOURCE_DB_USER" \
    --schema="$SOURCE_DB_SCHEMA" \
    --dbname="$SOURCE_DB_NAME" \
    --file="$FILE_NAME" \
    --format=t

    # Check if the backup was successful
    if [ $? -eq 0 ]; then
        echo "Backup successful."
    else
        echo "Backup failed. Exiting..."
        exit 1
    fi

    # Unset the PGPASSWORD environment variable
    unset PGPASSWORD
fi

echo "Loading the dump to target database"

# Set the PGPASSWORD environment variable for restore
export PGPASSWORD="$TARGET_PASSWORD"

# RESTORE DATABASE
pg_restore \
--clean \
--host="$TARGET_DB_HOST" \
--port=5432 \
--username="$TARGET_DB_USER" \
--schema="$TARGET_DB_SCHEMA" \
--dbname="$TARGET_DB_NAME" \
--format=t \
"$FILE_NAME"

# Check if the restore was successful
if [ $? -eq 0 ]; then
    echo "Restore successful."
else
    echo "Restore failed. Exiting..."
    exit 1
fi

# Clean up backup file
rm "$FILE_NAME"

echo "Script completed successfully."