Configuración de alertas y notificaciones cuando hay problemas

Todos queremos enterarnos de los problemas antes de que nuestro servidor se caiga o le complique la vida a los usuarios. PostgreSQL nos da herramientas para crear notificaciones y lanzar tareas: pg_notify y pg_cron. Básicamente, son como nuestro despertador y planificador personal para la base de datos.

Imagina esto: tienes una base de datos para un curso de cambio de divisas, y de repente uno de los procesos bloquea a los demás. En vez de estar revisando el estado de la base a mano todo el rato, puedes configurar alertas para estar al tanto. Y para chequear el estado de la base de forma regular, pg_cron viene genial. Vamos a ver cómo se hace todo esto.

Notificaciones rápidas desde la base de datos: pg_notify

Empezamos con pg_notify. Es una función nativa de PostgreSQL que te deja mandar una notificación desde la base de datos a un "canal" concreto. Puedes usarla para avisar de eventos como consultas largas, bloqueos detectados u otras situaciones raras.

La sintaxis de pg_notify es bastante sencilla:

NOTIFY <canal>, <mensaje>;

• canal — es el nombre del canal al que mandas la notificación.
• mensaje — es el texto de la notificación.

Vamos con un ejemplo de pg_notify. Vamos a crear una notificación para cuando se detecta un bloqueo:

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', '¡Bloqueo en la base de datos!');
    END IF;
END $$;

Este código comprueba si hay algún bloqueo pendiente y manda una notificación al canal alerts.

Para escuchar las notificaciones, usa el comando LISTEN en otra conexión:

LISTEN alerts;

Ahora, si pg_notify manda un mensaje al canal alerts, verás la notificación en la consola.

Ejemplo:

NOTIFY alerts, '¡Oye, aquí hay un bloqueo!';

En la otra conexión donde ejecutaste LISTEN alerts, recibirás al instante:
NOTIFY: ¡Oye, aquí hay un bloqueo!

El uso de pg_notify no se limita a notificaciones simples. Por ejemplo, puedes conectarlo con triggers para avisos automáticos cuando se añaden, cambian o eliminan datos:

Notificación sobre nuevos registros

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', '¡Nuevo registro añadido a la tabla!');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Ahora, si se añade un nuevo registro a la tabla your_table, recibirás la notificación al momento.

Aprenderás más sobre triggers y funciones nativas en solo un par de niveles más :P

Errores típicos y cómo evitarlos

Si usas LISTEN pero no ves notificaciones, revisa:

1. Si estás en la misma conexión desde la que se mandan las notificaciones.
2. Si el canal está bien escrito.
3. Asegúrate de que llamas a pg_notify dentro de una transacción que se ha hecho COMMIT.

Planificador de tareas en PostgreSQL: pg_cron

pg_cron es una extensión para PostgreSQL que te deja ejecutar tareas programadas, igual que el cron clásico de Linux. Por ejemplo, puedes programar chequeos regulares de bloqueos o recolectar estadísticas.

Crear tareas con pg_cron

Ahora que pg_cron está instalado y listo, vamos a crear una tarea que borre registros antiguos de la tabla logs cada día.

SELECT cron.schedule('Borrado de logs antiguos',
'0 0 * * *',
$$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$);

¿Qué pasa aquí?

• '0 0 * * *' — es el horario de ejecución (cada día a medianoche).
• DELETE FROM logs ... — es la consulta SQL que cron va a ejecutar.

Ver tareas

Para ver todas las tareas programadas con pg_cron, usa:

SELECT * FROM cron.job;

Desactivar tareas

Puedes desactivar una tarea con:

SELECT cron.unschedule(jobid);

Donde jobid es el identificador de la tarea. Puedes verlo en la tabla cron.job.

Ejemplos útiles con pg_cron

Chequeo regular de actividad de consultas

Vamos a crear una tarea que cada 5 minutos revise si hay consultas que llevan mucho tiempo ejecutándose:

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

Esta tarea busca consultas que llevan más de 5 minutos ejecutándose.

Integración con sistemas externos

Tanto pg_notify como pg_cron pueden integrarse con sistemas externos como Slack, Telegram o sistemas de monitorización (por ejemplo, Prometheus).

Telegram

Puedes combinar pg_notify con un bot de Telegram para enviar notificaciones. La idea es escribir un script en Python u otro lenguaje que escuche las notificaciones y las mande a Telegram.

Ejemplo de un bot sencillo en Python:

import psycopg2
import telegram

# Conexión a PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# Crear el bot de Telegram
bot = telegram.Bot(token='your_telegram_bot_token')

# Abrimos cursor para escuchar
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# Escuchamos notificaciones
print("Escuchando notificaciones...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("Notificación recibida:", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

Ahora tu bot recibirá las notificaciones enviadas por pg_notify.

¿Cuándo usar pg_notify y pg_cron?

Usa pg_notify para reacciones instantáneas (por ejemplo, avisar al admin de bloqueos).

Usa pg_cron para tareas regulares (chequear actividad de consultas, limpiar datos antiguos).

Notas y trampas

pg_notify genera notificaciones al instante, pero no guarda el historial. Mejor intégralo con logs de archivos o sistemas externos.

pg_cron puede causar carga inesperada si las tareas se ejecutan demasiado seguido. Siempre prueba las consultas antes de ponerlas en el cron.

¡Ahora ya puedes optimizar tu monitorización y automatizar la gestión de tu base de datos! Adelante — configura alertas y conviértete no solo en programador SQL, sino en DBA!