🤔 왜 데이터베이스를 컨테이너로 운영할까?

💾 데이터 영속성 전략: 볼륨이 답이다

📌 볼륨 타입 비교

🔧 실전 볼륨 설정 예제

version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: my-postgres
    environment:
      POSTGRES_USER: dbuser
      POSTGRES_PASSWORD: securepassword
      POSTGRES_DB: myapp
    volumes:
      # Named Volume 사용 (권장)
      - postgres_data:/var/lib/postgresql/data
      # 초기화 스크립트
      - ./init-scripts:/docker-entrypoint-initdb.d
      # 커스텀 설정 파일
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    ports:
      - "5432:5432"
    restart: unless-stopped
    # 리소스 제한
    deploy:
      resources:
        limits:
          cpus: '2'
          memory: 4G
        reservations:
          cpus: '1'
          memory: 2G

volumes:
  postgres_data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /data/postgres

🔐 보안 설정: 안전한 DB 컨테이너 만들기

🛡️ 필수 보안 체크리스트

# .env 파일 사용 (반드시 .gitignore에 추가!)
POSTGRES_PASSWORD=your_secure_password_here
POSTGRES_USER=dbadmin
DB_NAME=production_db

# docker-compose.yml에서 참조
services:
  postgres:
    image: postgres:15-alpine
    env_file:
      - .env
    # 또는 Docker Secrets 사용 (Swarm 모드)
    secrets:
      - db_password
      - db_user

secrets:
  db_password:
    file: ./secrets/db_password.txt
  db_user:
    file: ./secrets/db_user.txt

version: '3.8'

services:
  app:
    image: myapp:latest
    networks:
      - frontend
      - backend
    ports:
      - "80:8080"
  
  postgres:
    image: postgres:15-alpine
    networks:
      - backend  # 백엔드 네트워크만 연결
    # ports를 명시하지 않으면 외부 접근 불가
    environment:
      POSTGRES_PASSWORD_FILE: /run/secrets/db_password

networks:
  frontend:
    driver: bridge
  backend:
    driver: bridge
    internal: true  # 외부 인터넷 접근 차단

services:
  postgres:
    image: postgres:15-alpine
    read_only: true  # 루트 파일시스템 읽기 전용
    tmpfs:
      - /tmp
      - /var/run/postgresql
    volumes:
      - postgres_data:/var/lib/postgresql/data

⚡ 성능 최적화: 빠르고 효율적인 DB 컨테이너

🎯 리소스 제한 및 할당

services:
  mysql:
    image: mysql:8.0
    deploy:
      resources:
        limits:
          cpus: '4'      # 최대 4코어 사용
          memory: 8G     # 최대 8GB 메모리
        reservations:
          cpus: '2'      # 최소 2코어 보장
          memory: 4G     # 최소 4GB 보장
    # MySQL 특화 설정
    command: >
      --max-connections=500
      --innodb-buffer-pool-size=4G
      --innodb-log-file-size=512M
      --innodb-flush-log-at-trx-commit=2
      --innodb-flush-method=O_DIRECT

💿 스토리지 드라이버 선택

🔥 캐싱 전략

# Redis를 캐시 레이어로 활용
version: '3.8'

services:
  app:
    image: myapp:latest
    depends_on:
      - redis
      - postgres
    environment:
      CACHE_URL: redis://redis:6379
      DATABASE_URL: postgresql://postgres:5432/mydb
  
  redis:
    image: redis:7-alpine
    command: >
      redis-server
      --maxmemory 2gb
      --maxmemory-policy allkeys-lru
      --save ""  # 영속성 비활성화 (순수 캐시용)
    volumes:
      - redis_data:/data
  
  postgres:
    image: postgres:15-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_SHARED_BUFFERS: 2GB
      POSTGRES_EFFECTIVE_CACHE_SIZE: 6GB

volumes:
  redis_data:
  postgres_data:

🔄 백업 및 복구 전략

📦 백업 방법 비교

#!/bin/bash
# PostgreSQL 볼륨 백업 스크립트

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

# 볼륨을 tar로 압축
docker run --rm \
  -v ${VOLUME_NAME}:/data \
  -v ${BACKUP_DIR}:/backup \
  alpine \
  tar czf /backup/postgres_${TIMESTAMP}.tar.gz -C /data .

# 30일 이상 된 백업 삭제
find ${BACKUP_DIR} -name "postgres_*.tar.gz" -mtime +30 -delete

echo "백업 완료: postgres_${TIMESTAMP}.tar.gz"

#!/bin/bash
# PostgreSQL 논리적 백업

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

# pg_dump 실행
docker exec my-postgres pg_dump -U dbuser -Fc myapp > \
  ${BACKUP_DIR}/myapp_${TIMESTAMP}.dump

# 또는 모든 데이터베이스 백업
docker exec my-postgres pg_dumpall -U postgres > \
  ${BACKUP_DIR}/all_databases_${TIMESTAMP}.sql

# S3에 업로드 (선택사항)
aws s3 cp ${BACKUP_DIR}/myapp_${TIMESTAMP}.dump \
  s3://my-backup-bucket/postgres/

# ZFS 스냅샷 예제
zfs snapshot tank/docker/volumes/postgres_data@backup_$(date +%Y%m%d_%H%M%S)

# Btrfs 스냅샷 예제
btrfs subvolume snapshot /var/lib/docker/volumes/postgres_data \
  /backup/postgres_snapshot_$(date +%Y%m%d_%H%M%S)

♻️ 복구 프로세스

# 볼륨 백업에서 복구
docker run --rm \
  -v postgres_data:/data \
  -v /backup/postgres:/backup \
  alpine \
  sh -c "cd /data && tar xzf /backup/postgres_20240115_120000.tar.gz"

# 논리적 백업에서 복구
docker exec -i my-postgres pg_restore -U dbuser -d myapp < \
  /backup/postgres/dumps/myapp_20240115_120000.dump

# 또는 SQL 파일에서 복구
docker exec -i my-postgres psql -U postgres < \
  /backup/postgres/dumps/all_databases_20240115_120000.sql

📊 모니터링과 로깅

📈 모니터링 스택 구성

version: '3.8'

services:
  # 프로덕션 DB
  postgres:
    image: postgres:15-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    labels:
      - "prometheus.io/scrape=true"
      - "prometheus.io/port=9187"
  
  # PostgreSQL Exporter (Prometheus용)
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:${DB_PASSWORD}@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on:
      - postgres
  
  # Prometheus
  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
      - prometheus_data:/prometheus
    ports:
      - "9090:9090"
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.retention.time=30d'
  
  # Grafana
  grafana:
    image: grafana/grafana:latest
    volumes:
      - grafana_data:/var/lib/grafana
      - ./grafana/dashboards:/etc/grafana/provisioning/dashboards
    ports:
      - "3000:3000"
    environment:
      GF_SECURITY_ADMIN_PASSWORD: ${GRAFANA_PASSWORD}
      GF_INSTALL_PLUGINS: grafana-piechart-panel
    depends_on:
      - prometheus

volumes:
  postgres_data:
  prometheus_data:
  grafana_data:

📝 로깅 전략

# docker-compose.yml에 로깅 드라이버 설정
services:
  postgres:
    image: postgres:15-alpine
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
        max-file: "5"
        labels: "service,environment"
        env: "POSTGRES_VERSION"
    labels:
      service: "postgres"
      environment: "production"

  # Loki (로그 수집)
  loki:
    image: grafana/loki:latest
    ports:
      - "3100:3100"
    volumes:
      - ./loki-config.yml:/etc/loki/local-config.yaml
      - loki_data:/loki
    command: -config.file=/etc/loki/local-config.yaml

  # Promtail (로그 전송)
  promtail:
    image: grafana/promtail:latest
    volumes:
      - /var/log:/var/log
      - /var/lib/docker/containers:/var/lib/docker/containers:ro
      - ./promtail-config.yml:/etc/promtail/config.yml
    command: -config.file=/etc/promtail/config.yml

🔄 고가용성(HA) 구성

🔁 PostgreSQL 레플리케이션

version: '3.8'

services:
  # 마스터 DB
  postgres-master:
    image: postgres:15-alpine
    environment:
      POSTGRES_USER: replicator
      POSTGRES_PASSWORD: ${REPLICATOR_PASSWORD}
      POSTGRES_DB: myapp
    volumes:
      - postgres_master_data:/var/lib/postgresql/data
      - ./master-init.sh:/docker-entrypoint-initdb.d/init.sh
    command: >
      postgres
      -c wal_level=replica
      -c max_wal_senders=3
      -c max_replication_slots=3
      -c hot_standby=on
    networks:
      - db_network
  
  # 슬레이브 DB 1
  postgres-slave1:
    image: postgres:15-alpine
    environment:
      POSTGRES_USER: replicator
      POSTGRES_PASSWORD: ${REPLICATOR_PASSWORD}
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - postgres_slave1_data:/var/lib/postgresql/data
      - ./slave-init.sh:/docker-entrypoint-initdb.d/init.sh
    depends_on:
      - postgres-master
    command: >
      bash -c "
      until pg_basebackup -h postgres-master -D /var/lib/postgresql/data/pgdata -U replicator -v -P -W; do
        echo 'Waiting for master to be ready...'
        sleep 5
      done
      echo 'standby_mode = on' > /var/lib/postgresql/data/pgdata/recovery.conf
      echo 'primary_conninfo = \"host=postgres-master port=5432 user=replicator password=${REPLICATOR_PASSWORD}\"' >> /var/lib/postgresql/data/pgdata/recovery.conf
      postgres
      "
    networks:
      - db_network
  
  # 슬레이브 DB 2
  postgres-slave2:
    image: postgres:15-alpine
    environment:
      POSTGRES_USER: replicator
      POSTGRES_PASSWORD: ${REPLICATOR_PASSWORD}
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - postgres_slave2_data:/var/lib/postgresql/data
    depends_on:
      - postgres-master
    networks:
      - db_network
  
  # PgPool (로드 밸런서)
  pgpool:
    image: bitnami/pgpool:latest
    environment:
      PGPOOL_BACKEND_NODES: 0:postgres-master:5432,1:postgres-slave1:5432,2:postgres-slave2:5432
      PGPOOL_SR_CHECK_USER: replicator
      PGPOOL_SR_CHECK_PASSWORD: ${REPLICATOR_PASSWORD}
      PGPOOL_ENABLE_LDAP: no
      PGPOOL_POSTGRES_USERNAME: postgres
      PGPOOL_POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      PGPOOL_ADMIN_USERNAME: admin
      PGPOOL_ADMIN_PASSWORD: ${PGPOOL_ADMIN_PASSWORD}
    ports:
      - "5432:5432"
    depends_on:
      - postgres-master
      - postgres-slave1
      - postgres-slave2
    networks:
      - db_network

volumes:
  postgres_master_data:
  postgres_slave1_data:
  postgres_slave2_data:

networks:
  db_network:
    driver: bridge

🎯 MySQL 클러스터 구성

version: '3.8'

services:
  # MySQL 마스터
  mysql-master:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: myapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    volumes:
      - mysql_master_data:/var/lib/mysql
      - ./master.cnf:/etc/mysql/conf.d/master.cnf
    command: >
      --server-id=1
      --log-bin=mysql-bin
      --binlog-do-db=myapp
    networks:
      - db_network
  
  # MySQL 슬레이브 1
  mysql-slave1:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    volumes:
      - mysql_slave1_data:/var/lib/mysql
      - ./slave.cnf:/etc/mysql/conf.d/slave.cnf
    command: >
      --server-id=2
      --relay-log=mysql-relay-bin
      --log-bin=mysql-bin
      --binlog-do-db=myapp
    depends_on:
      - mysql-master
    networks:
      - db_network
  
  # MySQL 슬레이브 2
  mysql-slave2:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
    volumes:
      - mysql_slave2_data:/var/lib/mysql
      - ./slave.cnf:/etc/mysql/conf.d/slave.cnf
    command: >
      --server-id=3
      --relay-log=mysql-relay-bin
      --log-bin=mysql-bin
      --binlog-do-db=myapp
    depends_on:
      - mysql-master
    networks:
      - db_network
  
  # ProxySQL (로드 밸런서)
  proxysql:
    image: proxysql/proxysql:latest
    volumes:
      - ./proxysql.cnf:/etc/proxysql.cnf
    ports:
      - "6033:6033"
      - "6032:6032"
    depends_on:
      - mysql-master
      - mysql-slave1
      - mysql-slave2
    networks:
      - db_network

volumes:
  mysql_master_data:
  mysql_slave1_data:
  mysql_slave2_data:

networks:
  db_network:
    driver: bridge

🚀 실전 배포 시나리오

📋 배포 체크리스트

# 1. Docker 및 Docker Compose 설치 확인
docker --version
docker-compose --version

# 2. 디렉토리 구조 생성
mkdir -p ~/docker-db/{config,scripts,backup,logs}
cd ~/docker-db

# 3. 환경 변수 파일 생성
cat > .env << EOF
# Database
POSTGRES_USER=dbadmin
POSTGRES_PASSWORD=$(openssl rand -base64 32)
POSTGRES_DB=production_db

# Replication
REPLICATOR_PASSWORD=$(openssl rand -base64 32)

# Monitoring
GRAFANA_PASSWORD=$(openssl rand -base64 32)

# Backup
BACKUP_SCHEDULE="0 2 * * *"
BACKUP_RETENTION_DAYS=30
EOF

# 4. .env 파일 권한 설정
chmod 600 .env

# postgresql.conf (커스텀 설정)
cat > config/postgresql.conf << EOF
# 연결 설정
max_connections = 200
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB

# 로깅
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'mod'
log_duration = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# 레플리케이션
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
hot_standby = on
EOF

# pg_hba.conf (접근 제어)
cat > config/pg_hba.conf << EOF
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             172.16.0.0/12           md5
host    replication     replicator      172.16.0.0/12           md5
EOF

# docker-compose.yml (프로덕션 구성)
cat > docker-compose.yml << 'EOF'
version: '3.8'

services:
  postgres:
    image: postgres:15-alpine
    container_name: production-postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf
      - ./config/pg_hba.conf:/etc/postgresql/pg_hba.conf
      - ./scripts:/docker-entrypoint-initdb.d
      - ./backup:/backup
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    ports:
      - "127.0.0.1:5432:5432"
    networks:
      - db_network
    deploy:
      resources:
        limits:
          cpus: '4'
          memory: 8G
        reservations:
          cpus: '2'
          memory: 4G
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER}"]
      interval: 10s
      timeout: 5s
      retries: 5
    labels:
      - "com.example.description=Production PostgreSQL Database"
      - "com.example.backup=enabled"

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    container_name: postgres-exporter
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}?sslmode=disable"
    ports:
      - "127.0.0.1:9187:9187"
    networks:
      - db_network
    depends_on:
      postgres:
        condition: service_healthy

  backup:
    image: postgres:15-alpine
    container_name: postgres-backup
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      BACKUP_SCHEDULE: ${BACKUP_SCHEDULE}
      BACKUP_RETENTION_DAYS: ${BACKUP_RETENTION_DAYS}
    volumes:
      - ./backup:/backup
      - ./scripts/backup.sh:/backup.sh
    entrypoint: /bin/sh
    command: -c "crond -f -l 2"
    networks:
      - db_network
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres_data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /data/postgres

networks:
  db_network:
    driver: bridge
    ipam:
      config:
        - subnet: 172.20.0.0/16
EOF

# scripts/backup.sh
cat > scripts/backup.sh << 'EOF'
#!/bin/sh
set -e

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

echo "Starting backup at ${TIMESTAMP}"

# 전체 데이터베이스 백업
pg_dumpall -h ${POSTGRES_HOST} -U ${POSTGRES_USER} | gzip > \
  ${BACKUP_DIR}/full_backup_${TIMESTAMP}.sql.gz

# 개별 데이터베이스 백업 (커스텀 포맷)
pg_dump -h ${POSTGRES_HOST} -U ${POSTGRES_USER} -Fc ${POSTGRES_DB} > \
  ${BACKUP_DIR}/${POSTGRES_DB}_${TIMESTAMP}.dump

# 오래된 백업 삭제
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${BACKUP_RETENTION_DAYS} -delete
find ${BACKUP_DIR} -name "*.dump" -mtime +${BACKUP_RETENTION_DAYS} -delete

echo "Backup completed successfully"

# 백업 검증
if [ -f "${BACKUP_DIR}/full_backup_${TIMESTAMP}.sql.gz" ]; then
    SIZE=$(du -h "${BACKUP_DIR}/full_backup_${TIMESTAMP}.sql.gz" | cut -f1)
    echo "Backup size: ${SIZE}"
else
    echo "ERROR: Backup file not found!"
    exit 1
fi
EOF

chmod +x scripts/backup.sh

# crontab 설정 (백업 컨테이너용)
cat > scripts/crontab << EOF
${BACKUP_SCHEDULE} /backup.sh >> /var/log/backup.log 2>&1
EOF

# 1. 설정 검증
docker-compose config

# 2. 컨테이너 시작
docker-compose up -d

# 3. 로그 확인
docker-compose logs -f postgres

# 4. 헬스체크 확인
docker-compose ps

# 5. DB 접속 테스트
docker exec -it production-postgres psql -U ${POSTGRES_USER} -d ${POSTGRES_DB}

# 6. 백업 테스트
docker exec postgres-backup /backup.sh

# 7. 모니터링 확인
curl http://localhost:9187/metrics

🔧 트러블슈팅: 자주 발생하는 문제들

❌ 문제 1: 컨테이너가 계속 재시작됨

# 로그 확인
docker logs production-postgres

# 일반적인 원인들:
# 1. 권한 문제
sudo chown -R 999:999 /data/postgres

# 2. 메모리 부족
# docker-compose.yml에서 메모리 제한 조정
deploy:
  resources:
    limits:
      memory: 8G  # 더 큰 값으로 변경

# 3. 설정 파일 오류
# postgresql.conf 문법 검사
docker run --rm -v $(pwd)/config:/config postgres:15-alpine \
  postgres --config-file=/config/postgresql.conf -C shared_buffers

# 4. 포트 충돌
# 이미 사용 중인 포트 확인
sudo netstat -tlnp | grep 5432
# 다른 포트로 변경하거나 기존 프로세스 종료

❌ 문제 2: 디스크 공간 부족

# 1. 디스크 사용량 확인
df -h
docker system df

# 2. 사용하지 않는 리소스 정리
docker system prune -a --volumes

# 3. 로그 파일 정리
docker exec production-postgres sh -c "find /var/lib/postgresql/data/pg_log -name '*.log' -mtime +7 -delete"

# 4. 오래된 백업 수동 삭제
find /backup -name "*.sql.gz" -mtime +30 -delete

# 5. WAL 파일 정리 (PostgreSQL)
docker exec production-postgres psql -U postgres -c "SELECT pg_switch_wal();"
docker exec production-postgres psql -U postgres -c "CHECKPOINT;"

# 6. 볼륨 크기 모니터링 스크립트
cat > scripts/monitor_disk.sh << 'EOF'
#!/bin/bash
THRESHOLD=80
USAGE=$(df -h /data/postgres | tail -1 | awk '{print $5}' | sed 's/%//')

if [ $USAGE -gt $THRESHOLD ]; then
    echo "WARNING: Disk usage is ${USAGE}%"
    # 알림 전송 (Slack, Email 등)
fi
EOF

❌ 문제 3: 성능 저하

# 1. 현재 실행 중인 쿼리 확인 (PostgreSQL)
docker exec production-postgres psql -U postgres -c "
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;
"

# 2. 느린 쿼리 찾기
docker exec production-postgres psql -U postgres -c "
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;
"

# 3. 인덱스 확인
docker exec production-postgres psql -U postgres -d myapp -c "
SELECT schemaname, tablename, indexname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 
ORDER BY schemaname, tablename;
"

# 4. 테이블 통계 업데이트
docker exec production-postgres psql -U postgres -d myapp -c "ANALYZE;"

# 5. 커넥션 풀 설정 확인
docker exec production-postgres psql -U postgres -c "
SELECT count(*) as connections, state 
FROM pg_stat_activity 
GROUP BY state;
"

# 6. 리소스 사용량 실시간 모니터링
docker stats production-postgres

❌ 문제 4: 데이터 손실 또는 손상

# 1. 데이터 무결성 검사 (PostgreSQL)
docker exec production-postgres psql -U postgres -d myapp -c "
SELECT datname, pg_database_size(datname) 
FROM pg_database;
"

# 2. 테이블 손상 검사
docker exec production-postgres psql -U postgres -d myapp -c "
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
"

# 3. 백업에서 복구
# 먼저 현재 상태 백업
docker exec production-postgres pg_dump -U postgres -Fc myapp > emergency_backup.dump

# 최신 백업에서 복구
docker exec -i production-postgres pg_restore -U postgres -d myapp -c < /backup/myapp_20240115_020000.dump

# 4. PITR (Point-In-Time Recovery) 설정
# postgresql.conf에 추가
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'
restore_command = 'cp /backup/archive/%f %p'

# 5. 특정 시점으로 복구
docker exec production-postgres psql -U postgres -c "
SELECT pg_create_restore_point('before_disaster');
"

🌟 베스트 프랙티스 총정리

✨ 핵심 원칙 10가지

🎓 학습 리소스 및 커뮤니티

🎯 마무리하며