cofin/cloud-sql

Cloud SQL

Overview

Cloud SQL is Google Cloud's fully managed relational database service supporting PostgreSQL, MySQL, and SQL Server. It handles automated backups, replication, patching, high availability, and scaling — letting you focus on your application instead of database administration.

Quick Reference

Cloud SQL vs AlloyDB

| Feature | Cloud SQL | AlloyDB | |---|---|---| | Engines | PostgreSQL, MySQL, SQL Server | PostgreSQL only | | Storage | Attached SSD (up to 64 TB) | Disaggregated, log-based | | Availability SLA | 99.95% (HA config) | 99.99% (regional) | | Columnar engine | Not available | Built-in adaptive | | ML embeddings | Manual setup | Native Vertex AI | | Read scaling | Manual read replicas | Read pool (auto-managed) | | Networking | Public IP or private IP | Private IP only (PSA required) | | Cost | Lower entry cost | Higher, performance-optimized | | Best for | General workloads, MySQL/SQL Server | High-performance PostgreSQL |

Instance Management

| Action | Command | |---|---| | Create instance | gcloud sql instances create NAME --database-version=POSTGRES_15 --tier=db-g1-small --region=REGION | | Clone instance | gcloud sql instances clone SOURCE DEST | | Restart instance | gcloud sql instances restart NAME | | Patch/resize | gcloud sql instances patch NAME --tier=db-n1-standard-4 | | Delete instance | gcloud sql instances delete NAME | | Set maintenance window | gcloud sql instances patch NAME --maintenance-window-day=SUN --maintenance-window-hour=3 |

Key Commands

| Action | Command | |---|---| | Create database | gcloud sql databases create DBNAME --instance=INSTANCE | | Create user | gcloud sql users create USERNAME --instance=INSTANCE --password=PASS | | Connect via proxy | cloud-sql-proxy PROJECT:REGION:INSTANCE | | Connect directly | gcloud sql connect INSTANCE --user=postgres --database=DBNAME | | Create backup | gcloud sql backups create --instance=INSTANCE | | List backups | gcloud sql backups list --instance=INSTANCE | | Restore backup | gcloud sql backups restore BACKUP_ID --restore-instance=INSTANCE |

Connection Patterns Overview

| Pattern | When to Use | |---|---| | Auth Proxy | Recommended default — handles IAM auth and TLS automatically | | Private IP | GKE/GCE on same VPC — lowest latency, no proxy overhead | | PSC (Private Service Connect) | Cross-project or cross-org access without VPC peering | | Public IP + authorized networks | Legacy only — always enforce SSL, restrict to known CIDRs |

# Enable required APIs
gcloud services enable sqladmin.googleapis.com
gcloud services enable sql-component.googleapis.com

# Create a PostgreSQL instance with HA
gcloud sql instances create my-postgres \
    --database-version=POSTGRES_15 \
    --tier=db-n1-standard-4 \
    --region=us-central1 \
    --availability-type=REGIONAL \
    --storage-type=SSD \
    --storage-size=100GB \
    --storage-auto-increase \
    --backup-start-time=03:00 \
    --enable-bin-log \
    --maintenance-window-day=SUN \
    --maintenance-window-hour=4 \
    --no-assign-ip \
    --network=projects/MY_PROJECT/global/networks/MY_VPC

# Connect via Auth Proxy
cloud-sql-proxy MY_PROJECT:us-central1:my-postgres --port=5432 &
psql "host=127.0.0.1 port=5432 dbname=mydb user=postgres"

Engine-Specific Notes

PostgreSQL — Use POSTGRES_15 or POSTGRES_16. Supports pgvector, PostGIS, pg_stat_statements. Set max_connections conservatively; use PgBouncer for connection pooling.

MySQL — Use MYSQL_8_0. InnoDB only. innodb_buffer_pool_size defaults to 75% of instance RAM. Binary logging required for read replicas.

SQL Server — Use SQLSERVER_2022_STANDARD or ENTERPRISE. Always-on availability groups supported. Windows Authentication not available; use SQL Server auth or IAM.

Backup and Restore

# Enable automated backups with PITR
gcloud sql instances patch my-postgres \
    --backup-start-time=03:00 \
    --enable-bin-log \
    --retained-backups-count=14 \
    --retained-transaction-log-days=7

# On-demand backup
gcloud sql backups create --instance=my-postgres --description="pre-migration"

# Point-in-time restore (PostgreSQL/MySQL)
gcloud sql instances clone my-postgres my-postgres-restored \
    --point-in-time="2025-06-15T14:30:00Z"

# Cross-region replica for disaster recovery
gcloud sql instances create my-postgres-replica \
    --master-instance-name=my-postgres \
    --region=us-east1

Replication

# Create read replica (same region)
gcloud sql instances create my-postgres-read \
    --master-instance-name=my-postgres \
    --region=us-central1

# Promote replica to standalone (for migrations)
gcloud sql instances promote-replica my-postgres-read

# List replicas
gcloud sql instances list --filter="masterInstanceName=my-postgres"

Security

# Enable IAM database authentication
gcloud sql instances patch my-postgres \
    --database-flags=cloudsql.iam_authentication=on

# Add IAM user (PostgreSQL)
gcloud sql users create [email protected] \
    --instance=my-postgres \
    --type=CLOUD_IAM_USER

# Enforce SSL
gcloud sql instances patch my-postgres \
    --require-ssl

# Enable audit logging
gcloud sql instances patch my-postgres \
    --database-flags=cloudsql.enable_pgaudit=on

<workflow>

Workflow

Step 1: Plan Instance Configuration

Choose engine version, tier (machine type), and storage based on workload. For production, always use --availability-type=REGIONAL for HA with automatic failover. Size memory to fit the working dataset with ~30% headroom.

Step 2: Configure Networking

Prefer private IP over public IP. If using private IP, ensure a VPC exists and pass --network= and --no-assign-ip at creation time. Private IP cannot be added after creation without recreation. For cross-project access, use PSC instead of VPC peering.

Step 3: Create Instance and Database Objects

Create the instance, then create databases and users. Use IAM database authentication over password auth when possible. Store passwords in Secret Manager.

Step 4: Set Up Auth Proxy for Application Connections

Deploy the Cloud SQL Auth Proxy as a sidecar (GKE), standalone binary (GCE), or let Cloud Run handle it automatically with --add-cloudsql-instances. The proxy handles TLS and IAM authentication transparently.

Step 5: Configure Backups and Monitoring

Enable automated backups, set PITR retention, and configure maintenance windows during off-peak hours. Enable Query Insights for performance monitoring. Set up alerts for disk usage, CPU, and active connections.

Step 6: (Optional) Add Read Replicas

For read-heavy workloads, create read replicas and update application connection strings to route read queries to replicas. For PostgreSQL, consider PgBouncer as a connection pool in front of both primary and replicas.

</workflow> <guardrails>

Guardrails

• Never expose public IP without authorized networks and SSL — use Auth Proxy or private IP; if public IP is required, set --require-ssl and restrict --authorized-networks to known CIDRs
• Always enable automated backups and PITR — set --backup-start-time and --retained-backups-count at creation; enabling after the fact risks a gap
• Set maintenance windows to off-peak hours — patch windows cause brief downtime on non-HA instances; set --maintenance-window-day and --maintenance-window-hour
• Prefer IAM database authentication over password auth for GCP service accounts and human users; passwords must still be rotated for legacy drivers
• Size for peak + 30% headroom — Cloud SQL scales storage automatically but compute requires a patch operation with brief restart
• Use read replicas for read-heavy workloads — replicas are not a substitute for connection pooling; address both separately
• Enable Query Insights — critical for diagnosing slow queries; off by default on older instances
• Private IP cannot be added post-creation — decide at instance creation time; recreation is required to switch

</guardrails> <validation>

Validation Checkpoint

Before delivering configurations, verify:

• [ ] Instance uses --availability-type=REGIONAL for production HA
• [ ] Private IP is configured (--no-assign-ip --network=) or Auth Proxy is in place
• [ ] Automated backups and PITR are enabled with appropriate retention
• [ ] Maintenance window is set to off-peak hours
• [ ] SSL is enforced (--require-ssl) if public IP exists
• [ ] Passwords are stored in Secret Manager, not hardcoded
• [ ] Storage auto-increase is enabled (--storage-auto-increase)

</validation> <example>

Example

Create a PostgreSQL 15 instance with HA, configure Auth Proxy, and connect a Python application:

# 1. Create instance
gcloud sql instances create app-postgres \
    --database-version=POSTGRES_15 \
    --tier=db-n1-standard-2 \
    --region=us-central1 \
    --availability-type=REGIONAL \
    --storage-type=SSD \
    --storage-size=50GB \
    --storage-auto-increase \
    --no-assign-ip \
    --network=projects/my-project/global/networks/my-vpc \
    --backup-start-time=02:00 \
    --retained-backups-count=14 \
    --enable-bin-log \
    --retained-transaction-log-days=7 \
    --maintenance-window-day=SAT \
    --maintenance-window-hour=3 \
    --database-flags=cloudsql.iam_authentication=on

# 2. Create database and user
gcloud sql databases create myapp --instance=app-postgres
gcloud sql users create myapp-user \
    --instance=app-postgres \
    --password="$(gcloud secrets versions access latest --secret=db-password)"

# 3. Grant IAM access for a service account
gcloud sql users create [email protected] \
    --instance=app-postgres \
    --type=CLOUD_IAM_SERVICE_ACCOUNT

# 4. Start Auth Proxy (local development)
cloud-sql-proxy my-project:us-central1:app-postgres --port=5432 &

Python connection string using the Auth Proxy (local) or Unix socket (Cloud Run):

# Via Auth Proxy (local dev / GCE)
DATABASE_URL = "postgresql+asyncpg://myapp-user:[email protected]:5432/myapp"

# Via Unix socket (Cloud Run — set INSTANCE_CONNECTION_NAME env var)
import os
INSTANCE = os.environ["INSTANCE_CONNECTION_NAME"]  # project:region:instance
DATABASE_URL = f"postgresql+asyncpg://myapp-user:password@/myapp?host=/cloudsql/{INSTANCE}"

</example>

---

References Index

For detailed guides and code examples, refer to the following documents in references/:

• Connection Patterns
  • GKE sidecar, Cloud Run, Compute Engine, local development, PSC, connection strings, pooling.
• Engine-Specific Tuning
  • PostgreSQL flags and extensions, MySQL InnoDB tuning, SQL Server settings, migration paths.

---

Cross-References

• Gemini CLI extensions: gemini extensions install https://github.com/gemini-cli-extensions/cloud-sql-postgresql (also cloud-sql-mysql, cloud-sql-sqlserver)
• Higher performance PostgreSQL: see flow:alloydb
• GKE deployment patterns: see flow:gke → Cloud SQL on GKE section

---

Official References

• https://cloud.google.com/sql/docs
• https://cloud.google.com/sql/docs/postgres/connect-auth-proxy
• https://cloud.google.com/sql/docs/postgres/instance-settings
• https://cloud.google.com/sql/pricing

Shared Styleguide Baseline

• Use shared styleguides for generic language/framework rules to reduce duplication in this skill.
• General Principles
• GCP Scripting
• Keep this skill focused on tool-specific workflows, edge cases, and integration details.