#!/usr/bin/env bash # Autor: Mattia Tadini # File: autoscale_cluster.sh # Version: 1.00 # Date 14/11/2025 # - Supporto PG 14 / 15 / 16 # - Profili: PRIVATO (100), SHARED (10000), ANALYTICS (10000) set -Ee # niente -u per evitare abort su variabili non settate # --- Guard rail: esecuzione come root if [ "${EUID:-$(id -u)}" -ne 0 ]; then echo "Errore: lo script deve essere eseguito come root" >&2 exit 1 fi show_error() { echo "ERRORE: $1" >&2 if command -v whiptail >/dev/null 2>&1; then whiptail --title "Errore Critico" --msgbox "$1" 20 74 fi exit 1 } show_msg() { if command -v whiptail >/dev/null 2>&1; then whiptail --title "$1" --msgbox "$2" 20 74 else echo -e "\n[$1]\n$2\n" fi } # --- Rilevamento risorse detect_ram_mb() { if [ -r /proc/meminfo ]; then awk '/MemTotal:/ { printf "%d\n", $2/1024 }' /proc/meminfo else free -m | awk '/Mem:/ {print $2}' fi } detect_cpu_cores() { if command -v nproc >/dev/null 2>&1; then nproc else getconf _NPROCESSORS_ONLN 2>/dev/null || echo 1 fi } # --- Selezione versione PostgreSQL tra quelle presenti get_postgres_version() { local available=() v sel for v in 14 15 16; do if [ -d "/etc/postgresql/${v}/main" ]; then available+=("$v") fi done if [ "${#available[@]}" -eq 0 ]; then show_error "Nessuna installazione trovata in /etc/postgresql/{14,15,16}/main" fi if command -v whiptail >/dev/null 2>&1; then local menu_args=() for v in "${available[@]}"; do case "$v" in 14) menu_args+=("14" "PostgreSQL 14 (profilo Zucchetti)") ;; 15) menu_args+=("15" "PostgreSQL 15") ;; 16) menu_args+=("16" "PostgreSQL 16 (profilo Zucchetti)") ;; esac done sel=$(whiptail --title "Versione PostgreSQL" --menu \ "Seleziona la versione DA TUNARE (già installata):" 20 74 10 \ "${menu_args[@]}" 3>&1 1>&2 2>&3) || show_error "Operazione annullata." echo "$sel" else echo "Installazioni trovate:" printf ' - %s\n' "${available[@]}" read -r -p "Versione da tunare [${available[*]}]: " v case "$v" in 14|15|16) echo "$v" ;; *) show_error "Versione non valida" ;; esac fi } # --- Scelta profilo max_connections get_connection_profile() { local p sel if command -v whiptail >/dev/null 2>&1; then sel=$(whiptail --title "Profilo Connessioni" --menu \ "Scegli il profilo server DB:\n- PRIVATO: singolo DB, poche connessioni\n- SHARED: multi-tenant, molte connessioni\n- ANALYTICS: carichi analitici pesanti (molta parallelizzazione)" \ 20 74 12 \ "PRIVATO" "max_connections = 100 (conservativo)" \ "SHARED" "max_connections = 10000 (multi-tenant)" \ "ANALYTICS" "max_connections = 10000 + parallelismo aggressivo" 3>&1 1>&2 2>&3) || show_error "Operazione annullata." p="$sel" else echo "Profili: PRIVATO (100), SHARED (10000), ANALYTICS (10000)" read -r -p "Profilo [PRIVATO/SHARED/ANALYTICS]: " p fi case "$p" in PRIVATO|SHARED|ANALYTICS) echo "$p" ;; *) show_error "Profilo non valido" ;; esac } # --- Calcolo parametri compute_tuning() { local RAM_MB="$1" MAX_CONN="$2" CORES="$3" PROFILE="$4" local sb=$(( RAM_MB / 4 )) [ "$sb" -lt 512 ] && sb=512 [ "$sb" -gt 65536 ] && sb=65536 SHARED_BUFFERS_MB=$sb local ecs=$(( (RAM_MB * 70) / 100 )) [ "$ecs" -lt 512 ] && ecs=512 EFFECTIVE_CACHE_SIZE_MB=$ecs local mwm=$(( (RAM_MB * 5) / 100 )) [ "$mwm" -lt 64 ] && mwm=64 [ "$mwm" -gt 4096 ] && mwm=4096 MAINTENANCE_WORK_MEM_MB=$mwm local wm=$(( (RAM_MB / 4) / (MAX_CONN * 2) )) [ "$wm" -lt 1 ] && wm=1 local wm_cap=64 case "$PROFILE" in SHARED) wm_cap=8 ;; ANALYTICS) wm_cap=128 ;; esac [ "$wm" -gt "$wm_cap" ] && wm="$wm_cap" WORK_MEM_MB=$wm local avw=3 if [ "$PROFILE" = "ANALYTICS" ]; then local mwp=$(( CORES * 3 )) [ "$mwp" -lt 12 ] && mwp=12 [ "$mwp" -gt 96 ] && mwp=96 MAX_WORKER_PROCESSES=$mwp local mpw=$(( mwp - avw - 2 )) local mpw_alt=$(( CORES * 3 )) [ "$mpw" -gt "$mpw_alt" ] && mpw=$mpw_alt [ "$mpw" -lt 4 ] && mpw=4 MAX_PARALLEL_WORKERS=$mpw local mpwpg=$(( CORES / 2 )) [ "$mpwpg" -lt 3 ] && mpwpg=3 [ "$mpwpg" -gt 6 ] && mpwpg=6 MAX_PARALLEL_WORKERS_PER_GATHER=$mpwpg local mpmw=$(( CORES / 2 )) [ "$mpmw" -lt 3 ] && mpmw=3 [ "$mpmw" -gt 6 ] && mpmw=6 MAX_PARALLEL_MAINT_WORKERS=$mpmw PARALLEL_SETUP_COST="200" PARALLEL_TUPLE_COST="0.06" MIN_PARALLEL_TABLE_SCAN_SIZE="'16MB'" MIN_PARALLEL_INDEX_SCAN_SIZE="'32MB'" DEFAULT_STATISTICS_TARGET="2000" else local mwp=$(( CORES * 2 )) [ "$mwp" -lt 8 ] && mwp=8 [ "$mwp" -gt 64 ] && mwp=64 MAX_WORKER_PROCESSES=$mwp local mpw=$(( mwp - avw - 2 )) local mpw_alt=$(( CORES * 2 )) [ "$mpw" -gt "$mpw_alt" ] && mpw=$mpw_alt [ "$mpw" -lt 2 ] && mpw=2 MAX_PARALLEL_WORKERS=$mpw local mpwpg=$(( CORES / 2 )) [ "$mpwpg" -lt 2 ] && mpwpg=2 [ "$mpwpg" -gt 4 ] && mpwpg=4 MAX_PARALLEL_WORKERS_PER_GATHER=$mpwpg local mpmw=$(( CORES / 2 )) [ "$mpmw" -lt 2 ] && mpmw=2 [ "$mpmw" -gt 4 ] && mpmw=4 MAX_PARALLEL_MAINT_WORKERS=$mpmw PARALLEL_SETUP_COST="500" PARALLEL_TUPLE_COST="0.08" MIN_PARALLEL_TABLE_SCAN_SIZE="'32MB'" MIN_PARALLEL_INDEX_SCAN_SIZE="'64MB'" DEFAULT_STATISTICS_TARGET="1000" fi } main() { PG_VERSION=$(get_postgres_version) PROFILE=$(get_connection_profile) case "$PROFILE" in PRIVATO) MAX_CONN=100 ;; SHARED) MAX_CONN=10000 ;; ANALYTICS) MAX_CONN=10000 ;; esac RAM_MB=$(detect_ram_mb) CPU_CORES=$(detect_cpu_cores) [ "$RAM_MB" -lt 1024 ] && RAM_MB=1024 [ "$CPU_CORES" -lt 1 ] && CPU_CORES=1 CONF_DIR="/etc/postgresql/${PG_VERSION}/main" POSTGRESQL_CONF="${CONF_DIR}/postgresql.conf" [ -f "$POSTGRESQL_CONF" ] || show_error "File ${POSTGRESQL_CONF} non trovato." compute_tuning "$RAM_MB" "$MAX_CONN" "$CPU_CORES" "$PROFILE" # include_if_exists sempre if ! grep -Eiq "^[[:space:]]*include_if_exists[[:space:]]*=.*zucchetti\.conf" "$POSTGRESQL_CONF"; then printf "\ninclude_if_exists = 'zucchetti.conf'\n" >> "$POSTGRESQL_CONF" fi ZUCC_CONF="${CONF_DIR}/zucchetti.conf" echo "Scrivo ${ZUCC_CONF}..." cat > "$ZUCC_CONF" <