22 - Relational DBs - Cloud SQL (Postgres and MySQL)

Welcome to Day 22 of Learn GCP in 30 Days! Yesterday, you mastered storing unstructured files (images, videos, and backups) in Google Cloud Storage. Today, we step into the world of structured, relational data with Google Cloud SQL.

🛑 The Core Problem: The 2:00 AM Database Disaster

In Week 2, we learned how to launch Linux Virtual Machines. You could theoretically install PostgreSQL or MySQL on a Compute Engine VM by running sudo apt install postgresql.

However, running a production database yourself on a VM is one of the most stressful jobs in engineering:

What Existed Previously:

Database administrators (DBAs) spent countless hours provisioning virtual disks, configuring replication masters and slaves, writing custom shell scripts for backups, and manually applying security patches during midnight maintenance windows.

Problems Faced:

• 💽 Disk Full Disasters: If disk space fills up during a sudden surge of transactions, relational database engines can corrupt their transaction logs and refuse to boot.
• 💾 Unreliable Backups: Most developers write backup scripts that dump SQL files to a local folder. But they rarely test restoring those backups until an emergency strikes—only to find the backup files are empty or corrupted.
• ⚡ Manual Failover Downtime: When a database VM crashes, a human engineer must wake up, log into the server, spin up a replacement, promote a replica, and reconfigure application IP addresses—costing hours of downtime.
• 🔒 Security Vulnerabilities: Keeping database engines patched against zero-day exploits requires continuous maintenance and downtime coordination.

How Present Technology Solves It:

Google created Cloud SQL (Fully Managed Relational Database):

1. Zero Database Administration: Google handles OS provisioning, database engine installation, routine security patching, and hardware health.
2. Automated Storage Auto-Increase: When your database reaches 90% disk capacity, Cloud SQL automatically expands your disk in real time with zero downtime.
3. Automated Backups & Point-in-Time Recovery (PITR): Google takes daily snapshots and retains transaction logs, allowing you to restore your database to any exact second in the last 7 days.
4. 1-Click High Availability (HA): Google runs an active standby replica in a separate availability zone. If the primary datacenter fails, failover happens automatically in under 60 seconds with zero IP changes.

🥛 Real-World Analogy: Owning a Cow vs. Automated Milk Delivery

• Installing MySQL on a Compute Engine VM = Owning a Live Cow:
  • You must build the barn, buy feed, clean manure, monitor health, and handle breeding. If you sleep in or get sick, the entire farm stops.
• Google Cloud SQL = Automated Fresh Milk Subscription:
  • You simply open the bottle and drink the milk.
  • The dairy farm handles cows, barns, pasteurization, veterinary care, and backup delivery trucks. You pay strictly for the milk you consume!

🐬 Engines Supported by Cloud SQL

Google Cloud SQL supports the three industry-standard relational database engines:

🏗️ Core Architecture & Resilience Features

1. High Availability (Regional Deployment)

• When you enable High Availability (HA), Cloud SQL provisions two identical instances:
  • Primary Instance in Zone A (e.g., us-central1-a).
  • Standby Instance in Zone B (e.g., us-central1-b).
• Storage is synchronously replicated across both zones.
• If Zone A suffers a hardware failure, Google switches traffic to Zone B in < 60 seconds with no manual intervention and the exact same database connection endpoint.

2. Read Replicas (Scaling Read Traffic)

• Relational databases typically handle 90% read queries (fetching user profiles, rendering products) and 10% write queries (inserting orders).
• Cloud SQL allows you to spin up multiple Read Replicas (in the same region or globally across other continents).
• Your application routes INSERT/UPDATE writes to the Primary instance and offloads heavy SELECT reads to Read Replicas, preventing database slowdowns.

3. Automated Storage Autoscaling

• Traditional DBs crash when hard disks reach 100% capacity.
• Cloud SQL provides Storage Auto-Increase:
  • You start with a small, cost-effective 10 GB disk.
  • When used storage exceeds 90%, Cloud SQL automatically adds more gigabytes in the background without restarting the database engine.

4. Automated Backups & Point-in-Time Recovery (PITR)

• Daily Automated Backups: Cloud SQL takes an automated daily backup during a configurable maintenance window.
• Write-Ahead Logging (PITR): By enabling Point-in-Time Recovery, Cloud SQL continuously records transaction logs.
• If an engineer accidentally runs DROP TABLE users; at 2:14:32 PM, you can restore a cloned database to 2:14:31 PM (one second before the mistake)!

🔐 How to Connect: Public IP vs. Private IP vs. Cloud SQL Auth Proxy

Connecting to a database securely is the most critical aspect of database architecture.

1. Public IP + Authorized Networks (Legacy / Simple Testing)

• The instance is assigned an external public IPv4 address.
• You must manually add your local office/home IP to an "Authorized Networks" whitelist.
• ⚠️ Risk: Exposes database ports (5432 or 3306) to the open internet.

2. Private IP (Internal VPC Access)

• The instance has zero public internet IP.
• It is assigned an internal IP address (e.g., 10.0.0.5) on your Virtual Private Cloud (VPC).
• Only VMs, Cloud Run services, or GKE containers inside the same VPC can reach it.

3. The Gold Standard: Cloud SQL Auth Proxy

What if a developer wants to connect their local laptop to a Cloud SQL instance without opening firewall ports, whitelisting IP addresses, or managing SSL/TLS certificates?

Google created the Cloud SQL Auth Proxy:

Follow the Data:

1. The developer runs the small, open-source Cloud SQL Auth Proxy binary on their local machine.
2. The proxy authenticates with Google Cloud using standard GCP IAM credentials.
3. The proxy opens an encrypted, mutual-TLS (mTLS) tunnel directly to the Cloud SQL instance.
4. The developer connects their database GUI (like DBeaver or pgAdmin) to localhost:5432—no firewall rule changes needed!

💰 Free Trial Cost Safety: Sizing for $0 Waste

🧪 Hands-on Lab: Provisioning Cloud SQL & Executing SQL Queries

In this lab, you will provision a lightweight PostgreSQL instance, connect to it securely using Cloud Shell, create a database and table, insert records, and safely delete the instance.

Step 1: Provision a Lightweight Cloud SQL Instance

Pathway A: Web Console (Click-by-Click)

1. Open the Google Cloud Console (https://console.cloud.google.com/).
2. In the top search bar, type Cloud SQL and select SQL (or navigate to Navigation Menu $\rightarrow$ Databases $\rightarrow$ SQL).
3. Click CREATE INSTANCE.
4. Choose PostgreSQL (or MySQL).
5. Configure Instance Settings:
   • Instance ID: gcp-day22-db
   • Password: Enter a secure password (e.g., SuperSecretPass123!) or click Generate.
   • Database version: Select PostgreSQL 15 (or 16).
   • Cloud SQL edition: Select Enterprise.
   • Preset: Select Development (1 vCPU, 3.75 GB RAM, Single zone - lowest cost).
   • Region: Select us-central1 (Iowa).
   • Zonal availability: Select Single zone (preserves Free Trial credits).
6. Under Customize your instance:
   • Expand Machine configuration: Choose Shared core $\rightarrow$ db-f1-micro or standard db-custom-1-3840.
   • Expand Storage: Select 10 GB (Storage type: SSD). Ensure Enable automatic storage increases is checked.
7. Click CREATE INSTANCE. (Provisioning takes about 3-5 minutes).

Pathway B: Cloud Shell CLI

Open Cloud Shell and run:

# 1. Set environment variables
export PROJECT_ID=$(gcloud config get-value project)
export INSTANCE_NAME="gcp-day22-db"
export REGION="us-central1"
export DB_PASSWORD="SuperSecretPass123"

# 2. Create a lightweight development PostgreSQL instance
gcloud sql instances create ${INSTANCE_NAME} \
  --database-version=POSTGRES_15 \
  --tier=db-custom-1-3840 \
  --region=${REGION} \
  --root-password=${DB_PASSWORD} \
  --storage-size=10GB \
  --storage-type=SSD \
  --storage-auto-increase \
  --availability-type=ZONAL \
  --no-deletion-protection

Step 2: Create an Application Database

By default, PostgreSQL comes with a system database named postgres. Let's create an application database named ecommerce_db.

Pathway A: Web Console (Click-by-Click)

1. In the Cloud SQL navigation menu on the left, click Databases.
2. Click + CREATE DATABASE.
3. Name: ecommerce_db.
4. Click CREATE.

Pathway B: Cloud Shell CLI

# Create application database via gcloud CLI
gcloud sql databases create ecommerce_db --instance=${INSTANCE_NAME}

Step 3: Connect to Cloud SQL & Run SQL Queries

You can interact with your database using either Google's built-in Cloud SQL Studio (Web UI) or via Cloud Shell (CLI).

Pathway A (Recommended): Cloud SQL Studio (Web UI)

Google provides Cloud SQL Studio directly inside the Cloud Console, allowing you to query databases without managing local terminal proxies or SSL certificates:

1. In the Google Cloud Console, navigate to Cloud SQL $\rightarrow$ Click on gcp-day22-db.
2. In the left-side navigation menu under Primary Instance, click Cloud SQL Studio.
3. Fill in the login credentials:
   • Database: ecommerce_db
   • User: postgres
   • Password: Enter your database password (e.g., SuperSecretPass123).
4. Click Authenticate.
5. You will see an interactive web SQL editor with syntax highlighting and a query runner!

Pathway B: Cloud Shell CLI

If you prefer using the terminal, Google Cloud Shell has built-in database client utilities:

# Connect interactively to PostgreSQL on Cloud SQL
gcloud sql connect ${INSTANCE_NAME} --user=postgres --database=ecommerce_db

(When prompted, enter your database password).

export MY_IP=$(curl -s ifconfig.me)
gcloud sql instances patch ${INSTANCE_NAME} --authorized-networks=${MY_IP}
export DB_IP=$(gcloud sql instances describe ${INSTANCE_NAME} --format="value(ipAddresses[0].ipAddress)")
psql -h ${DB_IP} -U postgres -d ecommerce_db

Step 4: Create a Table, Insert Records & Run Queries

Now execute the following standard SQL statements (either in Cloud SQL Studio by clicking Run, or inside the psql terminal):

-- 1. Create a customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    signup_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Insert sample records
INSERT INTO customers (full_name, email) VALUES
('Manikanta', 'mani@example.com'),
('Sarah Connor', 'sarah@skynet.com'),
('Bruce Wayne', 'bruce@wayne-enterprises.com');

-- 3. Query all customer records
SELECT * FROM customers;

Output:

 customer_id |    full_name    |           email            |        signup_date         
-------------+-----------------+----------------------------+----------------------
   1 | Manikanta  | mani@example.com           | 2026-08-22 06:45:10.123456
   2 | Sarah Connor    | sarah@skynet.com           | 2026-08-22 06:45:10.123456
   3 | Bruce Wayne     | bruce@wayne-enterprises.com| 2026-08-22 06:45:10.123456
(3 rows)

-- 4. Run a filtered query
SELECT full_name, email FROM customers WHERE email LIKE '%wayne%';

-- 5. Exit (if using psql terminal)
\q

Step 5: Trigger an On-Demand Backup

While Cloud SQL performs automated daily backups, you can also trigger a manual backup before major application schema migrations:

# Trigger an immediate manual backup
gcloud sql backups create \
  --instance=${INSTANCE_NAME} \
  --description="Pre-migration safety backup"

# List all backups for this instance
gcloud sql backups list --instance=${INSTANCE_NAME}

🛡️ Step 6: Credit Safety & Resource Teardown

Pathway A: Web Console (Click-by-Click)

1. Go to Cloud SQL $\rightarrow$ click on gcp-day22-db.
2. Click DELETE on the top action bar.
3. Type the instance name gcp-day22-db in the confirmation box and click DELETE.

Pathway B: Cloud Shell CLI

# Delete the Cloud SQL instance permanently
gcloud sql instances delete ${INSTANCE_NAME} --quiet

Output:

Deleted [https://sqladmin.googleapis.com/sql/v1beta4/projects/.../instances/gcp-day22-db].

📝 Day 22 Summary & Quick Reference

Core Architecture Takeaways

1. Managed vs Self-Hosted: Cloud SQL eliminates the overhead of managing Linux VMs, manual disk resizing, patch cycles, and broken backup scripts.
2. High Availability (HA): 1-click synchronous replication to a standby zone with $<60\text{s}$ automated failover.
3. Storage Autoscaling: Cloud SQL expands disk capacity on the fly without database downtime.
4. Cloud SQL Auth Proxy: The gold standard for secure developer connections—creates an encrypted IAM-authorized mTLS tunnel without opening firewall ports.
5. Point-in-Time Recovery: Restores databases to any exact second in the past week.

Quick Command Cheat Sheet

Tomorrow, in Day 23, we explore NoSQL document databases with Google Cloud Firestore—learning how to store dynamic JSON-like documents, build real-time collaborative mobile/web apps, and scale automatically to millions of queries!

← 21 - Storage 101 - Cloud Storage (GCS) Buckets | Next Topic → 23 - NoSQL DBs - Firestore for Realtime Apps