設定警報和通知來應對問題發生時

大家都想在伺服器掛掉或讓用戶抓狂之前，先知道有什麼狀況。PostgreSQL 給我們一些工具可以做通知和排程任務：pg_notify 跟 pg_cron。這兩個就像是我們資料庫的鬧鐘和排程器啦。

想像一下，你有個外幣兌換課程的資料庫，突然有個 process 把其他的都鎖住了。你不用一直手動檢查資料庫狀態，只要設定好通知，馬上就能知道發生什麼事。定期檢查資料庫狀態的話，pg_cron 就超好用。這些我們等等都會講。

從資料庫發送即時通知：pg_notify

先來看 pg_notify。這是 PostgreSQL 內建的 function，可以從資料庫發送通知到指定的「頻道」。你可以用它來通知一些事件，比如長查詢結束、偵測到鎖定或其他奇怪的狀況。

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 $$;

這段 code 會檢查有沒有未處理的鎖定，有的話就發通知到 alerts 頻道。

要監聽通知的話，在另一個連線用 LISTEN：

LISTEN alerts;

這樣只要 pg_notify 有送訊息到 alerts，你就會在 console 看到通知。

範例：

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 跟內建 function 的細節，之後幾個章節會再講啦 :P

常見錯誤跟怎麼避免

如果你用了 LISTEN 卻沒收到通知，檢查一下：

1. 你是不是在同一個連線發送通知？
2. 頻道名字有沒有打對？
3. 你是不是在 transaction 裡呼叫 pg_notify，而且有 COMMIT？

PostgreSQL 的排程器：pg_cron

pg_cron 是 PostgreSQL 的 extension，可以讓你像 Linux 的 cron 一樣排程執行任務。比如定期檢查鎖定、收集統計資料都可以自動化。

用 pg_cron 建立任務

假設 pg_cron 已經裝好，來建一個每天清除 logs 資料表舊資料的任務：

SELECT cron.schedule('刪除舊 log',
'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）。

Telegram

你可以把 pg_notify 跟 Telegram bot 結合，發送通知。基本做法就是寫個 Python（或其他語言）script，監聽通知然後轉發到 Telegram。

簡單的 Python bot 範例：

import psycopg2
import telegram

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

# 建立 Telegram bot
bot = telegram.Bot(token='your_telegram_bot_token')

# 開啟監聽用的 cursor
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)

這樣你的 bot 就會收到 pg_notify 發送的通知啦。

什麼時候該用 pg_notify 跟 pg_cron？

pg_notify 適合即時反應（例如通知管理員有鎖定）。

pg_cron 適合定期任務（查詢活動、清除舊資料）。

注意事項和小陷阱

pg_notify 發送通知是即時的，但不會記錄歷史。建議跟檔案 log 或外部系統整合。

pg_cron 如果任務排太密，可能會造成意外負載。加任務前一定要先測試查詢。

現在你已經 ready，可以優化你的監控，還有自動化資料庫管理啦。快去設定警報，成為不只是 SQL 工程師，而是 DBA 吧！