pg_cron 사용

The latest service changes have not yet been reflected in this content. We will update the content as soon as possible. Please refer to the Korean version for information on the latest updates.

VPC 환경에서 이용 가능합니다.

Cloud DB for PostgreSQL에서 pg_cron extension을 제공하며 아래 사용 가이드를 참고해 주십시오. pg_cron은 PostgreSQL 내부에서 cron 표현식 기반으로 SQL 작업을 예약 실행하는 extension입니다.

콘솔을 통한 extension 설치

Cloud DB for PostgreSQL에서는 superuser 권한을 제공하지 않으므로 콘솔을 통해 superuser 권한이 필요한 extension을 설치할 수 있습니다. 자세한 내용은 아래 가이드를 참조해 주십시오.

• Extension 설치

pg_cron을 설치할 때는 작업을 예약할 대상 데이터베이스를 선택해 주십시오. 설치 과정에서 DB Server가 재시작되며, HA(고가용성) 구성에서는 Failover 방식으로 서버가 순차적으로 재시작됩니다.

pg_cron 개념

pg_cron을 설치하면 대상 데이터베이스에 cron 스키마가 생성되며, 다음 구성 요소를 통해 작업을 예약하고 관리합니다. Cloud DB for PostgreSQL은 superuser 권한을 제공하지 않으므로 아래 표의 DB owner 계정 사용 항목에 따라 일부 함수는 사용할 수 없습니다.

예약된 작업은 작업을 등록한 사용자의 권한으로 실행됩니다. 등록한 사용자가 접근할 수 없는 테이블에는 작업을 수행할 수 없습니다.

작업 예약 및 관리

설치 시 선택한 데이터베이스에 DB owner 계정으로 접속한 후 작업을 예약합니다.

작업 예약

cron.schedule(작업이름, 스케줄, 실행할 SQL) 형식으로 예약합니다.

-- 매일 03:00에 sales.orders 테이블의 VACUUM ANALYZE 실행
SELECT cron.schedule('nightly-vacuum-orders', '0 3 * * *', 'VACUUM ANALYZE sales.orders');

-- 매주 일요일 05:00에 90일 이전 주문 삭제
SELECT cron.schedule('purge-old-orders', '0 5 * * 0', $$DELETE FROM sales.orders WHERE ordered_at < now() - interval '90 days'$$);

작업 조회 및 변경

예약된 작업은 cron.job에서 조회합니다. 스케줄이나 명령을 변경할 때는 cron.alter_job을 사용할 수 없으므로, cron.unschedule로 삭제한 후 같은 이름으로 다시 예약합니다.

-- 예약된 작업 조회 (본인이 등록한 작업만 표시)
SELECT jobid, jobname, schedule, command, active FROM cron.job;

-- 스케줄 변경: 기존 작업을 삭제한 후 새 스케줄로 다시 예약합니다.
SELECT cron.unschedule('purge-old-orders');
SELECT cron.schedule('purge-old-orders', '0 6 * * 0', $$DELETE FROM sales.orders WHERE ordered_at < now() - interval '90 days'$$);

-- 작업 삭제
SELECT cron.unschedule('nightly-vacuum-orders');

사용 예제: 일별 매출 집계 적재

매일 새벽에 전날 주문을 집계하여 요약 테이블에 적재하는 예제입니다. 아래 예제는 스키마 및 테이블 생성부터 검증까지 그대로 실행할 수 있습니다. 이 예제에서는 pg_cron을 설치한 데이터베이스를 maindb, 해당 데이터베이스의 DB owner를 mainowner로 가정합니다. 모든 작업은 maindb에 mainowner 계정으로 접속한 후 등록합니다.

-- maindb 에 mainowner 계정으로 접속합니다.
\c maindb mainowner

-- 1. 사용할 스키마를 생성합니다. (Cloud DB for PostgreSQL은 public 스키마 사용을 권장하지 않으므로 별도 스키마를 사용합니다.)
CREATE SCHEMA sales;

-- 2. 원본 주문 테이블을 생성하고 샘플 데이터를 입력합니다. (운영 중인 테이블이 있다면 생략합니다.)
CREATE TABLE sales.orders (
    order_id   bigserial     PRIMARY KEY,
    amount     numeric(12,2) NOT NULL,
    ordered_at timestamptz   NOT NULL DEFAULT now()
);

INSERT INTO sales.orders (amount, ordered_at) VALUES
    (15000, current_date - 1 + time '09:30'),  -- 어제 주문
    (32000, current_date - 1 + time '13:10'),  -- 어제 주문
    ( 8900, current_date - 1 + time '20:05'),  -- 어제 주문
    (47000, current_date     + time '08:00');  -- 오늘 주문 (어제 집계에는 미포함)

-- 3. 집계 결과를 저장할 요약 테이블을 생성합니다.
CREATE TABLE sales.daily_sales_summary (
    sales_date   date          PRIMARY KEY,
    order_cnt    integer       NOT NULL,
    total_amount numeric(14,2) NOT NULL
);

-- 4. 먼저 동작을 빠르게 검증하기 위해 매분 실행으로 작업을 예약합니다.
--    스키마를 포함한 이름을 사용하며, 같은 날짜로 다시 실행되어도 중복되지 않도록 ON CONFLICT 로 갱신합니다.
SELECT cron.schedule(
    'rollup-daily-sales',
    '* * * * *',
    $$INSERT INTO sales.daily_sales_summary (sales_date, order_cnt, total_amount)
      SELECT current_date - 1, count(*), coalesce(sum(amount), 0)
      FROM sales.orders
      WHERE ordered_at >= current_date - 1
        AND ordered_at <  current_date
      ON CONFLICT (sales_date)
      DO UPDATE SET order_cnt    = EXCLUDED.order_cnt,
                    total_amount = EXCLUDED.total_amount$$
);

-- 5. 1~2분 정도 기다린 후 적재 결과와 작업 실행 이력을 확인합니다.
SELECT sales_date, order_cnt, total_amount
FROM sales.daily_sales_summary
ORDER BY sales_date DESC
LIMIT 10;

SELECT jobid, status, return_message, start_time
FROM cron.job_run_details
WHERE jobid = (SELECT jobid FROM cron.job WHERE jobname = 'rollup-daily-sales')
ORDER BY start_time DESC
LIMIT 5;

-- 6. 정상 동작을 확인했으면 테스트 작업을 삭제하고 운영 스케줄(매일 00:05)로 다시 예약합니다.
--    (cron.alter_job 은 사용할 수 없으므로 unschedule 후 재예약합니다.)
SELECT cron.unschedule('rollup-daily-sales');
SELECT cron.schedule(
    'rollup-daily-sales',
    '5 0 * * *',
    $$INSERT INTO sales.daily_sales_summary (sales_date, order_cnt, total_amount)
      SELECT current_date - 1, count(*), coalesce(sum(amount), 0)
      FROM sales.orders
      WHERE ordered_at >= current_date - 1
        AND ordered_at <  current_date
      ON CONFLICT (sales_date)
      DO UPDATE SET order_cnt    = EXCLUDED.order_cnt,
                    total_amount = EXCLUDED.total_amount$$
);

실행 이력 모니터링

cron.job_run_details에서 작업 실행 이력을 확인할 수 있습니다. 본인이 등록한 작업의 이력만 조회됩니다.

-- 최근 실행 이력 조회
SELECT jobid, jobname, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

cron.job_run_details는 자동으로 정리되지 않으므로, 다음과 같이 정리 작업을 예약하는 것을 권장합니다.

-- 매주 일요일 05:00에 30일 이전 실행 이력 삭제
SELECT cron.schedule('cleanup-cron-history', '0 5 * * 0', $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '30 days'$$);