Split View: Spring Boot Flyway 완전 가이드: DB 마이그레이션 전략과 실전 패턴

-- flyway_schema_history 테이블 구조 예시
SELECT installed_rank, version, description, type, script, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;

<!-- Maven pom.xml -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

<!-- MySQL 사용 시 추가 -->
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-mysql</artifactId>
</dependency>

<!-- PostgreSQL은 flyway-core만으로 충분 -->

// build.gradle
dependencies {
    implementation 'org.flywaydb:flyway-core'
    // MySQL 사용 시
    implementation 'org.flywaydb:flyway-mysql'
}

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: myuser
    password: mypassword
    driver-class-name: org.postgresql.Driver
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    validate-on-migrate: true
    out-of-order: false
    table: flyway_schema_history
    encoding: UTF-8
    connect-retries: 3

V{version}__{description}.sql

U{version}__{description}.sql

R__{description}.sql

src/main/resources/
└── db/
    └── migration/
        ├── V1__init_schema.sql
        ├── V2__add_user_status.sql
        ├── V3__insert_seed_data.sql
        ├── V4__add_indexes.sql
        └── R__create_reporting_views.sql

-- V1__init_schema.sql
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMP    NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock       INTEGER        NOT NULL DEFAULT 0,
    created_at  TIMESTAMP      NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT       NOT NULL REFERENCES users(id),
    total       DECIMAL(10, 2) NOT NULL,
    status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    created_at  TIMESTAMP    NOT NULL DEFAULT NOW()
);

-- V2__add_user_status.sql
ALTER TABLE users
    ADD COLUMN status       VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    ADD COLUMN last_login   TIMESTAMP,
    ADD COLUMN phone_number VARCHAR(20);

-- 기존 데이터에 기본값 적용 (이미 DEFAULT로 설정되어 있지만 명시적으로)
UPDATE users SET status = 'ACTIVE' WHERE status IS NULL;

-- V3__insert_seed_data.sql
INSERT INTO products (name, description, price, stock) VALUES
    ('노트북', '고성능 개발자용 노트북', 1500000, 50),
    ('마우스', '무선 에르고노믹 마우스', 80000, 200),
    ('키보드', '기계식 RGB 키보드', 120000, 150);

-- 관리자 계정 생성
INSERT INTO users (username, email, password, status) VALUES
    ('admin', 'admin@example.com', '$2a$10$hashedpassword', 'ACTIVE');

-- V4__add_indexes.sql
-- 자주 조회되는 컬럼에 인덱스 추가
CREATE INDEX idx_users_email     ON users(email);
CREATE INDEX idx_users_status    ON users(status);
CREATE INDEX idx_orders_user_id  ON orders(user_id);
CREATE INDEX idx_orders_status   ON orders(status);
CREATE INDEX idx_orders_created  ON orders(created_at DESC);

-- 복합 인덱스
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.SingleConnectionDataSource;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * V5__MigrateUserData
 * 기존 users 테이블의 full_name 컬럼을 first_name, last_name으로 분리
 */
public class V5__MigrateUserData extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(
            new SingleConnectionDataSource(context.getConnection(), true)
        );

        // 1단계: full_name 컬럼이 있는 경우에만 마이그레이션 실행
        List<String[]> users = new ArrayList<>();

        try (Statement stmt = context.getConnection().createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT id, full_name FROM users WHERE full_name IS NOT NULL")) {
            while (rs.next()) {
                long id = rs.getLong("id");
                String fullName = rs.getString("full_name");
                String[] parts = fullName.split(" ", 2);
                users.add(new String[]{
                    String.valueOf(id),
                    parts[0],
                    parts.length > 1 ? parts[1] : ""
                });
            }
        }

        // 2단계: 분리된 이름 저장
        for (String[] user : users) {
            jdbcTemplate.update(
                "UPDATE users SET first_name = ?, last_name = ? WHERE id = ?",
                user[1], user[2], Long.parseLong(user[0])
            );
        }

        // 3단계: 마이그레이션 결과 로깅
        System.out.printf("Migrated %d user records%n", users.size());
    }
}

package db.migration;

import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class V6__EncryptSensitiveData extends BaseJavaMigration {

    // 주의: Spring Bean 주입을 위해 @Component 필요
    // FlywayAutoConfiguration에서 JavaMigration Bean을 자동 인식
    @Autowired
    private EncryptionService encryptionService;

    @Override
    public void migrate(Context context) throws Exception {
        // encryptionService 사용 가능
        var jdbcTemplate = new org.springframework.jdbc.core.JdbcTemplate(
            new org.springframework.jdbc.datasource.SingleConnectionDataSource(
                context.getConnection(), true)
        );

        jdbcTemplate.query(
            "SELECT id, phone_number FROM users WHERE phone_number IS NOT NULL",
            (rs) -> {
                long id = rs.getLong("id");
                String phone = rs.getString("phone_number");
                String encrypted = encryptionService.encrypt(phone);
                jdbcTemplate.update(
                    "UPDATE users SET phone_number = ? WHERE id = ?",
                    encrypted, id
                );
            }
        );
    }
}

# application.yml (공통)
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration

---
# application-dev.yml
spring:
  config:
    activate:
      on-profile: dev
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/dev
    clean-on-validation-error: true # 개발 환경에서만 허용

---
# application-test.yml
spring:
  config:
    activate:
      on-profile: test
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/test
    clean-disabled: false # 테스트 전 DB 초기화 허용

---
# application-prod.yml
spring:
  config:
    activate:
      on-profile: prod
  flyway:
    locations: classpath:db/migration
    clean-disabled: true # 운영 환경에서 clean 명령 비활성화
    out-of-order: false
    validate-on-migrate: true

spring:
  flyway:
    locations:
      - classpath:db/migration
      - classpath:db/migration/{vendor}

db/migration/
├── V1__create_tables.sql          # 공통
├── V2__add_indexes.sql            # 공통
├── postgresql/
│   └── V3__add_pg_specific.sql   # PostgreSQL 전용
└── mysql/
    └── V3__add_mysql_specific.sql # MySQL 전용

@Configuration
public class FlywayConfig {

    @Bean
    public FlywayMigrationStrategy migrationStrategy() {
        return flyway -> {
            // 마이그레이션 전 커스텀 로직
            System.out.println("Starting Flyway migration...");
            flyway.migrate();
            System.out.println("Flyway migration completed.");
        };
    }

    @Bean
    @Profile("!prod")
    public FlywayMigrationStrategy devMigrationStrategy() {
        return flyway -> {
            // 개발 환경: 스키마 초기화 후 재실행
            flyway.clean();
            flyway.migrate();
        };
    }
}

<!-- flywaydb-test 라이브러리 -->
<dependency>
    <groupId>org.flywaydb.flyway-test-extensions</groupId>
    <artifactId>flyway-spring-test</artifactId>
    <version>9.5.0</version>
    <scope>test</scope>
</dependency>

@SpringBootTest
@FlywayTest
class UserRepositoryTest {

    @Autowired
    private UserRepository userRepository;

    @Test
    @FlywayTest(locationsForMigrate = {"classpath:db/migration/test"})
    void testUserCreation() {
        User user = new User("testuser", "test@example.com");
        User saved = userRepository.save(user);
        assertThat(saved.getId()).isNotNull();
    }
}

# application-test.yml
spring:
  datasource:
    url: jdbc:h2:mem:testdb;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
    driver-class-name: org.h2.Driver
    username: sa
    password:
  flyway:
    enabled: true
    locations: classpath:db/migration

@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@ActiveProfiles("test")
class ProductRepositoryTest {

    @Autowired
    private ProductRepository productRepository;

    @Test
    void findByStatus_returnsActiveProducts() {
        List<Product> products = productRepository.findByStatus("ACTIVE");
        assertThat(products).isNotEmpty();
    }
}

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>junit-jupiter</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class IntegrationTest {

    @Container
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:15")
            .withDatabaseName("testdb")
            .withUsername("testuser")
            .withPassword("testpass");

    @DynamicPropertySource
    static void setProperties(DynamicPropertyRegistry registry) {
        registry.add("spring.datasource.url", postgres::getJdbcUrl);
        registry.add("spring.datasource.username", postgres::getUsername);
        registry.add("spring.datasource.password", postgres::getPassword);
    }

    @Autowired
    private UserRepository userRepository;

    @Test
    void flywayMigration_appliesAllScripts() {
        // Flyway가 적용된 실제 PostgreSQL 컨테이너에서 테스트
        assertThat(userRepository.count()).isGreaterThanOrEqualTo(0);
    }
}

-- V10__make_old_column_nullable.sql
ALTER TABLE users ALTER COLUMN old_field DROP NOT NULL;

-- V12__drop_old_column.sql
ALTER TABLE users DROP COLUMN IF EXISTS old_field;

-- V15__add_large_index.sql
-- CONCURRENTLY 옵션: 테이블 잠금 없이 인덱스 생성 (운영 환경 권장)
-- 주의: Flyway 트랜잭션 내에서는 CONCURRENTLY 사용 불가
-- Java 마이그레이션이나 별도 스크립트로 실행 필요
CREATE INDEX CONCURRENTLY idx_orders_large ON orders(created_at, status);

public class V15__AddLargeIndexConcurrently extends BaseJavaMigration {

    @Override
    public boolean canExecuteInTransaction() {
        return false; // 트랜잭션 외부 실행
    }

    @Override
    public void migrate(Context context) throws Exception {
        try (Statement stmt = context.getConnection().createStatement()) {
            stmt.execute(
                "CREATE INDEX CONCURRENTLY IF NOT EXISTS " +
                "idx_orders_large ON orders(created_at, status)"
            );
        }
    }
}

-- V16__migrate_large_table.sql
-- 1. 새 컬럼 추가 (NULL 허용)
ALTER TABLE orders ADD COLUMN new_status_code INTEGER;

-- 2. 배치 업데이트 (한 번에 1000건씩)
-- 이 방식은 Flyway SQL에서 직접 사용 어려우므로 Java 마이그레이션 권장

public class V16__BatchMigrateLargeTable extends BaseJavaMigration {

    private static final int BATCH_SIZE = 1000;

    @Override
    public void migrate(Context context) throws Exception {
        JdbcTemplate jdbc = new JdbcTemplate(
            new SingleConnectionDataSource(context.getConnection(), true)
        );

        long maxId = jdbc.queryForObject("SELECT MAX(id) FROM orders", Long.class);
        long processedCount = 0;

        for (long offset = 0; offset <= maxId; offset += BATCH_SIZE) {
            final long batchOffset = offset;
            int updated = jdbc.update(
                "UPDATE orders SET new_status_code = CASE status " +
                "WHEN 'PENDING' THEN 1 WHEN 'COMPLETED' THEN 2 ELSE 0 END " +
                "WHERE id > ? AND id <= ? AND new_status_code IS NULL",
                batchOffset, batchOffset + BATCH_SIZE
            );
            processedCount += updated;
            System.out.printf("Batch progress: %d records updated%n", processedCount);
        }
    }
}

# Maven
./mvnw flyway:repair

# Gradle
./gradlew flywayRepair

# CLI
flyway -url=jdbc:postgresql://localhost:5432/mydb \
       -user=myuser \
       -password=mypassword \
       repair

// 애플리케이션 코드에서 repair 실행
@Autowired
private Flyway flyway;

public void repairMigration() {
    flyway.repair();
}

# .github/workflows/flyway-validate.yml
name: Flyway Validation

on:
  pull_request:
    paths:
      - 'src/main/resources/db/migration/**'
      - 'src/main/java/db/migration/**'

jobs:
  flyway-validate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpass
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v4

      - name: Set up JDK 21
        uses: actions/setup-java@v4
        with:
          java-version: '21'
          distribution: 'temurin'

      - name: Validate Flyway migrations
        run: |
          ./mvnw flyway:validate \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

      - name: Run migration
        run: |
          ./mvnw flyway:migrate \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

      - name: Verify migration info
        run: |
          ./mvnw flyway:info \
            -Dflyway.url=jdbc:postgresql://localhost:5432/testdb \
            -Dflyway.user=testuser \
            -Dflyway.password=testpass

#!/bin/bash
# scripts/pre-deploy-flyway-check.sh

set -e

echo "Running Flyway validation against production database..."

./mvnw flyway:validate \
  -Dflyway.url="${PROD_DB_URL}" \
  -Dflyway.user="${PROD_DB_USER}" \
  -Dflyway.password="${PROD_DB_PASSWORD}" \
  -Dflyway.cleanDisabled=true

echo "Flyway validation passed. Safe to deploy."

# Docker Compose 개발 환경
version: '3.8'
services:
  app:
    build: .
    environment:
      SPRING_DATASOURCE_URL: jdbc:postgresql://db:5432/appdb
      SPRING_FLYWAY_ENABLED: 'true'
    depends_on:
      db:
        condition: service_healthy

  db:
    image: postgres:15
    environment:
      POSTGRES_DB: appdb
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: apppass
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U appuser -d appdb']
      interval: 5s
      timeout: 5s
      retries: 5

ERROR: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 3

# 수정된 파일의 체크섬으로 히스토리 업데이트
./mvnw flyway:repair

-- V3_1__fix_incorrect_v3_migration.sql
-- V3의 잘못된 부분을 수정하는 새 마이그레이션
ALTER TABLE users MODIFY COLUMN email VARCHAR(500);

spring:
  flyway:
    baseline-on-migrate: true
    baseline-version: 1 # 현재 상태를 V1로 베이스라인 설정
    baseline-description: 'Initial baseline'

# CLI로 베이스라인 설정
flyway -url=jdbc:postgresql://localhost:5432/mydb \
       -user=myuser \
       -password=mypassword \
       baseline