Database DevOps: миграции схемы БД в CI/CD-конвейерах

-- V004__alter_orders_add_status_column.sql
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- V005__fix_orders_status_default.sql
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new_pending';

flyway.url=jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME}
flyway.user=${DB_USER}
flyway.password=${DB_PASSWORD}
flyway.locations=filesystem:./migrations
flyway.baselineOnMigrate=true
flyway.outOfOrder=false
flyway.validateOnMigrate=true

name: Database Migrations
on:
  push:
    paths:
      - 'db/**'
      - 'src/**'
jobs:
  validate-migrations:
    runs-on: ubuntu-latest
    env:
      DB_HOST: localhost
      DB_PORT: 5432
      DB_NAME: testdb
      DB_USER: testuser
      DB_PASSWORD: testpassword
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_DB: testdb
          POSTGRES_USER: testuser
          POSTGRES_PASSWORD: testpassword
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v4
      - name: Set up Flyway
        run: |
          curl -L https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.22.3/flyway-commandline-9.22.3.zip -o flyway.zip
          unzip flyway.zip
          sudo mv flyway-9.22.3 /opt/flyway
          sudo ln -s /opt/flyway/flyway /usr/local/bin/flyway
      - name: Validate migration files
        run: flyway -configFiles=db/flyway.conf validate
      - name: Run migrations against test database
        run: flyway -configFiles=db/flyway.conf migrate
      - name: Run application integration tests
        run: ./gradlew integrationTest
      - name: Inspect migration history
        run: flyway -configFiles=db/flyway.conf info

apiVersion: argoproj.io/v1alpha1
kind: Application
metadata:
  name: orders-db-migrations
  namespace: argocd
spec:
  project: default
  source:
    repoURL: https://github.com/your-org/orders-service
    targetRevision: main
    path: db
  destination:
    server: https://kubernetes.default.svc
    namespace: orders-db
  syncPolicy:
    automated:
      prune: false
      selfHeal: false
    syncOptions:
      - ApplyOutOfSyncOnly=true
    retry:
      limit: 3
      backoff:
        duration: 5s
        factor: 2
        maxDuration: 60s

apiVersion: batch/v1
kind: Job
metadata:
  name: orders-migration-v004
  namespace: orders-db
  annotations:
    argocd.argoproj.io/sync-wave: "-1"
spec:
  ttlSecondsAfterFinished: 300
  template:
    spec:
      restartPolicy: OnFailure
      containers:
        - name: flyway
          image: flyway/flyway:9.22.3
          args:
            - migrate
            - -configFiles=/mnt/config/flyway.conf
            - -locations=filesystem:/flyway/sql
          env:
            - name: DB_HOST
              valueFrom:
                secretKeyRef:
                  name: orders-db-credentials
                  key: host
            - name: DB_PORT
              value: "5432"
            - name: DB_NAME
              value: orders
            - name: DB_USER
              valueFrom:
                secretKeyRef:
                  name: orders-db-credentials
                  key: username
            - name: DB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: orders-db-credentials
                  key: password
          volumeMounts:
            - name: flyway-config
              mountPath: /mnt/config
            - name: flyway-sql
              mountPath: /flyway/sql
      volumes:
        - name: flyway-config
          configMap:
            name: flyway-config
        - name: flyway-sql
          configMap:
            name: orders-db-migrations-sql

-- Избегайте немедленного NOT NULL на существующих строках
ALTER TABLE orders ADD COLUMN priority VARCHAR(10) NOT NULL;  -- упадёт

ALTER TABLE orders ADD COLUMN priority VARCHAR(10);
UPDATE orders SET priority = 'medium' WHERE priority IS NULL;
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

-- V005: изменение схемы (быстро)
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP;

-- V006: миграция данных (можно async батчами)
UPDATE orders SET shipped_at = updated_at WHERE status = 'shipped';

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100);