Configurazione di alert e notifiche quando ci sono problemi

Tutti vogliamo sapere dei problemi prima che il nostro server vada giù o renda la vita difficile agli utenti. PostgreSQL ti dà gli strumenti per creare notifiche e lanciare task: pg_notify e pg_cron. Sono tipo la nostra sveglia e il nostro scheduler personale per il database.

Immagina questa situazione: hai un database per un corso di cambio valuta e all’improvviso uno dei processi blocca tutti gli altri. Invece di controllare manualmente lo stato del db ogni volta, puoi settare delle notifiche per essere sempre aggiornato. E per i controlli regolari sullo stato del database, pg_cron è perfetto. Vediamo tutto nel dettaglio.

Notifiche veloci dal database: pg_notify

Partiamo da pg_notify. È una funzione built-in di PostgreSQL che ti permette di mandare una notifica dal database su un certo "canale". Puoi usarla per segnalare eventi tipo la fine di query lunghe, il rilevamento di lock o altre situazioni strane.

La sintassi di pg_notify è super semplice:

NOTIFY <channel>, <message>;

• channel — il nome del canale su cui mandi la notifica.
• message — la stringa con il testo della notifica.

Ecco un esempio di come usare pg_notify. Facciamo una notifica quando viene trovato un lock:

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_locks l
        JOIN pg_stat_activity a
        ON l.pid = a.pid
        WHERE NOT l.granted
    ) THEN
        PERFORM pg_notify('alerts', 'Blocco nel database!');
    END IF;
END $$;

Questo codice controlla se c’è un lock non gestito e manda una notifica sul canale alerts.

Per ascoltare le notifiche, usa il comando LISTEN su un’altra connessione:

LISTEN alerts;

Ora, se pg_notify manda un messaggio sul canale alerts, vedrai la notifica in console.

Esempio:

NOTIFY alerts, 'Ehi, c’è un blocco!';

Sull’altra connessione dove hai fatto LISTEN alerts, riceverai subito:
NOTIFY: Ehi, c’è un blocco!

L’uso di pg_notify non si limita alle notifiche semplici. Puoi anche collegarlo ai trigger per notifiche automatiche quando vengono aggiunti, modificati o cancellati dati:

Notifica su nuovi record

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', 'Nuovo record aggiunto alla tabella!');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER record_added
AFTER INSERT ON your_table
FOR EACH ROW EXECUTE FUNCTION notify_new_record();

Ora, ogni volta che aggiungi un nuovo record nella tabella your_table, ricevi subito una notifica.

Scoprirai di più su trigger e funzioni integrate tra qualche lezione :P

Errori tipici e come evitarli

Se usi LISTEN ma non vedi notifiche, controlla:

1. Sei sulla stessa connessione da cui mandi le notifiche?
2. Hai scritto giusto il nome del canale?
3. Hai chiamato pg_notify dentro una transazione che è stata committata (COMMIT)?

Scheduler di task in PostgreSQL: pg_cron

pg_cron è un’estensione per PostgreSQL che ti permette di eseguire task a orari programmati, proprio come il classico cron su Linux. Ad esempio, puoi schedulare controlli regolari sui lock o la raccolta di statistiche.

Creare task con pg_cron

Ora che pg_cron è installato e pronto, creiamo un task che ogni giorno pulisce i record vecchi dalla tabella logs.

SELECT cron.schedule('Cancellazione dei log vecchi',
'0 0 * * *',
$$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$);

Ecco cosa succede qui:

• '0 0 * * *' — è la schedule del comando (ogni giorno a mezzanotte).
• DELETE FROM logs ... — è la query SQL che cron eseguirà.

Vedere i task

Per vedere tutti i task schedulati con pg_cron, usa:

SELECT * FROM cron.job;

Disattivare i task

Per spegnere un task:

SELECT cron.unschedule(jobid);

Dove jobid è l’id del task. Puoi trovarlo nella tabella cron.job.

Esempi utili con pg_cron

Controllo regolare delle query attive

Facciamo un task che ogni 5 minuti controlla le query che durano troppo:

SELECT cron.schedule('Controllo delle query lunghe',
'*/5 * * * *',
$$ SELECT pid, query, state
    FROM pg_stat_activity
    WHERE state = 'active'
        AND now() - query_start > INTERVAL '5 minutes' $$);

Questo task cerca le query che girano da più di 5 minuti.

Integrazione con sistemi esterni

Sia pg_notify che pg_cron possono essere integrati con sistemi esterni come Slack, Telegram o sistemi di monitoring (tipo Prometheus).

Telegram

Puoi collegare pg_notify a un bot Telegram per mandare notifiche. L’idea base è scrivere uno script in Python o altro linguaggio che ascolta le notifiche e le manda su Telegram.

Esempio di bot Python semplice:

import psycopg2
import telegram

# Connessione a PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# Creazione del bot Telegram
bot = telegram.Bot(token='your_telegram_bot_token')

# Apriamo il cursore per ascoltare
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# Ascoltiamo le notifiche
print("Ascoltiamo le notifiche...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("Notifica ricevuta:", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

Ora il tuo bot riceverà le notifiche mandate tramite pg_notify.

Quando usare pg_notify e pg_cron?

Usa pg_notify per reazioni immediate (tipo avvisare l’admin di un blocco).

Usa pg_cron per task regolari (controllo delle query attive, pulizia dei dati vecchi).

Note e insidie

pg_notify manda notifiche subito, ma non tiene uno storico. Meglio integrarlo con log file o sistemi esterni.

pg_cron può creare carico inaspettato se i task girano troppo spesso. Testa sempre le query prima di metterle in schedule.

Ora sei pronto per ottimizzare il tuo monitoring e automatizzare la gestione del database. Vai — configura gli alert e diventa non solo un SQL developer, ma un vero DBA!