PgSQL_restore.sh

#!/bin/bash
#
# PgSQL_restore.sh - Restores a PostgreSQL database from a selected backup file to a given application through its Docker compose file..
#
# This script:
# - Loads database credentials from a .env file and restore settings from a .conf file. Both files must be in the app dirrectory containing the compose file.
# - Lists all available SQL dump files and prompts the user to choose one to restore
# - Renames the current database before restoration to allow rollback if needed
# - Offers the option to revert the restore if the user is not satisfied
# - Supports optional pruning of old backups
#
# Usage:
#   bash PgSQL_restore.sh <app_dir>
#
# Example:
#   bash PgSQL_restore.sh ./authentik
#

# Globals (set after config/env loaded)
db_name=""
db_user=""
db_password=""
postgres_container_name=""
postgres_service_name=""
backup_dir=""

# -------- Functions --------

error_exit() {
    echo "❌ $1"
    exit 1
}

fetch_config_settings() {
    local config_file="$1"
    if [[ ! -f "$config_file" ]]; then
        error_exit "Config file '$config_file' not found."
    fi
    # shellcheck source=/dev/null
    source "$config_file"
}

fetch_env_variables() {
    local env_file="$1"
    if [[ ! -f "$env_file" ]]; then
        error_exit "Environment file '$env_file' not found."
    fi
    db_name=$(grep "^$env_db_name_var" "$env_file" | cut -d '=' -f2 | xargs)
    db_user=$(grep "^$env_db_user_var" "$env_file" | cut -d '=' -f2 | xargs)
    db_password=$(grep "^$env_db_password_var" "$env_file" | cut -d '=' -f2 | xargs)
}

list_backup_files() {
    local dir="$1"
    mapfile -t dump_files < <(find "$dir" -maxdepth 1 -type f -name "*.sql.gz" | sort)
    if [[ ${#dump_files[@]} -eq 0 ]]; then
        error_exit "No .sql.gz files found in $dir"
    fi

    echo -e "\nAvailable backup files:"
    for i in "${!dump_files[@]}"; do
        echo "$((i + 1))) ${dump_files[$i]}"
    done
}

prompt_backup_choice() {
    local count=$1
    local choice
    while true; do
        read -rp "Select a file to restore (1-$count): " choice
        if [[ "$choice" =~ ^[1-9][0-9]*$ ]] && (( choice >= 1 && choice <= count )); then
            echo "$choice"
            return
        fi
        echo "Invalid selection. Please try again."
    done
}

rename_database() {
    local from_db="$1"
    local to_db="$2"

    # Check if target database already exists
    local db_exists
    db_exists=$(docker exec -i "$postgres_container_name" psql -U "$db_user" -d postgres -tAc "SELECT 1 FROM pg_database WHERE datname = '$to_db';")

    if [[ "$db_exists" == "1" ]]; then
        echo "⚠️ Database '$to_db' already exists. Skipping rename from '$from_db'."
        drop_database "$from_db"
    else
        echo "🔁 Renaming database '$from_db' to '$to_db'..."
        docker exec -i "$postgres_container_name" psql -U "$db_user" -d postgres -c "ALTER DATABASE \"$from_db\" RENAME TO \"$to_db\";"
    fi
}


create_database() {
    local new_db="$1"
    echo "Creating database '$new_db'..."
    docker exec -i "$postgres_container_name" psql -U "$db_user" -d postgres -c "CREATE DATABASE $new_db;"
}

drop_database() {
    local db="$1"
    echo "Dropping database '$db'..."
    docker exec -i "$postgres_container_name" psql -U "$db_user" -d postgres -c "DROP DATABASE $db;"
}

restore_database() {
    local restore_file=$1
    echo "Restoring from: $restore_file"
    cat "$restore_file" | docker exec -i "$postgres_container_name" psql -U "$db_user" -d "$db_name"
}

prompt_continue() {
    read -n 1 -s -r -p "$1"
    echo
}

prompt_yes_no() {
    local prompt_msg="$1"
    local response
    while true; do
        read -rp "$prompt_msg (y/n): " response
        case "$response" in
            [Yy]) echo "yes"; return ;;
            [Nn]) echo "no"; return ;;
            *) echo "Please answer y or n." ;;
        esac
    done
}

# -------- Main --------

main() {
    if [[ $# -lt 1 ]]; then
        echo "Usage: $0 <app_dir>"
        exit 1
    fi

    cd "$(dirname "$0")"  # Run script from its own directory (useful case of relative paths for $1)

    # Validate input directory
    if [ ! -d "$1" ]; then
        echo "Error: input path '$1' is not a directory or does not exist."
        exit 1
    fi
    cd $1

    # Load config and env
    fetch_config_settings "PgSQL_backup_restore.conf"
    fetch_env_variables ".env"

    prompt_continue "⚠️  Careful, you are about to delete the current database. Use CTRL+C to cancel, press any key to continue..."

    docker compose down
    docker compose up $postgres_service_name -d

    list_backup_files "$backup_dir"

    local choice
    choice=$(prompt_backup_choice "${#dump_files[@]}")

    local restore_file="${dump_files[$((choice - 1))]}"
    local old_db="${db_name}_old"

    rename_database "$db_name" "$old_db"
    create_database "$db_name"

    sleep 2

    restore_database "$restore_file"

    prompt_continue "Press any key to docker compose up -d..."
    docker compose up -d

    local feedback
    feedback=$(prompt_yes_no "Are you happy with the restored database?")

    if [[ "$feedback" == "no" ]]; then
        echo "⏪ Reverting to the previous database..."
        drop_database "$db_name"
        rename_database "$old_db" "$db_name"
        echo "✅ Database has been reverted to its previous state."
    else
        echo "👍 Keeping the restored database."
        drop_database "$old_db"
    fi
}

main "$@"