Cloud SQL PostgreSQL Setup

This guide provides detailed instructions for setting up Google Cloud SQL PostgreSQL for the HRMS application.

Overview

Setting	Value
Database	PostgreSQL 17
Region	europe-west6 (Belgium)
Tier	db-f1-micro (staging) / db-custom-2-4096 (production)
Storage	10GB SSD (auto-increase enabled)

Prerequisites

GCP project with billing enabled
gcloud CLI installed and authenticated
APIs enabled: sqladmin.googleapis.com

Step 1: Create Cloud SQL Instance

Staging Instance

export PROJECT_ID="bluewoo-hrms"
export REGION="europe-west6"

gcloud sql instances create hrms-db-staging \
  --database-version=POSTGRES_17 \
  --tier=db-f1-micro \
  --region=$REGION \
  --storage-size=10GB \
  --storage-auto-increase \
  --backup-start-time=03:00 \
  --availability-type=zonal \
  --no-assign-ip

Production Instance

gcloud sql instances create hrms-db-prod \
  --database-version=POSTGRES_17 \
  --tier=db-custom-2-4096 \
  --region=$REGION \
  --storage-size=20GB \
  --storage-auto-increase \
  --backup-start-time=03:00 \
  --maintenance-window-day=SUN \
  --maintenance-window-hour=04 \
  --availability-type=zonal \
  --deletion-protection

Note: For HA (High Availability), change --availability-type=zonal to --availability-type=regional. This doubles the cost but provides automatic failover.

Step 2: Set Root Password

# Generate strong password
openssl rand -base64 24

# Set password
gcloud sql users set-password postgres \
  --instance=hrms-db-staging \
  --password=<GENERATED_PASSWORD>

Step 3: Create Database and User

# Create database
gcloud sql databases create hrms_staging --instance=hrms-db-staging
gcloud sql databases create hrms_prod --instance=hrms-db-prod

# Create application user
gcloud sql users create hrms_app \
  --instance=hrms-db-staging \
  --password=<APP_PASSWORD>

gcloud sql users create hrms_app \
  --instance=hrms-db-prod \
  --password=<APP_PASSWORD>

Step 4: Get Connection Information

# Get connection name (needed for Cloud Run)
gcloud sql instances describe hrms-db-staging \
  --format="value(connectionName)"
# Output: bluewoo-hrms:europe-west6:hrms-db-staging

# Get connection name for production
gcloud sql instances describe hrms-db-prod \
  --format="value(connectionName)"
# Output: bluewoo-hrms:europe-west6:hrms-db-prod

Step 5: Configure Cloud Run Connection

Cloud Run connects to Cloud SQL via the Cloud SQL Auth Proxy, which is built-in.

Connection String Format

For Cloud Run with Cloud SQL Proxy:

postgresql://USER:PASSWORD@/DATABASE?host=/cloudsql/CONNECTION_NAME

Example:

postgresql://hrms_app:password123@/hrms_prod?host=/cloudsql/bluewoo-hrms:europe-west6:hrms-db-prod

Deploy Cloud Run with Cloud SQL

gcloud run deploy hrms-api \
  --image=... \
  --region=$REGION \
  --add-cloudsql-instances=bluewoo-hrms:europe-west6:hrms-db-prod \
  --set-env-vars="DATABASE_URL=postgresql://hrms_app:xxx@/hrms_prod?host=/cloudsql/bluewoo-hrms:europe-west6:hrms-db-prod"

Step 6: Run Prisma Migrations

Option A: Cloud SQL Proxy (Local)

# Download Cloud SQL Proxy
# macOS
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.0/cloud-sql-proxy.darwin.amd64
chmod +x cloud-sql-proxy

# Start proxy
./cloud-sql-proxy bluewoo-hrms:europe-west6:hrms-db-prod &

# Set DATABASE_URL for local proxy
export DATABASE_URL="postgresql://hrms_app:<PASSWORD>@127.0.0.1:5432/hrms_prod"

# Run migrations
cd packages/database
npx prisma migrate deploy

Option B: Cloud Shell

# In Cloud Shell (cloud.google.com/shell)
gcloud sql connect hrms-db-prod --user=hrms_app --database=hrms_prod

# Or clone repo and run migrations
git clone <your-repo>
cd hrms/packages/database
npm install
DATABASE_URL="..." npx prisma migrate deploy

Configuration Reference

Instance Tiers

Tier	vCPUs	Memory	Use Case	Monthly Cost
db-f1-micro	Shared	0.6 GB	Development/staging	~$10
db-g1-small	Shared	1.7 GB	Small production	~$25
db-custom-1-3840	1	3.75 GB	Medium production	~$50
db-custom-2-4096	2	4 GB	Production	~$75
db-custom-4-8192	4	8 GB	High traffic	~$150

Storage Options

Option	Description
`--storage-size`	Initial size (10GB minimum)
`--storage-auto-increase`	Auto-grow when >90% full
`--storage-type`	SSD (default) or HDD

Backup Options

Option	Description
`--backup-start-time`	Daily backup window (UTC)
`--retained-backups-count`	Number of backups to keep (default: 7)
`--enable-point-in-time-recovery`	Enable PITR

Security Best Practices

1. Use Private IP Only

# Create instance with private IP only
gcloud sql instances create hrms-db \
  --network=default \
  --no-assign-ip

2. Require SSL

gcloud sql instances patch hrms-db \
  --require-ssl

3. Use Secret Manager for Credentials

# Store DATABASE_URL in Secret Manager
echo -n "postgresql://..." | \
  gcloud secrets create hrms-database-url --data-file=-

# Reference in Cloud Run
gcloud run deploy hrms-api \
  --set-secrets=DATABASE_URL=hrms-database-url:latest

4. Limit User Permissions

-- Connect as postgres user, then:
REVOKE ALL ON DATABASE hrms_prod FROM hrms_app;
GRANT CONNECT ON DATABASE hrms_prod TO hrms_app;
GRANT USAGE ON SCHEMA public TO hrms_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO hrms_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO hrms_app;

Monitoring

Enable Logging

gcloud sql instances patch hrms-db \
  --database-flags=log_statement=all,log_min_duration_statement=1000

View Logs

gcloud logging read "resource.type=cloudsql_database AND resource.labels.database_id=bluewoo-hrms:hrms-db" \
  --limit=50

Metrics Dashboard

Go to Cloud Console → SQL → Instance → Monitoring
Key metrics:
- CPU utilization
- Memory usage
- Storage usage
- Active connections
- Query latency

Troubleshooting

Connection Refused

Error: connect ECONNREFUSED 127.0.0.1:5432

Fix: Ensure Cloud SQL Proxy is running or Cloud Run has --add-cloudsql-instances flag.

SSL Required

Error: SSL connection is required

Fix: Add ?sslmode=require to connection string or disable SSL requirement for development.

Permission Denied

Error: permission denied for table employees

Fix: Grant proper permissions to the application user:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO hrms_app;

Out of Connections

Error: too many connections for role "hrms_app"

Fix:

Increase max_connections in database flags
Use connection pooling (PgBouncer or Prisma connection pool)

Cost Optimization

Use appropriate tier: Start small, upgrade as needed
Enable auto-scaling storage: Avoid over-provisioning
Schedule backups during off-peak: Reduces I/O impact
Delete unused instances: Staging can be stopped overnight
Use committed use discounts: 1-year commit saves 25%

Cloud SQL PostgreSQL Setup

On this page