ethicnology · September 4, 2024 01:31
diff --git a/pg_backup.sh b/pg_backup.sh
 #!/bin/bash

 # Configuration
 DB_HOST=""
 DB_PORT=""
 DB_NAME=""
 DB_USER=""
 DB_PASS=""
 DIR=""
 DATE=$(date +"%Y%m%d_%H%M%S")
 ERROR_OCCURRED=false
 DATA_ONLY=false
 INSERTS=false
 SCHEMA_ONLY=false

 # Function to log messages
 log() {
    echo "$(date +"%Y-%m-%d %H:%M:%S") - $1"
 }

 # Function to display help message
 display_help() {
    echo "Usage: $0 [options]"
    echo ""
    echo "Parameters can be passed directly or set via environment variables as per your convenience:"
    echo "$ export DB_PASS='mysecretpassword'"
    echo ""
    echo "Options:"
    echo "  --DB_HOST=<host>         Database host (e.g., 127.0.0.1)"
    echo "  --DB_PORT=<port>         Database port (e.g., 5432)"
    echo "  --DB_NAME=<name>         Database name (e.g., postgres)"
    echo "  --DB_USER=<user>         Database user (e.g., postgres)"
    echo "  --DB_PASS=<password>     Database password (can also be set via environment variable DB_PASS)"
    echo "  --DIR=<dir>              Directory to store backup files (e.g., /path/to/directory)"
    echo "  --RETENTION=<days>       Number of days to retain backups (e.g., 90) (can also be set via environment variable RETENTION)"
    echo "  --data-only              Only dump data, not schema"
    echo "  --inserts                Use INSERT statements instead of COPY for data"
    echo "  --schema-only            Only dump schema, not data"
    echo "  -h, --help               Display this help message"
    exit 0
 }

 # Function to check if a variable is empty and exit with an error message if it is
 check_variable() {
    local var_name=$1
    local var_value=$2
    if [ -z "$var_value" ]; then
        log "Error: $var_name is not set. Please set it as a parameter, provide it as a parameter, or export it as an environment variable."
        display_help
        exit 1
    fi
 }

 resolve_dir() {
    if [[ "$DIR" == "." ]]; then
        # If DIR is '.', use the current directory
        DIR=$(pwd)
    elif [[ "$DIR" == .* ]]; then
        # If DIR starts with '.', use it as a relative path from the current directory
        DIR="$(pwd)/${DIR:1}"
    fi

    # Ensure the directory is within the home directory
    if [[ "$DIR" != "$HOME"* && "$DIR" != "$(pwd)"* ]]; then
        log "Error: The directory $DIR is outside of the home or current directory. You might need to use 'sudo' to create this directory."
        exit 1
    fi

    # Attempt to create the directory
    if ! mkdir -p "$DIR"; then
        log "Error: Unable to create the directory $DIR. You might need to use 'sudo' to create this directory."
        exit 1
    fi
 }

 # Parse command-line arguments
 while [ $# -gt 0 ]; do
    case "$1" in
        --DB_HOST=*) DB_HOST="${1#*=}" ;;
        --DB_PORT=*) DB_PORT="${1#*=}" ;;
        --DB_NAME=*) DB_NAME="${1#*=}" ;;
        --DB_USER=*) DB_USER="${1#*=}" ;;
        --DB_PASS=*) DB_PASS="${1#*=}" ;;
        --DIR=*) DIR="${1#*=}" ;;
        --RETENTION=*) RETENTION="${1#*=}" ;;
        --data-only) DATA_ONLY=true ;;
        --inserts) INSERTS=true ;;
        --schema-only) SCHEMA_ONLY=true ;;
        -h|--help)
            display_help
            ;;
        *)
            log "Error: Invalid option $1"
            exit 1
            ;;
    esac
    shift
 done

 # Check required variables
 check_variable "DB_HOST" "$DB_HOST"
 check_variable "DB_PORT" "$DB_PORT"
 check_variable "DB_NAME" "$DB_NAME"
 check_variable "DB_USER" "$DB_USER"
 check_variable "DIR" "$DIR"

 # Resolve directory path and ensure it's valid
 resolve_dir

 # Prompt for DB_PASS if not provided
 if [ -z "$DB_PASS" ]; then
    echo -n "Enter password for PostgreSQL user $DB_USER: "
    read -s DB_PASS
    echo
 fi

 # Check database connection
 export PGPASSWORD="$DB_PASS"
 if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c '\q' 2>/dev/null; then
    log "Error: Unable to connect to the database."
    ERROR_OCCURRED=true
    exit 1
 fi

 log "Starting schema listing for database $DB_NAME"

 # Get the list of schemas that do not start with 'pg_'
 schemas=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -Atc "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%';")

 if [ -z "$schemas" ]; then
    log "Error: No schemas found in the database."
    ERROR_OCCURRED=true
    exit 1
 fi

 # Display the available schemas
 echo "Available schemas: $schemas"

 # Prompt the user to select schemas
 echo -n "Enter schemas to back up (comma-separated), or press Enter to back up all: "
 read input_schemas

 # If the user presses Enter without input, back up all schemas
 if [ -z "$input_schemas" ]; then
    selected_schemas="$schemas"
 else
    selected_schemas=$(echo $input_schemas | tr ',' ' ')
 fi

 log "Selected schemas for backup: $selected_schemas"

 # Loop through selected schemas and back up each one
 for schema in $selected_schemas; do
    dump_options=""
    if [ "$DATA_ONLY" = true ]; then
        dump_options+=" --data-only"
    fi
    if [ "$INSERTS" = true ]; then
        dump_options+=" --inserts"
    fi
    if [ "$SCHEMA_ONLY" = true ]; then
        dump_options+=" --schema-only"
    fi
    
    if ! pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" $dump_options --schema="$schema" -F p -b -v -f "$DIR/${DATE}_${schema}.sql"; then
        log "Error: Backup of schema $schema failed."
        ERROR_OCCURRED=true
        continue
    fi
    log "Schema $schema backed up successfully."
    done

 # Unset the password variable for security
 unset DB_PASS
 unset PGPASSWORD

 # Cleanup old backups if RETENTION is set
 if [ -n "$RETENTION" ]; then
    find "$DIR" -type f -name "*.sql" -mtime +$RETENTION -exec rm {} \;
    log "Old backups older than $RETENTION days have been deleted."
 fi

 # Only display "Backup completed" if no errors occurred
 if [ "$ERROR_OCCURRED" = false ]; then
    log "Backup completed. Files are stored in $DIR"
 fi
	#!/bin/bash

	# Configuration
	DB_HOST=""
	DB_PORT=""
	DB_NAME=""
	DB_USER=""
	DB_PASS=""
	DIR=""
	DATE=$(date +"%Y%m%d_%H%M%S")
	ERROR_OCCURRED=false
	DATA_ONLY=false
	INSERTS=false
	SCHEMA_ONLY=false

	# Function to log messages
	log() {
	echo "$(date +"%Y-%m-%d %H:%M:%S") - $1"
	}

	# Function to display help message
	display_help() {
	echo "Usage: $0 [options]"
	echo ""
	echo "Parameters can be passed directly or set via environment variables as per your convenience:"
	echo "$ export DB_PASS='mysecretpassword'"
	echo ""
	echo "Options:"
	echo " --DB_HOST=<host> Database host (e.g., 127.0.0.1)"
	echo " --DB_PORT=<port> Database port (e.g., 5432)"
	echo " --DB_NAME=<name> Database name (e.g., postgres)"
	echo " --DB_USER=<user> Database user (e.g., postgres)"
	echo " --DB_PASS=<password> Database password (can also be set via environment variable DB_PASS)"
	echo " --DIR=<dir> Directory to store backup files (e.g., /path/to/directory)"
	echo " --RETENTION=<days> Number of days to retain backups (e.g., 90) (can also be set via environment variable RETENTION)"
	echo " --data-only Only dump data, not schema"
	echo " --inserts Use INSERT statements instead of COPY for data"
	echo " --schema-only Only dump schema, not data"
	echo " -h, --help Display this help message"
	exit 0
	}

	# Function to check if a variable is empty and exit with an error message if it is
	check_variable() {
	local var_name=$1
	local var_value=$2
	if [ -z "$var_value" ]; then
	log "Error: $var_name is not set. Please set it as a parameter, provide it as a parameter, or export it as an environment variable."
	display_help
	exit 1
	fi
	}

	resolve_dir() {
	if [[ "$DIR" == "." ]]; then
	# If DIR is '.', use the current directory
	DIR=$(pwd)
	elif [[ "$DIR" == .* ]]; then
	# If DIR starts with '.', use it as a relative path from the current directory
	DIR="$(pwd)/${DIR:1}"
	fi

	# Ensure the directory is within the home directory
	if [[ "$DIR" != "$HOME"* && "$DIR" != "$(pwd)"* ]]; then
	log "Error: The directory $DIR is outside of the home or current directory. You might need to use 'sudo' to create this directory."
	exit 1
	fi

	# Attempt to create the directory
	if ! mkdir -p "$DIR"; then
	log "Error: Unable to create the directory $DIR. You might need to use 'sudo' to create this directory."
	exit 1
	fi
	}

	# Parse command-line arguments
	while [ $# -gt 0 ]; do
	case "$1" in
	--DB_HOST=) DB_HOST="${1#=}" ;;
	--DB_PORT=) DB_PORT="${1#=}" ;;
	--DB_NAME=) DB_NAME="${1#=}" ;;
	--DB_USER=) DB_USER="${1#=}" ;;
	--DB_PASS=) DB_PASS="${1#=}" ;;
	--DIR=) DIR="${1#=}" ;;
	--RETENTION=) RETENTION="${1#=}" ;;
	--data-only) DATA_ONLY=true ;;
	--inserts) INSERTS=true ;;
	--schema-only) SCHEMA_ONLY=true ;;
	-h\|--help)
	display_help
	;;
	*)
	log "Error: Invalid option $1"
	exit 1
	;;
	esac
	shift
	done

	# Check required variables
	check_variable "DB_HOST" "$DB_HOST"
	check_variable "DB_PORT" "$DB_PORT"
	check_variable "DB_NAME" "$DB_NAME"
	check_variable "DB_USER" "$DB_USER"
	check_variable "DIR" "$DIR"

	# Resolve directory path and ensure it's valid
	resolve_dir

	# Prompt for DB_PASS if not provided
	if [ -z "$DB_PASS" ]; then
	echo -n "Enter password for PostgreSQL user $DB_USER: "
	read -s DB_PASS
	echo
	fi

	# Check database connection
	export PGPASSWORD="$DB_PASS"
	if ! psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c '\q' 2>/dev/null; then
	log "Error: Unable to connect to the database."
	ERROR_OCCURRED=true
	exit 1
	fi

	log "Starting schema listing for database $DB_NAME"

	# Get the list of schemas that do not start with 'pg_'
	schemas=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -Atc "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%';")

	if [ -z "$schemas" ]; then
	log "Error: No schemas found in the database."
	ERROR_OCCURRED=true
	exit 1
	fi

	# Display the available schemas
	echo "Available schemas: $schemas"

	# Prompt the user to select schemas
	echo -n "Enter schemas to back up (comma-separated), or press Enter to back up all: "
	read input_schemas

	# If the user presses Enter without input, back up all schemas
	if [ -z "$input_schemas" ]; then
	selected_schemas="$schemas"
	else
	selected_schemas=$(echo $input_schemas \| tr ',' ' ')
	fi

	log "Selected schemas for backup: $selected_schemas"

	# Loop through selected schemas and back up each one
	for schema in $selected_schemas; do
	dump_options=""
	if [ "$DATA_ONLY" = true ]; then
	dump_options+=" --data-only"
	fi
	if [ "$INSERTS" = true ]; then
	dump_options+=" --inserts"
	fi
	if [ "$SCHEMA_ONLY" = true ]; then
	dump_options+=" --schema-only"
	fi

	if ! pg_dump -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" $dump_options --schema="$schema" -F p -b -v -f "$DIR/${DATE}_${schema}.sql"; then
	log "Error: Backup of schema $schema failed."
	ERROR_OCCURRED=true
	continue
	fi
	log "Schema $schema backed up successfully."
	done

	# Unset the password variable for security
	unset DB_PASS
	unset PGPASSWORD

	# Cleanup old backups if RETENTION is set
	if [ -n "$RETENTION" ]; then
	find "$DIR" -type f -name "*.sql" -mtime +$RETENTION -exec rm {} \;
	log "Old backups older than $RETENTION days have been deleted."
	fi

	# Only display "Backup completed" if no errors occurred
	if [ "$ERROR_OCCURRED" = false ]; then
	log "Backup completed. Files are stored in $DIR"
	fi