Comment déployer Cloud SQL en haute disponibilité avec Terraform en 2026

#!/bin/bash

gcloud auth login
gcloud config set project YOUR_PROJECT_ID
gcloud services enable sqladmin.googleapis.com compute.googleapis.com monitoring.googleapis.com

mkdir cloudsql-ha-terraform && cd cloudsql-ha-terraform

cat > versions.tf << EOF
terraform {
  required_version = ">= 1.9.0"
  required_providers {
    google = {
      source  = "hashicorp/google"
      version = "~> 6.0"
    }
  }
}
EOF

terraform init

provider "google" {
  project = var.project_id
  region  = var.region
}

resource "google_compute_network" "vpc" {
  name                    = "cloudsql-vpc"
  auto_create_subnetworks = false
  mtu                     = 1460
}

resource "google_compute_subnetwork" "private" {
  name                     = "cloudsql-subnet"
  ip_cidr_range            = "10.0.0.0/20"
  region                   = var.region
  network                  = google_compute_network.vpc.id
  private_ip_google_access = true
  description              = "Sous-réseau privé pour Cloud SQL"
}

variable "project_id" {
  description = "GCP Project ID"
  type        = string
}

variable "region" {
  description = "Region GCP"
  type        = string
  default     = "europe-west1"
}

output "vpc_id" {
  value = google_compute_network.vpc.id
}

resource "google_sql_database_instance" "ha_postgres" {
  name             = "ha-postgres"
  database_version = "POSTGRES_16"
  region           = var.region

  settings {
    tier              = "db-custom-4-15360"  # 4 vCPU, 15GB RAM pour prod
    availability_type = "REGIONAL"           # HA avec failover auto
    disk_autoresize   = true
    disk_size         = "100"
    disk_type         = "PD_SSD"

    ip_configuration {
      ipv4_enabled    = false               # Pas d'IP publique
      private_network = google_compute_network.vpc.id
      require_ssl     = true
    }

    backup_configuration {
      enabled                        = true
      start_time                     = "02:00"
      location                       = "EU"
      point_in_time_recovery_enabled = true
      retention_period_days          = 7
    }

    maintenance_window {
      day  = 7  # Dimanche
      hour = 3
    }

    database_flags {
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
  }

  deletion_protection = true
}

resource "google_sql_database" "app_db" {
  name     = "appdb"
  instance = google_sql_database_instance.ha_postgres.name
}

resource "google_sql_user" "app_user" {
  name     = "appuser"
  instance = google_sql_database_instance.ha_postgres.name
  password = "SecurePass123!"  # Utilisez secrets manager en prod
}

resource "google_monitoring_dashboard" "cloudsql_dashboard" {
  dashboard_json = jsonencode({
    displayName = "Cloud SQL HA Dashboard"
    row = [{
      title = "Métriques Clés"
      charts = [{
        displayName = "CPU Utilisation"
        metricType = "cloudsql.googleapis.com/database/cpu/utilization"
        resourceType = "cloud_sql_database"
      }, {
        displayName = "Connexions Actives"
        metricType = "cloudsql.googleapis.com/database/postgresql/connections"
        resourceType = "cloud_sql_database"
      }]
    }]
  })
}

resource "google_monitoring_alert_policy" "high_cpu" {
  display_name = "Alerte CPU Cloud SQL >80%"
  combiner     = "OR"

  conditions {
    display_name = "cpu_high"
    condition_threshold {
      filter     = "resource.type=\"cloud_sql_database\" AND metric.type=\"cloudsql.googleapis.com/database/cpu/utilization\" AND resource.label.\"database_id\"=\"${google_sql_database_instance.ha_postgres.name}\""
      threshold_value = 0.8
      duration   = "60s"
      comparison = "COMPARISON_GT"
    }
  }

  notification_channels = []  # Ajoutez vos channels
}

-- Connexion via gcloud sql connect ha-postgres --user=postgres --database=appdb

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (email) VALUES 
  ('user1@example.com'),
  ('user2@example.com');

-- Vérifier
SELECT * FROM users;

-- Index pour perf
CREATE INDEX idx_users_email ON users(email);

import os
import pymysql  # pip install pymysql

from google.cloud.sql.connector import Connector, IPTypes
import sqlalchemy

# Instance connexion ID: projects/PROJECT/locations/REGION/instances/INSTANCE
INSTANCE_CONNECTION_NAME = "YOUR_PROJECT:REGION:ha-postgres"

connector = Connector(ip_type=IPTypes.PRIVATE)

def getconn() -> sqlalchemy.engine.base.Connection:
    conn = connector.connect(
        INSTANCE_CONNECTION_NAME,
        "tcp:5432",
        user="appuser",
        password="SecurePass123!",
        db="appdb"
    )
    return conn

engine = sqlalchemy.create_engine(
    "postgresql://",
    creator=getconn,
)

with engine.connect() as conn:
    result = conn.execute(sqlalchemy.text("SELECT * FROM users"))
    print([dict(row._mapping) for row in result])

connector.close()

#!/bin/bash

terraform validate
terraform plan -var="project_id=YOUR_PROJECT_ID" -var="region=europe-west1"
terraform apply -auto-approve -var="project_id=YOUR_PROJECT_ID" -var="region=europe-west1"

# Outputs
terraform output

# Cleanup
# terraform destroy -auto-approve