테스트 DB가 레플리카 DB의 데이터와 일치하도록 매일 새벽 자동으로 동기화하자.

테스트 DB와 레플리카 DB의 안정적인 동기화 시스템 구축기

테스트 DB가 레플리카 DB의 데이터와 일치하도록 매일 새벽 자동으로 동기화하자.

질문과 탐구

처음에는 현재 테스트 DB가 레플리카 DB로부터 주기적으로 덤프 및 리스토어 되는지 확인하는 것부터 시작했습니다. SSH 접속 후 PSQL 명령어를 활용하여 DB 상태를 확인하는 방법을 문의했습니다.

주요 질문:
- 현재 DB 동기화가 정상적으로 이루어지고 있는지 어떻게 확인할 수 있는가?
- 덤프 및 리스토어 자동화를 위한 쉘 스크립트는 어떻게 작성해야 하는가?
- 스크립트 실행 시 발생할 수 있는 권한 문제 및 에러 처리는 어떻게 해야 하는가?
- 동기화의 신뢰성을 높이기 위한 검증 로직은 무엇인가?
탐구 과정:
- psql -c "\\dt blog.*" 와 crontab -l 명령어로 현재 DB 스키마 및 스케줄 확인
- find 및 grep 명령어로 관련 스크립트 탐색
- pg_dump 와 pg_restore (혹은 psql을 이용한 스키마 복구) 명령어를 활용한 동기화 스크립트 구성
- CREATE SCHEMA IF NOT EXISTS 와 sed 명령어를 이용한 스키마 이름 변경 및 중복 생성 방지
- /tmp 디렉토리 권한 문제, /opt/db-sync 디렉토리 소유권 문제 해결
- 로그 파일명에 시간 정보 추가하여 덮어쓰기 방지
- pg_dump, psql 명령어 실행 권한 및 sudo 명령어 설정

핵심 학습 내용

PostgreSQL DB를 덤프하고 다른 DB에 리스토어하는 과정을 자동화하는 쉘 스크립트 작성법을 익혔습니다.

배운 주요 개념:
- pg_dump: PostgreSQL 데이터베이스를 백업하는 명령어. 특정 스키마(-n blog)를 파일(-f $DUMP_FILE)로 덤프할 수 있습니다.
- psql: PostgreSQL 대화형 터미널. 스키마 생성(CREATE SCHEMA) 및 삭제(DROP SCHEMA) 명령어를 실행할 수 있습니다.
- sed: 스트림 편집기. 덤프 파일 내의 스키마 이름(blog -> test_blog)을 변경하는 데 사용됩니다.
- crontab: 주기적인 작업을 예약하는 유닉스 유틸리티. 매일 새벽 3시에 스크립트가 실행되도록 설정했습니다.
- 권한 관리: /tmp 디렉토리나 커스텀 디렉토리(opt/db-sync)에 파일을 생성하거나 수정할 때 발생할 수 있는 권한 문제를 chown 명령어로 해결했습니다.
- 스크립트 검증: 동기화 후 실제 데이터의 행 수를 비교하여 동기화가 성공했는지 검증하는 로직을 추가했습니다.
중요한 포인트 정리:
- 스키마 이름 변경: 레플리카 DB의 blog 스키마를 테스트 DB의 test_blog 스키마로 변경하기 위해 sed를 활용했습니다. sed 's/blog\\./test_blog\\./g; s/SCHEMA blog/SCHEMA test_blog/g; s/CREATE SCHEMA test_blog/CREATE SCHEMA IF NOT EXISTS test_blog/g' 와 같이 사용하여 스키마 이름과 CREATE SCHEMA 구문을 안전하게 변환했습니다.
- 오류 처리 및 로깅: 스크립트 실행 중 발생하는 오류를 로그 파일에 기록하고, exit 1로 스크립트 실행을 중단하여 실패 사실을 명확히 했습니다.
- 자동화: crontab -u postgres -e 를 통해 postgres 사용자의 crontab에 0 3 * * * /opt/db-sync/sync.sh 를 추가하여 매일 새벽 3시에 스크립트가 실행되도록 설정했습니다.
- 검증 로직: pg_dump 및 psql 명령어의 COUNT(*) 결과를 비교하여 레플리카 DB와 테스트 DB의 데이터 일관성을 확인하는 로직을 추가했습니다.

예시 코드:

메인 동기화 스크립트 (/opt/db-sync/sync.sh):

#!/bin/bash
LOG=/opt/db-sync/logs/sync_$(date +%Y%m%d_%H%M%S).log
DUMP_FILE=/opt/db-sync/blog_dump.sql

echo "[ $(d a t e)] St a r t sy n c " >>$ LOG

# dump from replica (5432)
pg_dump -p 5432 -d my_blog -n blog -F p -f  $D U M P_{F} I L E 2 >>$ LOG
if [ $? -ne 0 ]; then
  echo "[ $(d a t e)] D U MPF A I L E D " >>$ LOG
  exit 1
fi

# clean and create test schema, handling potential existing schema
psql -p 5433 -d my_blog -c "DROP SCHEMA IF EXISTS test_blog CASCADE; CREATE SCHEMA test_blog;" 2>> $LOG

# restore & rename schema, handling CREATE SCHEMA IF NOT EXISTS
sed 's/blog\\./test_blog\\./g; s/SCHEMA blog/SCHEMA test_blog/g; s/CREATE SCHEMA test_blog/CREATE SCHEMA IF NOT EXISTS test_blog/g' $DUMP_FILE | \
  psql -p 5433 -d my_blog 2>> $LOG

if [ $? -ne 0 ]; then
  echo "[ $(d a t e)] RESTOREF A I L E D " >>$ LOG
  exit 1
fi

rm $DUMP_FILE
echo "[ $(d a t e)] Do n e " >>$ LOG

# verification
REPLICA_POSTS=$(psql -p 5432 -d my_blog -t -c "SELECT COUNT(*) FROM blog.posts;" | tr -d ' ')
TEST_POSTS=$(psql -p 5433 -d my_blog -t -c "SELECT COUNT(*) FROM test_blog.posts;" | tr -d ' ')

REPLICA_PROJECTS=$(psql -p 5432 -d my_blog -t -c "SELECT COUNT(*) FROM blog.projects;" | tr -d ' ')
TEST_PROJECTS=$(psql -p 5433 -d my_blog -t -c "SELECT COUNT(*) FROM test_blog.projects;" | tr -d ' ')

if [ " $REP L I C A_{P} OSTS " - e q "$ TEST_POSTS" ] && [ " $REP L I C A_{P} RO J ECTS " - e q "$ TEST_PROJECTS" ]; then
  echo "[ $(d a t e)] V ER I F Y O K - p os t s :$ TEST_POSTS, projects:  $TES T_{P} RO J ECTS " >>$ LOG
else
  echo "[ $(d a t e)] V ER I F Y F A I L E D - p os t s (re pl i c a / t es t) :$ REPLICA_POSTS/ $TES T_{P} OSTS, p ro j ec t s (re pl i c a / t es t) :$ REPLICA_PROJECTS/ $TES T_{P} RO J ECTS " >>$ LOG
  exit 1
fi

이해한 내용

처음에는 CREATE SCHEMA 시 발생할 수 있는 "already exists" 에러와 /tmp 디렉토리의 권한 문제로 인해 스크립트 실행에 어려움을 겪었습니다. 하지만 CREATE SCHEMA IF NOT EXISTS 와 DROP SCHEMA IF EXISTS를 조합하거나, sed 명령어를 활용하여 CREATE SCHEMA 줄 자체를 안전하게 처리하는 방법을 배웠습니다. 또한, /opt/db-sync 디렉토리의 소유권을 postgres로 변경하는 것이 필수적임을 알게 되었습니다.

가장 중요했던 부분은 단순히 동기화가 되는 것을 넘어, 실제로 데이터가 올바르게 복제되었는지 검증하는 로직을 추가했다는 점입니다. 이를 통해 동기화 실패 시에도 즉시 인지하고 대응할 수 있게 되어 시스템의 신뢰도를 높일 수 있었습니다.

실전 적용

이 지식은 다음과 같은 상황에 바로 적용할 수 있습니다.

개발/테스트 환경 데이터 동기화: 실제 운영 DB의 데이터를 주기적으로 복제하여 개발 및 테스트 환경의 데이터 불일치 문제를 해결할 때 유용합니다.
긴급 복구 대비: 갑작스러운 데이터 손실이나 오류 발생 시, 최신 상태의 복제본을 활용하여 신속하게 복구할 수 있습니다.
정기 백업 및 복원 훈련: 자동화된 백업 및 복원 스크립트를 구축하고 정기적으로 테스트하여 재해 복구 계획의 실행 가능성을 검증할 수 있습니다.

추가 학습 계획

이번 경험을 통해 쉘 스크립트와 DB 관리 자동화에 대한 흥미를 느꼈습니다.

더 깊이 공부하고 싶은 부분:
- Bash 스크립트 고급 기법 (함수, 오류 처리 강화, 변수 관리 등)
- PostgreSQL의 다양한 백업 전략 (WAL 아카이빙, PITR 등)
- 모니터링 시스템과의 연동 (예: Prometheus, Grafana를 이용한 DB 상태 및 동기화 성공/실패 알림)
- Docker 환경에서의 DB 동기화 구현
다음 학습 주제:
- Docker Compose를 활용한 개발 환경 구축 및 DB 관리 자동화

참고 자료

PostgreSQL 공식 문서 (pg_dump, psql, CREATE SCHEMA 등)
Bash 스크립트 문법 관련 자료 (예: tldp.org/LDP/Bash-Beginners-Guide/html/)

이 학습을 통해 DB 관리 자동화의 중요성과 구현 방법을 실질적으로 익힐 수 있었습니다. 앞으로도 꾸준히 관련 기술을 익히고 적용해나갈 계획입니다.