PostgreSQL과 Python의 만남, PL/Python 활용부터 pg_cron 스케줄링까지

단순한 SMTP 전송을 넘어, 데이터 분석부터 오라클 DB Job 대체까지

이전 포스팅(https://odinbox.co.kr/562)에서 우리는 PostgreSQL에서 Python을 연동하여 SMTP 메일을 발송하는 방법을 다루었습니다. 하지만 이것은 PL/Python이 가진 잠재력의 극히 일부에 불과합니다.

많은 분이 "Python으로 만든 함수의 결과값을 SQL 쿼리 내에서 바로 사용할 수 있는가?", "외부 API와 통신하여 데이터를 가져와 DB에 넣을 수 있는가?", 그리고 "오라클의 DB Job처럼 스케줄링이 가능한가?"에 대해 궁금합니다.

이번 글에서는 PostgreSQL의 확장 기능인 PL/Python을 활용하여 Python의 방대한 라이브러리 생태계를 DB 내부로 가져오는 방법과, pg_cron을 이용해 Python 프로시저를 주기적으로 실행하는 스케줄링 구현 방법까지, OdinBOX 예제 코드를 통해 상세하게 정리해 드립니다.

1. PL/Python의 이해와 설정 (Trusted vs Untrusted)

PostgreSQL에서 Python을 사용하려면 plpython3u 확장을 설치해야 합니다. 여기서 뒤에 붙은 'u'는 Untrusted(신뢰할 수 없음)를 의미합니다. 이는 보안상의 이유로 붙은 이름이지만, 역설적으로 '제약이 없다'는 뜻이기도 합니다.

Trusted vs Untrusted 차이점

• Trusted (plpgSQL 등) : DB 내부 데이터만 건드릴 수 있으며, 파일 시스템이나 네트워크 접근이 차단됩니다.
• Untrusted (plpython3u) : Python이 할 수 있는 모든 것을 할 수 있습니다. OS 파일 읽기/쓰기, 소켓 통신, 외부 API 요청 등이 가능합니다. 즉, 슈퍼유저 권한이 필요합니다.

먼저 해당 기능을 활성화합니다.

SQL Command

CREATE EXTENSION plpython3u;

2. Python 함수 제작 및 결과값 활용

Python으로 로직을 짜고 그 결과값을 SQL 문에서 `SELECT` 하거나 `WHERE` 절 조건으로 즉시 사용할 수 있습니다. 복잡한 텍스트 처리나 정규식 연산은 SQL보다 Python이 훨씬 강력하고 간결합니다.

예제 1, OdinBOX 텍스트 분석기

입력받은 텍스트에서 키워드를 추출하여 JSON 형태로 반환하는 함수를 만들어 보겠습니다.

PostgreSQL Function (PL/Python)

CREATE OR REPLACE FUNCTION odinbox_analyze_text(input_text text)
RETURNS json
AS $$
    import json
    import re

    words = re.findall(r'\w+', input_text.lower())
    word_count = {}
    
    for word in words:
        if word in word_count:
            word_count[word] += 1
        else:
            word_count[word] = 1
            
    result = {
        "status": "success",
        "analyzer": "OdinBOX_v1",
        "total_words": len(words),
        "data": word_count
    }
    
    return json.dumps(result)
$$ LANGUAGE plpython3u;

이제 이 함수를 일반 SQL처럼 조회할 수 있습니다.

Usage Example

SELECT odinbox_analyze_text('OdinBOX is great. OdinBOX uses Postgres.');

3. 외부 라이브러리 활용 : DB에서 직접 웹 크롤링하기

DB 서버에 requests나 pandas 라이브러리가 설치되어 있다면(pip install requests), DB 함수 내에서 이를 import하여 외부 데이터를 실시간으로 가져올 수 있습니다. 이는 ETL 도구 없이도 외부 데이터를 적재할 수 있음을 의미합니다.

OdinBOX External Data Fetcher

CREATE OR REPLACE FUNCTION odinbox_fetch_currency(currency_code text)
RETURNS numeric
AS $$
    import requests
    
    try:
        url = f"https://api.exchangerate-api.com/v4/latest/{currency_code}"
        response = requests.get(url, timeout=5)
        
        if response.status_code == 200:
            data = response.json()
            return data['rates'].get('KRW', 0)
        else:
            plpy.warning(f"OdinBOX API Error: {response.status_code}")
            return -1
    except Exception as e:
        plpy.error(f"Network Error: {e}")
$$ LANGUAGE plpython3u;

4. 오라클 DB Job 대체하기 : pg_cron과 Python의 만남

오라클에는 DBMS_SCHEDULER나 DBMS_JOB이 내장되어 있습니다. PostgreSQL은 기본적으로는 스케줄러가 없지만, 표준처럼 사용되는 확장 기능인 pg_cron을 통해 동일한 기능을 수행할 수 있습니다.

이 조합이 강력한 이유는, "Python으로 복잡한 로직을 짜고, pg_cron으로 스케줄링"할 수 있기 때문입니다.

Step 1. pg_cron 설치 및 설정

postgresql.conf 파일의 shared_preload_libraries에 pg_cron을 추가하고 DB를 재시작해야 합니다.

CREATE EXTENSION pg_cron;

Step 2. Python 프로시저 스케줄링 등록

예를 들어, 매일 오전 9시에 위에서 만든 odinbox_fetch_currency 함수를 실행하여 환율 테이블에 저장하는 작업을 등록해 보겠습니다.

Schedule Registration

CREATE TABLE odinbox_currency_log (
    log_time TIMESTAMP DEFAULT now(),
    usd_rate NUMERIC
);

CREATE OR REPLACE PROCEDURE odinbox_daily_job()
LANGUAGE sql
AS $$
    INSERT INTO odinbox_currency_log (usd_rate)
    SELECT odinbox_fetch_currency('USD');
$$;

SELECT cron.schedule('odinbox_exchange_job', '0 9 * * *', 'CALL odinbox_daily_job()');

주의사항

pg_cron은 기본적으로 GMT 기준입니다. 한국 시간(KST)으로 9시에 실행하려면 시간을 -9시간 하여 0 0 * * *(자정)으로 설정하거나, cron.timezone 설정을 확인해야 합니다.

마무리

PostgreSQL의 PL/Python은 단순히 DB 안에서 스크립트를 돌리는 수준을 넘어, 데이터베이스를 지능형 데이터 허브로 만들어줍니다. Python의 강력한 라이브러리(Pandas, Scikit-learn, Requests 등)를 데이터가 저장된 곳에서 바로 실행함으로써 이동 비용을 줄이고 효율을 극대화할 수 있습니다.

또한 pg_cron과 결합하면 오라클의 DB Job 부럽지 않은 강력한 스케줄링 환경을 구축할 수 있습니다. 오늘 소개한 OdinBOX 예제들을 응용하여 여러분만의 자동화 시스템을 구축해 보시기 바랍니다.