이번 학습에서는 PostgreSQL 레플리카(Replica) 환경에서 테스트용 데이터베이스 스키마를 생성하고 관리하는 방법을 탐구했습니다. 복잡한 레플리카의 read-only 특성을 이해하고, 이를 극복하여 독립적인 테스트 환경을 구축하는 과정에 초점을 맞추었습니다.

테스트 DB 구성을 위한 PostgreSQL 인스턴스 복제 및 분리 전략

학습 주제

공부 주제: PostgreSQL 레플리카 환경에서 테스트 DB 스키마 생성 및 관리
대화 제목: 테스트 DB 생성
학습 날짜: 2026년 3월 2일

질문과 탐구

주요 질문은 다음과 같습니다:

레플리카 DB 스키마를 복사하여 테스트 실행 시 사용하고, 테스트 종료 시 원래 상태로 복구하는 방법은 무엇인가?
레플리카 DB가 read-only인데, 테스트용 쓰기 가능한 스키마를 어떻게 생성할 수 있는가?
WAL(Write-Ahead Logging) 스트리밍이 이미 구성된 환경에서 최적의 테스트 DB 구축 방식은 무엇인가?
트랜잭션 롤백이나 LSN(Log Sequence Number) 기반 복구가 테스트 스키마 관리에 적합한가?
PostgreSQL 인스턴스, 데이터 디렉토리, 데이터베이스, 스키마의 관계는 무엇인가?
독립적인 테스트 DB 인스턴스를 별도로 운영하는 것이 일반적인가?

이러한 질문들을 해결하기 위해 AI와의 대화를 통해 다양한 방안을 탐색하고, 각 방식의 장단점을 비교하며 최적의 구조를 찾아 나갔습니다.

핵심 학습 내용

1. 레플리카 DB의 제약사항 이해

Replica의 Read-Only 특성: PostgreSQL 레플리카는 기본적으로 Primary DB와 WAL 스트리밍을 통해 동기화되며, 데이터 수정이 불가능한 read-only 모드로 동작합니다. 이는 CREATE SCHEMA와 같은 쓰기 작업을 직접 수행할 수 없음을 의미합니다.
WAL 수신 중 프로세스 모드: 레플리카 인스턴스는 WAL 데이터를 수신하고 적용하는 동안 프로세스 자체가 쓰기를 거부하는 모드로 동작합니다. 이는 일반적인 권한 문제와는 다르며, superuser 권한으로도 쓰기 작업이 불가능합니다.

2. 테스트 DB 구성 방식 탐색

트랜잭션 롤백: 테스트 프레임워크가 트랜잭션을 열어 변경 사항을 롤백하는 방식은 Express와 같은 백엔드 프레임워크가 내부적으로 트랜잭션을 열고 커밋하는 경우 꼬일 수 있어 적합하지 않다는 결론에 이르렀습니다.
LSN 기반 복구: LSN은 Primary DB의 Point-in-Time Recovery(PITR)에 사용되는 개념으로, 테스트 스키마 하나를 초기화하는 데에는 오버스펙이며 Primary 전체를 롤백할 위험이 있어 부적합했습니다.
Dump/Restore 방식: 테스트 시작 시점에 Primary 또는 Replica의 특정 스키마를 덤프(pg_dump)하여 독립된 테스트 DB에 복원(psql과 sed 활용)하는 방식이 가장 안정적이고 격리된 환경을 제공한다고 판단했습니다.

3. 아키텍처 설계: 독립 인스턴스 구축

목표: 노드2(raspiWorker2)에 쓰기 가능한 테스트 DB 인스턴스를 마련하여, 노드1(Primary)의 실제 운영 데이터베이스에 영향을 주지 않으면서 독립적인 테스트 환경을 구축하는 것입니다.
최종 아키텍처:
- 노드2 5432 (Replica): 노드1의 Primary와 WAL 스트리밍으로 실시간 동기화되는 read-only 인스턴스. my_blog DB의 blog, learning 스키마를 복제합니다.
- 노드2 5433 (독립 테스트 인스턴스): 완전히 독립된 PostgreSQL 프로세스로, 별도의 데이터 디렉토리를 사용하며 read/write가 가능합니다. my_blog DB 내에 test_blog 스키마를 생성하여 테스트 데이터로 활용합니다.

4. `test_blog` 스키마 생성 및 복제 과정

데이터 디렉토리 초기화: 노드2에 5433 인스턴스를 위한 새 데이터 디렉토리( /var/lib/postgresql/test)를 생성하고 initdb로 초기화했습니다.
독립 인스턴스 실행: pg_ctl을 사용하여 5433 포트로 새 PostgreSQL 인스턴스를 시작했습니다.
데이터베이스 및 스키마 생성: 5433 인스턴스에 my_blog 데이터베이스를 생성하고, 노드2 5432(Replica)에서 덤프한 blog 스키마를 test_blog로 이름만 변경하여 복원했습니다.
- pg_dump -p 5432 -d my_blog -n blog > /tmp/blog_dump.sql (Replica에서 데이터 덤프)
- sed 's/blog\\./test_blog\\./g; s/SCHEMA blog/SCHEMA test_blog/g' /tmp/blog_dump.sql | psql -p 5433 -d my_blog (blog를 test_blog로 치환하며 복원)
에러 처리:
- role "jcw" does not exist: dump 파일에 포함된 jcw 권한 설정을 위해 5433 인스턴스에 jcw 역할을 새로 생성했습니다.
- function public.update_updated_at_column() does not exist: blog 스키마만 덤프할 때 public 스키마의 트리거 함수가 누락되었습니다. 노드1에서 public 스키마를 따로 덤프하여 5433에 적용한 후, test_blog.posts 및 test_blog.projects 테이블에 대한 트리거를 수동으로 다시 생성했습니다.
  - CREATE TRIGGER ... EXECUTE FUNCTION public.update_updated_at_column();

5. 트리거 함수 이해

update_updated_at_column 함수는 테이블의 UPDATE 작업 시 updated_at 컬럼을 자동으로 현재 시간으로 갱신하는 트리거 함수입니다. 이 함수는 blog 및 learning 스키마의 여러 테이블에서 사용되며, 여러 스키마에서 공유되는 함수이므로 public 스키마에 두는 것이 합리적임을 확인했습니다.

이해한 내용

PostgreSQL 레플리카는 WAL 스트리밍 중에는 프로세스 레벨에서 쓰기 작업이 완전히 차단된다는 점을 명확히 이해했습니다. 이는 단순한 권한 문제가 아니라 근본적인 모드 제약임을 알게 되었습니다.
테스트 환경 구축 시, 프로덕션 DB와 완벽하게 격리된 독립적인 인스턴스를 사용하는 것이 일반적이며, 이는 데이터 무결성을 보장하고 예측 가능한 테스트 환경을 제공함을 알게 되었습니다.
pg_dump, pg_ctl, initdb, psql, sed 등 PostgreSQL 관리 및 데이터 조작에 사용되는 여러 유용한 명령어들의 역할과 사용법을 익혔습니다.
데이터베이스 인스턴스, 데이터 디렉토리, 데이터베이스, 스키마, 테이블 간의 계층적 구조를 명확히 이해하게 되었습니다.

실전 적용

백엔드 API 통합 테스트: Express와 같은 백엔드 애플리케이션이 데이터베이스와 올바르게 통신하고 CRUD 작업을 수행하는지 검증하는 데 이 구조를 활용할 수 있습니다. Express 애플리케이션을 설정하여 5433 포트의 test_blog 스키마를 바라보게 하면 됩니다.
데이터 마이그레이션 시뮬레이션: 데이터 구조 변경 후 실제 운영 DB에 적용하기 전, test_blog 스키마에 변경된 구조를 적용하고 테스트하여 잠재적인 문제를 미리 발견할 수 있습니다.
성능 테스트: 실제 운영 데이터의 스냅샷을 test_blog 스키마에 복원하여 다양한 쿼리나 작업의 성능을 테스트하고 병목 지점을 찾을 수 있습니다.

추가 학습 계획

PostgreSQL 고가용성(HA) 구성: 이번 학습에서 레플리카 구성을 다루면서, Failover 및 Switchover와 같은 고가용성 관련 기술에 대한 이해를 높이고 싶습니다.
Docker를 활용한 DB 환경 구축: 로컬 개발 환경에서 PostgreSQL 인스턴스를 더 쉽고 격리적으로 관리하기 위해 Docker Compose를 활용하는 방법을 학습할 예정입니다.
트리거 함수 심화: update_updated_at_column 함수 외에 더 복잡한 비즈니스 로직을 처리하는 트리거 함수 작성 및 관리 기법을 심도 있게 학습하고자 합니다.

참고 자료

PostgreSQL Documentation: PostgreSQL 공식 문서를 통해 pg_dump, pg_ctl, initdb 및 기타 관리 명령어에 대한 상세 정보를 찾아볼 예정입니다.
Stack Overflow: 복잡한 DB 설정이나 에러 발생 시 유용한 해결책과 다양한 사례를 참고할 것입니다.