restore_database.sh

#!/bin/bash

source db_config.sh

restore_from_backup() {
    local backup_file="$1"

    if [ ! -f "$backup_file" ]; then
        echo "Backup file not found: $backup_file"
        return 1
    fi

    if [ ! -s "$backup_file" ]; then
        echo "Backup file is empty: $backup_file"
        return 1
    fi

    echo "Terminating active connections to ${PGDATABASE}..."
    sudo -u postgres psql -d postgres -c "
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = '${PGDATABASE}'
  AND pid <> pg_backend_pid();
"

    echo "Dropping database ${PGDATABASE}..."
    sudo -u postgres dropdb --if-exists "$PGDATABASE"

    echo "Recreating database ${PGDATABASE}..."
    sudo -u postgres createdb -O "$PGUSER" "$PGDATABASE"

    echo "Granting schema privileges..."
    sudo -u postgres psql -d "$PGDATABASE" -c "GRANT ALL ON SCHEMA public TO ${PGUSER};"

    echo "Restoring backup from ${backup_file}..."
    psql -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" -d "$PGDATABASE" -f "$backup_file"

    if [ $? -ne 0 ]; then
        echo "Restore failed during psql execution."
        return 1
    fi

    echo "Verifying restoration..."
    psql -c "\dt"

    if [ $? -ne 0 ]; then
        echo "Restore verification failed."
        return 1
    fi

    echo "Restore completed successfully."
    return 0
}

if [ -z "$1" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

restore_from_backup "$1"