-- === Flyway 形式 ===
-- ファイル命名: V{version}__{description}.sql
-- V2__add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- ロールバック用（Flyway Pro/Enterprise のみ）
-- U2__add_phone_to_users.sql
ALTER TABLE users DROP COLUMN phone;
 
-- === golang-migrate 形式 ===
-- 000002_add_phone.up.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- 000002_add_phone.down.sql
ALTER TABLE users DROP COLUMN phone;
 
-- === Liquibase 形式 (XML) ===
-- changelog-2.0.xml
-- <changeSet id="2" author="developer">
--   <addColumn tableName="users">
--     <column name="phone" type="VARCHAR(20)"/>
--   </addColumn>
--   <rollback>
--     <dropColumn tableName="users" columnName="phone"/>
--   </rollback>
-- </changeSet>

# golang-migrate の使用
# マイグレーション作成
migrate create -ext sql -dir ./migrations -seq add_phone_to_users
 
# 全マイグレーション適用
migrate -path ./migrations -database "postgres://user:pass@localhost/mydb" up
 
# 1つロールバック
migrate -path ./migrations -database "postgres://user:pass@localhost/mydb" down 1
 
# 特定バージョンまで適用
migrate -path ./migrations -database "postgres://user:pass@localhost/mydb" goto 5
 
# 現在のバージョン確認
migrate -path ./migrations -database "postgres://user:pass@localhost/mydb" version
 
# Flyway
flyway -url=jdbc:postgresql://localhost/mydb migrate
flyway -url=jdbc:postgresql://localhost/mydb info
flyway -url=jdbc:postgresql://localhost/mydb validate
flyway -url=jdbc:postgresql://localhost/mydb repair  # メタデータの修復

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  phone     String?  // 新規追加
  status    String   @default("active")  // 新規追加
  createdAt DateTime @default(now())
  orders    Order[]
}
 
model Order {
  id        Int      @id @default(autoincrement())
  userId    Int
  user      User     @relation(fields: [userId], references: [id])
  total     Decimal
  status    String   @default("pending")
  createdAt DateTime @default(now())
}

# マイグレーション生成（開発環境）
npx prisma migrate dev --name add_phone_and_status
# → prisma/migrations/20260211_add_phone_and_status/migration.sql が生成される
 
# 本番適用（CI/CD パイプライン内）
npx prisma migrate deploy
 
# ステータス確認
npx prisma migrate status
 
# マイグレーションのリセット（開発環境のみ）
npx prisma migrate reset
 
# スキーマの差分確認
npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma

-- [NG] 直接リネーム --> ダウンタイム発生
ALTER TABLE users RENAME COLUMN name TO full_name;
-- --> 既存アプリが "name" を参照してエラー
 
-- [OK] Expand-Contract パターン（3フェーズ）
 
-- ===== Phase 1: Expand（新カラム追加 + トリガー） =====
-- マイグレーション: 20260211_001_expand_user_name.sql
 
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
 
-- 既存データをコピー
UPDATE users SET full_name = name WHERE full_name IS NULL;
 
-- 双方向同期トリガー
CREATE OR REPLACE FUNCTION sync_user_name() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NEW.full_name IS NULL THEN
      NEW.full_name := NEW.name;
    ELSIF NEW.name IS NULL THEN
      NEW.name := NEW.full_name;
    END IF;
  ELSIF TG_OP = 'UPDATE' THEN
    IF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
      NEW.name := NEW.full_name;
    ELSIF NEW.name IS DISTINCT FROM OLD.name THEN
      NEW.full_name := NEW.name;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_user_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_name();
 
-- ===== Phase 2: アプリデプロイ =====
-- アプリを full_name を使うように変更してデプロイ
-- name と full_name の両方を読み書きする互換コードをデプロイ
 
-- ===== Phase 3: Contract（旧カラム・トリガー削除） =====
-- マイグレーション: 20260218_001_contract_user_name.sql
-- （1週間以上の監視期間を経てから実行）
 
DROP TRIGGER trg_sync_user_name ON users;
DROP FUNCTION sync_user_name();
ALTER TABLE users DROP COLUMN name;

-- ユーザーテーブルからプロフィール情報を分離する例
 
-- Phase 1: 新テーブル作成 + トリガーで同期
CREATE TABLE user_profiles (
    user_id     INTEGER PRIMARY KEY REFERENCES users(id),
    bio         TEXT,
    avatar_url  VARCHAR(500),
    website     VARCHAR(500),
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW()
);
 
-- 既存データの移行
INSERT INTO user_profiles (user_id, bio, avatar_url, website)
SELECT id, bio, avatar_url, website
FROM users
WHERE bio IS NOT NULL OR avatar_url IS NOT NULL;
 
-- 書き込みの同期トリガー
CREATE OR REPLACE FUNCTION sync_user_profile() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    INSERT INTO user_profiles (user_id, bio, avatar_url, website, updated_at)
    VALUES (NEW.id, NEW.bio, NEW.avatar_url, NEW.website, NOW())
    ON CONFLICT (user_id) DO UPDATE SET
      bio = EXCLUDED.bio,
      avatar_url = EXCLUDED.avatar_url,
      website = EXCLUDED.website,
      updated_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_profile
AFTER INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_profile();
 
-- Phase 2: アプリを新テーブルに切替
-- Phase 3: 旧カラムとトリガーを削除
DROP TRIGGER trg_sync_profile ON users;
DROP FUNCTION sync_user_profile();
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users DROP COLUMN avatar_url;
ALTER TABLE users DROP COLUMN website;

-- [NG] テーブルロックでダウンタイム
CREATE INDEX idx_orders_email ON orders (email);
-- ShareLock: INSERT/UPDATE/DELETEがブロックされる
 
-- [OK] ロックなし（CONCURRENTLY）
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (email);
-- 注意事項:
-- 1. トランザクション内では使用不可
-- 2. 構築時間は約2-3倍
-- 3. 失敗するとINVALIDインデックスが残る
-- 4. テーブルが2回スキャンされる
 
-- INVALID インデックスの確認と対処
SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;
 
-- INVALIDインデックスの再構築
REINDEX INDEX CONCURRENTLY idx_orders_email;
-- または削除して再作成
DROP INDEX CONCURRENTLY idx_orders_email;
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (email);

-- [NG] 直接NOT NULLを設定 → 全行スキャン + AccessExclusiveLock
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- 1000万行: 数秒〜数十秒のロック
 
-- [OK] 3段階で安全に追加
-- Step 1: CHECK 制約を NOT VALID で追加（瞬時、ロック最小）
SET lock_timeout = '5s';
ALTER TABLE users
ADD CONSTRAINT chk_users_email_not_null
CHECK (email IS NOT NULL) NOT VALID;
-- → 新しい行のみチェック、既存行は未検証
 
-- Step 2: 既存データの検証（ShareUpdateExclusiveLock のみ）
-- SELECT/INSERT/UPDATE/DELETEは並行実行可能
ALTER TABLE users VALIDATE CONSTRAINT chk_users_email_not_null;
-- → 全行を検証するが、弱いロックのみ
 
-- Step 3: NOT NULL に昇格（PostgreSQL 12+は自動認識）
-- PostgreSQL 12+: CHECK制約が存在すれば瞬時に設定可能
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT chk_users_email_not_null;
 
-- 安全な外部キー制約の追加
-- Step 1: NOT VALID で追加
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
 
-- Step 2: 既存データの検証
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user_id;

-- [NG] 一括 UPDATE --> 長時間ロック + WAL 肥大化 + VACUUM負荷
UPDATE orders SET status = 'active' WHERE status IS NULL;
-- 1000万行の場合:
-- - 数分~数十分のロック
-- - WALが数GB生成される
-- - VACUUM が必要になる
-- - レプリカの遅延が発生する
 
-- [OK] バッチ処理で段階的に更新
DO $$
DECLARE
  batch_size INT := 10000;
  total_updated INT := 0;
  rows_affected INT;
BEGIN
  LOOP
    UPDATE orders
    SET status = 'active'
    WHERE id IN (
      SELECT id FROM orders
      WHERE status IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED  -- 他トランザクションと競合回避
    );
 
    GET DIAGNOSTICS rows_affected = ROW_COUNT;
    total_updated := total_updated + rows_affected;
 
    RAISE NOTICE 'Updated: % (total: %)', rows_affected, total_updated;
 
    EXIT WHEN rows_affected = 0;
 
    PERFORM pg_sleep(0.1);  -- 負荷調整（レプリカ遅延を防ぐ）
    COMMIT;
  END LOOP;
END $$;
 
-- [推奨] 主キー範囲ベースのバッチ処理（より予測可能）
DO $$
DECLARE
  batch_size INT := 10000;
  min_id INT;
  max_id INT;
  current_id INT;
BEGIN
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM orders WHERE status IS NULL;
 
  current_id := min_id;
  WHILE current_id <= max_id LOOP
    UPDATE orders
    SET status = 'active'
    WHERE id >= current_id
      AND id < current_id + batch_size
      AND status IS NULL;
 
    current_id := current_id + batch_size;
 
    RAISE NOTICE 'Progress: %/%', current_id - min_id, max_id - min_id;
    COMMIT;
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

-- 進捗確認クエリ（別セッションから実行）
SELECT
    COUNT(*) FILTER (WHERE status IS NOT NULL) AS completed,
    COUNT(*) FILTER (WHERE status IS NULL) AS remaining,
    COUNT(*) AS total,
    ROUND(
        COUNT(*) FILTER (WHERE status IS NOT NULL)::NUMERIC / COUNT(*) * 100, 1
    ) AS progress_pct
FROM orders;
 
-- WAL生成量の確認
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS wal_bytes;
 
-- レプリカ遅延の確認
SELECT
    client_addr,
    state,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    replay_lag
FROM pg_stat_replication;

-- MySQL: ALGORITHM指定
ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
ALGORITHM=INSTANT;  -- 瞬時（MySQL 8.0.12+）
 
-- MySQL: pt-online-schema-change（Percona Tool）
-- 大規模テーブルのスキーマ変更に推奨
-- 内部的に:
-- 1. 新しいテーブルを作成
-- 2. トリガーで書き込みを同期
-- 3. データをバッチコピー
-- 4. テーブルを切り替え（RENAME）
 
-- bash:
-- pt-online-schema-change \
--   --alter "ADD COLUMN phone VARCHAR(20)" \
--   --execute \
--   D=mydb,t=users
 
-- gh-ost（GitHubのツール）
-- トリガーなしでオンラインスキーマ変更
-- bash:
-- gh-ost \
--   --alter="ADD COLUMN phone VARCHAR(20)" \
--   --database=mydb \
--   --table=users \
--   --execute

# squawk: PostgreSQL マイグレーション lint
npm install -g squawk-cli
 
# 危険な操作を検出
squawk migrations/V3__add_index.sql
 
# 出力例:
# migrations/V3__add_index.sql:1:1
#   warning: prefer-create-index-concurrently
#   CREATE INDEX on a large table without CONCURRENTLY
#   can lock the table for a long time.
#
#   Instead:
#   CREATE INDEX CONCURRENTLY idx_orders_email ON orders (email);
 
# squawk の設定ファイル (.squawk.toml)
# [general]
# excluded_rules = []
#
# [custom_rules]
# ban_drop_column = true  # DROP COLUMNを禁止

# GitHub Actions での自動 lint
# .github/workflows/migration-check.yml
name: Migration Check
on:
  pull_request:
    paths:
      - 'migrations/**'
 
jobs:
  lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
 
      - name: Install squawk
        run: npm install -g squawk-cli
 
      - name: Get changed migration files
        id: changes
        run: |
          echo "files=$(git diff --name-only origin/main -- 'migrations/*.sql' | tr '\n' ' ')" >> $GITHUB_OUTPUT
 
      - name: Run squawk
        run: squawk ${{ steps.changes.outputs.files }}
 
  test-apply:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
    steps:
      - uses: actions/checkout@v4
 
      - name: Apply all migrations
        run: |
          migrate -path ./migrations \
            -database "postgres://postgres:test@localhost:5432/test?sslmode=disable" \
            up
 
      - name: Verify schema
        run: |
          pg_dump -s postgres://postgres:test@localhost:5432/test > /tmp/schema.sql
          diff expected_schema.sql /tmp/schema.sql

#!/bin/bash
# deploy_migration.sh - 安全なマイグレーション適用スクリプト
 
set -euo pipefail
 
DATABASE_URL="${DATABASE_URL:?DATABASE_URL is required}"
MIGRATIONS_PATH="${MIGRATIONS_PATH:-./migrations}"
LOCK_TIMEOUT="${LOCK_TIMEOUT:-5s}"
STATEMENT_TIMEOUT="${STATEMENT_TIMEOUT:-30s}"
 
echo "=== マイグレーション開始 ==="
echo "Database: ${DATABASE_URL%%@*}@..."
echo "Path: ${MIGRATIONS_PATH}"
 
# 1. 現在のバージョンを確認
CURRENT_VERSION=$(migrate -path "${MIGRATIONS_PATH}" -database "${DATABASE_URL}" version 2>&1 || true)
echo "現在のバージョン: ${CURRENT_VERSION}"
 
# 2. ドライラン（適用するマイグレーション一覧）
echo ""
echo "=== 適用予定のマイグレーション ==="
migrate -path "${MIGRATIONS_PATH}" -database "${DATABASE_URL}" up -dry-run 2>&1 || true
 
# 3. 確認
read -p "続行しますか？ (y/N): " confirm
if [[ "${confirm}" != "y" ]]; then
    echo "中止しました"
    exit 0
fi
 
# 4. タイムアウト設定を適用
psql "${DATABASE_URL}" -c "ALTER DATABASE $(psql "${DATABASE_URL}" -t -c 'SELECT current_database()') SET lock_timeout = '${LOCK_TIMEOUT}';"
psql "${DATABASE_URL}" -c "ALTER DATABASE $(psql "${DATABASE_URL}" -t -c 'SELECT current_database()') SET statement_timeout = '${STATEMENT_TIMEOUT}';"
 
# 5. マイグレーション適用
echo ""
echo "=== マイグレーション適用中 ==="
if migrate -path "${MIGRATIONS_PATH}" -database "${DATABASE_URL}" up; then
    echo "✓ マイグレーション成功"
else
    echo "✗ マイグレーション失敗"
    echo "ロールバックを検討してください:"
    echo "  migrate -path ${MIGRATIONS_PATH} -database \"\${DATABASE_URL}\" down 1"
    exit 1
fi
 
# 6. タイムアウト設定をリセット
psql "${DATABASE_URL}" -c "ALTER DATABASE $(psql "${DATABASE_URL}" -t -c 'SELECT current_database()') RESET lock_timeout;"
psql "${DATABASE_URL}" -c "ALTER DATABASE $(psql "${DATABASE_URL}" -t -c 'SELECT current_database()') RESET statement_timeout;"
 
# 7. 新バージョンの確認
NEW_VERSION=$(migrate -path "${MIGRATIONS_PATH}" -database "${DATABASE_URL}" version 2>&1 || true)
echo ""
echo "=== マイグレーション完了 ==="
echo "バージョン: ${CURRENT_VERSION} → ${NEW_VERSION}"

-- UP マイグレーション
-- 20260211_003_add_orders_status.up.sql
BEGIN;
 
-- ロック待ちのタイムアウト設定
SET lock_timeout = '5s';
 
ALTER TABLE orders ADD COLUMN status_new VARCHAR(20);
 
-- デフォルト値の設定（新しい行のみ）
ALTER TABLE orders ALTER COLUMN status_new SET DEFAULT 'pending';
 
-- マイグレーションバージョンの記録
INSERT INTO schema_migrations (version, description, applied_at)
VALUES ('20260211_003', 'add_orders_status', NOW());
 
COMMIT;
 
-- DOWN マイグレーション
-- 20260211_003_add_orders_status.down.sql
BEGIN;
 
SET lock_timeout = '5s';
 
ALTER TABLE orders DROP COLUMN IF EXISTS status_new;
 
DELETE FROM schema_migrations WHERE version = '20260211_003';
 
COMMIT;

-- テーブル削除は直接行わず、リネームで段階的に実施
-- Phase 1: リネーム（即座にロールバック可能）
ALTER TABLE legacy_data RENAME TO _deprecated_legacy_data_20260211;
 
-- Phase 2: 1-2週間の監視期間
-- アプリケーションエラーがないことを確認
 
-- Phase 3: バックアップ後に削除
-- pg_dump -t _deprecated_legacy_data_20260211 > backup.sql
DROP TABLE IF EXISTS _deprecated_legacy_data_20260211;
 
-- カラム削除も同様に段階的に
-- Phase 1: カラムを使用していないことを確認
SELECT count(*) FROM pg_stat_user_tables
WHERE relname = 'users';
 
-- Phase 2: アプリケーションログで確認（1週間）
-- Phase 3: 削除
ALTER TABLE users DROP COLUMN IF EXISTS old_column;

# database.yml (Rails風の設定例)
development:
  adapter: postgresql
  database: myapp_dev
  pool: 5
  timeout: 5000
  migration_options:
    lock_timeout: "30s"
    statement_timeout: "5min"
 
staging:
  adapter: postgresql
  database: myapp_staging
  pool: 10
  migration_options:
    lock_timeout: "10s"
    statement_timeout: "2min"
 
production:
  adapter: postgresql
  database: myapp_prod
  pool: 25
  migration_options:
    lock_timeout: "5s"
    statement_timeout: "30s"
    concurrent_index: true
    batch_backfill: true
    batch_size: 10000
    batch_sleep: 0.1

-- PostgreSQL の ENUM 型にはALTER TYPEの制約がある
 
-- [OK] 値の追加（安全）
ALTER TYPE order_status ADD VALUE 'cancelled';
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'shipped';
 
-- [NG] 値の削除/リネーム → 直接は不可能
-- 安全な代替手順:
 
-- 1. 新しいENUM型を作成
CREATE TYPE order_status_v2 AS ENUM (
    'pending', 'confirmed', 'shipped', 'delivered', 'cancelled', 'refunded'
);
 
-- 2. カラムの型を変更
ALTER TABLE orders
    ALTER COLUMN status TYPE order_status_v2
    USING status::text::order_status_v2;
 
-- 3. 旧ENUM型を削除
DROP TYPE order_status;
 
-- 4. 新ENUM型をリネーム
ALTER TYPE order_status_v2 RENAME TO order_status;

-- 既存の大規模テーブルをパーティション化する
-- 注意: PostgreSQLでは既存テーブルを直接パーティション化できない
 
-- Phase 1: パーティションテーブルを作成
CREATE TABLE orders_partitioned (
    id          SERIAL,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    total       DECIMAL(10, 2),
    status      VARCHAR(20),
    created_at  TIMESTAMP DEFAULT NOW()
) PARTITION BY RANGE (order_date);
 
-- 月次パーティションを作成
CREATE TABLE orders_y2024m01 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_y2024m02 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... 他の月も同様
 
-- デフォルトパーティション（範囲外データの受け皿）
CREATE TABLE orders_default PARTITION OF orders_partitioned DEFAULT;
 
-- Phase 2: データ移行（バッチで）
INSERT INTO orders_partitioned
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-02-01';
-- バッチごとにCOMMIT
 
-- Phase 3: ビューで透過的にアクセス
CREATE VIEW orders_v AS
SELECT * FROM orders_partitioned
UNION ALL
SELECT * FROM orders WHERE order_date < '2024-01-01';
 
-- Phase 4: アプリを新テーブルに切替
-- Phase 5: 旧テーブルをアーカイブ/削除

-- 問題: 長時間トランザクションがある状態でALTER TABLEを実行すると
-- ロック待ちのカスケードが発生する
 
-- セッション1（アプリ）: 長時間トランザクション
BEGIN;
SELECT * FROM users WHERE id = 1;  -- AccessShareLock取得
-- ... 10分間放置 ...
 
-- セッション2（マイグレーション）: ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- → AccessExclusiveLock を要求
-- → セッション1のAccessShareLockを待つ
 
-- セッション3-N（アプリ）: 新しいSELECT
SELECT * FROM users WHERE id = 2;
-- → AccessShareLock を要求
-- → セッション2のAccessExclusiveLockを待つ
-- → 全アプリがブロックされる！
 
-- 対策: lock_timeout を設定
SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- 5秒以内にロック取得できなければエラー → リトライ
RESET lock_timeout;

-- トランザクション内で複数操作を実行する場合
-- 途中で失敗すると全体がロールバックされる
 
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN fax VARCHAR(20);
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);
-- → エラー: CREATE INDEX CONCURRENTLYはトランザクション内で使用不可
ROLLBACK;
 
-- 対策: CONCURRENTLYはトランザクション外で実行
-- migration_part1.sql (トランザクション内)
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN fax VARCHAR(20);
COMMIT;
 
-- migration_part2.sql (トランザクション外)
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- 問題: DDL実行後、レプリカに反映されるまでの遅延
-- レプリカを読み取りに使用している場合、スキーマ不整合が発生
 
-- 対策1: レプリカ遅延の確認
SELECT
    client_addr,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    replay_lag
FROM pg_stat_replication;
 
-- 対策2: マイグレーション後にレプリカの同期を待つ
-- アプリケーション側:
-- 1. マイグレーション実行
-- 2. レプリカ遅延が0になるまで待機
-- 3. アプリデプロイ

-- 専用のマイグレーションユーザーを作成
CREATE ROLE migration_user WITH LOGIN PASSWORD 'secure_password';
 
-- 必要最小限の権限を付与
GRANT CONNECT ON DATABASE mydb TO migration_user;
GRANT CREATE ON SCHEMA public TO migration_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO migration_user;
 
-- DDL権限（PostgreSQL）
ALTER ROLE migration_user CREATEDB;  -- 必要な場合のみ
 
-- アプリケーションユーザーとは分離
-- アプリユーザーにはDDL権限を付与しない
CREATE ROLE app_user WITH LOGIN PASSWORD 'app_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- マイグレーション履歴テーブルの設計
CREATE TABLE migration_audit (
    id           SERIAL PRIMARY KEY,
    version      VARCHAR(50) NOT NULL,
    description  VARCHAR(255),
    applied_by   VARCHAR(100) DEFAULT current_user,
    applied_at   TIMESTAMP DEFAULT NOW(),
    execution_ms INTEGER,
    checksum     VARCHAR(64),  -- ファイルのSHA-256
    rollback_sql TEXT          -- ロールバック用SQLを保存
);

# 設計判断の記録テンプレート
class ArchitectureDecisionRecord:
    """ADR (Architecture Decision Record) の作成"""
 
    def __init__(self, title: str):
        self.title = title
        self.context = ""
        self.decision = ""
        self.consequences = []
        self.alternatives = []
 
    def set_context(self, context: str):
        """背景と課題の記述"""
        self.context = context
        return self
 
    def set_decision(self, decision: str):
        """決定内容の記述"""
        self.decision = decision
        return self
 
    def add_consequence(self, consequence: str, positive: bool = True):
        """結果の追加"""
        self.consequences.append({
            'description': consequence,
            'type': 'positive' if positive else 'negative'
        })
        return self
 
    def add_alternative(self, name: str, reason_rejected: str):
        """却下した代替案の追加"""
        self.alternatives.append({
            'name': name,
            'reason_rejected': reason_rejected
        })
        return self
 
    def to_markdown(self) -> str:
        """Markdown形式で出力"""
        md = f"# ADR: {self.title}\n\n"
        md += f"## 背景\n{self.context}\n\n"
        md += f"## 決定\n{self.decision}\n\n"
        md += "## 結果\n"
        for c in self.consequences:
            icon = "✅" if c['type'] == 'positive' else "⚠️"
            md += f"- {icon} {c['description']}\n"
        md += "\n## 却下した代替案\n"
        for a in self.alternatives:
            md += f"- **{a['name']}**: {a['reason_rejected']}\n"
        return md

# チーム間のAPI契約定義
from dataclasses import dataclass
from typing import List, Optional
from enum import Enum
 
class Priority(Enum):
    LOW = "low"
    MEDIUM = "medium"
    HIGH = "high"
    CRITICAL = "critical"
 
@dataclass
class APIContract:
    """チーム間のAPI契約"""
    endpoint: str
    method: str
    owner_team: str
    consumers: List[str]
    sla_ms: int  # レスポンスタイムSLA
    priority: Priority
 
    def validate_sla(self, actual_ms: int) -> bool:
        """SLA準拠の確認"""
        return actual_ms <= self.sla_ms
 
    def to_openapi(self) -> dict:
        """OpenAPI形式で出力"""
        return {
            'path': self.endpoint,
            'method': self.method,
            'x-owner': self.owner_team,
            'x-consumers': self.consumers,
            'x-sla-ms': self.sla_ms
        }
 
# 使用例
contracts = [
    APIContract(
        endpoint="/api/v1/users",
        method="GET",
        owner_team="user-team",
        consumers=["order-team", "notification-team"],
        sla_ms=200,
        priority=Priority.HIGH
    ),
    APIContract(
        endpoint="/api/v1/orders",
        method="POST",
        owner_team="order-team",
        consumers=["payment-team", "inventory-team"],
        sla_ms=500,
        priority=Priority.CRITICAL
    )
]

# golang-migrate: dirty状態の解消
# 1. 現在の状態を確認
migrate -path ./migrations -database "$DB_URL" version
# → 出力: 5 (dirty)
 
# 2. dirty フラグをクリア（手動で修正済みの場合）
migrate -path ./migrations -database "$DB_URL" force 5
 
# 3. 修正後に再適用
migrate -path ./migrations -database "$DB_URL" up

-- 原因: 長時間トランザクションがロックを保持
-- 1. ロック保持しているクエリを確認
SELECT
    pid,
    usename,
    state,
    query_start,
    NOW() - query_start AS duration,
    LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '1 minute'
ORDER BY duration DESC;
 
-- 2. 長時間トランザクションが完了するのを待つか、キャンセル
SELECT pg_cancel_backend(pid);  -- クエリのキャンセル
-- SELECT pg_terminate_backend(pid);  -- セッションの強制終了（最終手段）

# スキーマの差分を確認
pg_dump -s $STAGING_URL > staging_schema.sql
pg_dump -s $PRODUCTION_URL > production_schema.sql
diff staging_schema.sql production_schema.sql
 
# もしくは migra を使用（差分SQLの自動生成）
pip install migra
migra $STAGING_URL $PRODUCTION_URL
# → ALTER TABLE ... が出力される