Skip to content

Cloud SQL (PostgreSQL)

Overview

Managed PostgreSQL 16 database on Google Cloud SQL with private IP connectivity.

Attribute Value
Instance scandora-postgres
Region us-central1
Tier db-f1-micro (~$8/mo)
Storage 10GB SSD
Private IP 10.200.0.3
Database dataeng
User joe (cloudsqlsuperuser)

Architecture

graph LR
    subgraph GCP ["scandoraproject (GCP)"]
        subgraph VPC ["scandora-vpc-us-central1"]
            dumbo["dumbo<br/>10.1.0.110"]
            proxy["cloud-sql-proxy<br/>localhost:5432"]
        end
        subgraph PSA ["Private Services Access<br/>10.200.0.0/16"]
            cloudsql["Cloud SQL<br/>scandora-postgres<br/>10.200.0.3"]
        end
    end

    dumbo --> proxy
    proxy -->|"Private IP"| cloudsql

How It Works

  1. Cloud SQL runs in Google's managed environment with a private IP (10.200.0.3)
  2. Private Services Access creates a VPC peering connection to Google's network
  3. Cloud SQL Auth Proxy on dumbo creates a secure tunnel using IAM authentication
  4. Applications connect to localhost:5432 - the proxy handles encryption and auth

No Public IP

The Cloud SQL instance has no public IP address. All connections go through the VPC peering, keeping database traffic off the public internet.

Connecting

From Dumbo

# Connect via the local proxy (recommended)
psql -h 127.0.0.1 -U joe -d dataeng

# With password from environment
PGPASSWORD=$(op item get "Cloud SQL - scandora-postgres - joe" --fields password --reveal) \
  psql -h 127.0.0.1 -U joe -d dataeng

Connection String

postgresql://joe:PASSWORD@127.0.0.1:5432/dataeng

Health Check

# Proxy readiness
curl http://127.0.0.1:9090/readiness

# Proxy status
systemctl status cloud-sql-proxy

Cloud SQL Auth Proxy

The proxy runs as a systemd service on dumbo:

Attribute Value
Service cloud-sql-proxy
Listen 127.0.0.1:5432
Health 127.0.0.1:9090
Auth IAM (service account)

Service Management

# Status
sudo systemctl status cloud-sql-proxy

# Logs
sudo journalctl -u cloud-sql-proxy -f

# Restart
sudo systemctl restart cloud-sql-proxy

Configuration

Managed by Ansible role cloudsql-client:

  • Service file: /etc/systemd/system/cloud-sql-proxy.service
  • Binary: /usr/local/bin/cloud-sql-proxy

Credentials

1Password Item

Field Value
Title Cloud SQL - scandora-postgres - joe
Vault Private
Tags scandora.net, gcp, database

Retrieve Password

# From workstation (interactive)
op item get "Cloud SQL - scandora-postgres - joe" --fields password --reveal

# From dumbo (service account)
export OP_SERVICE_ACCOUNT_TOKEN=$(sudo cat /etc/op-service-account.token)
op item get "Cloud SQL - scandora-postgres - joe" --vault Private --fields password --reveal

Reset Password

# Generate new password
NEW_PASSWORD=$(openssl rand -base64 24 | tr -d '/+=' | head -c 32)

# Update in Cloud SQL
gcloud sql users set-password joe \
  --instance=scandora-postgres \
  --password="$NEW_PASSWORD"

# Update in 1Password manually
echo "New password: $NEW_PASSWORD"

Administration

Instance Management

# Instance status
gcloud sql instances describe scandora-postgres --format="get(state)"

# Restart instance
gcloud sql instances restart scandora-postgres

# Resize instance (minimal downtime)
gcloud sql instances patch scandora-postgres --tier=db-g1-small

Database Management

# List databases
gcloud sql databases list --instance=scandora-postgres

# Create database
gcloud sql databases create mydb --instance=scandora-postgres

# Delete database
gcloud sql databases delete mydb --instance=scandora-postgres

User Management

# List users
gcloud sql users list --instance=scandora-postgres

# Create user
gcloud sql users create newuser \
  --instance=scandora-postgres \
  --password="$(openssl rand -base64 24 | tr -d '/+=' | head -c 32)"

# Delete user
gcloud sql users delete newuser --instance=scandora-postgres

Backups

Automated Backups

Setting Value
Schedule Daily at 4:00 AM UTC (10 PM MDT)
Retention 7 days
PITR Disabled

Manual Operations

# List backups
gcloud sql backups list --instance=scandora-postgres

# Create on-demand backup
gcloud sql backups create --instance=scandora-postgres

# Restore from backup
gcloud sql backups restore BACKUP_ID --restore-instance=scandora-postgres

Restore Behavior

Restoring overwrites the current database. For production data, create a clone first.

Cost

Resource Monthly Cost
db-f1-micro ~$7
10GB SSD ~$2
Network $0 (private)
Backups (7 days) ~$0.50
Total ~$10/month

Resizing

Tier vCPU RAM Monthly
db-f1-micro Shared 0.6GB $8
db-g1-small Shared 1.7GB $25
db-custom-1-3840 1 3.75GB $50 
# Upgrade tier
gcloud sql instances patch scandora-postgres --tier=db-g1-small

Infrastructure as Code

Terraform

# Directory
cd cloud/terraform/environments/production/gce/cloud-sql

# Plan
terraform plan

# Apply (5-10 min for new instance)
terraform apply

# Outputs
terraform output connection_name
terraform output private_ip

Ansible

# Deploy proxy to dumbo
cd cloud/ansible
ansible-playbook -i inventory/dumbo.yml playbooks/site.yml --tags cloudsql

Troubleshooting

Proxy Won't Start

# Check logs
sudo journalctl -u cloud-sql-proxy -n 50

# Common issues:
# - "Config error: instance does not have IP of type PUBLIC"
#   Fix: Ensure --private-ip flag is in service file
#
# - "could not find default credentials"
#   Fix: Check IAM role on dumbo's service account

Can't Connect to Database

# 1. Verify proxy is running
systemctl status cloud-sql-proxy

# 2. Test port is listening
ss -tlnp | grep 5432

# 3. Check health endpoint
curl http://127.0.0.1:9090/readiness

# 4. Check IAM permissions
gcloud projects get-iam-policy scandoraproject \
  --flatten="bindings[].members" \
  --filter="bindings.role:roles/cloudsql.client"

Slow Performance

db-f1-micro is limited. If experiencing slowness:

  1. Check CPU usage in Cloud Console
  2. Consider upgrading to db-g1-small (~$25/mo)
  3. Optimize queries (add indexes, EXPLAIN ANALYZE)

SSL Errors

Cloud SQL enforces SSL. The proxy handles this automatically, but direct connections need:

# Download CA certificate
gcloud sql ssl server-ca-certs list --instance=scandora-postgres

# Connect with SSL
psql "host=10.200.0.3 dbname=dataeng user=joe sslmode=verify-ca sslrootcert=server-ca.pem"

Security

IAM Roles

Role Principal Purpose
roles/cloudsql.client Compute default SA Proxy auth

Network Security

  • No public IP - Database unreachable from internet
  • VPC peering - Traffic stays in Google network
  • SSL required - All connections encrypted
  • IAM auth - Proxy authenticates via service account

User Privileges

User Privileges
joe cloudsqlsuperuser (CREATEDB, CREATEROLE)
cloudsqladmin True superuser (Google internal)

Superuser Limitation

Cloud SQL doesn't grant true PostgreSQL SUPERUSER to customers. cloudsqlsuperuser is the highest available privilege level.