우리 모두 서버가 뻗거나 사용자들한테 민폐 끼치기 전에 문제를 미리 알고 싶잖아. 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는 단순 알림에만 쓰는 게 아니야. 예를 들어 트리거랑 연결해서, 데이터 추가/수정/삭제 시 자동 알림도 만들 수 있어:
새 레코드 알림
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에 새 레코드가 추가되면 바로 알림을 받을 수 있어.
트리거나 내장 함수에 대해서는 곧 더 자세히 배울 거니까 조금만 기다려 :P
자주 하는 실수와 피하는 법
LISTEN을 썼는데 알림이 안 보이면, 아래를 체크해봐:
- 알림을 보내는 커넥션이랑 같은 커넥션에서 작업 중인지 확인해.
- 채널 이름이 맞는지 확인해.
pg_notify를 커밋(COMMIT)된 트랜잭션 안에서 호출했는지 확인해.
PostgreSQL의 작업 스케줄러: pg_cron
pg_cron은 PostgreSQL용 확장 기능으로, 리눅스의 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는 작업의 식별자야. 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 봇이랑 연결해서 알림을 보낼 수 있어. 핵심은 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로 보낸 알림을 봇이 받아서 Telegram으로 보내줄 거야.
pg_notify랑 pg_cron 언제 써야 할까?
pg_notify는 즉각적인 반응이 필요할 때(예: 락 걸렸을 때 관리자한테 알림) 써.
pg_cron은 정기적으로 작업을 실행할 때(쿼리 활동 체크, 오래된 데이터 정리 등) 써.
주의사항과 함정
pg_notify는 알림을 바로 보내지만, 기록을 남기진 않아. 파일 로그나 외부 시스템이랑 연동하는 게 좋아.
pg_cron은 작업을 너무 자주 돌리면 예상치 못한 부하가 생길 수 있어. 쿼리를 스케줄에 넣기 전에 꼭 테스트해봐.
이제 너도 데이터베이스 모니터링을 최적화하고 관리 자동화할 준비가 됐어. 알림 세팅해서 그냥 SQL 프로그래머가 아니라 진짜 DBA가 되어보자!
GO TO FULL VERSION