Backup selected (or all) schema from your postgres database

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