Skills Development GCP Cloud SQL Provisioning

GCP Cloud SQL Provisioning

v20261104
gcp-cloud-sql
Provision and manage Google Cloud SQL instances for PostgreSQL, MySQL, and SQL Server, including high availability, backups, private IP, replicas, and secure connectivity.
Get Skill
240 downloads
Overview

GCP Cloud SQL

Deploy and manage fully managed relational databases (PostgreSQL, MySQL, SQL Server) on Google Cloud.

When to Use

  • Running production relational databases without managing replication, patching, or backups
  • Migrating on-premises PostgreSQL or MySQL workloads to a managed service
  • Applications requiring ACID transactions, relational schemas, and SQL query support
  • Workloads that need automated high availability with regional failover

Prerequisites

Confirm project/region, database edition/version and cost before provisioning. Examples require adaptation: IAM database auth needs the database flag, an IAM database user and login permissions in addition to cloudsql.client; a built-in appuser is not automatically IAM-enabled. Grant application database privileges explicitly. Auth Proxy does not create private network connectivity; use --private-ip and an existing route for private-only instances. Pin and verify the proxy binary from official releases. Backups/HA are not a tested DR plan; exports expose data and imports/promotion/restarts require an approved target/recovery plan. Terraform stores passwords in state; protect it. Preserve existing authorized-network/flag settings when patching rather than replacing them accidentally.

  • Google Cloud SDK (gcloud) installed and authenticated
  • Cloud SQL Admin API and Service Networking API enabled
  • IAM role roles/cloudsql.admin for full management
gcloud services enable sqladmin.googleapis.com servicenetworking.googleapis.com

Instance Tiers Reference

TiervCPUsMemoryUse Case
db-f1-microShared0.6 GBDev/test only
db-g1-smallShared1.7 GBLow-traffic staging
db-custom-2-819228 GBSmall production
db-custom-4-16384416 GBMedium production
db-custom-8-32768832 GBHigh-traffic production

Create a PostgreSQL Instance

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 \
  --labels=env=production,team=backend

gcloud sql databases create myapp --instance=prod-db --charset=UTF8
# Create the application identity/password through a supported protected prompt/secret workflow.
# Persist credentials securely before use; never generate and discard a password in process arguments.

Create a MySQL Instance

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 \
  --labels=env=production

Private IP Configuration

# Allocate IP range and create private connection
gcloud compute addresses create google-managed-services \
  --global --purpose=VPC_PEERING --prefix-length=16 --network=my-vpc

gcloud services vpc-peerings connect \
  --service=servicenetworking.googleapis.com \
  --ranges=google-managed-services --network=my-vpc

# Create instance with private IP only
gcloud sql instances create private-db \
  --database-version=POSTGRES_16 --tier=db-custom-2-8192 \
  --region=us-central1 \
  --network=projects/${PROJECT_ID}/global/networks/my-vpc \
  --no-assign-ip --availability-type=REGIONAL \
  --storage-type=SSD --storage-size=50GB --storage-auto-increase

Read Replicas

# Same-region replica
gcloud sql instances create prod-db-replica-1 \
  --master-instance-name=prod-db --tier=db-custom-4-16384 \
  --region=us-central1 --availability-type=ZONAL

# Cross-region replica for DR
gcloud sql instances create prod-db-replica-eu \
  --master-instance-name=prod-db --tier=db-custom-4-16384 \
  --region=europe-west1 --availability-type=ZONAL

# Promote a replica to standalone (disaster recovery)
gcloud sql instances promote-replica prod-db-replica-eu

Backups and Restore

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

# Point-in-time recovery
gcloud sql instances clone prod-db prod-db-pitr \
  --point-in-time="2026-03-23T10:00:00Z"

# Export / import
gcloud sql export sql prod-db gs://my-bucket/export.sql.gz --database=myapp
gcloud sql import sql prod-db gs://my-bucket/export.sql.gz --database=myapp

Cloud SQL Auth Proxy

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

# Unix socket (for Kubernetes sidecar pattern)
./cloud-sql-proxy ${PROJECT_ID}:us-central1:prod-db --unix-socket=/tmp/cloudsql
psql "host=/tmp/cloudsql/${PROJECT_ID}:us-central1:prod-db user=appuser dbname=myapp"

Connection Methods Summary

MethodUse CaseRequirement
Public IP + SSLDev/test accessAuthorized networks configured
Cloud SQL Auth ProxyProduction on GCE/GKESA with roles/cloudsql.client
Private IPVPC-native appsVPC peering configured
Cloud SQL Connector libApp-level integrationSA credentials

Terraform Configuration

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]
}

Common Operations

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

Troubleshooting

SymptomCauseFix
Connection refused via public IPIP not in authorized networksAdd IP with gcloud sql instances patch --authorized-networks
SSL required errorrequire_ssl=true but client not using SSLUse Cloud SQL Proxy or pass sslmode=require
High replication lagReplica tier too small or write-heavy primaryIncrease replica tier; reduce write load
Instance slow despite RUNNABLEUnder-provisioned CPU/memoryScale tier with gcloud sql instances patch --tier
Proxy returns ECONNREFUSEDWrong connection name or missing IAM roleVerify project:region:instance format; grant roles/cloudsql.client
Cannot create private IP instanceVPC peering not establishedRun gcloud services vpc-peerings connect first
Backup restore failsIncompatible versionEnsure same major database version between source and target

Related Skills

  • gcp-networking - VPC and private service connect for Cloud SQL private IP
  • terraform-gcp - Provision Cloud SQL with Infrastructure as Code
  • gcp-gke - Connecting Kubernetes workloads to Cloud SQL via sidecar proxy
  • gcp-compute - Running applications on Compute Engine that connect to Cloud SQL

Limitations

  • Infrastructure commands can disrupt services: confirm target host/scope and have backups/snapshots before mutating state.
  • Docs-only import: upstream scripts and templates not bundled.
Info
Category Development
Name gcp-cloud-sql
Version v20261104
Size 10.28KB
Updated At 2026-11-05
Language