In questa lezione vedremo come amministrare il nostro database PostgreSQL attraverso i suoi principali file di configurazione, ognuno con uno scopo ben preciso:

  • postgresql.conf - parametri generali del server
  • pg_hba.conf - autenticazione e autorizzazione delle connessioni client
  • pg_ident.conf - mappatura tra utenti del sistema operativo e ruoli PostgreSQL

Vedremo inoltre come modificare le configurazioni, capire se una modifica richiede un semplice reload oppure un restart, monitorare le sessioni attive e intervenire quando una query deve essere annullata.

Al termine della lezione sarai in grado di:

  • individuare i file di configurazione utilizzati dalla tua installazione
  • capire quale ruolo svolge ciascun file
  • modificare i parametri di configurazione in modo consapevole
  • distinguere quando è necessario un reload o un restart
  • individuare le sessioni e le query in esecuzione
  • annullare una query o terminare una sessione quando necessario

Dove si trovano i file di configurazione?

Nella lezione precedente abbiamo visto come installare PostgreSQL nei vari sistemi operativi.

I file di configurazione non si trovano necessariamente tutti nella stessa directory e il loro percorso può variare in base al sistema operativo e alla modalità di installazione. Per questo è preferibile non affidarsi a percorsi predefiniti, ma chiedere direttamente a PostgreSQL dove si trovano.

Possiamo ottenere le informazioni dalla vista di sistema pg_settings:

SELECT name, setting
FROM pg_settings
WHERE category = 'File Locations';

Il risultato mostrerà i percorsi assoluti di tutti i file di configurazione rilevanti. Un output tipico potrebbe essere:

| name           | setting                                   |
| -------------- | ----------------------------------------- |
| config_file    | `/etc/postgresql/18/main/postgresql.conf` |
| data_directory | `/var/lib/postgresql/18/main`             |
| hba_file       | `/etc/postgresql/18/main/pg_hba.conf`     |
| ident_file     | `/etc/postgresql/18/main/pg_ident.conf`   |

I percorsi cambiano in base alla distribuzione e alla modalità di installazione, quindi è sempre meglio utilizzare i valori restituiti dalla propria istanza.

Puoi anche ottenere direttamente il percorso dei singoli file:

SHOW config_file;
SHOW hba_file;
SHOW ident_file;
SHOW data_directory;

I principali file di configurazione

postgresql.conf

È il file principale della configurazione del server. Contiene centinaia di parametri che controllano il comportamento del server: memoria, connessioni, logging e molto altro. La maggior parte delle impostazioni che modificherai nella tua attività di amministrazione si trovano qui.

I parametri sono organizzati in sezioni tematiche e la maggior parte è commentata con il valore predefinito. Un esempio tipico:

# work_mem = 4MB          # valore predefinito (commentato)
work_mem = 64MB            # valore personalizzato (attivo)

pg_hba.conf

Il file pg_hba.conf (Host-Based Authentication) definisce quali connessioni possono essere accettate e quale metodo di autenticazione deve essere utilizzato.

Ogni regola contiene diversi campi. Una forma semplificata è:

| tipo    | database | utente     | indirizzo        | metodo          |
| ------- | -------- | ---------- | ---------------- | --------------- |
| `local` | `all`    | `postgres` | *(nessuno)*      | `peer`          |
| `host`  | `all`    | `all`      | `127.0.0.1/32`   | `scram-sha-256` |
| `host`  | `mydb`   | `myuser`   | `192.168.1.0/24` | `scram-sha-256` |

Il campo indirizzo viene utilizzato per le connessioni di tipo host; per le connessioni local tramite socket Unix non viene specificato.

Una regola può quindi essere letta, ad esempio, come:

host    mydb    myuser    192.168.1.0/24    scram-sha-256

ovvero:

consenti connessioni TCP al database mydb, per l'utente myuser, provenienti da indirizzi appartenenti alla rete 192.168.1.0/24, utilizzando scram-sha-256 per l'autenticazione.

Un aspetto fondamentale è l'ordine delle regole:

PostgreSQL valuta le regole dall'alto verso il basso e utilizza la prima regola che corrisponde alla connessione. Non continua a cercare una regola successiva più specifica.

Per approfondire il significato dei singoli campi puoi consultare la guida a pg_hba.conf.

Dopo una modifica a pg_hba.conf è normalmente sufficiente ricaricare la configurazione:

SELECT pg_reload_conf();

pg_ident.conf

Il file pg_ident.conf permette di definire una mappatura tra gli utenti del sistema operativo e i ruoli PostgreSQL.

Viene utilizzato insieme ai metodi di autenticazione peer e ident quando è necessario stabilire quale ruolo PostgreSQL corrisponde a un determinato utente del sistema operativo.

In molte installazioni standard non è necessario modificarlo, ma diventa utile in configurazioni che richiedono una mappatura esplicita.

Come modificare la configurazione

Puoi modificare postgresql.conf direttamente con un editor di testo, ma PostgreSQL mette a disposizione anche il comando ALTER SYSTEM.

Ad esempio:

ALTER SYSTEM SET work_mem = '64MB';

Questo comando non modifica direttamente postgresql.conf. La modifica viene invece scritta nel file:

postgresql.auto.conf

Questo file viene letto insieme alla configurazione principale e le impostazioni presenti al suo interno hanno precedenza sulle impostazioni corrispondenti di postgresql.conf.

Per questo motivo è importante sapere da dove proviene il valore effettivamente utilizzato.

Possiamo verificarlo tramite pg_settings:

SELECT
    name,
    setting,
    source,
    sourcefile
FROM pg_settings
WHERE name = 'work_mem';

Le colonne source e sourcefile ci aiutano a capire quale origine ha il valore attuale.

Un esempio:

| name       | setting  | source     | sourcefile  |
| ---------- |----------|------------|-------------|
| `work_mem` | `4096`   | `default`  | `null`      |

Per ripristinare un parametro precedentemente impostato con ALTER SYSTEM:

ALTER SYSTEM RESET work_mem;

Per rimuovere tutte le impostazioni gestite tramite ALTER SYSTEM:

ALTER SYSTEM RESET ALL;

ALTER SYSTEM è uno strumento utile per gestire la configurazione, ma non significa che ogni modifica diventi attiva immediatamente. Anche in questo caso bisogna verificare il context del parametro e, quando necessario, eseguire un reload o un restart.

Riavvio o reload?

Non tutte le modifiche alla configurazione richiedono un riavvio completo del server.

Molti parametri possono diventare effettivi con un semplice reload, che rilegge i file di configurazione senza interrompere le connessioni esistenti:

SELECT pg_reload_conf();

Il modo più affidabile per sapere quale operazione è necessaria è controllare la colonna context di pg_settings.

Ad esempio:

SELECT
    name,
    context,
    setting,
    unit
FROM pg_settings
WHERE name IN (
    'work_mem',
    'shared_buffers',
    'max_connections',
    'listen_addresses'
);

I valori più importanti da conoscere sono:

  • user - il parametro può essere modificato a livello di sessione;
  • superuser - il parametro può essere modificato a livello di sessione da un superutente;
  • sighup - la nuova configurazione richiede un reload;
  • postmaster - la nuova configurazione richiede un restart del server.

Ad esempio, shared_buffers richiede un restart, mentre log_min_duration_statement può essere applicato con un reload.

ll comportamento da adottare dipende dal singolo parametro.

Se vuoi approfondire come interpretare questi valori, trovi la guida dedicata a postgresql.conf

Verificare se è necessario un restart

Dopo aver modificato un parametro che richiede un restart, PostgreSQL può indicare che il valore attuale non corrisponde ancora a quello configurato.

Puoi verificare i parametri che hanno un riavvio in sospeso con:

SELECT
    name,
    setting,
    pending_restart
FROM pg_settings
WHERE pending_restart = true;

Questo è particolarmente utile dopo aver utilizzato ALTER SYSTEM.

Verificare la presenza di errori nella configurazione

Quando si modificano i file di configurazione è utile anche verificare se PostgreSQL ha trovato direttive non valide o non applicabili.

La vista pg_file_settings permette di analizzare le impostazioni presenti nei file di configurazione:

SELECT
    name,
    setting,
    applied,
    error
FROM pg_file_settings
WHERE error IS NOT NULL;

Se la query non restituisce righe, non risultano errori nelle impostazioni analizzate.

La colonna applied può inoltre aiutare a individuare impostazioni presenti nei file ma non effettivamente applicate, ad esempio perché una configurazione successiva ha sovrascritto quella precedente.

Gestire le query inefficienti

A volte può capitare che una query richieda più tempo del previsto, consumando risorse e bloccando altri processi. In questi casi è possibile individuarla e interromperla senza riavviare il server.

Identificare le query in esecuzione

La vista pg_stat_activity mostra tutte le connessioni attive e le query in corso:

SELECT
    pid,
    usename,
    application_name,
    state,
    now() - query_start AS durata,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY durata DESC;

Presta attenzione alla colonna durata: query con tempi molto elevati sono probabilmente candidate all'interruzione.

Interrompere una query

Una volta identificato il process ID (pid) della query problematica, hai due opzioni:

⚠️ Usa il codice con cautela

-- Annulla solo la query in corso, mantenendo la connessione attiva
SELECT pg_cancel_backend(1234);

⚠️ Usa il codice con cautela

-- Termina l'intera connessione (più drastico)
SELECT pg_terminate_backend(1234);

Differenza tra i due comandi:

  • pg_cancel_backend invia un segnale di interruzione alla query. La connessione rimane aperta e il client riceverà un errore.
  • pg_terminate_backend chiude forzatamente la connessione. Usalo solo se pg_cancel_backend() non ha avuto effetto.

⚠️ Attenzione: entrambi i comandi richiedono privilegi di superutente (o il ruolo pg_signal_backend su PostgreSQL 14+). Usali con cautela in ambienti di produzione.

Prossima lezione

Nella Lezione 4 vedremo come creare i ruoli, il meccanismo centrale per gestire autenticazione e autorizzazioni.

Fonti e approfondimenti