Cloud SQL: HA, Replicas, Backups and Private IP

GCP Basics · Storage & Databases – Cloud Storage, Cloud SQL and Choosing a Database · Lesson 3 of 5

Cloud SQL runs managed MySQL, PostgreSQL and SQL Server; regional HA fails over to a standby in another zone, read replicas scale reads, backups plus point-in-time recovery protect data, and private IP keeps it off the Internet.

Cloud SQL is managed MySQL, PostgreSQL and SQL Server. Google handles patching, backups, replication and failover; you choose size, availability and connectivity.

flowchart LR
  APP["App on Cloud Run / GKE"] -- "Cloud SQL connector, IAM" --> P["Primary shop-db<br/>zone a"]
  P -- "synchronous" --> S["Standby<br/>zone b"]
  P -- "asynchronous" --> RR["Read replica<br/>shop-db-replica"]
  P --> BK["Automated backups<br/>+ point-in-time recovery"]
  RO["Reporting jobs"] --> RR
  %% step APP: The app connects through a Cloud SQL language connector or the Auth Proxy, using IAM and TLS, to a private IP.
  %% step P: The primary instance in zone a takes all writes.
  %% step S: With HA (availability type REGIONAL), a standby in zone b receives every write synchronously and takes over in about a minute if the primary’s zone fails.
  %% step RR: A read replica copies changes asynchronously and serves read-only queries.
  %% step RO: Heavy reporting runs on the replica so it does not slow down the primary.
  %% step BK: Daily automated backups plus write-ahead/binary logs let you restore to any second within the retention window.

Editions and sizing

	Enterprise	Enterprise Plus
SLA (HA)	99.95%	99.99%
Extras	–	Data cache, faster maintenance with near-zero downtime, longer PITR

Pick vCPU and memory (for example db-custom-2-8192 = 2 vCPU, 8 GB), SSD storage with automatic storage increase, and a maintenance window at a quiet time.

Availability and scale

High availability: --availability-type=REGIONAL. Failover keeps the same IP address.
Read replicas: in the same or another region (cross-region replicas help disaster recovery and can be promoted). Replicas are asynchronous, so reads may lag slightly.
Scale up by changing the tier (short restart); scale reads with replicas.

Backups and recovery

Automated backups daily (7 kept by default) plus on-demand backups before risky changes.
Point-in-time recovery restores to a new instance at a chosen timestamp.
Deleting an instance can delete its automated backups: enable retained backups / deletion protection.

Connectivity and security

Option	Notes
Private IP	Through private services access (peering) or Private Service Connect; recommended
Public IP + authorized networks	Avoid; if needed, combine with the connectors and SSL
Cloud SQL Auth Proxy / language connectors	IAM-authorized, TLS-encrypted connections without managing certificates
IAM database authentication	Users and service accounts log in with IAM instead of passwords (MySQL, PostgreSQL)

gcloud sql instances create shop-db --database-version=POSTGRES_16 --edition=ENTERPRISE   --tier=db-custom-2-8192 --region=asia-southeast1 --availability-type=REGIONAL   --network=projects/dl-lab-prod/global/networks/vpc-shop --no-assign-ip
gcloud sql instances create shop-db-replica --master-instance-name=shop-db --region=asia-southeast1

Command	Meaning
`gcloud sql backups create --instance=shop-db`	On-demand backup
`gcloud sql instances failover shop-db`	Test an HA failover
`gcloud sql instances patch shop-db --deletion-protection`	Protect from deletion
`gcloud sql connect shop-db --user=postgres`	Quick psql session

Traps: a read replica is not a high-availability standby; HA doubles the instance cost; maintenance and tier changes restart the instance unless you use Enterprise Plus features.