# Create an HA PostgreSQL Instance
gcloud sql instances create prod-db \
    --database-version=POSTGRES_14 \
    --tier=db-custom-4-15360 \       # Why custom: 4 vCPUs, 15GB RAM -- tailored to our workload, avoids paying for unused RAM
    --region=us-central1 \
    --availability-type=REGIONAL \    # Why REGIONAL: Enables cross-zone HA with automatic failover (~60s)
    --storage-type=SSD \              # Why SSD: 30x IOPS vs HDD; use HDD only for dev/test to save ~40%
    --storage-size=100GB \
    --storage-auto-increase \         # Why: Prevents out-of-disk crashes. Storage only grows, never shrinks.
    --backup-start-time=03:00         # Why 03:00: Schedule during lowest-traffic window to minimize performance impact

# Why Auth Proxy: It creates an encrypted tunnel authenticated via IAM -- no need to
# manage SSL certs, IP allowlists, or database passwords in config files.
# AWS equivalent: RDS Proxy (but that is a separate managed service with its own pricing).
# The Cloud SQL Auth Proxy is free -- you only pay for the Cloud SQL instance itself.
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
chmod +x cloud_sql_proxy

# The proxy listens on localhost:5432 and forwards traffic over an encrypted channel
./cloud_sql_proxy -instances=PROJECT_ID:REGION:INSTANCE_NAME=tcp:5432

# Your app connects to localhost as if the database were local -- zero code changes needed
psql "host=127.0.0.1 port=5432 dbname=mydb user=postgres"

# Why sidecar pattern: The proxy runs alongside your app in the same pod, so your app
# connects to localhost. This eliminates network hops and simplifies firewall rules.
# The proxy handles IAM auth + TLS automatically. No database password rotation needed.
# Common Mistake: Forgetting to set resource requests/limits on the proxy container.
# The proxy uses ~50MB RAM and minimal CPU, but without limits, Kubernetes cannot bin-pack efficiently.
apiVersion: apps/v1
kind: Deployment
metadata:
  name: my-app
spec:
  template:
    spec:
      containers:
      - name: app
        image: gcr.io/project/app:latest
        env:
        - name: DB_HOST
          value: "127.0.0.1"   # App talks to the sidecar on localhost
        - name: DB_PORT
          value: "5432"
      - name: cloud-sql-proxy
        image: gcr.io/cloudsql-docker/gce-proxy:latest
        command:
        - "/cloud_sql_proxy"
        - "-instances=PROJECT_ID:REGION:INSTANCE_NAME=tcp:5432"
        securityContext:
          runAsNonRoot: true    # Security best practice: never run as root

# Why /16 prefix: Cloud SQL instances are created in a Google-managed VPC and connected
# to yours via peering. A /16 gives 65K IPs -- generous, but Cloud SQL creates hidden
# resources (HA replicas, proxy VMs) that consume IPs. A /20 works for small deployments
# but can run out if you have many instances. /16 is the safe default.
# Common Mistake: Using a range that overlaps with your existing subnets. This silently
# breaks peering and causes "connection refused" errors that are painful to debug.
gcloud compute addresses create google-managed-services-default \
    --global \
    --purpose=VPC_PEERING \
    --prefix-length=16 \
    --network=default

# This creates the VPC peering between your network and Google's service networking
gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=google-managed-services-default \
    --network=default

# Configure backup settings
gcloud sql instances patch prod-db \
    --backup-start-time=03:00 \
    --retained-backups-count=30 \
    --retained-transaction-log-days=7

# Create manual backup
gcloud sql backups create \
    --instance=prod-db \
    --description="Pre-migration backup"

# Restore from a specific backup
gcloud sql backups restore BACKUP_ID \
    --instance=prod-db

# Point-in-time restore to 1 hour ago
gcloud sql instances clone prod-db prod-db-restore \
    --point-in-time='2024-01-24T15:00:00.000Z'

# Why same-region replica: Offloads read traffic from the primary, reducing latency for
# read-heavy workloads. Replication lag is typically <1ms within the same region.
# Cost: A replica costs the same as a standalone instance of the same tier.
gcloud sql instances create prod-db-replica-1 \
    --master-instance-name=prod-db \
    --region=us-central1 \
    --tier=db-custom-2-7680       # Smaller tier is fine if replica handles lighter read load

# Why cross-region replica: Serves two purposes -- (1) low-latency reads for European users,
# (2) disaster recovery if the US region goes down. You can promote this to a standalone
# primary if needed, but promotion is a one-way operation (it breaks replication).
# Common Mistake: Making the DR replica too small. During a failover, it becomes the primary
# and must handle ALL traffic. Size it to match (or exceed) the primary tier.
gcloud sql instances create prod-db-replica-dr \
    --master-instance-name=prod-db \
    --region=europe-west1 \
    --tier=db-custom-4-15360      # Match primary tier for DR readiness

# Python example using SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Primary (read-write)
primary_engine = create_engine('postgresql://user:pass@primary-ip/db')

# Replica (read-only)
replica_engine = create_engine('postgresql://user:pass@replica-ip/db')

# Use primary for writes
def create_order(order_data):
    session = sessionmaker(bind=primary_engine)()
    # ... insert order
    session.commit()

# Use replica for reads
def get_order_list():
    session = sessionmaker(bind=replica_engine)()
    return session.query(Order).all()

# Create health check
gcloud compute health-checks create tcp read-replica-health \
    --port=5432

# Create backend service with all replicas
gcloud compute backend-services create read-replica-backend \
    --protocol=TCP \
    --health-checks=read-replica-health \
    --region=us-central1

# Add replicas as backends
gcloud compute backend-services add-backend read-replica-backend \
    --instance=prod-db-replica-1 \
    --instance-zone=us-central1-a \
    --region=us-central1

# Why Sunday 4 AM: Choose the lowest-traffic window for your timezone. Maintenance can
# cause a brief restart (60-90 seconds with HA). If you skip setting a window, Google
# picks one for you -- and it might be during your peak hours.
# Common Mistake: Setting the window to "never" (you cannot). All Cloud SQL instances
# receive mandatory patches. Plan for it rather than being surprised.
gcloud sql instances patch prod-db \
    --maintenance-window-day=SUN \
    --maintenance-window-hour=04 \
    --maintenance-release-channel=production   # "production" = stable, less frequent updates

# Enable Query Insights
gcloud sql instances patch prod-db \
    --insights-config-query-insights-enabled \
    --insights-config-query-string-length=1024 \
    --insights-config-record-application-tags \
    --insights-config-record-client-address

from google.cloud.sql.connector import Connector

# Why the Cloud SQL Python Connector: It handles IAM authentication and TLS automatically,
# eliminating the need to run the Auth Proxy as a separate process.
# This is the recommended approach for Python apps since 2023.
connector = Connector()

def getconn():
    conn = connector.connect(
        "PROJECT:REGION:INSTANCE",  # Connection name from Cloud Console
        "pg8000",                    # PostgreSQL driver
        user="postgres",
        password="password",         # In production: fetch from Secret Manager, never hardcode
        db="mydb"
    )
    return conn

# Why pool_size=10: Each connection holds a PostgreSQL backend process (~10MB RAM).
# Cloud SQL limits connections based on your instance tier (e.g., 100 for db-custom-4).
# Setting pool_size too high wastes DB resources; too low causes connection wait times.
# Why pool_pre_ping=True: Validates connections before use, preventing "connection reset" errors
# after idle periods or Cloud SQL maintenance events.
engine = create_engine(
    "postgresql+pg8000://",
    creator=getconn,
    pool_size=10,        # Active connections to maintain
    max_overflow=5,      # Extra connections allowed during traffic spikes (total max: 15)
    pool_pre_ping=True   # Detect and replace stale connections automatically
)

gcloud sql instances patch prod-db \
    --storage-auto-increase \
    --storage-auto-increase-limit=500GB

# Why AlloyDB over Cloud SQL: If your PostgreSQL workload needs both fast transactions
# AND analytical queries (dashboards, reporting) on the same data, AlloyDB eliminates
# the need for a separate data warehouse. The columnar engine runs analytics 100x faster.
# Cost: AlloyDB starts at ~$0.50/vCPU-hour (~$2,900/month for 8 vCPUs), roughly 2x Cloud SQL.
# The premium pays for itself if it eliminates a separate BigQuery ETL pipeline.
gcloud alloydb clusters create prod-alloydb-cluster \
    --region=us-central1 \
    --password=secure-password    # Store this in Secret Manager, not in scripts

# Why 4096 MB for columnar engine: This determines how much analytical data is kept in-memory.
# More memory = more data cached in columnar format = faster analytical queries.
# Start with 25-50% of your instance RAM and tune based on query performance.
gcloud alloydb instances create prod-alloydb-primary \
    --cluster=prod-alloydb-cluster \
    --region=us-central1 \
    --instance-type=PRIMARY \
    --cpu-count=8 \
    --database-flags=google_columnar_engine.enabled=on,google_columnar_engine.memory_size_mb=4096

# Why read pools instead of read replicas: AlloyDB read pool nodes share the same storage
# layer as the primary, so there is zero replication lag (unlike Cloud SQL replicas which
# use async replication). All 3 nodes see the exact same data at all times.
# Common Mistake: Over-provisioning CPU on read pool nodes. Start with fewer vCPUs and let
# monitoring guide your scaling decisions.
gcloud alloydb instances create prod-alloydb-read-pool \
    --cluster=prod-alloydb-cluster \
    --region=us-central1 \
    --instance-type=READ_POOL \
    --cpu-count=4 \
    --read-pool-node-count=3       # 3 nodes for HA; AlloyDB auto-distributes reads across them

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  Name STRING(MAX),
  Email STRING(MAX)
) PRIMARY KEY (CustomerId);

CREATE TABLE Orders (
  CustomerId INT64 NOT NULL,
  OrderId INT64 NOT NULL,
  OrderDate DATE,
  TotalAmount NUMERIC
) PRIMARY KEY (CustomerId, OrderId),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;

-- Sequential IDs create hotspot on last split
CREATE TABLE Events (
  EventId INT64 NOT NULL,  -- Auto-increment
  ...
) PRIMARY KEY (EventId);

-- UUID spreads writes across splits
CREATE TABLE Events (
  EventId STRING(36) NOT NULL,  -- UUID
  ...
) PRIMARY KEY (EventId);

-- Or hash-based key
CREATE TABLE Events (
  ShardId INT64 NOT NULL,  -- Hash(userId) % 1000
  EventId INT64 NOT NULL,
  ...
) PRIMARY KEY (ShardId, EventId);

# DON'T: Single transaction with 1M rows
with database.batch() as batch:
    for i in range(1_000_000):
        batch.insert("Orders", columns, values)
# ERROR: COMMIT_FAILS_TOO_MANY_MUTATIONS

# DO: Batch in chunks of 10,000 mutations
def batch_insert(rows, batch_size=5000):
    for i in range(0, len(rows), batch_size):
        with database.batch() as batch:
            chunk = rows[i:i+batch_size]
            for row in chunk:
                batch.insert("Orders", columns, row)

# Use Dataflow for massive ingests
import apache_beam as beam
from apache_beam.io.gcp.experimental import SpannerIO

with beam.Pipeline() as pipeline:
    (pipeline
     | 'Read CSV' >> beam.io.ReadFromText('gs://bucket/data.csv')
     | 'Parse' >> beam.Map(parse_csv_row)
     | 'Write to Spanner' >> SpannerIO.Write(
         instance_id='my-instance',
         database_id='my-database',
         table='Orders',
         batch_size_bytes=10_000_000))

-- Create secondary index for common queries
CREATE INDEX OrdersByDate ON Orders(OrderDate);

-- Force index usage if needed
SELECT * FROM Orders@{FORCE_INDEX=OrdersByDate}
WHERE OrderDate = '2024-01-24';

-- BAD: Full table scan
SELECT * FROM Orders WHERE LOWER(CustomerName) = 'acme';

-- GOOD: Use indexed column
SELECT * FROM Orders WHERE CustomerId = 12345;

# 1. Create migration job
gcloud database-migration migration-jobs create my-migration \
    --source=mysql-on-prem \
    --destination=cloudsql \
    --type=CONTINUOUS \
    --region=us-central1

# 2. Start migration
gcloud database-migration migration-jobs start my-migration \
    --region=us-central1

# 3. Monitor progress
gcloud database-migration migration-jobs describe my-migration \
    --region=us-central1

# 4. Promote (cutover to Cloud SQL)
gcloud database-migration migration-jobs promote my-migration \
    --region=us-central1

# Example: Create Cloud Monitoring alert
gcloud alpha monitoring policies create \
    --notification-channels=CHANNEL_ID \
    --display-name="Cloud SQL High CPU" \
    --condition-display-name="CPU > 80%" \
    --condition-threshold-value=0.8 \
    --condition-threshold-duration=300s

# Create VPC
gcloud compute networks create prod-vpc --subnet-mode=custom

# Create subnet
gcloud compute networks subnets create db-subnet \
    --network=prod-vpc \
    --region=us-central1 \
    --range=10.0.1.0/24

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

# Create private service connection
gcloud services vpc-peerings connect \
    --service=servicenetworking.googleapis.com \
    --ranges=google-managed-services \
    --network=prod-vpc

# Create HA PostgreSQL instance with private IP
gcloud sql instances create prod-db \
    --database-version=POSTGRES_14 \
    --tier=db-custom-4-15360 \
    --region=us-central1 \
    --network=prod-vpc \
    --no-assign-ip \
    --availability-type=REGIONAL \
    --storage-type=SSD \
    --storage-size=100GB \
    --storage-auto-increase \
    --storage-auto-increase-limit=500GB \
    --enable-bin-log \
    --backup-start-time=03:00 \
    --retained-backups-count=30 \
    --retained-transaction-log-days=7 \
    --maintenance-window-day=SUN \
    --maintenance-window-hour=04 \
    --maintenance-release-channel=production \
    --insights-config-query-insights-enabled \
    --insights-config-query-string-length=1024

# Create cross-region read replica for DR
gcloud sql instances create prod-db-replica \
    --master-instance-name=prod-db \
    --region=us-east1 \
    --tier=db-custom-2-7680 \
    --availability-type=ZONAL

# Create database and user
gcloud sql databases create appdb --instance=prod-db
gcloud sql users create appuser --instance=prod-db --password=SECURE_PASSWORD

# Set up IAM authentication (recommended)
gcloud sql users create app-service-account@PROJECT_ID.iam \
    --instance=prod-db \
    --type=CLOUD_IAM_SERVICE_ACCOUNT

# Enable Cloud Monitoring
gcloud services enable monitoring.googleapis.com

# Create notification channel (email)
gcloud alpha monitoring channels create \
    --display-name="DBA Team" \
    --type=email \
    --channel-labels=email_address=dba@company.com

# Create alert for high CPU
gcloud alpha monitoring policies create \
    --notification-channels=CHANNEL_ID \
    --display-name="Cloud SQL High CPU" \
    --condition-display-name="CPU > 80%" \
    --condition-threshold-value=0.8 \
    --condition-threshold-duration=300s

# Install Cloud SQL Proxy
curl -o cloud_sql_proxy https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64
chmod +x cloud_sql_proxy

# Run proxy
./cloud_sql_proxy -instances=PROJECT_ID:us-central1:prod-db=tcp:5432 &

# Connect and test
psql "host=127.0.0.1 port=5432 dbname=appdb user=appuser"

# Trigger manual backup
gcloud sql backups create --instance=prod-db

# List backups
gcloud sql backups list --instance=prod-db

# Simulate failover (triggers automatic failover to standby)
gcloud sql instances failover prod-db