📖
cloud sql(postgres) log戦略

2025/03/02に公開
Database
Google CloudでPostgreSQLを使用する場合の一般的なログ戦略のベストプラクティスをTerraformコードで説明します。
PostgreSQLのログ戦略は、セキュリティ監査、パフォーマンス最適化、障害調査のために重要です。以下にTerraformコードの例を示します：
# PostgreSQL Cloud SQLインスタンスの設定
resource "google_sql_database_instance" "postgres_instance" {
  name             = "postgres-instance"
  database_version = "POSTGRES_14"
  region           = "asia-northeast1"
  
  settings {
    tier = "db-custom-2-7680"
    
    # PostgreSQL監査ログの設定
    database_flags {
      name  = "log_checkpoints"
      value = "on"
    }
    
    database_flags {
      name  = "log_connections"
      value = "on"
    }
    
    database_flags {
      name  = "log_disconnections"
      value = "on"
    }
    
    database_flags {
      name  = "log_lock_waits"
      value = "on"
    }
    
    database_flags {
      name  = "log_min_duration_statement"
      value = "1000"  # 1秒以上かかるクエリをログ
    }
    
    database_flags {
      name  = "log_statement"
      value = "ddl"  # DDLステートメントのみログ記録（本番環境向け）
      # 開発環境では "all" を使用してすべてのステートメントを記録することも可能
    }
    
    database_flags {
      name  = "log_temp_files"
      value = "0"  # 一時ファイル使用のログを記録
    }
    
    database_flags {
      name  = "log_min_error_statement"
      value = "error"  # エラーを含むステートメントをログ
    }
    
    # バックアップ設定
    backup_configuration {
      enabled    = true
      start_time = "00:00"
      point_in_time_recovery_enabled = true
    }
  }
}

# ログシンク設定（Cloud Storageへ）
resource "google_logging_project_sink" "postgres_logs_storage" {
  name        = "postgres-logs-storage-sink"
  destination = "storage.googleapis.com/${google_storage_bucket.postgres_logs.name}"
  
  # PostgreSQL特有のログフィルター
  filter      = "resource.type=cloudsql_database AND resource.labels.database_id:${google_sql_database_instance.postgres_instance.name}"

  unique_writer_identity = true
}

# ログ保存用のStorageバケット（階層型ストレージ）
resource "google_storage_bucket" "postgres_logs" {
  name          = "postgres-logs-${var.project_id}"
  location      = "asia-northeast1"
  storage_class = "STANDARD"
  
  # ライフサイクルルール（階層型保存戦略）
  lifecycle_rule {
    condition {
      age = 30  # 30日後
    }
    action {
      type = "NEARLINE"  # 低頻度アクセス用ストレージへ移行
    }
  }
  
  lifecycle_rule {
    condition {
      age = 90  # 90日後
    }
    action {
      type = "COLDLINE"  # コールドストレージへ移行
    }
  }
  
  lifecycle_rule {
    condition {
      age = 365  # 1年後
    }
    action {
      type = "ARCHIVE"  # アーカイブストレージへ移行
    }
  }
  
  lifecycle_rule {
    condition {
      age = 730  # 2年後
    }
    action {
      type = "DELETE"  # 削除
    }
  }
  
  # バージョニング設定（誤削除防止）
  versioning {
    enabled = true
  }
}

# BigQueryへのエクスポート（分析用）
resource "google_bigquery_dataset" "postgres_logs" {
  dataset_id                  = "postgres_logs"
  friendly_name               = "PostgreSQL Logs"
  description                 = "Dataset for PostgreSQL logs analysis"
  location                    = "asia-northeast1"
  default_table_expiration_ms = 7776000000  # 90日
}

resource "google_logging_project_sink" "postgres_logs_bigquery" {
  name        = "postgres-logs-bigquery-sink"
  destination = "bigquery.googleapis.com/projects/${var.project_id}/datasets/${google_bigquery_dataset.postgres_logs.dataset_id}"
  
  # パフォーマンス問題に関連するログのみをフィルタリング
  filter      = "resource.type=cloudsql_database AND resource.labels.database_id:${google_sql_database_instance.postgres_instance.name} AND textPayload:\"duration:\""

  unique_writer_identity = true
}

# Pub/Subへのエクスポート（リアルタイムモニタリング用）
resource "google_pubsub_topic" "postgres_logs_alerts" {
  name = "postgres-logs-alerts"
}

resource "google_logging_project_sink" "postgres_logs_pubsub" {
  name        = "postgres-logs-pubsub-sink"
  destination = "pubsub.googleapis.com/projects/${var.project_id}/topics/${google_pubsub_topic.postgres_logs_alerts.name}"
  
  # 重大なエラーのみをフィルタリング
  filter      = "resource.type=cloudsql_database AND resource.labels.database_id:${google_sql_database_instance.postgres_instance.name} AND severity>=ERROR"

  unique_writer_identity = true
}

# 必要なIAM権限の設定
resource "google_storage_bucket_iam_binding" "postgres_logs_writer" {
  bucket = google_storage_bucket.postgres_logs.name
  role   = "roles/storage.objectCreator"
  
  members = [
    google_logging_project_sink.postgres_logs_storage.writer_identity,
  ]
}

resource "google_project_iam_binding" "bigquery_postgres_sink_writer" {
  project = var.project_id
  role    = "roles/bigquery.dataEditor"
  
  members = [
    google_logging_project_sink.postgres_logs_bigquery.writer_identity,
  ]
}

resource "google_pubsub_topic_iam_binding" "postgres_logs_publisher" {
  project = var.project_id
  topic   = google_pubsub_topic.postgres_logs_alerts.name
  role    = "roles/pubsub.publisher"
  
  members = [
    google_logging_project_sink.postgres_logs_pubsub.writer_identity,
  ]
}

# ログベースのアラート（オプション）
resource "google_monitoring_alert_policy" "postgres_error_alert" {
  display_name = "PostgreSQL Critical Errors"
  combiner     = "OR"
  
  conditions {
    display_name = "Critical PostgreSQL Errors"
    
    condition_threshold {
      filter          = "resource.type=cloudsql_database AND resource.labels.database_id=${google_sql_database_instance.postgres_instance.name} AND severity>=CRITICAL"
      duration        = "60s"
      comparison      = "COMPARISON_GT"
      threshold_value = 0
      
      aggregations {
        alignment_period   = "60s"
        per_series_aligner = "ALIGN_COUNT"
      }
    }
  }

  notification_channels = [
    # 通知チャンネルIDを設定
  ]
}

 PostgreSQLログ戦略のベストプラクティス段階的なログレベル設定
開発環境: log_statement = "all" で詳細なデバッグ
テスト環境: log_statement = "mod" でDMLを記録
本番環境: log_statement = "ddl" でスキーマ変更のみ記録
多層的なログ保存戦略
ホットデータ: Cloud Logging (30日)
ウォームデータ: BigQuery (90日、分析用)
コールドデータ: Cloud Storage (階層型ストレージで長期保存)
目的別フィルタリング
セキュリティ: 認証関連ログ
パフォーマンス: 長時間クエリ
運用: エラーと警告
コスト最適化
必要なログのみを選択的に保存
時間経過とともに低コストのストレージに移行
BigQueryでは分析に必要なフィールドのみを選択
この設定は、セキュリティ、パフォーマンス、コンプライアンス要件のバランスを取りながら、効率的なPostgreSQLのログ管理を実現します。組織の要件に応じて適宜調整してください。
PostgreSQLログ戦略のベストプラクティス

Discussion
