우분투 24.04 LTS 서버, 도커 이용 postgresql 18.3 설치

디렉토리 구조 및 설계 원칙

/opt/postgres/   → 설정파일, 일반 로그 (pg_log)
/data/postgres/  → 데이터파일 (PGDATA, pg_data)
/wal/postgres/   → WAL 트랜잭션 로그 (pg_wal)

각 환경별 서브디렉토리:
  dev/     → 개발
  staging/ → 스테이징
  prod/    → 운영

※ WAL을 별도 물리 볼륨으로 분리하면 데이터 디스크 I/O와 WAL 쓰기 I/O가 경합하지 않아 성능과 안정성이 모두 향상됩니다.

---

1. 사전 준비 스크립트

#!/bin/bash
# init-postgresql-dirs.sh - 호스트에서 1회 실행

ENVS=("dev" "staging" "prod")

for ENV in "${ENVS[@]}"; do
  sudo mkdir -p /opt/postgres/${ENV}/{logs,config,secrets}
  sudo mkdir -p /data/postgres/${ENV}/pgdata
  sudo mkdir -p /wal/postgres/${ENV}/{pgwal,archive}

  # PostgreSQL 공식 이미지의 내부 UID=999
  sudo chown -R 999:999 /opt/postgres/${ENV}
  sudo chown -R 999:999 /data/postgres/${ENV}
  sudo chown -R 999:999 /wal/postgres/${ENV}

  # pgdata는 700 필수 (PostgreSQL이 퍼미션 체크함)
  sudo chmod 750 /data/postgres/${ENV}/pgdata
  sudo chmod 750 /wal/postgres/${ENV}/pgwal
done

echo "✅ 디렉토리 초기화 완료"

• init-postgresql-dirs.sh 파일생성
• 실행권한 부여: chmod +x init-postgresql-dirs.sh
• 스크립트 실행: ./init-postresql-dirs.sh

생성 디렉토리 구조 확인 ( tree 설치: sudo apt install tree)

• sudo tree /opt/postgres /data/postgres /wal/postgres

---

2. 공통 PostgreSQL 설정 파일

# /opt/postgres/postgresql-base.conf
# 각 환경에서 include하거나 env별로 오버라이드

# --- 연결 ---
listen_addresses = '*'
max_connections = 100          # 환경별 오버라이드

# --- 메모리 ---
shared_buffers = 256MB         # 환경별 오버라이드 (RAM의 25%)
work_mem = 4MB
maintenance_work_mem = 64MB
effective_cache_size = 1GB     # 환경별 오버라이드

# --- WAL 분리 경로 ---
# WAL은 docker-compose에서 심링크 또는 initdb --waldir 로 지정
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
wal_compression = on           # PG17+에서 lz4 가능

# --- 체크포인트 ---
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min

# --- 로그 설정 ---
logging_collector = on
log_directory = '/var/log/postgresql'   # /opt/postgres/xxx/logs 마운트
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000   # 1초 이상 쿼리 기록
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 250ms

# --- 자동 Vacuum ---
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

# --- pg18 신규: 비동기 I/O ---
io_method = io_uring           # Ubuntu 24.04 커널 6.x 지원

---

3. 설정파일 배치

# 작업 디렉토리 구성 (파일을 받은 위치 기준)
ls -la
# documents/dev/docker-compose.dev.yml
# documents/staging/docker-compose.staging.yml
# documents/prod/docker-compose.prod.yml
# documents/dev/postgresql.conf
# documents/staging/postgresql.conf
# documents/prod/postgresql.conf

# postgresql.conf → 각 환경 경로에 복사
sudo cp documents/dev/postgresql.conf     /opt/postgres/dev/config/
sudo cp documents/staging/postgresql.conf /opt/postgres/staging/config/
sudo cp documents/prod/postgresql.conf    /opt/postgres/prod/config/

# 파일 소유자 맞춤
sudo chown 999:999 /opt/postgres/dev/config/postgresql.conf
sudo chown 999:999 /opt/postgres/staging/config/postgresql.conf
sudo chown 999:999 /opt/postgres/prod/config/postgresql.conf

---

4. 시크릿 파일 생성

# staging 비밀번호 파일
echo -n 'staging_strong_password_here' | sudo tee /opt/postgres/staging/secrets/db_password.txt
sudo chmod 600 /opt/postgres/staging/secrets/db_password.txt
sudo chown 999:999 /opt/postgres/staging/secrets/db_password.txt

# prod 비밀번호 파일
echo -n 'prod_very_strong_password_here' | sudo tee /opt/postgres/prod/secrets/db_password.txt
sudo chmod 600 /opt/postgres/prod/secrets/db_password.txt
sudo chown 999:999 /opt/postgres/prod/secrets/db_password.txt

---

5. 컨테이너 기동

# 개발 DB 기동
docker compose -f docker-compose.dev.yml up -d

# 스테이징 DB 기동
docker compose -f docker-compose.staging.yml up -d

# 운영 DB 기동 (app-net 외부 네트워크가 있어야 함)
docker network create app-net   # 최초 1회만
docker compose -f docker-compose.prod.yml up -d

---

6. 상태 확인

# 컨테이너 상태 (healthy 확인)
docker compose -f docker-compose.dev.yml ps

# 실시간 로그 보기
docker compose -f docker-compose.dev.yml logs -f

# PostgreSQL 프로세스 준비 여부
docker exec postgres-dev pg_isready -U postgres -d postgres

# WAL 경로가 실제로 분리됐는지 확인 (-d가 존재하지 않으면 -U의 user명 이름으로 db를 찾음)
docker exec postgres-dev psql -U dev_user -d devdb -c "SHOW data_directory;"
docker exec postgres-dev psql -U dev_user -d devdb -c "SHOW wal_level;"
docker exec postgres-dev psql -U dev_user -d devdb -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

# 타임존 확인
docker exec postgres-dev psql -U dev_user -c "SHOW timezone;"

---

7. 설치확인

# 컨테이너 들어가기
docker exec -it postgres-dev bash

# 내부에서 확인
which psql
  -> /usr/bin/psql
  
# 데이터 위치 확인
psql -U dev_user -d devdb -c "SHOW data_directory;"

---

8. 접속 테스트

# 호스트에서 직접 접속 (dev)
psql -h localhost -p 5432 -U dev_user -d devdb

# 컨테이너 bash 사용
docker exec -it postgres-dev bash

# staging은 루프백만 열려 있으므로 SSH 터널 사용
ssh -L 5433:127.0.0.1:5433 user@staging-server
psql -h 127.0.0.1 -p 5433 -U staging_user -d stagingdb

# 접속 후 기본 확인 쿼리
SELECT version();
SELECT current_setting('timezone');
SELECT current_setting('wal_level');
SELECT current_setting('shared_buffers');

---

9. 자주 쓰는 명령어

# 설정 변경 후 재시작 없이 반영 (일부 파라미터만 가능)
docker exec postgres-prod psql -U prod_user -c "SELECT pg_reload_conf();"

# 재시작이 필요한 파라미터 변경 시
docker compose -f docker-compose.prod.yml restart

# 컨테이너 완전 중지 (데이터 보존)
docker compose -f docker-compose.prod.yml down

# pgdata 비우고 재초기화
docker compose -f docker-compose.prod.yml down
sudo rm -rf /data/postgres/prod/pgdata/*
docker compose -f docker-compose.prod.yml up -d

# 로그 파일 위치 확인
ls -lh /opt/postgres/prod/logs/

# 실행 중인 쿼리 모니터링
docker exec postgres-prod psql -U prod_user -d proddb -c "
  SELECT pid, now() - query_start AS duration, state, query
  FROM pg_stat_activity
  WHERE state != 'idle'
  ORDER BY duration DESC;"

# 수동 백업 (개발 환경)
docker exec postgres-dev pg_dump -U dev_user devdb > backup_$(date +%Y%m%d).sql
```

---

## 전체 파일 배치 최종 확인
```
/
├── opt/postgres/
│   ├── dev/config/postgresql.conf
│   ├── staging/config/postgresql.conf
│   ├── staging/secrets/db_password.txt
│   ├── prod/config/postgresql.conf
│   └── prod/secrets/db_password.txt
│
├── data/postgres/{dev,staging,prod}/pgdata/   ← PostgreSQL이 자동 초기화
└── wal/postgres/{dev,staging,prod}/pgwal/     ← initdb --waldir로 분리됨

---

10. postgresql 18.3 삭제

• Docker Compose 리소스 삭제

# dev 설정 파일을 지정하여 컨테이너 및 이미지 삭제
docker compose -f docker-compose.dev.yml down --rmi all

 

• 호스트 데이터 디렉토리 완전 삭제

# PostgreSQL 데이터 및 WAL 파일 삭제
sudo rm -rf /data/postgres/dev/pgdata
sudo rm -rf /wal/postgres/dev/pgwal

# 로그 및 설정 파일 삭제
sudo rm -rf /opt/postgres/dev/logs
sudo rm -rf /opt/postgres/dev/config/postgresql.conf

---

11. 운영 백업 스크립트

#!/bin/bash
# /opt/postgres/prod/scripts/backup.sh
# cron: 0 2 * * * /opt/postgres/prod/scripts/backup.sh

BACKUP_DIR="/backup/postgres/prod"
CONTAINER="postgres-prod"
DATE=$(date +%Y%m%d_%H%M%S)
RETAIN_DAYS=14

mkdir -p ${BACKUP_DIR}

# pg_basebackup 방식 (WAL 포함 일관된 백업)
docker exec ${CONTAINER} pg_basebackup \
  -U prod_user \
  -D /tmp/backup_${DATE} \
  -Ft -z \
  --wal-method=stream \
  --checkpoint=fast \
  --progress

docker cp ${CONTAINER}:/tmp/backup_${DATE} ${BACKUP_DIR}/
docker exec ${CONTAINER} rm -rf /tmp/backup_${DATE}

# 보관 기간 초과 백업 삭제
find ${BACKUP_DIR} -maxdepth 1 -type d -mtime +${RETAIN_DAYS} -exec rm -rf {} \;

echo "✅ 백업 완료: ${BACKUP_DIR}/backup_${DATE}"
```

---

## 전체 구조 요약
```
/
├── opt/postgres/
│   ├── dev/
│   │   ├── config/postgresql.conf
│   │   └── logs/
│   ├── staging/
│   │   ├── config/postgresql.conf
│   │   ├── secrets/db_password.txt
│   │   └── logs/
│   └── prod/
│       ├── config/postgresql.conf
│       ├── secrets/db_password.txt
│       ├── scripts/backup.sh
│       └── logs/
│
├── data/postgres/
│   ├── dev/pgdata/       ← PGDATA (테이블, 인덱스)
│   ├── staging/pgdata/
│   └── prod/pgdata/
│
└── wal/postgres/
    ├── dev/pgwal/        ← pg_wal (트랜잭션 로그)
    ├── staging/pgwal/
    └── prod/pgwal/

 

---

실무 핵심 주의사항

이미지 버전	postgres:18.3 고정. latest 절대 사용 금지
WAL 분리	POSTGRES_INITDB_ARGS에 --waldir 지정해야 실제 분리됨
포트 노출	운영은 ports 미사용, expose만. 앱과 동일 Docker 네트워크
시크릿	비밀번호는 환경변수 직접 입력 금지 → _FILE + Docker Secrets
data-checksums	운영만 활성화 (초기화 시 1회만 지정 가능, 나중에 변경 불가)
io_uring	Ubuntu 24.04 커널 6.8 이상에서 정상 동작, PG18 신기능
백업	pg_dump보다 pg_basebackup이 WAL 포함 일관성 보장
업그레이드	minor(18.3→18.4): 이미지 교체 후 재시작 / major: pg_upgrade 필요