GCP Cloud SQL PostgreSQL Deployment Guide

Project: CODITECT Cloud Backend Environment: Development (Phase 2.1 - Week 1) GCP Project: serene-voltage-464305-n2 Region: us-central1 Last Updated: 2025-11-17

Overview

This guide deploys PostgreSQL to Google Cloud SQL for the CODITECT platform, aligned with existing infrastructure in serene-voltage-464305-n2 (production GKE cluster for Coditect V5 IDE).

Deployment Strategy:

Development-first approach: Cost-optimized instance for Week 1 pilot
Same project as GKE: Leverages existing VPC, networking, and monitoring
Private IP preferred: Secure connection via VPC peering
Automated backups: Daily snapshots with 7-day retention
Multi-tenant ready: RLS policies for future tenant isolation

Prerequisites

1. GCP Authentication

# Verify authentication
gcloud auth list

# Should show: 1@az1.ai (ACTIVE)
# Current project: serene-voltage-464305-n2
gcloud config get-value project

# If not set
gcloud config set project serene-voltage-464305-n2

2. Enable Required APIs

# Enable Cloud SQL Admin API
gcloud services enable sqladmin.googleapis.com

# Enable Cloud Resource Manager API (if not enabled)
gcloud services enable cloudresourcemanager.googleapis.com

# Enable Compute Engine API (for VPC peering)
gcloud services enable compute.googleapis.com

# Enable Service Networking API (for private IP)
gcloud services enable servicenetworking.googleapis.com

# Verify APIs enabled
gcloud services list --enabled --filter="name:(sqladmin OR servicenetworking)"

Deployment Steps

Step 1: Create Cloud SQL Instance (Development Tier)

# Create Cloud SQL PostgreSQL instance (db-f1-micro for dev)
gcloud sql instances create coditect-dev-db \
  --database-version=POSTGRES_15 \
  --tier=db-f1-micro \
  --region=us-central1 \
  --network=default \
  --no-assign-ip \
  --root-password="$(openssl rand -base64 32)" \
  --database-flags=cloudsql.iam_authentication=on \
  --backup-start-time=03:00 \
  --backup-location=us \
  --maintenance-window-day=SUN \
  --maintenance-window-hour=4 \
  --maintenance-release-channel=production \
  --enable-bin-log \
  --retained-backups-count=7 \
  --retained-transaction-log-days=7 \
  --project=serene-voltage-464305-n2

# Note: Root password generated securely - store in Secret Manager (see Step 3)

Instance Specifications:

Instance Name: coditect-dev-db
PostgreSQL Version: 15 (latest stable)
Machine Type: db-f1-micro (0.6 GB RAM, shared CPU)
Storage: 10 GB SSD (auto-resize enabled by default)
High Availability: Disabled (single zone for dev)
Region: us-central1 (same as GKE cluster)
Private IP: Yes (no public IP)
Backups: Daily at 3 AM UTC, 7-day retention
Maintenance Window: Sunday 4 AM UTC

Cost Estimate:

Instance: ~$7.60/month (db-f1-micro)
Storage: ~$1.70/month (10 GB SSD)
Backups: ~$1.00/month (7 days)
Total: ~$10.30/month

Step 2: Configure Private IP Access

# Allocate IP range for VPC peering (if not already done)
gcloud compute addresses create google-managed-services-default \
  --global \
  --purpose=VPC_PEERING \
  --prefix-length=16 \
  --network=default \
  --project=serene-voltage-464305-n2

# Create private service connection
gcloud services vpc-peerings connect \
  --service=servicenetworking.googleapis.com \
  --ranges=google-managed-services-default \
  --network=default \
  --project=serene-voltage-464305-n2

# Verify private IP assigned
gcloud sql instances describe coditect-dev-db \
  --format="value(ipAddresses[0].ipAddress)" \
  --project=serene-voltage-464305-n2

Verification:

# Check instance status
gcloud sql instances list --project=serene-voltage-464305-n2

# Expected output:
# NAME             DATABASE_VERSION  LOCATION       TIER         PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
# coditect-dev-db  POSTGRES_15       us-central1-a  db-f1-micro  -                10.x.x.x         RUNNABLE

Step 3: Store Credentials in Secret Manager

# Enable Secret Manager API
gcloud services enable secretmanager.googleapis.com --project=serene-voltage-464305-n2

# Get root password (from instance creation - replace with actual password)
# IMPORTANT: Store the password generated in Step 1
ROOT_PASSWORD="<password-from-step-1>"

# Store root password in Secret Manager
echo -n "$ROOT_PASSWORD" | gcloud secrets create coditect-db-root-password \
  --data-file=- \
  --replication-policy=automatic \
  --project=serene-voltage-464305-n2

# Create application user password
APP_PASSWORD="$(openssl rand -base64 32)"
echo -n "$APP_PASSWORD" | gcloud secrets create coditect-db-app-password \
  --data-file=- \
  --replication-policy=automatic \
  --project=serene-voltage-464305-n2

# Verify secrets created
gcloud secrets list --project=serene-voltage-464305-n2 --filter="name~coditect-db"

Step 4: Create Database and Application User

# Connect to Cloud SQL instance
gcloud sql connect coditect-dev-db --user=postgres --project=serene-voltage-464305-n2

# You'll be prompted for the root password (from Secret Manager)
# Retrieve it first:
gcloud secrets versions access latest --secret="coditect-db-root-password" --project=serene-voltage-464305-n2

Inside PostgreSQL shell:

-- Create application database
CREATE DATABASE coditect_dev;

-- Create application user
CREATE USER coditect_app WITH PASSWORD '<app-password-from-secret-manager>';

-- Grant privileges
GRANT CONNECT ON DATABASE coditect_dev TO coditect_app;

-- Connect to database
\c coditect_dev

-- Grant schema privileges
GRANT USAGE, CREATE ON SCHEMA public TO coditect_app;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO coditect_app;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO coditect_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO coditect_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO coditect_app;

-- Verify user created
\du

-- Exit
\q

Step 5: Apply Database Schema

# Upload schema file to Cloud Shell or local machine with gcloud
# Assuming you're in coditect-cloud-backend directory

# Apply schema (migration includes idempotent DDL)
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2 < database/migrations/001_initial_schema.sql

# Apply RLS policies
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2 < database/rls_policies.sql

# Verify schema applied
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2

-- Inside psql:
\dn  -- List schemas (should show coditect_shared)
\dt coditect_shared.*  -- List tables
\q

Step 6: Test Connection from GKE

# Create Kubernetes secret with connection details
kubectl create secret generic coditect-db-credentials \
  --from-literal=host=$(gcloud sql instances describe coditect-dev-db --format="value(ipAddresses[0].ipAddress)" --project=serene-voltage-464305-n2) \
  --from-literal=port=5432 \
  --from-literal=database=coditect_dev \
  --from-literal=username=coditect_app \
  --from-literal=password=$(gcloud secrets versions access latest --secret="coditect-db-app-password" --project=serene-voltage-464305-n2) \
  --namespace=coditect-app \
  --dry-run=client -o yaml | kubectl apply -f -

# Test connection with temporary pod
kubectl run psql-test --rm -it --image=postgres:15 --namespace=coditect-app -- \
  psql "postgresql://coditect_app:$(gcloud secrets versions access latest --secret="coditect-db-app-password" --project=serene-voltage-464305-n2)@$(gcloud sql instances describe coditect-dev-db --format="value(ipAddresses[0].ipAddress)" --project=serene-voltage-464305-n2):5432/coditect_dev?sslmode=require"

# Inside psql:
SELECT version();
\dt coditect_shared.*
\q

Step 7: Configure Cloud SQL Proxy (Optional)

For local development:

# Install Cloud SQL Proxy
# macOS
brew install --cask google-cloud-sdk
gcloud components install cloud-sql-proxy

# Linux
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
chmod +x cloud_sql_proxy

# Get connection name
CONNECTION_NAME=$(gcloud sql instances describe coditect-dev-db --format="value(connectionName)" --project=serene-voltage-464305-n2)

# Start proxy (in separate terminal)
./cloud_sql_proxy -instances=${CONNECTION_NAME}=tcp:5432

# Connect from local machine
psql "postgresql://coditect_app:<app-password>@localhost:5432/coditect_dev?sslmode=disable"

Connection Configuration

Environment Variables

# For FastAPI application
export DATABASE_URL="postgresql://coditect_app:<password>@<private-ip>:5432/coditect_dev?sslmode=require"
export DB_HOST="<private-ip>"
export DB_PORT="5432"
export DB_NAME="coditect_dev"
export DB_USER="coditect_app"
export DB_PASSWORD="<password-from-secret-manager>"

Kubernetes ConfigMap

apiVersion: v1
kind: ConfigMap
metadata:
  name: coditect-db-config
  namespace: coditect-app
data:
  DB_HOST: "<private-ip>"
  DB_PORT: "5432"
  DB_NAME: "coditect_dev"
  DB_SSLMODE: "require"

Kubernetes Secret (Already Created in Step 6)

apiVersion: v1
kind: Secret
metadata:
  name: coditect-db-credentials
  namespace: coditect-app
type: Opaque
data:
  DB_USER: <base64-encoded>
  DB_PASSWORD: <base64-encoded>

FastAPI Database Configuration

# database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os

DATABASE_URL = os.getenv(
    "DATABASE_URL",
    f"postgresql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_NAME')}?sslmode={os.getenv('DB_SSLMODE', 'require')}"
)

engine = create_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
    pool_pre_ping=True,
    pool_recycle=3600,
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

Verification & Testing

1. Instance Health Check

# Check instance status
gcloud sql instances describe coditect-dev-db --project=serene-voltage-464305-n2

# Check operations
gcloud sql operations list --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Check backups
gcloud sql backups list --instance=coditect-dev-db --project=serene-voltage-464305-n2

2. Database Connectivity Test

# From GKE pod
kubectl run psql-test --rm -it --image=postgres:15 --namespace=coditect-app -- \
  psql "postgresql://coditect_app:<password>@<private-ip>:5432/coditect_dev?sslmode=require"

-- Inside psql:
-- Test basic queries
SELECT NOW();
SELECT * FROM coditect_shared.organizations LIMIT 1;

-- Verify RLS enabled
SELECT tablename, rowsecurity FROM pg_tables WHERE schemaname = 'coditect_shared';

3. Performance Test

# Test connection latency
kubectl run psql-test --rm -it --image=postgres:15 --namespace=coditect-app -- bash -c "
time psql 'postgresql://coditect_app:<password>@<private-ip>:5432/coditect_dev?sslmode=require' -c 'SELECT 1'
"

# Expected: <100ms (private network)

4. Security Validation

# Verify private IP only
gcloud sql instances describe coditect-dev-db \
  --format="value(ipAddresses[].type)" \
  --project=serene-voltage-464305-n2
# Expected output: PRIVATE

# Verify SSL required
gcloud sql instances describe coditect-dev-db \
  --format="value(settings.ipConfiguration.requireSsl)" \
  --project=serene-voltage-464305-n2
# Expected output: True

# Verify IAM authentication enabled
gcloud sql instances describe coditect-dev-db \
  --format="value(settings.databaseFlags)" \
  --project=serene-voltage-464305-n2

Monitoring & Alerting

Cloud Monitoring Dashboard

# View metrics in Cloud Console
gcloud sql instances describe coditect-dev-db --project=serene-voltage-464305-n2

# Key metrics to monitor:
# - CPU utilization
# - Memory utilization
# - Connections count
# - Read/write operations
# - Storage usage

Alerting Policies

# 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 \
  --condition-filter='resource.type="cloudsql_database" AND metric.type="cloudsql.googleapis.com/database/cpu/utilization"' \
  --project=serene-voltage-464305-n2

# Create alert for connection count
gcloud alpha monitoring policies create \
  --notification-channels=<channel-id> \
  --display-name="Cloud SQL High Connections" \
  --condition-display-name="Connections > 80" \
  --condition-threshold-value=80 \
  --condition-threshold-duration=300s \
  --condition-filter='resource.type="cloudsql_database" AND metric.type="cloudsql.googleapis.com/database/network/connections"' \
  --project=serene-voltage-464305-n2

Maintenance & Operations

Backup Management

# List backups
gcloud sql backups list --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Create on-demand backup
gcloud sql backups create --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Restore from backup
gcloud sql backups restore <backup-id> \
  --backup-instance=coditect-dev-db \
  --backup-project=serene-voltage-464305-n2

Scaling Up (Future)

# Upgrade to db-g1-small (production-ready)
gcloud sql instances patch coditect-dev-db \
  --tier=db-g1-small \
  --project=serene-voltage-464305-n2

# Enable high availability (after testing)
gcloud sql instances patch coditect-dev-db \
  --availability-type=REGIONAL \
  --project=serene-voltage-464305-n2

Database Migrations

# Apply new migrations
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2 < database/migrations/002_add_feature.sql

# Or use migration tool (Alembic)
# In FastAPI application:
alembic upgrade head

Troubleshooting

Connection Refused

# Check instance status
gcloud sql instances describe coditect-dev-db --project=serene-voltage-464305-n2

# Check VPC peering
gcloud services vpc-peerings list --network=default --project=serene-voltage-464305-n2

# Check firewall rules (should allow GKE to Cloud SQL)
gcloud compute firewall-rules list --project=serene-voltage-464305-n2

# Test from GKE pod
kubectl run curl-test --rm -it --image=curlimages/curl --namespace=coditect-app -- sh
# Inside pod:
nc -zv <private-ip> 5432

Authentication Failures

# Verify user exists
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2

-- Inside psql:
\du coditect_app

-- Check user permissions
\l+  -- List databases
\dn+  -- List schemas

Performance Issues

# Check slow queries
gcloud sql operations list --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Connect and analyze
gcloud sql connect coditect-dev-db --user=postgres --database=coditect_dev --project=serene-voltage-464305-n2

-- Inside psql:
-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View slow queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Cost Optimization

Current Configuration (Dev)

Resource	Specification	Monthly Cost
Instance	db-f1-micro (0.6 GB RAM)	$7.60
Storage	10 GB SSD	$1.70
Backups	7 days retention	$1.00
Total		~$10.30

Production Configuration (Future)

Resource	Specification	Monthly Cost
Instance	db-g1-small (1.7 GB RAM) + HA	$50.50
Storage	50 GB SSD	$8.50
Backups	30 days retention	$4.30
Total		~$63.30

Cost Savings Tips:

Use db-f1-micro for dev/staging
Enable auto-storage increase (prevent manual intervention)
Delete old backups (automated with retention policy)
Use private IP (no egress charges)
Monitor query performance (avoid inefficient queries)

Rollback Procedures

Delete Cloud SQL Instance

# ⚠️ WARNING: This deletes all data permanently

# Create final backup first
gcloud sql backups create --instance=coditect-dev-db --project=serene-voltage-464305-n2

# Delete instance
gcloud sql instances delete coditect-dev-db --project=serene-voltage-464305-n2

# Clean up secrets
gcloud secrets delete coditect-db-root-password --project=serene-voltage-464305-n2
gcloud secrets delete coditect-db-app-password --project=serene-voltage-464305-n2

# Clean up Kubernetes resources
kubectl delete secret coditect-db-credentials --namespace=coditect-app
kubectl delete configmap coditect-db-config --namespace=coditect-app

Next Steps

Week 1 Day 4-5: FastAPI Backend Integration
- Implement SQLAlchemy models
- Create database repositories
- Test CRUD operations
- Implement connection pooling
Week 2: API Endpoints Development
- User registration/authentication
- Project management endpoints
- License validation endpoints
- Organization management
Week 3: Security Hardening
- Implement RLS policy testing
- Add rate limiting
- Configure audit logging
- Penetration testing
Week 4: Production Migration Plan
- Upgrade to db-g1-small
- Enable high availability
- Setup read replicas
- Configure automated failover

Reference Documentation

Internal:

Schema: /database/schema.sql
RLS Policies: /database/rls_policies.sql
Migration: /database/migrations/001_initial_schema.sql
API Spec: /api/openapi_spec.yaml

External:

Deployment Date: 2025-11-17 Deployed By: DevOps Engineer (AI-assisted) Environment: Development Status: ✅ Ready for Deployment Next Checkpoint: Phase 2.2 - FastAPI Backend Integration

Overview​

Prerequisites​

1. GCP Authentication​

2. Enable Required APIs​

Deployment Steps​

Step 1: Create Cloud SQL Instance (Development Tier)​

Step 2: Configure Private IP Access​

Step 3: Store Credentials in Secret Manager​

Step 4: Create Database and Application User​

Step 5: Apply Database Schema​

Step 6: Test Connection from GKE​

Step 7: Configure Cloud SQL Proxy (Optional)​

Connection Configuration​

Environment Variables​

Kubernetes ConfigMap​

Kubernetes Secret (Already Created in Step 6)​

FastAPI Database Configuration​

Verification & Testing​

1. Instance Health Check​

2. Database Connectivity Test​

3. Performance Test​

4. Security Validation​

Monitoring & Alerting​

Cloud Monitoring Dashboard​

Alerting Policies​

Maintenance & Operations​

Backup Management​

Scaling Up (Future)​

Database Migrations​

Troubleshooting​

Connection Refused​

Authentication Failures​

Performance Issues​

Cost Optimization​

Current Configuration (Dev)​

Production Configuration (Future)​

Rollback Procedures​

Delete Cloud SQL Instance​

Next Steps​

Reference Documentation​