gcloud services enable sqladmin.googleapis.com servicenetworking.googleapis.com

gcloud sql instances create prod-db \
  --database-version=POSTGRES_16 \
  --tier=db-custom-4-16384 \
  --region=us-central1 \
  --availability-type=REGIONAL \
  --storage-type=SSD --storage-size=100GB --storage-auto-increase \
  --backup-start-time=02:00 --enable-point-in-time-recovery \
  --retained-backups-count=14 \
  --maintenance-window-day=SUN --maintenance-window-hour=4 \
  --database-flags=max_connections=200,log_min_duration_statement=1000 \
  --root-password=$(openssl rand -base64 24) \
  --labels=env=production,team=backend

gcloud sql databases create myapp --instance=prod-db --charset=UTF8
gcloud sql users create appuser --instance=prod-db \
  --password=$(openssl rand -base64 24)

gcloud sql instances create mysql-prod \
  --database-version=MYSQL_8_0 \
  --tier=db-custom-4-16384 --region=us-central1 \
  --availability-type=REGIONAL \
  --storage-type=SSD --storage-size=100GB --storage-auto-increase \
  --backup-start-time=02:00 --enable-bin-log --retained-backups-count=14 \
  --database-flags=slow_query_log=on,long_query_time=2,max_connections=500 \
  --root-password=$(openssl rand -base64 24)

undefined

undefined

undefined

undefined

gcloud sql backups create --instance=prod-db --description="pre-migration"
gcloud sql backups list --instance=prod-db

undefined

curl -o cloud-sql-proxy \
  https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.11.0/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

./cloud-sql-proxy ${PROJECT_ID}:us-central1:prod-db --port=5432 --auto-iam-authn

undefined

resource "google_sql_database_instance" "main" {
  name             = "prod-db"
  database_version = "POSTGRES_16"
  region           = "us-central1"

  settings {
    tier              = "db-custom-4-16384"
    availability_type = "REGIONAL"
    disk_type         = "PD_SSD"
    disk_size         = 100
    disk_autoresize   = true

    backup_configuration {
      enabled                        = true
      start_time                     = "02:00"
      point_in_time_recovery_enabled = true
      backup_retention_settings { retained_backups = 14 }
    }

    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.vpc.id
      require_ssl     = true
    }

    maintenance_window { day = 7; hour = 4 }
    database_flags { name = "max_connections"; value = "200" }

    user_labels = { env = "production" }
  }

  deletion_protection = true
  depends_on          = [google_service_networking_connection.private_vpc]
}

resource "google_sql_database" "app" {
  name     = "myapp"
  instance = google_sql_database_instance.main.name
}

resource "google_sql_user" "app" {
  name     = "appuser"
  instance = google_sql_database_instance.main.name
  password = random_password.db_password.result
}

resource "google_sql_database_instance" "replica" {
  name                 = "prod-db-replica-1"
  master_instance_name = google_sql_database_instance.main.name
  region               = "us-central1"
  database_version     = "POSTGRES_16"

  replica_configuration { failover_target = false }

  settings {
    tier            = "db-custom-4-16384"
    disk_type       = "PD_SSD"
    disk_autoresize = true
    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.vpc.id
    }
  }
}

resource "google_compute_global_address" "private_ip" {
  name          = "google-managed-services"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.vpc.id
}

resource "google_service_networking_connection" "private_vpc" {
  network                 = google_compute_network.vpc.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip.name]
}

gcloud sql instances list
gcloud sql instances describe prod-db \
  --format="yaml(state,settings.tier,settings.availabilityType,ipAddresses)"
gcloud sql instances patch prod-db --storage-size=200GB
gcloud sql instances patch prod-db --database-flags=max_connections=300
gcloud sql instances restart prod-db