GCP Cloud SQL

GCP Cloud SQL

Overview

Cloud SQL is Google Cloud's managed relational database service for MySQL, PostgreSQL, and SQL Server. It handles patches, upgrades, replication, automated backups, point-in-time recovery, and HA failover so applications focus on schema and queries instead of database administration.

Instructions

Core Concepts

• Instance — a managed VM running MySQL/Postgres/SQL Server with attached storage
• High Availability (HA) — synchronous replica in another zone with automatic failover
• Read replica — async read-only copy for scaling reads or cross-region DR
• Cloud SQL Auth Proxy — local sidecar that handles IAM auth, TLS, and connection routing
• Private IP — instance reachable only via VPC peering, never the public internet
• Point-in-time recovery (PITR) — restore to any second within the retention window using binary logs

Prerequisites

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

# One-time: reserve a private range for VPC peering (for private IP instances)
gcloud compute addresses create google-managed-services-default \
  --global --purpose=VPC_PEERING --prefix-length=16 \
  --network=default

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

Creating a PostgreSQL Instance with HA

gcloud sql instances create orders-db \
  --database-version=POSTGRES_15 \
  --tier=db-custom-2-7680 \
  --region=us-central1 \
  --availability-type=REGIONAL \
  --network=default \
  --no-assign-ip \
  --backup-start-time=02:00 \
  --enable-point-in-time-recovery \
  --retained-backups-count=14 \
  --database-flags=cloudsql.iam_authentication=on,log_min_duration_statement=500

# Set the postgres password (or skip and use IAM auth exclusively)
gcloud sql users set-password postgres \
  --instance=orders-db \
  --password="$(openssl rand -base64 24)"

# Create application database and user
gcloud sql databases create orders --instance=orders-db
gcloud sql users create app_user --instance=orders-db --password="$(openssl rand -base64 24)"

Creating a MySQL Instance

gcloud sql instances create analytics-db \
  --database-version=MYSQL_8_0 \
  --tier=db-n1-standard-2 \
  --region=us-central1 \
  --storage-type=SSD \
  --storage-size=100 \
  --storage-auto-increase \
  --backup-start-time=03:00 \
  --enable-bin-log

Read Replicas

# Read replica in the same region (scale reads)
gcloud sql instances create orders-db-replica \
  --master-instance-name=orders-db \
  --region=us-central1 \
  --tier=db-custom-2-7680

# Cross-region replica (DR + low-latency reads in another region)
gcloud sql instances create orders-db-eu \
  --master-instance-name=orders-db \
  --region=europe-west1 \
  --tier=db-custom-2-7680

# Promote a replica to a standalone primary (DR failover)
gcloud sql instances promote-replica orders-db-eu

Connecting via Cloud SQL Auth Proxy

# Get the connection name
gcloud sql instances describe orders-db --format="value(connectionName)"
# Returns: my-project:us-central1:orders-db

# Run the proxy locally
./cloud-sql-proxy --port 5432 my-project:us-central1:orders-db

# In another terminal:
psql "host=127.0.0.1 port=5432 user=app_user dbname=orders"

# Cloud Run sidecar pattern (Cloud Run handles the proxy automatically with --add-cloudsql-instances)
# For GKE, deploy the proxy as a sidecar in the same pod:
apiVersion: apps/v1
kind: Deployment
metadata:
  name: api
spec:
  template:
    spec:
      serviceAccountName: api-sa  # bound to a GSA with roles/cloudsql.client
      containers:
        - name: api
          image: gcr.io/my-project/api:latest
          env:
            - name: DATABASE_URL
              value: "postgresql://app_user@127.0.0.1:5432/orders"
        - name: cloud-sql-proxy
          image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.11.0
          args:
            - "--auto-iam-authn"
            - "--private-ip"
            - "my-project:us-central1:orders-db"
          securityContext:
            runAsNonRoot: true

IAM Database Authentication

# Add a service account as a Postgres database user
gcloud sql users create app-sa@my-project.iam \
  --instance=orders-db \
  --type=cloud_iam_service_account

-- Grant database privileges (run as postgres superuser)
GRANT CONNECT ON DATABASE orders TO "app-sa@my-project.iam";
GRANT USAGE ON SCHEMA public TO "app-sa@my-project.iam";
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO "app-sa@my-project.iam";

The application uses no static password — the proxy fetches a short-lived OAuth token from the metadata server and passes it as the database password.

Backups and Point-in-Time Recovery

# Manual on-demand backup before risky migration
gcloud sql backups create --instance=orders-db --description="pre-v2-migration"

# Restore the database to a specific point in time (requires PITR enabled)
gcloud sql instances clone orders-db orders-db-recovery \
  --point-in-time='2026-04-15T14:30:00Z'

Terraform

resource "google_sql_database_instance" "orders" {
  name             = "orders-db"
  database_version = "POSTGRES_15"
  region           = "us-central1"
  deletion_protection = true

  settings {
    tier              = "db-custom-2-7680"
    availability_type = "REGIONAL"

    ip_configuration {
      ipv4_enabled    = false
      private_network = data.google_compute_network.default.id
    }

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

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

resource "google_sql_user" "app_sa" {
  name     = "app-sa@${var.project_id}.iam"
  instance = google_sql_database_instance.orders.name
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

Examples

Example 1 — Migrate a self-hosted Postgres to Cloud SQL

User wants to move a 200 GB Postgres database to Cloud SQL with minimal downtime. Create a target instance with --availability-type=REGIONAL and PITR enabled, use Database Migration Service to perform a continuous logical replication from the source, run validation queries, then promote the destination during a short cutover window. Hand the user the new connection string via the Auth Proxy and a Terraform module for the instance.

Example 2 — Add cross-region read replica for EU users

User reports high read latency from European users. Create a read replica in europe-west1 with the same tier as primary, point the EU app instances at the replica's connection name through their Auth Proxy sidecars, and verify replication lag stays under 5 seconds via gcloud sql operations and Cloud Monitoring metrics.

Guidelines

• Use private IP only in production — never expose Cloud SQL on a public IP
• Always set availability-type=REGIONAL for production workloads
• Enable PITR (--enable-point-in-time-recovery) — it's cheap insurance against accidental writes
• Prefer IAM database authentication over passwords — works for service accounts and human users
• Run the Cloud SQL Auth Proxy as a sidecar (Cloud Run, GKE) rather than embedding TLS logic in the app
• Pin database flags via Terraform so they survive instance recreation
• For schema migrations, take an on-demand backup first
• Monitor cloudsql.googleapis.com/database/cpu/utilization and connection count — alert at 80%
• Read replicas are async — never write to them and assume eventual consistency for reads from them