Setting Up Alerts and Notifications for Issues

We all want to know about problems before our server crashes or makes life harder for users. PostgreSQL gives us tools to create notifications and run tasks: pg_notify and pg_cron. Basically, these are our personal alarm clock and scheduler for the database.

Imagine this: you have a currency exchange course database, and suddenly one of the processes locks up the rest. Instead of constantly checking the database state by hand, you can set up alerts to stay in the loop. And for regular health checks, pg_cron comes in handy. Let’s break it all down now.

Quick Notifications from the Database: pg_notify

Let’s start with pg_notify. This is a built-in PostgreSQL function that lets you send a notification from the database to a specific "channel." You can use it to signal events like long-running queries finishing, detecting locks, or other unusual situations.

The pg_notify syntax is pretty simple:

NOTIFY <channel>, <message>;

• channel — the name of the channel where the notification is sent.
• message — a string with the notification text.

Here’s an example of using pg_notify. Let’s create a notification for when a lock is detected:

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', 'Lock detected in the database!');
    END IF;
END $$;

This code checks if there’s an ungranted lock and sends a notification to the alerts channel.

To listen for notifications, use the LISTEN command in another connection:

LISTEN alerts;

Now, if pg_notify sends a message to the alerts channel, you’ll see the notification in your console.

Example:

NOTIFY alerts, 'Hey, there’s a lock!';

On another connection where you ran LISTEN alerts, you’ll instantly get:
NOTIFY: Hey, there’s a lock!

The use of pg_notify isn’t limited to simple notifications. For example, you can hook it up to triggers for automatic alerts about added, changed, or deleted data:

Notification about new records

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', 'A new record was added to the table!');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

Now, if a new record is added to the your_table table, you’ll get a notification right away.

You’ll learn more about triggers and built-in functions in just a couple more levels :P

Common Mistakes and How to Avoid Them

If you’re using LISTEN but not seeing notifications, check:

1. Are you working in the same connection where notifications are sent from?
2. Did you specify the channel name correctly?
3. Make sure you’re calling pg_notify inside a transaction that was committed (COMMIT).

Task Scheduler in PostgreSQL: pg_cron

pg_cron is an extension for PostgreSQL that lets you run tasks on a schedule, just like classic cron in Linux. For example, you can set up regular checks for locks or collect stats.

Creating Tasks with pg_cron

Now that pg_cron is installed and ready, let’s create a task that will clean up old records in the logs table every day.

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

Here’s what’s going on:

• '0 0 * * *' — this is the schedule (every day at midnight).
• DELETE FROM logs ... — this is the SQL query cron will run.

Viewing Tasks

To see all tasks running through pg_cron, use:

SELECT * FROM cron.job;

Disabling Tasks

You can turn off a task with:

SELECT cron.unschedule(jobid);

Where jobid is the task’s ID. You can find it in the cron.job table.

Useful pg_cron Examples

Regularly Checking Query Activity

Let’s make a task that checks for long-running queries every 5 minutes:

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

This task looks for queries running longer than 5 minutes.

Integration with External Systems

Both pg_notify and pg_cron can be integrated with external systems like Slack, Telegram, or monitoring systems (for example, Prometheus).

Telegram

You can combine pg_notify with a Telegram bot to send notifications. The main idea is to write a script in Python or another language that listens for notifications and sends them to Telegram.

Here’s a simple Python bot example:

import psycopg2
import telegram

# Connect to PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# Create a Telegram bot
bot = telegram.Bot(token='your_telegram_bot_token')

# Open a cursor to listen
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# Listen for notifications
print("Listening for notifications...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("Notification received:", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

Now your bot will get notifications sent via pg_notify.

When to Use pg_notify and pg_cron?

Use pg_notify for instant reactions (like letting the admin know about locks).

Use pg_cron for regular tasks (checking query activity, cleaning up old data).

Notes and Gotchas

pg_notify sends notifications instantly, but doesn’t keep a history. It’s better to integrate it with file logs or external systems.

pg_cron can cause unexpected load if tasks run too often. Always test your queries before adding them to the schedule.

Now you’re ready to optimize your monitoring and automate your database management. Go ahead—set up those alerts and become not just an SQL programmer, but a DBA!