10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery

10.3 Cloud SQL: High Availability, Read Replicas, Maintenance, and Point-in-Time Recovery

Exam Focus: The Professional Data Engineer exam frequently tests operational administration, disaster recovery, and networking architectures for Google Cloud SQL. Candidates must master the architectural differences between High Availability (synchronous Regional Persistent Disk replication with RPO=0) and Read Replicas (asynchronous database replication with replication lag), configure Point-in-Time Recovery (PITR) using transaction logs, design maintenance window schedules with update deferrals, and secure database connections using the Cloud SQL Auth Proxy and Private Service Access (PSA).

Google Cloud SQL is a fully managed relational database service supporting three popular open-source and commercial database engines: PostgreSQL, MySQL, and Microsoft SQL Server. While Cloud Spanner is designed for globally distributed, horizontally scalable workloads exceeding 64 TB, Cloud SQL is optimized for traditional, single-primary relational workloads up to 64 TB and 128 vCPUs per instance. For data engineers, mastering Cloud SQL involves architecting resilient high-availability configurations, designing read-scaling topologies, formulating disaster recovery strategies, and establishing zero-trust database connectivity.

---

1. Cloud SQL Managed Architecture & Scale Boundaries

Cloud SQL abstracts host provisioning, OS patching, database installation, disk volume striping, and routine backups behind a managed control plane.

Instance Specifications & Limits

• Compute Scaling: Vertical scaling from 1 shared vCPU up to 128 vCPUs and 864 GB of RAM.
• Storage Scaling: Powered by Google Cloud Persistent Disk (Standard HDD, Balanced SSD, or SSD). Storage scales from 10 GB up to a maximum of 64 TB.
• Automatic Storage Increase: When enabled, Cloud SQL automatically expands persistent disk capacity when free space drops below a dynamic threshold (typically 10% or 5 GB). Critical Exam Rule: Disk capacity can be scaled up automatically or manually, but disk capacity can NEVER be scaled down. Reducing disk size requires creating a new smaller instance and performing a logical database export and import.
• Single Primary Architecture: Unlike Cloud Spanner, Cloud SQL is fundamentally a single-writer system. All SQL write operations (INSERT, UPDATE, DELETE, DDL) must target the single primary instance. Relational writes cannot be scaled horizontally across multiple active master instances.

---

2. High Availability (HA) Architecture: Synchronous Regional Disk Replication

To protect relational databases against infrastructure hardware failures, rack outages, and zone-level disasters, Cloud SQL offers a managed High Availability (HA) configuration.

+───────────────────────────────────────────────────────────────────────────────────+
|                     CLOUD SQL HIGH AVAILABILITY (HA) ARCHITECTURE                 |
+───────────────────────────────────────────────────────────────────────────────────+
| PRIMARY ZONE (e.g., us-central1-a)        STANDBY ZONE (e.g., us-central1-b)      |
|  ┌───────────────────────────────┐         ┌───────────────────────────────┐      |
|  │   Primary Cloud SQL Instance  │         │   Standby Cloud SQL Instance  │      |
|  │   - Active DB Engine Process  │         │   - Dormant DB Engine Process │      |
|  │   - Serves ALL Reads & Writes │         │   - DOES NOT SERVE TRAFFIC    │      |
|  └───────────────┬───────────────┘         └───────────────┬───────────────┘      |
|                  │                                         │                      |
|                  ▼                                         ▼                      |
|  ┌─────────────────────────────────────────────────────────────────────────┐      |
|  │                 SYNCHRONOUS REGIONAL PERSISTENT DISK (PD)               │      |
|  │   Block-level synchronous replication across Zone A and Zone B (RPO = 0)│      |
|  └─────────────────────────────────────────────────────────────────────────┘      |
+───────────────────────────────────────────────────────────────────────────────────+

How Cloud SQL HA Works

1. Dual-Zone Deployment: An HA-configured instance consists of a primary instance in the primary zone (e.g., us-central1-a) and a standby instance in a secondary zone within the same region (e.g., us-central1-b).
2. Block-Level Synchronous Replication (Regional PD): Data durability does not rely on database-level replication mechanisms. Instead, Cloud SQL attaches a Regional Persistent Disk (Regional PD). Every file system write, commit log entry, and data block change written by the primary engine is synchronously replicated at the storage block level to the standby zone before the write is acknowledged as committed to the client application.
3. Recovery Point Objective (RPO = 0): Because replication is synchronous at the storage layer, zero uncommitted or committed data is lost during an unexpected zone failure (RPO = 0).
4. Automated Cross-Zone Failover: Heartbeat monitors continuously check the health of the primary instance. If the primary instance fails to respond for approximately 60 seconds (due to host crash, network partition, or datacenter failure), the control plane triggers automated failover:
   • The standby instance detaches the Regional PD from the failed primary, attaches it in read-write mode, and starts the database engine process.
   • The database engine replays any active crash recovery logs from the Regional PD.
   • Cloud SQL updates internal DNS routing pointers so that the instance's static IP address points to the newly promoted primary instance in Zone B.
   • Recovery Time Objective (RTO): Failover completes automatically, typically within 60 to 120 seconds.

The Standby Instance Anti-Pattern

A common misconception tested on the exam is assuming the standby instance can be used to offload read traffic. The standby instance does NOT accept connections or serve read traffic. The standby engine is dormant; it only assumes active database processing upon failover. To serve read traffic, you must deploy dedicated Read Replicas.

---

3. Read Replicas: Read Scaling and Cross-Region Disaster Recovery

To scale read throughput and provide multi-region disaster recovery, Cloud SQL supports Read Replicas.

+───────────────────────────────────────────────────────────────────────────────────+
|                     CLOUD SQL READ REPLICATION TOPOLOGY                           |
+───────────────────────────────────────────────────────────────────────────────────+
| PRIMARY REGION (us-central1)                                                      |
|  ┌───────────────────────────────┐                                                |
|  │   Primary Instance (HA)       │◄── Application Writes (INSERT/UPDATE/DELETE)   |
|  └───────────────┬───────────────┘                                                |
|                  │ Asynchronous Engine Replication (WAL / Binlog)                 |
|                  ├───────────────────────────────┐                                |
|                  ▼                               ▼                                |
|  ┌───────────────────────────────┐ ┌───────────────────────────────┐              |
|  │   Intra-Region Read Replica   │ │   Cross-Region Read Replica   │              |
|  │   (us-central1-c)             │ │   (europe-west1)              │              |
|  │   - Offloads BI / Dashboards  │ │   - Low-latency EU reads      │              |
|  └───────────────────────────────┘ │   - DR Standby (Promotable)   │              |
|                                    └───────────────────────────────┘              |
+───────────────────────────────────────────────────────────────────────────────────+

Replication Mechanics

• Native Database Engine Replication: Unlike HA failover, read replicas do not use Regional PD. They use native asynchronous replication: Write-Ahead Log (WAL) streaming in PostgreSQL and Binary Logging (binlog) in MySQL.
• Asynchronous Replication Lag: Because writes are replicated asynchronously, read replicas experience replication lag. An update committed on the primary instance may take tens to hundreds of milliseconds (or seconds during high write loads or cross-region network congestion) to reflect on the read replica (RPO > 0). Applications requiring immediate read-your-writes consistency must query the primary instance.
• Read Offloading: Read replicas are assigned their own distinct IP addresses and connection strings. They are ideal for offloading heavy analytical reporting, business intelligence dashboards, and search queries from the primary instance.

Cross-Region Read Replicas for Disaster Recovery (DR)

• Regional Resilience: A cross-region read replica resides in an entirely different Google Cloud region (e.g., primary in us-central1, replica in europe-west1).
• Promotion to Primary: If an entire region experiences a catastrophic, prolonged outage, a cross-region read replica can be manually or programmatically promoted to a standalone primary database.
• Promotion Trade-off: Promoting a replica breaks replication. The promoted instance becomes an independent read-write primary database with a new IP address. Any transactions that were in-flight or un-replicated due to replication lag at the time of regional failure are lost (RPO > 0).

---

4. Backup Strategies and Point-in-Time Recovery (PITR)

Cloud SQL provides two complementary data protection mechanisms: Automated Daily Backups and Point-in-Time Recovery (PITR).

Automated vs. On-Demand Backups

• Automated Daily Backups: Cloud SQL executes scheduled daily backups within a user-defined 4-hour backup window. Backups are incremental: only altered data blocks are stored, minimizing backup duration and Cloud Storage costs. Up to 365 daily backups can be retained.
• On-Demand Backups: Created manually at any time via the Cloud Console or gcloud sql backups create. On-demand backups persist indefinitely until explicitly deleted, even if the Cloud SQL instance itself is deleted (provided the backup is retained).

Point-in-Time Recovery (PITR) Mechanics

• Continuous Transaction Logging: When PITR is enabled, Cloud SQL continuously archives write-ahead logs (PostgreSQL) or binary logs (MySQL) to Cloud Storage alongside daily base backups.
• Granular Recovery: PITR enables restoring database state to any exact second within the configured retention window (configurable between 1 and 35 days, default 7 days).
• Restoration Process: Restoring from a backup or performing a PITR recovery does NOT overwrite the existing instance in-place. Cloud SQL always provisions a brand new Cloud SQL instance and hydrates it with the restored data. Once restoration completes, client connection strings or DNS records must be updated to target the new instance.

Timeline:   [Day 1 Backup] ──► [Day 2 Backup] ──► [WAL / Binlogs] ──► [ACCIDENTAL DROP TABLE!]
                                                            │                  (14:32:15 UTC)
                                                            ▼
                                        Target Recovery: 14:32:14 UTC
                         (Day 2 Base Backup + Replay Logs to 14:32:14 UTC -> New Instance)

---

5. Maintenance Windows, Rollout Tracks, and Reschedule Rules

Google Cloud regularly applies operating system patches, hardware infrastructure upgrades, and database minor engine releases to Cloud SQL instances.

Maintenance Window Governance

• Configurable Schedule: Administrators define the preferred day of the week (e.g., Sunday) and hour of the day (e.g., 03:00 UTC) for maintenance to occur.
• Maintenance Rollout Tracks:
  • Earlier Track: Updates are applied approximately 7 days prior to general release. Recommended for development, QA, and staging environments to validate application compatibility.
  • Later Track: Updates are applied after the release has been proven in the earlier track. Recommended for production environments.
• Rescheduling and Deferrals: If an upcoming maintenance window conflicts with a critical business event (e.g., Black Friday or fiscal year-end closing), administrators can:
  • Reschedule: Postpone the scheduled maintenance by up to 28 days from the original scheduled date.
  • Deny Maintenance Period: Configure up to 90 consecutive days where no routine maintenance will be performed.
• High Availability Impact During Maintenance: On instances configured with High Availability, Cloud SQL performs updates with minimal downtime (typically under 60 seconds). Google updates the standby instance first, triggers a quick failover from primary to standby, updates the original primary, and synchronizes the disks.

---

6. Zero-Trust Security: Cloud SQL Auth Proxy and Private Service Access

Securing relational database traffic across enterprise networks requires eliminating public IP exposure and enforcing cryptographic identity verification.

+───────────────────────────────────────────────────────────────────────────────────+
|                     SECURE CLOUD SQL CONNECTIVITY ARCHITECTURE                    |
+───────────────────────────────────────────────────────────────────────────────────+
| CLIENT VPC NETWORK (e.g., GKE / Compute Engine)                                   |
|  ┌────────────────────────────────────────────────────────┐                       |
|  │ Client Application (Pod / VM)                          │                       |
|  │   Connects to 127.0.0.1:5432 (Local Unix/TCP Socket)   │                       |
|  └────────────────────────┬───────────────────────────────┘                       |
|                           ▼                                                       |
|  ┌────────────────────────────────────────────────────────┐                       |
|  │ Cloud SQL Auth Proxy (Sidecar / Daemon)                │                       |
|  │ - Authenticates via Google Cloud IAM (Service Account) │                       |
|  │ - Ephemeral mTLS certificate generation                │                       |
|  └────────────────────────┬───────────────────────────────┘                       |
|                           │ Encrypted mTLS Tunnel (Port 3307)                     |
|                           ▼                                                       |
| GOOGLE-MANAGED SERVICE PRODUCER VPC                                               |
|  ┌────────────────────────────────────────────────────────┐                       |
|  │ Cloud SQL Instance (Private IP via Private Service     │                       |
|  │ Access / VPC Peering)                                  │                       |
|  └────────────────────────────────────────────────────────┘                       |
+───────────────────────────────────────────────────────────────────────────────────+

Cloud SQL Auth Proxy

• Client-Side Daemon: A lightweight binary run as a local background process or Kubernetes sidecar container alongside client applications.
• IAM-Based Mutual TLS (mTLS): The proxy authenticates with the Cloud SQL Admin API using Google Cloud IAM credentials (e.g., Compute Engine service account or GKE Workload Identity). It requests a dynamic, short-lived (ephemeral) SSL/TLS certificate that rotates automatically every hour.
• Zero Whitelisting: Eliminates the need to maintain static authorized IP networks or manage public IP addresses.
• Local Loopback Interface: Client applications connect to localhost:5432 (or a local Unix domain socket) without needing to implement custom SSL connection logic.

Private Service Access (PSA) vs. Authorized Networks

• Authorized Networks (Legacy / Anti-Pattern): Whitelisting external public CIDR blocks (0.0.0.0/0 or corporate public IPs) exposes database ports to the internet and requires static IP management.
• Private Service Access (PSA - Best Practice): Provisions Cloud SQL with an internal RFC 1918 private IP address inside a Google-managed VPC network. A private VPC peering connection bridges the enterprise client VPC to the Google-managed database VPC. Traffic travels entirely over Google's internal private backbone network without traversing the public internet.
• IAM Database Authentication: Replaces static, hard-coded database usernames and passwords with Google Cloud IAM service account tokens. When combined with the Auth Proxy, applications authenticate end-to-end without managing database secrets.

---

7. Comparative Architecture Matrix: Cloud SQL HA vs. Read Replicas vs. Spanner

---

8. Real-World Exam Scenarios and Architectural Anti-Patterns

Scenario 1: Accidental Data Deletion in Financial Accounting DB

• Requirement: At 14:32:15 UTC, an automated batch migration script accidentally executes a DROP TABLE customer_invoices; on a production Cloud SQL for PostgreSQL database. The data engineering team must recover the table as quickly as possible without losing legitimate invoice transactions processed between 00:00 UTC and 14:32:14 UTC.
• The Anti-Pattern: Restoring the previous midnight's automated daily backup over the existing instance. This overwrites the entire database and permanently destroys over 14 hours of valid business transactions.
• The Certified Architecture: Perform a Point-in-Time Recovery (PITR) to 14:32:14 UTC (one second prior to the drop command). Cloud SQL provisions a new instance, restores the previous base backup, and replays write-ahead logs up to 14:32:14 UTC. The missing customer_invoices table is exported from the new instance and re-imported into production, achieving near-zero data loss.

Scenario 2: Connecting Kubernetes Pods to Cloud SQL Securely

• Requirement: A microservices application deployed on Google Kubernetes Engine (GKE) must connect to a Cloud SQL for MySQL instance. Security compliance strictly mandates: no public IP addresses on the database, no static database passwords stored in Kubernetes Secrets, and automatic rotation of all cryptographic certificates.
• The Anti-Pattern: Assigning Cloud SQL a public IP address, configuring an Authorized Network for the GKE NAT gateway, and embedding a root password in a Kubernetes configmap.
• The Certified Architecture: Deploy the Cloud SQL instance with Private Service Access (Private IP). Deploy the Cloud SQL Auth Proxy as a sidecar container inside the GKE pods. Configure GKE Workload Identity to map the pod's Kubernetes service account to a Google Cloud IAM service account with Cloud SQL Client permissions, and enable IAM Database Authentication. The proxy handles mTLS certificate rotation automatically every hour.

---

9. Common Exam Pitfalls and Gotchas

• Pitfall 1: Attempting to Downsize Cloud SQL Persistent Disks: You can increase storage capacity at any time, and enable automatic storage increases. However, Cloud SQL disks cannot be decreased in size. If you accidentally provision 30 TB for a 500 GB database, you must export the database and import it into a newly created smaller instance to reduce storage costs.
• Pitfall 2: Believing HA Standbys Accelerate Read Queries: A Cloud SQL HA standby instance cannot be queried. It does not run an active database listener and serves zero read requests. Only Read Replicas can offload read traffic.
• Pitfall 3: Assuming PITR Overwrites the Existing Database Instance: Point-in-Time Recovery always creates a new Cloud SQL instance. Applications will continue connecting to the unrecovered old instance unless DNS records or application configuration files are updated to point to the new instance.
• Pitfall 4: Neglecting Replication Lag When Designing Read Replicas: Read replicas use asynchronous replication. Querying a read replica immediately after executing a write on the primary can return stale data. Systems requiring strong read-after-write consistency must route reads to the primary instance.