스케줄에 따라 자동으로 리포트 생성하기

작은 데이터베이스로 작업할 때는 직접 쿼리나 프로시저를 실행해서 리포트 만드는 게 별로 어렵지 않아. 근데 현실 세계에서는 데이터베이스가 커지면서 반복되는 작업은 무조건 자동화해야 돼. 예를 들어, 매일매일 너한테 판매 리포트 만들어 달라고 한다고 생각해봐. 쿼리 한 번에 2분 걸린다고 해도, 1년이면 12시간 넘게 쓰는 거야. 그 시간에 커피 한 잔 하면서 자동 프로시저가 알아서 처리하게 두는 게 훨씬 낫지.

자동화의 장점은 이래:

• 수작업을 줄일 수 있어.
• 리포트가 정기적으로 만들어져 (예: 매일, 매주 리포트).
• 사람 실수로 인한 에러를 최소화할 수 있어.
• 리포트 신뢰도가 올라가: 항상 정해진 조건대로 만들어지니까.

자동 리포트 생성의 기본 단계

자동 리포트 실행은 이런 단계로 진행돼:

1. 리포트를 만들어주는 PL/pgSQL 프로시저 작성.
2. 결과를 로그로 남기기 (필요하다면).
3. 스케줄러로 프로시저를 정해진 시간에 실행.

이제 하나씩 해보자!

리포트 생성 프로시저 만들기

먼저, 오늘 날짜 기준으로 모든 주문의 총 매출을 계산해서 로그 테이블에 저장하는 간단한 프로시저를 만들어볼게. 로그용 테이블은 이미 있다고 치고 (이름은 sales_report_log로 하자):

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

이제 PL/pgSQL로 프로시저를 만들어보자:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- 오늘 날짜 기준으로 총 매출 계산
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE '보고서 % 성공적으로 생성됨', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

여기서 무슨 일이 일어나냐면:

• SUM() 집계 함수로 orders 테이블에서 총 매출을 계산해.
• 리포트 날짜(report_date)는 항상 오늘 날짜(CURRENT_DATE)야.
• 결과는 sales_report_log 테이블에 저장돼.
• RAISE NOTICE 메시지는 디버깅용: 리포트가 잘 만들어졌다는 걸 알려줘.

프로시저 테스트하기

자동화하기 전에, 항상 수동으로 먼저 테스트해보는 게 좋아. 함수 실행해보자:

SELECT generate_daily_sales_report();

그리고 sales_report_log 테이블 내용도 확인해보자:

SELECT * FROM sales_report_log;

오늘 날짜랑 올바른 총 매출 값이 보이면 — 축하해, 함수 잘 돌아가는 거야!

PostgreSQL에서 작업 자동화하기

가끔은 데이터베이스가 알아서 뭔가 해줬으면 좋겠지: 리포트 실행, 오래된 데이터 정리, 집계 갱신 등등. PostgreSQL에서는 pg_cron 확장이나 외부 작업 스케줄러 — 시스템 cron이나 Task Scheduler로 이걸 할 수 있어.

Linux라면 pg_cron이 최고야. 이 확장은 SQL을 PostgreSQL 안에서 바로 실행해주니까 shell이나 스크립트 쓸 필요 없어.

pg_cron 설치는 이렇게 해 (여기서 XX는 네 PostgreSQL 버전으로 바꿔줘):

sudo apt install postgresql-XX-cron

설치 후에는 설정 파일에서 활성화해야 해. postgresql.conf 열고 이 줄을 추가해:

shared_preload_libraries = 'pg_cron'

그 다음 PostgreSQL 재시작하고, 데이터베이스에서 확장 활성화:

CREATE EXTENSION pg_cron;

이제 작업을 예약할 수 있어. 예를 들어, generate_daily_sales_report() 함수를 매일 자정에 실행하려면:

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

여기서:

• 'daily_sales_report' — 작업 이름;
• '0 0 * * *' — cron 스타일 스케줄 (여기선 매일 00:00);
• $$ 사이의 SQL — 실행할 코드.

예약된 작업 전체를 보려면:

SELECT * FROM cron.job;

Windows나 macOS라면 pg_cron이 아예 지원 안 되거나(Windows), 직접 빌드해야 해서(macOS) 귀찮아. 대부분의 경우 시스템 스케줄러 쓰는 게 더 편해.

방법은 이래:

1. 필요한 명령이 들어간 SQL 파일 만들기:

echo "SELECT generate_daily_sales_report();" > /path/to/script.sql

2. psql로 파일 실행:

psql -h localhost -U postgres -d your_database -f /path/to/script.sql

3. 이 명령을 작업 스케줄러에 등록:

   • Linux/macOS에서는 crontab -e로:

     0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

   • Windows에서는 Task Scheduler에서 psql.exe를 필요한 옵션으로 실행하는 작업을 만들어.

• Linux라면 pg_cron 쓰는 게 제일 편하고 PostgreSQL에 내장돼 있어.
• Windows나 Mac이면 시스템 스케줄러(cron이나 Task Scheduler)로 psql로 SQL 실행하는 게 더 나아.

이렇게 하면 PostgreSQL에서 어떤 작업이든 쉽게 자동화할 수 있어.

자동 리포트 예시

1. 지역별 일일 리포트

각 지역별로 매출 리포트를 자동으로 만들고 싶다고 해보자. 함수 확장하면 돼:

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE '지역별 보고서 % 성공적으로 생성됨', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. 월간 리포트

비슷하게, 한 달치 리포트 만드는 프로시저도 만들 수 있어. 쿼리에서 필터만 바꿔주면 돼:

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

자주 하는 실수와 예방법

자동 리포트 만들 때 이런 문제가 생길 수 있어:

• 함수에 문법 에러: 자동화 전에 항상 수동으로 함수 테스트해봐.
• 작업 실행 빈도: 너무 자주 실행하면 DB가 버거워질 수 있어. 스케줄은 신중하게!
• 데이터 중복: 하루에 여러 번 리포트가 실행되면 중복될 수 있어. 중복 방지하려면 유니크 키를 써.

이 강의에서 PostgreSQL에서 자동 리포트 만드는 방법을 알아봤어. 이제 분석 작업을 더 효율적으로 할 수 있고, 남는 시간엔... 버그 찾기, 코드 짜기, 아니면 네 SQL 쿼리를 완벽하게 만들 꿈을 꿔도 돼!