CodeGym /课程 /SQL SELF /遇到问题时设置警报和通知

遇到问题时设置警报和通知

SQL SELF

第 46 级, 课程 1

可用

我们都想在服务器挂掉或者让用户抓狂之前，提前知道数据库出问题了。PostgreSQL 给我们提供了搞通知和定时任务的工具：pg_notify 和 pg_cron。这俩就像数据库里的闹钟和定时器。

想象一下：你有个外汇课程的数据库，突然有个进程把其他的都锁住了。你不用一直手动查数据库状态，可以直接设置通知，随时掌握情况。定期检查数据库状态就交给 pg_cron 吧。下面我们就来搞明白这些。

数据库里的快速通知：`pg_notify`

先说 pg_notify。这是 PostgreSQL 自带的函数，可以把通知发到指定的“频道”里。你可以用它来提示一些事件，比如长查询结束、发现锁、或者其他奇怪的情况。

pg_notify 的语法很简单：

NOTIFY <channel>, <message>;

channel —— 通知要发到哪个频道。
message —— 通知的文本内容。

来个 pg_notify 的例子。假如我们要在发现锁的时候发个通知：

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', '数据库里有锁啦！');
    END IF;
END $$;

这段代码会检查有没有没处理的锁，然后把通知发到 alerts 频道。

要监听通知，可以在另一个连接里用 LISTEN 命令：

LISTEN alerts;

现在，只要 pg_notify 往 alerts 频道发消息，你就能在控制台看到通知了。

举个例子：

NOTIFY alerts, '嘿，这里有锁啦！';

在另一个执行了 LISTEN alerts 的连接里，你会立刻收到：
NOTIFY: 嘿，这里有锁啦！

pg_notify 不只是发简单通知。比如你可以把它和触发器（trigger）结合起来，自动通知有数据被添加、修改或删除：

新记录通知

CREATE OR REPLACE FUNCTION notify_new_record()
RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify('table_changes', '有新记录加到表里啦！');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

现在，只要 your_table 有新记录插入，你就能立刻收到通知。

关于 trigger 和内置函数，后面几节我们还会详细讲 :P

常见错误和怎么避免

如果你用了 LISTEN 但收不到通知，检查下：

是不是在同一个连接里发通知和监听。
频道名是不是写对了。
记得 pg_notify 要在已经提交（COMMIT）的事务里才会生效。

PostgreSQL 的任务调度器：`pg_cron`

pg_cron 是 PostgreSQL 的一个扩展，可以像 Linux 里的 cron 一样定时跑任务。比如你可以定期检查锁，或者收集统计信息。

用 `pg_cron` 创建任务

假设 pg_cron 已经装好了，我们来建个每天清理 logs 表里旧记录的任务。

SELECT cron.schedule('删除旧日志',
'0 0 * * *',
$$ DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days' $$);

解释一下：

'0 0 * * *' —— 这是定时表达式（每天零点执行）。
DELETE FROM logs ... —— 这是 cron 要执行的 SQL。

查看任务

想看所有 pg_cron 的任务，用：

SELECT * FROM cron.job;

关闭任务

要关掉某个任务，用：

SELECT cron.unschedule(jobid);

这里 jobid 是任务的 ID，可以在 cron.job 表里查到。

`pg_cron` 的实用例子

定期检查活跃查询

来个每 5 分钟查一次长时间运行查询的任务：

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

这个任务会找出运行超过 5 分钟的查询。

和外部系统集成

pg_notify 和 pg_cron 都能和外部系统集成，比如 Slack、Telegram 或监控系统（比如 Prometheus）。

你可以把 pg_notify 和 Telegram 机器人结合起来发通知。思路就是写个 Python 脚本或者别的语言，监听通知然后发到 Telegram。

简单的 Python 机器人例子：

import psycopg2
import telegram

# 连接 PostgreSQL
conn = psycopg2.connect("dbname=your_database user=your_user")

# 创建 Telegram 机器人
bot = telegram.Bot(token='your_telegram_bot_token')

# 打开游标监听
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN alerts;")

# 监听通知
print("正在监听通知...")
while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop()
        print("收到通知：", notify.payload)
        bot.send_message(chat_id='your_chat_id', text=notify.payload)

现在你的机器人就能收到通过 pg_notify 发的通知啦。

什么时候用 `pg_notify` 和 `pg_cron`？

用 pg_notify 做即时响应（比如有锁就立刻通知管理员）。

用 pg_cron 跑定期任务（查活跃查询、清理旧数据啥的）。

注意事项和坑

pg_notify 发通知是秒发，但不会存历史。最好和文件日志或者外部系统结合用。

pg_cron 如果任务太频繁可能会让数据库压力山大。加任务前一定要先测试 SQL。

现在你已经可以优化自己的监控和自动化数据库管理啦。快去设置警报，做个不只是会写 SQL 的 DBA 吧！

上一课上一课

下一课下一课

TO VIEW ALL COMMENTS OR TO MAKE A COMMENT,
GO TO FULL VERSION

遇到问题时设置警报和通知

数据库里的快速通知：pg_notify

PostgreSQL 的任务调度器：pg_cron

用 pg_cron 创建任务