DB

[PostgreSql] 로그 테이블(파티션) 백업 및 DROP 방법 (로그 테이블에서 DELETE 하면 DB 터집니다.

관리자

[PostgreSql] 로그 테이블(파티션) 생성 방안 (파티션 + 6개월 자동 삭제 + 인덱스 + 페이징 조회) 이 글에 이어서 기록되는 내용입니다.

백업 후 DROP 전략 (가장 안전한 방법)

방법 A — 파티션 단위 CSV 백업 (가장 많이 씀)

COPY TB_APP_LOG_2025_01
TO '/backup/TB_APP_LOG_2025_01.csv'
DELIMITER ','
CSV HEADER;

→ 월별 파티션 단위로 백업
→ 이후 바로 DROP 가능

DROP TABLE TB_APP_LOG_2025_01;

방법 B — SQL 백업 (pg_dump)

pg_dump -t TB_APP_LOG_2025_01 mydb > TB_APP_LOG_2025_01.sql

복구:

psql mydb < TB_APP_LOG_2025_01.sql

방법 C — DB 내부 백업 테이블

CREATE TABLE TB_APP_LOG_2025_01_BAK AS
SELECT * FROM TB_APP_LOG_2025_01;


특정 기간만 남기고 정리 (파티션 기준)

예: 최근 6개월만 유지

DROP TABLE IF EXISTS TB_APP_LOG_2025_01;
DROP TABLE IF EXISTS TB_APP_LOG_2025_02;
DROP TABLE IF EXISTS TB_APP_LOG_2025_03;

👉 파티션이면 DELETE보다 DROP이 100배 빠름

자동 정리 SQL (월 기준 계산)

DO $$
DECLARE
    v_limit_date DATE := date_trunc('month', now() - interval '6 months');
    r RECORD;
BEGIN
    FOR r IN
        SELECT relname
        FROM pg_class
        WHERE relname LIKE 'tb_app_log_%'
    LOOP
        IF substring(r.relname from 12 for 7)::date < v_limit_date THEN
            EXECUTE format('DROP TABLE IF EXISTS %I', r.relname);
        END IF;
    END LOOP;
END $$;

pg_cron 자동 정리 스케줄

SELECT cron.schedule(
  'log_partition_cleanup',
  '0 3 1 * *',
  $$DO $$
  DECLARE
      v_limit_date DATE := date_trunc('month', now() - interval '6 months');
      r RECORD;
  BEGIN
      FOR r IN
          SELECT relname
          FROM pg_class
          WHERE relname LIKE 'tb_app_log_%'
      LOOP
          IF substring(r.relname from 12 for 7)::date < v_limit_date THEN
              EXECUTE format('DROP TABLE IF EXISTS %I', r.relname);
          END IF;
      END LOOP;
  END $$;$$
);

👉 매월 1일 새벽 3시
👉 6개월 초과 파티션 자동 삭제

실무 운영 정책 예시

항목정책
온라인 조회최근 3개월
DB 보관최근 6개월
CSV 백업1년
DROP파티션 단위

실무 한 줄 요약

로그 테이블은
DELETE ❌
DROP PARTITION ✅

운영 체크리스트

SELECT relname
FROM pg_class
WHERE relname LIKE 'tb_app_log_%'
ORDER BY relname;

이 결과 보고
→ 삭제 대상만 DROP

절대 하면 안 되는 것

DELETE FROM TB_APP_LOG WHERE created_at < ...

👉 로그 테이블에서 이거 하면 DB 터집니다.

🎯 결론

✔ 백업 → DROP
✔ 기간 기준 → 파티션 DROP
✔ pg_cron 자동화
✔ DELETE 금지

Hi, I’m 관리자

Related Posts