Configurer des alertes et des notifications en cas de souci

On veut tous être au courant des problèmes avant que notre serveur ne plante ou ne rende la vie difficile aux utilisateurs. PostgreSQL propose des outils pour créer des notifications et lancer des tâches : pg_notify et pg_cron. En gros, c’est notre réveil et notre planificateur perso pour la base de données.

Imagine : t’as une base de données pour un cours d’échange de devises, et d’un coup, un des process bloque les autres. Plutôt que de checker l’état de la base à la main tout le temps, tu peux configurer des alertes pour rester au courant. Et pour checker régulièrement l’état de la base, pg_cron est super utile. On va voir tout ça ensemble.

Notifications instantanées depuis la base : pg_notify

On commence avec pg_notify. C’est une fonction intégrée à PostgreSQL qui permet d’envoyer une notification depuis la base vers un "canal" précis. Tu peux t’en servir pour signaler des événements comme la fin de requêtes longues, la détection de blocages ou d’autres situations bizarres.

La syntaxe de pg_notify est super simple :

NOTIFY <channel>, <message>;

• channel — c’est le nom du canal où tu envoies la notif.
• message — la chaîne de texte de la notification.

Voici un exemple d’utilisation de pg_notify. On va créer une notif pour le cas où un blocage est détecté :

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', 'Blocage dans la base de données !');
    END IF;
END $$;

Ce code vérifie s’il y a un blocage non traité et envoie une notif sur le canal alerts.

Pour écouter les notifications, utilise la commande LISTEN dans une autre connexion :

LISTEN alerts;

Maintenant, si pg_notify envoie un message sur le canal alerts, tu verras la notif dans ta console.

Exemple :

NOTIFY alerts, 'Hey, y a un blocage ici !';

Sur l’autre connexion où tu as fait LISTEN alerts, tu reçois direct :
NOTIFY: Hey, y a un blocage ici !

L’utilisation de pg_notify ne se limite pas aux notifications simples. Par exemple, tu peux le lier à des triggers pour des alertes automatiques quand des données sont ajoutées, modifiées ou supprimées :

Notification sur les nouveaux enregistrements

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', 'Nouvel enregistrement ajouté dans la table !');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Maintenant, dès qu’une nouvelle ligne est ajoutée dans la table your_table, tu reçois une notif direct.

Tu en sauras plus sur les triggers et les fonctions intégrées dans quelques chapitres :P

Erreurs classiques et comment les éviter

Si tu utilises LISTEN mais que tu ne vois pas les notifications, vérifie :

1. Si tu bosses dans la même connexion que celle qui envoie les notifications.
2. Si le nom du canal est bien correct.
3. Si tu appelles pg_notify dans une transaction qui a bien été validée (COMMIT).

Le planificateur de tâches dans PostgreSQL : pg_cron

pg_cron — c’est une extension pour PostgreSQL qui permet d’exécuter des tâches à intervalle régulier, comme le bon vieux cron sous Linux. Par exemple, tu peux planifier un check régulier des blocages ou la collecte de stats.

Créer des tâches avec pg_cron

Maintenant que pg_cron est installé et prêt, créons une tâche qui va nettoyer chaque jour les vieux enregistrements de la table logs.

SELECT cron.schedule('Suppression des vieux logs',
'0 0 * * *',
$$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$);

Explication de ce qui se passe ici :

• '0 0 * * *' — c’est le planning d’exécution (tous les jours à minuit).
• DELETE FROM logs ... — c’est la requête SQL que cron va exécuter.

Voir les tâches

Pour voir toutes les tâches gérées par pg_cron, utilise :

SELECT * FROM cron.job;

Désactiver des tâches

Pour désactiver une tâche :

SELECT cron.unschedule(jobid);

Où jobid — c’est l’identifiant de la tâche. Tu peux le trouver dans la table cron.job.

Exemples utiles avec pg_cron

Vérification régulière de l’activité des requêtes

Créons une tâche qui va checker toutes les 5 minutes les requêtes qui tournent trop longtemps :

SELECT cron.schedule('Vérification des requêtes longues',
'*/5 * * * *',
$$ SELECT pid, query, state
    FROM pg_stat_activity
    WHERE state = 'active'
        AND now() - query_start > INTERVAL '5 minutes' $$);

Cette tâche cherche les requêtes qui tournent depuis plus de 5 minutes.

Intégration avec des systèmes externes

Et pg_notify, et pg_cron peuvent s’intégrer avec des systèmes externes comme Slack, Telegram ou des outils de monitoring (genre Prometheus).

Telegram

Tu peux combiner pg_notify avec un bot Telegram pour envoyer des notifications. L’idée, c’est d’écrire un script en Python ou autre qui écoute les notifications et les balance sur Telegram.

Exemple de bot Python tout simple :

import psycopg2
import telegram

# Connexion à PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# Création du bot Telegram
bot = telegram.Bot(token='your_telegram_bot_token')

# Ouvre un curseur pour écouter
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# Écoute les notifications
print("On écoute les notifications...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("Notification reçue :", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

Maintenant, ton bot va recevoir les notifications envoyées via pg_notify.

Quand utiliser pg_notify et pg_cron ?

Utilise pg_notify pour réagir direct (genre prévenir l’admin en cas de blocage).

Utilise pg_cron pour les tâches régulières (check de l’activité, nettoyage des vieilles données).

Remarques et pièges à éviter

pg_notify balance les notifications instantanément, mais il ne garde pas d’historique. Mieux vaut l’intégrer avec des logs fichiers ou des systèmes externes.

pg_cron peut causer une charge inattendue si les tâches tournent trop souvent. Teste toujours tes requêtes avant de les mettre dans le planning.

Maintenant, t’es prêt à optimiser ton monitoring et à automatiser la gestion de ta base de données. Go — configure tes alertes pour devenir non seulement un SQL dev, mais un vrai DBA !