ENUMに直接変更するのではなく、新カラムを追加して段階的に移行する方法を検討しよう。既存データのバリデーションも重要。

事前準備:

# バックアップ取得
pg_dump -h $DB_HOST -d myapp -F c -f pre_migration_enum.dump
aws s3 cp pre_migration_enum.dump s3://backups/pre-migration/

Phase 1: Expand（ENUM型作成 + 新カラム追加）

-- マイグレーション 1: ENUM型と新カラム作成
CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'paid', 'shipped', 'delivered', 'cancelled');

ALTER TABLE orders ADD COLUMN status_new order_status;

-- 既存データのバリデーション（不正値の確認）
SELECT status, COUNT(*)
FROM orders
WHERE status NOT IN ('pending', 'confirmed', 'paid', 'shipped', 'delivered', 'cancelled')
GROUP BY status;
-- → 不正値があれば事前にクリーニング

Phase 2: データ移行 + Dual Write

-- バッチでデータコピー
UPDATE orders SET status_new = status::order_status
WHERE status_new IS NULL
AND id BETWEEN 1 AND 100000;
-- ... バッチを繰り返す

// アプリ更新: Dual Write
async function updateOrderStatus(orderId: number, status: string) {
  await prisma.$executeRaw`
    UPDATE orders
    SET status = ${status}, status_new = ${status}::order_status
    WHERE id = ${orderId}
  `;
}

Phase 3: 切り替え

-- 全データの移行完了を確認
SELECT COUNT(*) FROM orders WHERE status_new IS NULL;  -- 0であること

-- NOT NULL制約追加
ALTER TABLE orders ALTER COLUMN status_new SET NOT NULL;
ALTER TABLE orders ALTER COLUMN status_new SET DEFAULT 'pending';

// アプリ更新: 新カラムから読み取り
async function getOrderStatus(orderId: number): Promise<string> {
  const order = await prisma.order.findUnique({ where: { id: orderId } });
  return order.statusNew;  // 新カラムを使用
}

Phase 4: Contract（旧カラム削除）

-- 十分な検証期間後
ALTER TABLE orders DROP COLUMN status;
ALTER TABLE orders RENAME COLUMN status_new TO status;

ロールバック計画:

• Phase 1-2: 新カラムをDROPするだけ
• Phase 3: アプリを旧バージョンに戻す
• Phase 4: バックアップからリストア

新テーブル作成 → データコピー → アプリのDual Read/Write → 旧カラム削除の手順で考えよう。

Phase 1: Expand

-- 新テーブル作成
CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  bio TEXT,
  avatar_url VARCHAR(500),
  website VARCHAR(500),
  location VARCHAR(100),
  birth_date DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Phase 2: データコピー + Dual Write

// バッチでデータコピー
async function migrateProfiles(batchSize: number = 5000) {
  let lastId = 0;
  while (true) {
    const result = await prisma.$executeRaw`
      INSERT INTO user_profiles (user_id, bio, avatar_url, website, location, birth_date, created_at)
      SELECT id, bio, avatar_url, website, location, birth_date, created_at
      FROM users
      WHERE id > ${lastId}
      AND id NOT IN (SELECT user_id FROM user_profiles)
      ORDER BY id
      LIMIT ${batchSize}
      ON CONFLICT (user_id) DO NOTHING
    `;

    if (result === 0) break;
    lastId += batchSize;
    await sleep(100);
  }
}

// Dual Write: 更新時は両方に書き込み
async function updateProfile(userId: number, data: ProfileData) {
  await prisma.$transaction([
    prisma.user.update({
      where: { id: userId },
      data: { bio: data.bio, avatarUrl: data.avatarUrl },
    }),
    prisma.userProfile.upsert({
      where: { userId },
      update: data,
      create: { userId, ...data },
    }),
  ]);
}

// Dual Read: 新テーブル優先
async function getProfile(userId: number) {
  const profile = await prisma.userProfile.findUnique({ where: { userId } });
  if (profile) return profile;

  // フォールバック
  const user = await prisma.user.findUnique({ where: { id: userId } });
  return { bio: user.bio, avatarUrl: user.avatarUrl, ... };
}

Phase 3: アプリ切り替え

// 新テーブルのみ使用
async function getProfile(userId: number) {
  return prisma.userProfile.findUnique({ where: { userId } });
}

Phase 4: Contract

ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users DROP COLUMN avatar_url;
ALTER TABLE users DROP COLUMN website;
ALTER TABLE users DROP COLUMN location;
ALTER TABLE users DROP COLUMN birth_date;

ロールバック計画: 各フェーズでuser_profilesをDROPし、アプリを旧バージョンに戻す。

障害対応手順書

1. マイグレーションSQL実行中にタイムアウト

確認:
  □ pg_stat_activity でクエリの状態を確認
  □ ロックの有無を確認: SELECT * FROM pg_locks WHERE NOT granted;

対処:
  □ 実行中のクエリをキャンセル: SELECT pg_cancel_backend(pid);
  □ 改善策: statement_timeout を設定してリトライ
  □ 大量データの場合: バッチ処理に分割

エスカレーション:
  □ 30分以内に解決しない場合 → DBA チームに連絡

2. データ移行バッチが途中で失敗

確認:
  □ 移行済み/未移行の件数を確認
  □ エラーログで失敗原因を特定
  □ データ不整合の範囲を確認

対処:
  □ 原因を修正して未移行分のみリトライ
  □ 冪等な設計であれば全体リトライ可能
  □ 移行済みデータの整合性チェック

エスカレーション:
  □ データ不整合が発見された場合 → バックアップからリストア検討

3. 新旧アプリの切り替え後にバグが発覚

確認:
  □ バグの影響範囲を特定
  □ データ破損の有無を確認

対処:
  □ 即座にアプリを旧バージョンにロールバック
  □ Dual Write期間中であればデータは旧カラムにも存在
  □ 新カラムのデータは破棄して再移行

エスカレーション:
  □ データ破損がある場合 → インシデント対応プロセス起動

4. ロールバックのマイグレーションが失敗

確認:
  □ ロールバックSQL のエラー内容を確認
  □ 依存関係（FK制約等）の確認

対処:
  □ 依存を解消してリトライ
  □ 手動でSQLを修正して実行
  □ 最終手段: バックアップからPITRで復旧

エスカレーション:
  □ 即座にDBAチームとインフラチームに連絡
  □ RPO/RTOに基づいてバックアップリストアを判断

マイグレーション用ダッシュボード

監視クエリ（マイグレーション進捗）:

-- 移行進捗の監視
SELECT
  (SELECT COUNT(*) FROM users WHERE full_name IS NOT NULL) AS migrated,
  (SELECT COUNT(*) FROM users WHERE full_name IS NULL) AS remaining,
  ROUND(
    (SELECT COUNT(*) FROM users WHERE full_name IS NOT NULL)::numeric /
    NULLIF((SELECT COUNT(*) FROM users), 0) * 100, 2
  ) AS progress_percent;

// Prometheusメトリクス
const migrationProgress = new Gauge({
  name: 'db_migration_progress_percent',
  help: 'Migration progress percentage',
  labelNames: ['migration_name'],
});

const dbQueryDuration = new Histogram({
  name: 'db_query_duration_seconds',
  help: 'Database query duration',
  labelNames: ['query_type'],
  buckets: [0.01, 0.05, 0.1, 0.5, 1, 5],
});