[PostgreSQL] 월 단위로 테이블 CSV 백업

🤔 Question

👉 PostgreSQL에서 특정 테이블을 1개월 단위로 CSV 백업하는 SQL을 만들려면, COPY 명령어를 활용하면 됩니다.

 

🎯 SQL 스크립트

👉 아래 코드는 COPY 명령어를 사용하여 특정 테이블의 데이터를 매월 CSV로 백업하는 SQL입니다.

DO $$ 
DECLARE 
    table_name TEXT := 'your_table';  -- 백업할 테이블명
    backup_path TEXT := '/path/to/backup/';  -- 백업할 디렉터리 (PostgreSQL 서버에서 접근 가능해야 함)
    file_name TEXT;
    query TEXT;
BEGIN
    -- 파일명: 테이블명_YYYY-MM.csv 형식
    file_name := table_name || '_' || TO_CHAR(CURRENT_DATE, 'YYYY-MM') || '.csv';
    
    -- COPY 명령어 생성
    query := FORMAT('COPY %I TO %L WITH CSV HEADER', table_name, backup_path || file_name);
    
    -- 동적 SQL 실행
    EXECUTE query;
    
    RAISE NOTICE 'Backup completed: %', file_name;
END $$;

 

🎯 실행 방법

👉 위 SQL을 실행하면 /path/to/backup/your_table_YYYY-MM.csv 파일이 생성됩니다.

your_table : 백업할 테이블명을 입력하세요.
/path/to/backup/ : PostgreSQL 서버에서 접근할 수 있는 디렉터리를 지정하세요. (예: /var/lib/postgresql/backups/)
YYYY-MM 형식으로 현재 월 기준 파일을 생성합니다.

🎯 스케줄링 (자동 실행)

👉 백업을 매월 자동 실행하려면 cron (리눅스) 또는 **pgAgent (PostgreSQL 스케줄러)**를 사용할 수 있습니다.

👉 리눅스 cron에 등록 (매월 1일 실행)

0 0 1 * * psql -U your_user -d your_database -f /path/to/backup_script.sql

 

👉  Windows 작업 스케줄러
Windows 환경에서는 task scheduler를 사용하여 psql 명령을 일정에 맞춰 실행할 수 있습니다.

 

🎯 백업 파일을 날짜별로 관리하고 싶다면?

👉 매월 1일 이전 달 데이터를 백업하려면 SQL을 다음과 같이 수정하면 됩니다.

file_name := table_name || '_' || TO_CHAR(CURRENT_DATE - INTERVAL '1 month', 'YYYY-MM') || '.csv';

이렇게 하면 이전 달 데이터가 백업됩니다.

🎯 특정 월 데이터만 백업하는 경우

👉 만약 테이블에 created_at 같은 날짜 컬럼이 있다면, 특정 월 데이터만 백업할 수도 있습니다.

query := FORMAT(
    'COPY (SELECT * FROM %I WHERE created_at >= DATE_TRUNC(''month'', CURRENT_DATE - INTERVAL ''1 month'')
                      AND created_at < DATE_TRUNC(''month'', CURRENT_DATE)) 
     TO %L WITH CSV HEADER',
    table_name, backup_path || file_name
);

이렇게 하면 특정 월의 데이터만 백업됩니다.

 

🎯 매월 첫째 날,  데이터 추출 & 압축 (gz)

👉 매월 첫째 날, 데이터를 추출하여 CSV로 압축하는 SQL입니다.

copy (SELECT * FROM Legs_IO) TO STDOUT WITH CSV HEADER“
| gzip > /home/EAI/IO_log_$(date +%F).csv.gz

이렇게 하면 이전 달 데이터가 백업됩니다.

 

☔ 정리

👉 위 방식을 활용하면 PostgreSQL의 테이블을 효과적으로 매월 CSV로 백업할 수 있습니다.

 

 

If I was of any help to you, please buy me coffee 😿😢😥

If you have any questions, please leave them in the comments

Buy me a coffee

▶ Youtube Sub

🧭 References

[1] reference : https://doctorson0309.tistory.com/

[2] Ads : https://play.google.com/store/apps/details?id=io.cordova.seoulfilter