postgresql18.3 로컬 주소DB 구축 (1) - DB 생성

1. 주소(juso)DB 구축 디렉토리 설정

데이터와 트랜잭션로그를 분리한다.

# PostgreSQL 공식 이미지의 내부 UID=999

# pgdata는 700 필수 (PostgreSQL이 퍼미션 체크함)

sudo mkdir -p /opt/postgres/juso/{logs,config,secrets}
sudo mkdir -p /data/postgres/juso/pgdata
sudo mkdir -p /wal/postgres/juso/{pgwal,archive}

sudo chown -R 999:999 /opt/postgres/juso
sudo chown -R 999:999 /data/postgres/juso
sudo chown -R 999:999 /wal/postgres/juso

sudo chmod 750 /data/postgres/juso/pgdata
sudo chmod 750 /wal/postgres/juso/pgwal
 
echo "✅ 디렉토리 초기화 완료"
sudo tree /opt/postgres /data/postgres /wal/postgres

---

2. docker-compose.yml 파일 작성

# 저장위치: ~/projects/postgresql18.3/juso/docker-compose.yml

# =============================================================
# PostgreSQL 18.3 - 개발급 환경
# 경로: /opt/postgres/juso | /data/postgres/juso | /wal/postgres/juso
# =============================================================
name: postgres-juso

services:
  postgres-juso:
    image: postgres:18.3
    container_name: postgres-juso
    restart: unless-stopped

    # ── 타임존 ─────────────────────────────────────────
    # 앱 서버, 로그 수집기와 반드시 통일
    environment:
      TZ: Asia/Seoul
      PGTZ: Asia/Seoul                            # PostgreSQL 내부 timezone
      POSTGRES_USER: juso_admin
      POSTGRES_PASSWORD: juso1#   # 개발환경: 평문 허용
      POSTGRES_DB: juso
      PGDATA: /var/lib/postgresql/data
      POSTGRES_INITDB_ARGS: >-
        --waldir=/var/lib/postgresql/wal
        --encoding=UTF8
        --locale=C
        --auth-host=scram-sha-256

    # ── 공유 메모리 ───────────────────────────────────────
    # Docker 기본 64MB → shared_buffers 설정값보다 반드시 크게
    # 공식 권장: shared_buffers × 1.2 이상
    # 개발 shared_buffers=128MB → shm_size=256MB (여유 2배)
    shm_size: 256mb

    # ── ulimits ──────────────────────────────────────────
    ulimits:
      # 파일 디스크립터: 접속수 × 3~5 + 여유 (개발 max_connections=50)
      nofile:
        soft: 1024
        hard: 4096
      # 메모리 잠금: huge_pages 또는 대형 SHM 영역 잠금에 필요
      memlock:
        soft: 67108864     # 64MB (bytes)
        hard: 67108864

    volumes:
      - /data/postgres/juso/pgdata:/var/lib/postgresql/data
      - /wal/postgres/juso/pgwal:/var/lib/postgresql/wal
      - /opt/postgres/juso/logs:/var/log/postgresql
      - /opt/postgres/juso/config/postgresql.conf:/etc/postgresql/postgresql.conf:ro

    command: >
      postgres -c config_file=/etc/postgresql/postgresql.conf

    ports:
      - "5032:5432"

    networks:
      - postgres-juso-net

    deploy:
      resources:
        limits:
          memory: 1G
          cpus: "1.0"

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U juso_admin -d juso"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

    logging:
      driver: json-file
      options:
        max-size: "50m"
        max-file: "3"

networks:
  postgres-juso-net:
    driver: bridge

---

3. postgresql.conf 파일 작성

# 저장위치: ~/projects/postgresql18.3/juso/postgresql.conf

# =============================================================
# postgresql.conf - 개발환경급 설정
# 목표: 빠른 피드백, 전체 쿼리 로깅, WAL 최소화
# =============================================================

# ── 연결 ───────────────────────
listen_addresses = '*'
port = 5432
max_connections = 50

# ── 타임존 ──────────────────────
timezone = 'Asia/Seoul'
log_timezone = 'Asia/Seoul'

# ── 메모리 ──────────────────────
shared_buffers = 128MB
work_mem = 8MB
maintenance_work_mem = 64MB
effective_cache_size = 512MB
# shm_size(256MB) > shared_buffers(128MB) ✓

# ── 비동기 I/O (PG18 핵심 성능 기능, 개발은 sync도 무방) ──
io_method = sync 
#io_method = io_uring           # Ubuntu 24.04 커널 6.8+ 필수 -> DB 시작 에러 발생 시 주석 처리
#io_combine_limit = 128kB       # 연속 I/O 병합 최대 크기

# ── WAL 설정 (개발: 최소화) ───────────────
wal_level = minimal          # 복제·아카이브 불필요, 최소 WAL 생성
# wal_level=minimal이면 max_wal_senders=0 강제
max_wal_senders = 0
wal_buffers = 4MB            # 기본값 수준 (shared_buffers의 1/32)
#synchronous_commit = off    # 개발: 비동기 커밋으로 속도 우선
                             # 크래시 시 마지막 수 트랜잭션 유실 가능 (개발 허용)
#COMMIT 시점에 WAL이 디스크에 flush될 때까지 기다림
synchronous_commit = on		 # 데이터 유실 없음, latency 증가
							 # 실무전략: 기본값(conf) on + 특정작업(sql) off

# 체크포인트 (개발: 빠른 재시작 위해 짧게)
checkpoint_timeout = 5min
max_wal_size = 512MB
min_wal_size = 32MB
checkpoint_completion_target = 0.9
checkpoint_warning = 30s     # 체크포인트가 30초 넘으면 경고

# WAL 압축 (PG15+, lz4가 zstd보다 CPU 낮음)
wal_compression = lz4

# WAL 아카이브: 개발은 비활성화
archive_mode = off

# ── 로그 설정 ────────────────────────
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# 개발: 모든 쿼리 + 실행계획 파라미터 기록
log_min_duration_statement = 0      # 전체 쿼리 기록
log_statement = 'all'
log_error_verbosity = verbose
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 = 0     # autovacuum 전체 기록
deadlock_timeout = 1s

# ── Autovacuum ─────────────────────
autovacuum = on
autovacuum_max_workers = 2          # 개발: 부하 최소화
autovacuum_naptime = 1min
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1

# ── 통계 ──────────────────────────
track_activities = on
track_counts = on
track_io_timing = on
track_wal_io_timing = on            # PG15+

---

4. 설정파일 배치

# postgresql.conf → 운영 경로에 복사

# 파일 소유자 설정

sudo cp ~/projects/postgresql18.3/juso/postgresql.conf     /opt/postgres/juso/config/
sudo chown 999:999 /opt/postgres/juso/config/postgresql.conf

---

5. 컨테이너 기동

docker compose -f docker-compose.yml up -d

---

6. 상태 확인

# 컨테이너 상태 (healthy 확인)

docker compose -f docker-compose.yml ps

 

# 실시간 로그 보기

docker compose -f docker-compose.yml logs -f

docker compose -f docker-compose.yml logs -f | less +F

docker compose -f docker-compose.yml logs -f --tail 50

 

# PostgreSQL 프로세스 준비 여부

docker exec postgres-juso pg_isready -U juso_admin -d juso

 

# WAL 경로가 실제로 분리됐는지 확인 (-d가 존재하지 않으면 -U의 user명 이름으로 db를 찾음)

docker exec postgres-juso psql -U juso_admin -d juso -c "SHOW data_directory;"
docker exec postgres-juso psql -U juso_admin -d juso -c "SHOW wal_level;"
docker exec postgres-juso psql -U juso_admin -d juso -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

 

# 타임존 확인

docker exec postgres-juso psql -U juso_admin -d juso -c "SHOW timezone;"

---

7. 설치확인

# 컨테이너 들어가기

docker exec -it postgres-juso bash

 

# 내부에서 확인

which psql

• 결과
  /usr/bin/psql

# 데이터 위치 확인

psql -U juso_admin -d juso -c "SHOW data_directory;"

---

8. 접속 테스트

# 호스트에서 직접 접속

psql -h localhost -p 5432 -U juso_admin -d juso

 

# 컨테이너 bash 사용

docker exec -it postgres-juso bash

 

# 접속 후 기본 확인 쿼리

SELECT version();
SELECT current_setting('timezone');
SELECT current_setting('wal_level');
SELECT current_setting('shared_buffers');

---

9. 자주 쓰는 명령어

# 설정 변경 후 재시작 없이 반영 (일부 파라미터만 가능)

docker exec postgres-juso psql -U juso_admin -d juso -c "SELECT pg_reload_conf();"

 

# 재시작이 필요한 파라미터 변경 시

docker compose -f docker-compose.yml restart

 

# 컨테이너 완전 중지 (데이터 보존)

docker compose -f docker-compose.yml down

 

# pgdata 비우고 재초기화

docker compose -f docker-compose.yml down
sudo rm -rf /data/postgres/juso/pgdata/*
docker compose -f docker-compose.yml up -d

 

# 로그 파일 위치 확인

ls -lh /opt/postgres/juso/logs/

 

# 실행 중인 쿼리 모니터링

docker exec postgres-juso psql -U juso_admin -d juso -c "
  SELECT pid, now() - query_start AS duration, state, query
  FROM pg_stat_activity
  WHERE state != 'idle'
  ORDER BY duration DESC;"

 

# 수동 백업 (개발 환경)

docker exec postgres-juso pg_dump -U juso_admin -d juso > backup_$(date +%Y%m%d).sql

---

10. postgresql 18.3 삭제

# Docker Compose 리소스 삭제 : yml(설정) 파일을 지정하여 컨테이너 및 이미지 삭제

docker compose -f docker-compose.yml down --rmi all

 

# PostgreSQL 데이터 및 WAL 파일 삭제

sudo rm -rf /data/postgres/juso/pgdata
sudo rm -rf /wal/postgres/juso/pgwal
sudo tree /opt/postgres /data/postgres /wal/postgres

 

# 로그 및 설정 파일 삭제

sudo rm -rf /opt/postgres/juso/logs
sudo rm -rf /opt/postgres/juso/config/postgresql.conf

 

# 디렉토리 및 소유권 재생성

sudo mkdir -p /data/postgres/juso/pgdata
sudo mkdir -p /wal/postgres/juso/pgwal

sudo chown -R 999:999 /data/postgres/juso
sudo chown -R 999:999 /wal/postgres/juso

sudo chmod 750 /data/postgres/juso/pgdata
sudo chmod 750 /wal/postgres/juso/pgwal

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