PostgreSQL 자동 백업 구성 정리 (pg_dump + cron)
PostgreSQL 데이터베이스를 운영하면서
정기적인 데이터 백업을 위해 서버 내부에서 자동 백업을 구성하였습니다.
별도의 백업 솔루션 없이
pg_dump, 쉘 스크립트, cron 만으로 구성하였으며,
복구 시 바로 실행 가능한 SQL 파일 형태를 기준으로 설계하였습니다.
구성 기준
- 데이터 위주의 백업
- 사람이 직접 확인 가능한 SQL 파일
- 서버 내부 자동 실행
- 백업 작업과 정리 작업을 분리하여 관리
스키마 구조 변경이 잦지 않은 환경이어서
이번 구성에서는 데이터만 백업하는 방식으로 진행하였습니다.
백업 디렉터리
/home2/backup/postgresql백업 스크립트와 백업 파일을 한 경로에서 관리하였으며,
백업 실행 계정만 접근할 수 있도록 권한을 제한하였습니다.
백업 스크립트
백업 스크립트는 백업 작업만 수행하도록 구성하였습니다.
#!/bin/bash
# 여러 PostgreSQL DB를 순차적으로 데이터만 백업하는 스크립트
# 백업 파일명에 사용할 날짜/시간
DATE=$(date +"%Y%m%d_%H%M")
# 백업 파일과 로그가 저장될 기준 디렉터리
BASE_DIR="/home3/postgrebackup"
# 백업 실행 로그 파일
LOG_FILE="$BASE_DIR/backup.log"
# PostgreSQL 서버 주소
DB_HOST=""
# PostgreSQL 포트
DB_PORT=""
# 백업 대상 DB 이름과 접속 계정을 매핑
DB_INFO=(
"백업대상 DB : user"
"백업대상 DB : user"
"백업대상 DB : user"
)
# 전체 백업 시작 로그
echo "[$(date)] 전체 백업 시작" >> "$LOG_FILE"
# DB 목록을 순회하며 순차적으로 백업 수행
for ITEM in "${DB_INFO[@]}"; do
# DB 이름과 접속 계정 분리
DB_NAME="${ITEM%%:*}"
DB_USER="${ITEM##*:}"
# DB별 백업 파일 저장 디렉터리
DB_DIR="$BASE_DIR/$DB_NAME"
# DB별 백업 디렉터리 생성
mkdir -p "$DB_DIR"
# DB 단위 백업 시작 로그
echo "[$(date)] $DB_NAME ($DB_USER) 백업 시작" >> "$LOG_FILE"
# PostgreSQL 데이터 덤프 실행 (데이터만, INSERT 문 형태)
/usr/bin/pg_dump \
-h "$DB_HOST" \
-p "$DB_PORT" \
-U "$DB_USER" \
-a \
--column-inserts \
--encoding=UTF8 \
-F p \
-d "$DB_NAME" \
-f "$DB_DIR/${DB_NAME}_data_${DATE}.sql" \
>> "$LOG_FILE" 2>&1
# 백업 결과 로그
if [ $? -eq 0 ]; then
echo "[$(date)] $DB_NAME 백업 성공" >> "$LOG_FILE"
else
echo "[$(date)] $DB_NAME 백업 실패" >> "$LOG_FILE"
fi
done
# 전체 백업 종료 로그
echo "[$(date)] 전체 백업 종료" >> "$LOG_FILE"
echo "================================================" >> "$LOG_FILE"
# 2주(30일) 지난 덤프 정리
find "$BASE_DIR" -name "*.sql" -type f -mtime +30 -exec rm -f {} \;
pg_dump 옵션 설명
-
-a
→ 데이터만 백업 (스키마 제외) -
-F p
→ plain SQL 포맷으로 저장 (.sql) -
--column-inserts
→ INSERT 문에 컬럼명을 명시
→ 컬럼 순서 변경 시에도 복구 안정성 확보
인증 정보 처리 (.pgpass)
스크립트에 비밀번호를 직접 작성하지 않기 위해
PostgreSQL의 .pgpass 파일을 사용하였습니다.( home경로 )
~/.pgpass형식은 다음과 같습니다.
호스트:포트:DB명:계정명:비밀번호파일 권한은 반드시 아래와 같이 설정해야 합니다.
chmod 600 ~/.pgpass권한이 맞지 않으면 PostgreSQL에서 해당 파일을 사용하지 않습니다.
cron 설정
백업 작업과 백업 파일 정리 작업을 cron에서 분리하여 관리하였습니다.
1️⃣ 백업 cron
0 2 * * * /home2/backup/postgresql/db_backup.sh >> /home/backup/postgresql/backup.log 2>&1- 매일 새벽 2시 실행
- 실행 로그 파일 저장
2️⃣ 백업 파일 정리 cron
백업 파일은 30일 보관 후 삭제하도록 설정하였습니다.
0 3 * * * find /home2/backup/postgresql -name "*.sql" -mtime +30 -delete- 매일 새벽 3시 실행
- 30일이 지난
.sql파일 삭제
백업과 정리 작업을 분리하여
각 작업의 역할을 명확히 관리할 수 있도록 구성하였습니다.
데이터 복구 방법
백업된 SQL 파일은 psql 명령어로 바로 복구할 수 있습니다.
psql -h xxx.xxx.xxx.xxx -p 5432 -U db_user -d service_db < 백업파일.sqlSQL 파일 형태이기 때문에
전체 복구뿐 아니라 필요한 테이블만 선택적으로 복구하는 것도 가능합니다.
백업 정책 요약
- 백업 대상: 데이터 전체
- 백업 단위: DB 단위
- 백업 주기: 하루 1회
- 보관 기간: 30일
- 백업 / 정리 작업: cron 분리 관리
스키마 변경이 잦은 환경이라면
스키마 백업을 별도로 구성하는 것이 적절하다고 판단됩니다.
정리
기본 유틸리티만으로도
PostgreSQL 데이터베이스의 자동 백업 환경을 충분히 구성할 수 있었습니다.
운영 환경에서는
백업 주기, 보관 기간, 백업 대상을
서비스 특성에 맞게 조정하는 것이 중요하다고 생각합니다.
