[이제와서 시작하는 Docker 마스터하기 - 고급편 #4] Docker로 데이터베이스 운영

“이제와서 시작하는 Docker 마스터하기” 데이터베이스는 애플리케이션의 핵심입니다. Docker로 데이터베이스를 운영하면 개발과 테스트가 쉬워지지만, 프로덕션 환경에서는 추가적인 고려사항이 필요합니다. 이번 편에서는 주요 데이터베이스를 Docker로 안전하게 운영하는 방법을 알아보겠습니다.

PostgreSQL 운영

1. 기본 PostgreSQL 설정

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: postgres_db
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-password}
      POSTGRES_DB: ${POSTGRES_DB:-myapp}
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    ports:
      - "5432:5432"
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER:-postgres}"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped
    networks:
      - db-network

volumes:
  postgres_data:
    driver: local

networks:
  db-network:
    driver: bridge

2. PostgreSQL 커스텀 설정

# postgresql.conf
# 연결 설정
listen_addresses = '*'
max_connections = 200

# 메모리 설정
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
work_mem = 4MB

# 체크포인트
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

# 로깅
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'
log_duration = on

# 성능 튜닝
random_page_cost = 1.1
effective_io_concurrency = 200

3. 초기화 스크립트

-- init-scripts/01-create-extensions.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- init-scripts/02-create-user.sql
CREATE USER app_user WITH PASSWORD 'app_password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

-- init-scripts/03-create-tables.sql
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

MySQL 운영

1. MySQL 설정

# docker-compose.yml
version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql_db
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-rootpassword}
      MYSQL_DATABASE: ${MYSQL_DATABASE:-myapp}
      MYSQL_USER: ${MYSQL_USER:-appuser}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-apppassword}
    volumes:
      - mysql_data:/var/lib/mysql
      - ./my.cnf:/etc/mysql/conf.d/my.cnf
      - ./init-mysql:/docker-entrypoint-initdb.d
    ports:
      - "3306:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      timeout: 20s
      retries: 10
    restart: unless-stopped
    networks:
      - db-network

volumes:
  mysql_data:

networks:
  db-network:

2. MySQL 커스텀 설정

# my.cnf
[mysqld]
# 기본 설정
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-authentication-plugin = mysql_native_password

# InnoDB 설정
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# 쿼리 캐시
query_cache_type = 1
query_cache_size = 128M

# 연결 설정
max_connections = 500
connect_timeout = 10

# 로깅
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 성능 스키마
performance_schema = ON

MongoDB 운영

1. MongoDB Replica Set

# docker-compose.yml
version: '3.8'

services:
  mongo1:
    image: mongo:6
    container_name: mongo1
    command: mongod --replSet rs0 --bind_ip_all
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password
    volumes:
      - mongo1_data:/data/db
      - mongo1_config:/data/configdb
    ports:
      - "27017:27017"
    networks:
      - mongo-network

  mongo2:
    image: mongo:6
    container_name: mongo2
    command: mongod --replSet rs0 --bind_ip_all
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password
    volumes:
      - mongo2_data:/data/db
      - mongo2_config:/data/configdb
    ports:
      - "27018:27017"
    networks:
      - mongo-network

  mongo3:
    image: mongo:6
    container_name: mongo3
    command: mongod --replSet rs0 --bind_ip_all
    environment:
      MONGO_INITDB_ROOT_USERNAME: admin
      MONGO_INITDB_ROOT_PASSWORD: password
    volumes:
      - mongo3_data:/data/db
      - mongo3_config:/data/configdb
    ports:
      - "27019:27017"
    networks:
      - mongo-network

  mongo-init:
    image: mongo:6
    depends_on:
      - mongo1
      - mongo2
      - mongo3
    volumes:
      - ./scripts:/scripts
    command: bash /scripts/mongo-init.sh
    networks:
      - mongo-network

volumes:
  mongo1_data:
  mongo1_config:
  mongo2_data:
  mongo2_config:
  mongo3_data:
  mongo3_config:

networks:
  mongo-network:

2. MongoDB 초기화 스크립트

#!/bin/bash
# scripts/mongo-init.sh

echo "Waiting for MongoDB nodes to start..."
sleep 10

echo "Initializing replica set..."
mongosh --host mongo1:27017 -u admin -p password --authenticationDatabase admin <<EOF
rs.initiate({
  _id: "rs0",
  members: [
    { _id: 0, host: "mongo1:27017" },
    { _id: 1, host: "mongo2:27017" },
    { _id: 2, host: "mongo3:27017" }
  ]
});
EOF

echo "Waiting for replica set to be ready..."
sleep 5

echo "Creating application user..."
mongosh --host mongo1:27017 -u admin -p password --authenticationDatabase admin <<EOF
use myapp;
db.createUser({
  user: "appuser",
  pwd: "apppassword",
  roles: [
    { role: "readWrite", db: "myapp" }
  ]
});
EOF

echo "MongoDB initialization complete!"

Redis 운영

1. Redis Cluster

# docker-compose.yml
version: '3.8'

services:
  redis-master:
    image: redis:7-alpine
    container_name: redis-master
    command: redis-server /usr/local/etc/redis/redis.conf
    volumes:
      - ./redis-master.conf:/usr/local/etc/redis/redis.conf
      - redis_master_data:/data
    ports:
      - "6379:6379"
    networks:
      - redis-network

  redis-slave:
    image: redis:7-alpine
    container_name: redis-slave
    command: redis-server /usr/local/etc/redis/redis.conf
    volumes:
      - ./redis-slave.conf:/usr/local/etc/redis/redis.conf
      - redis_slave_data:/data
    ports:
      - "6380:6379"
    depends_on:
      - redis-master
    networks:
      - redis-network

  redis-sentinel:
    image: redis:7-alpine
    container_name: redis-sentinel
    command: redis-sentinel /usr/local/etc/redis/sentinel.conf
    volumes:
      - ./sentinel.conf:/usr/local/etc/redis/sentinel.conf
    ports:
      - "26379:26379"
    depends_on:
      - redis-master
      - redis-slave
    networks:
      - redis-network

volumes:
  redis_master_data:
  redis_slave_data:

networks:
  redis-network:

2. Redis 설정 파일

# redis-master.conf
bind 0.0.0.0
port 6379
requirepass yourpassword
masterauth yourpassword

# 영속성
save 900 1
save 300 10
save 60 10000
appendonly yes
appendfsync everysec

# 메모리 정책
maxmemory 2gb
maxmemory-policy allkeys-lru

# 로깅
loglevel notice
logfile /data/redis.log

# redis-slave.conf
bind 0.0.0.0
port 6379
requirepass yourpassword
masterauth yourpassword
replicaof redis-master 6379

# 읽기 전용
replica-read-only yes

백업과 복원

1. PostgreSQL 백업

#!/bin/bash
# backup-postgres.sh

BACKUP_DIR="/backup/postgres"
DB_NAME="myapp"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# 디렉토리 생성
mkdir -p $BACKUP_DIR

# 백업 실행
docker exec postgres_db pg_dump -U postgres -d $DB_NAME | gzip > $BACKUP_DIR/backup_$TIMESTAMP.sql.gz

# 오래된 백업 삭제 (7일 이상)
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

echo "Backup completed: backup_$TIMESTAMP.sql.gz"

#!/bin/bash
# restore-postgres.sh

BACKUP_FILE=$1
DB_NAME="myapp"

if [ -z "$BACKUP_FILE" ]; then
    echo "Usage: $0 <backup_file>"
    exit 1
fi

# 기존 연결 종료
docker exec postgres_db psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$DB_NAME' AND pid <> pg_backend_pid();"

# 데이터베이스 재생성
docker exec postgres_db psql -U postgres -c "DROP DATABASE IF EXISTS $DB_NAME;"
docker exec postgres_db psql -U postgres -c "CREATE DATABASE $DB_NAME;"

# 복원
gunzip -c $BACKUP_FILE | docker exec -i postgres_db psql -U postgres -d $DB_NAME

echo "Restore completed from: $BACKUP_FILE"

2. MySQL 백업

# docker-compose.yml에 추가
  mysql-backup:
    image: databack/mysql-backup
    container_name: mysql-backup
    environment:
      - DB_SERVER=mysql
      - DB_USER=root
      - DB_PASS=${MYSQL_ROOT_PASSWORD:-rootpassword}
      - DB_DUMP_FREQ=1440  # 매일
      - DB_DUMP_BEGIN=0200  # 새벽 2시
      - DB_DUMP_TARGET=/backup
      - COMPRESSION=gzip
    volumes:
      - ./backup:/backup
    depends_on:
      - mysql
    networks:
      - db-network

3. MongoDB 백업

#!/bin/bash
# backup-mongo.sh

BACKUP_DIR="/backup/mongo"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# 백업 실행
docker exec mongo1 mongodump \
  --host rs0/mongo1:27017,mongo2:27017,mongo3:27017 \
  -u admin -p password \
  --authenticationDatabase admin \
  --out /backup/dump_$TIMESTAMP

# 압축
tar -czf $BACKUP_DIR/mongodb_backup_$TIMESTAMP.tar.gz -C /backup dump_$TIMESTAMP

# 임시 파일 삭제
rm -rf /backup/dump_$TIMESTAMP

모니터링

1. 데이터베이스 모니터링 스택

# monitoring-compose.yml
version: '3.8'

services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:password@postgres:5432/myapp?sslmode=disable"
    ports:
      - "9187:9187"
    networks:
      - db-network

  mysql-exporter:
    image: prom/mysqld-exporter
    environment:
      DATA_SOURCE_NAME: "root:rootpassword@(mysql:3306)/"
    ports:
      - "9104:9104"
    networks:
      - db-network

  mongodb-exporter:
    image: percona/mongodb_exporter:0.35
    command:
      - "--mongodb.uri=mongodb://admin:password@mongo1:27017"
      - "--collect-all"
    ports:
      - "9216:9216"
    networks:
      - mongo-network

  redis-exporter:
    image: oliver006/redis_exporter
    environment:
      REDIS_ADDR: "redis-master:6379"
      REDIS_PASSWORD: "yourpassword"
    ports:
      - "9121:9121"
    networks:
      - redis-network

2. Grafana 대시보드

// PostgreSQL 대시보드 설정
{
  "dashboard": {
    "title": "PostgreSQL Monitoring",
    "panels": [
      {
        "title": "Active Connections",
        "targets": [{
          "expr": "pg_stat_activity_count"
        }]
      },
      {
        "title": "Database Size",
        "targets": [{
          "expr": "pg_database_size_bytes"
        }]
      },
      {
        "title": "Transaction Rate",
        "targets": [{
          "expr": "rate(pg_stat_database_xact_commit[5m])"
        }]
      }
    ]
  }
}

성능 최적화

1. 볼륨 최적화

services:
  postgres:
    volumes:
      # 성능 향상을 위한 tmpfs 사용
      - postgres_data:/var/lib/postgresql/data
      - type: tmpfs
        target: /tmp
        tmpfs:
          size: 1G

volumes:
  postgres_data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /ssd/postgres  # SSD 사용

2. 리소스 제한

services:
  postgres:
    deploy:
      resources:
        limits:
          cpus: '4'
          memory: 8G
        reservations:
          cpus: '2'
          memory: 4G

고가용성 구성

PostgreSQL HA

# postgres-ha-compose.yml
version: '3.8'

services:
  postgres-master:
    image: bitnami/postgresql:15
    environment:
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=replicator
      - POSTGRESQL_REPLICATION_PASSWORD=replicatorpassword
      - POSTGRESQL_USERNAME=postgres
      - POSTGRESQL_PASSWORD=password
      - POSTGRESQL_DATABASE=myapp
    volumes:
      - postgres_master_data:/bitnami/postgresql

  postgres-slave:
    image: bitnami/postgresql:15
    depends_on:
      - postgres-master
    environment:
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=replicator
      - POSTGRESQL_REPLICATION_PASSWORD=replicatorpassword
      - POSTGRESQL_MASTER_HOST=postgres-master
      - POSTGRESQL_PASSWORD=password
      - POSTGRESQL_MASTER_PORT_NUMBER=5432
    volumes:
      - postgres_slave_data:/bitnami/postgresql

  pgpool:
    image: bitnami/pgpool:4
    depends_on:
      - postgres-master
      - postgres-slave
    environment:
      - PGPOOL_BACKEND_NODES=0:postgres-master:5432,1:postgres-slave:5432
      - PGPOOL_SR_CHECK_USER=replicator
      - PGPOOL_SR_CHECK_PASSWORD=replicatorpassword
      - PGPOOL_ENABLE_LDAP=no
      - PGPOOL_POSTGRES_USERNAME=postgres
      - PGPOOL_POSTGRES_PASSWORD=password
      - PGPOOL_ADMIN_USERNAME=admin
      - PGPOOL_ADMIN_PASSWORD=adminpassword
    ports:
      - "5432:5432"

volumes:
  postgres_master_data:
  postgres_slave_data:

보안 고려사항

1. 네트워크 격리

networks:
  frontend:
    external: false
  backend:
    internal: true  # 외부 접근 차단

services:
  app:
    networks:
      - frontend
      - backend
  
  postgres:
    networks:
      - backend  # 내부 네트워크만

2. 시크릿 관리

# docker-compose.yml
version: '3.8'

secrets:
  postgres_password:
    file: ./secrets/postgres_password.txt
  
services:
  postgres:
    image: postgres:15
    secrets:
      - postgres_password
    environment:
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password

문제 해결

일반적인 문제들

# 연결 문제 디버깅
docker exec postgres_db psql -U postgres -c "SELECT * FROM pg_stat_activity;"

# 느린 쿼리 확인
docker exec postgres_db psql -U postgres -c "SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;"

# 로그 확인
docker logs postgres_db --tail 100 -f

# 디스크 사용량 확인
docker exec postgres_db df -h /var/lib/postgresql/data

마무리

Docker로 데이터베이스를 운영할 때는 데이터 영속성, 백업, 성능, 보안을 모두 고려해야 합니다. 개발 환경에서는 간단하게 시작할 수 있지만, 프로덕션에서는 신중한 계획이 필요합니다. 다음 편에서는 Docker 이미지 최적화 기법을 자세히 알아보겠습니다.

다음 편 예고

• 이미지 크기 줄이기
• 빌드 시간 단축
• 레이어 캐싱 전략
• 멀티 스테이지 빌드 고급 기법

효율적인 이미지로 더 빠른 배포를! 🚀

📚 Docker 마스터하기 시리즈

🐳 기초편 (입문자용 - 5편)

1. Docker란 무엇인가?
2. Docker 설치 및 환경 설정
3. 첫 번째 컨테이너 실행하기
4. Docker 이미지 이해하기
5. Dockerfile 작성하기

💼 실전편 (중급자용 - 6편)

1. Docker 네트워크 기초
2. Docker 볼륨과 데이터 관리
3. Docker Compose 입문
4. 멀티 컨테이너 애플리케이션
5. Docker Hub 활용하기
6. Docker 보안 베스트 프랙티스

🚀 고급편 (전문가용 - 9편)

1. Docker 로그와 모니터링
2. Docker로 Node.js 애플리케이션 배포
3. Docker로 Python 애플리케이션 배포
4. Docker로 데이터베이스 운영 ← 현재 글
5. Docker 이미지 최적화
6. Docker와 CI/CD
7. Docker Swarm 기초
8. 문제 해결과 트러블슈팅
9. Docker 생태계와 미래