tuning-postgres

Script Bash per applicare un tuning automatico di PostgreSQL (solo configurazione, niente installazione o spostamento dati), pensato in particolare per ambienti ERP (es. Zucchetti Infinity/AdHoc), ma utilizzabile in qualsiasi contesto.

Supporta:

• PostgreSQL 14 / 15 / 16
• Sistemi Debian/Ubuntu (layout postgresql-common, es. /etc/postgresql/16/main)
• Profili di carico:
  • PRIVATO
  • SHARED
  • ANALYTICS

⚠️ Lo script deve essere eseguito come root.

---

Cosa fa lo script

1. Rileva automaticamente le versioni PostgreSQL installate in:

   /etc/postgresql/14/main
   /etc/postgresql/15/main
   /etc/postgresql/16/main
   

e ti chiede, tramite whiptail, quale versione vuoi tunare.

Chiede il profilo di utilizzo del DB:

PRIVATO → istanza singola, poche connessioni (max_connections contenuto)

SHARED → istanza multi-tenant, molte connessioni

ANALYTICS → carichi analitici pesanti, parallelismo più aggressivo

Rileva in automatico:

RAM (MB) dalla macchina (da /proc/meminfo)

Numero core CPU (da nproc o getconf)

Calcola i parametri di tuning in base a:

RAM totale

Numero core CPU

Profilo scelto

max_connections associato al profilo

Scrive (o sovrascrive) il file:

/etc/postgresql//main/zucchetti.conf con tutti i parametri di tuning (memoria, cache, WAL, autovacuum, parallel query, ecc.).

Si assicura che in:

/etc/postgresql//main/postgresql.conf sia presente:

include_if_exists = 'zucchetti.conf' in modo che il tuning venga caricato automaticamente all’avvio.

Riavvia il servizio:

systemctl restart postgresql@-main Parametri configurati Connessioni max_connections

PRIVATO → 100

SHARED → 10000

ANALYTICS → 10000

Memoria Calcolati in modo proporzionale alla RAM totale:

shared_buffers ≈ 25% della RAM

effective_cache_size ≈ 70% della RAM

maintenance_work_mem ≈ 5% della RAM (max 4096MB)

work_mem ≈ (25% RAM) / (max_connections × 2), con limiti:

PRIVATO: max 64MB

SHARED: max 8MB

ANALYTICS: max 128MB

WAL e Checkpoint checkpoint_completion_target = 0.9

wal_buffers = -1

min_wal_size = 1GB

max_wal_size = 4GB

Planner & IO default_statistics_target

ANALYTICS: 2000

altri profili: 1000

random_page_cost = 1.1

effective_io_concurrency = 200

Autovacuum Parametri pensati per ambienti ERP:

autovacuum = on

autovacuum_analyze_scale_factor = 0.1

autovacuum_analyze_threshold = 500

autovacuum_vacuum_scale_factor = 0.2

autovacuum_vacuum_threshold = 1500

autovacuum_freeze_max_age = 200000000

autovacuum_multixact_freeze_max_age = 400000000

autovacuum_naptime = 5s

autovacuum_vacuum_cost_delay = 20ms

autovacuum_vacuum_cost_limit = -1

autovacuum_work_mem = -1

Parallel Query Tutti i profili abilitano il parallelismo, con intensità diversa:

max_worker_processes

max_parallel_workers

max_parallel_workers_per_gather

max_parallel_maintenance_workers

parallel_setup_cost

parallel_tuple_cost

min_parallel_table_scan_size

min_parallel_index_scan_size

Nel profilo ANALYTICS questi valori sono più aggressivi (maggiore parallelismo, costi minori, soglie più basse).

Requisiti Sistema operativo: Debian/Ubuntu (o derivati con layout analogo)

PostgreSQL 14/15/16 installato con struttura:

/etc/postgresql//main/postgresql.conf

Gestione servizi tramite systemd:

postgresql@-main

Pacchetti:

whiptail (opzionale, ma consigliato per l’interfaccia testuale)

Installazione Copia lo script nel server (ad esempio in /root):

scp tuning-postgres.sh root@server:/root/ chmod +x /root/tuning-postgres.sh Utilizzo Esegui lo script come root:

sudo /root/tuning-postgres.sh Flusso tipico:

Seleziona la versione PostgreSQL (14/15/16) tramite menù whiptail.

Seleziona il profilo (PRIVATO, SHARED, ANALYTICS).

Lo script calcola il tuning in base alle risorse della VM.

Viene generato / aggiornato zucchetti.conf.

Il servizio PostgreSQL della versione scelta viene riavviato.

Verifiche consigliate Dopo l’esecuzione:

cat /etc/postgresql/16/main/zucchetti.conf Controlla che contenga:

il profilo corretto (Profilo: PRIVATO/SHARED/ANALYTICS)

i valori di RAM/CPU attesi

la sezione # ===== Parallel Query =====

Verifica anche che il postgresql.conf includa:

include_if_exists = 'zucchetti.conf' e che il servizio sia attivo:

systemctl status postgresql@16-main Note operative Lo script non modifica i dati del cluster, né utenti né password.

Puoi rieseguirlo in qualsiasi momento (ad es. dopo upgrade di RAM/CPU o cambio profilo).

È pensato come tuning “base intelligente” da cui partire: in casi particolari (tabelle enormi, query molto specifiche) può essere comunque utile ottimizzare manualmente indici o parametri aggiuntivi.