Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 45 ยท Storage and Databases

Cloud SQL

Learn Cloud SQL, Google's managed MySQL, PostgreSQL and SQL Server: instances, high availability, replicas, backups, private IP and connectors, with SQL to run.

Running a database yourself means installing it, patching it, tuning it, taking backups and rebuilding it when a machine dies. Cloud SQL is Google Cloud's managed service for relational databases: you ask for a MySQL, PostgreSQL or SQL Server instance, and Google runs the machine, the operating system patches, automated backups and failover. You keep the part that matters to your application: schemas, queries and data.

This lesson covers what an instance is, how high availability, read replicas and backups work, how applications connect safely, and what you still have to do yourself. Versions, limits and prices change, so confirm details in the current documentation.

What Cloud SQL gives you

Managed, not magic

Google handles Hardware, OS, database patches, backups.
You handle Schema, queries, indexes, users, sizing.

A managed database still needs good design.

1 / 2

You do not get full access to the underlying server. You cannot log in over SSH, install arbitrary extensions, or change every setting. Supported settings are exposed as database flags, and a supported list of extensions is available for PostgreSQL. If you need total control, you run the database on a VM yourself (Compute Engine Virtual Machines), accepting the extra work.

Instances, tiers and storage

A Cloud SQL instance is one database server. Inside it you create databases, tables and users the usual way. When you create an instance you choose:

  • the engine and major version (for example a recent PostgreSQL major version);
  • the region (and optionally zone), which should be close to your application (Regions, Zones and Latency);
  • the machine size: number of vCPUs and memory;
  • the storage type (SSD or HDD) and size, with an option to grow automatically as data fills it;
  • the edition and availability setting, which changes features and price.
Terminal
# Create a small PostgreSQL instance (read-only: needs a real project and billing)
gcloud sql instances create shop-db \
  --database-version=POSTGRES_16 \
  --region=europe-west1 \
  --cpu=2 --memory=8GB \
  --storage-size=50GB --storage-auto-increase

gcloud sql databases create shop --instance=shop-db
gcloud sql users create app_user --instance=shop-db --password="use-secret-manager"

Instances bill while they exist, running or not (a stopped instance still pays for storage), so delete practice instances. Never put a real password in a command; store it in Secret Manager.

High availability, replicas and backups

These three features are often confused. They solve different problems.

High availability (HA)

With HA enabled, Cloud SQL keeps a standby in a different zone of the same region. Data is written to both. If the primary zone fails, or the instance needs maintenance, Cloud SQL fails over to the standby, usually within a minute or so, and the instance keeps the same address. HA protects against a zone failure, not against you running DELETE FROM orders.

Read replicas

A read replica is a separate instance that copies changes from the primary asynchronously. It serves read-only queries, so reports and heavy reads do not slow down writes. Replicas can be in other regions. Because they copy with a delay (replication lag), a replica can briefly return old data. Replicas are for scaling reads and, in some designs, disaster recovery; they are not a substitute for HA or backups.

Backups and point-in-time recovery

  • Automated backups run daily in a window you choose and are kept for a set number of days.
  • On-demand backups can be started before a risky change.
  • Point-in-time recovery (PITR) replays the transaction logs, so you can restore to a chosen moment, such as just before a bad deployment. You enable it separately.
  • A restore creates or overwrites an instance; practice it before you need it.
Need Feature
Survive a zone outage with fast failover High availability
Offload reporting or read-heavy traffic Read replicas
Undo a mistake or corruption Backups and PITR
Terminal
# Read-only examples
gcloud sql instances patch shop-db --availability-type=REGIONAL
gcloud sql instances create shop-db-replica \
  --master-instance-name=shop-db --region=europe-west4
gcloud sql backups create --instance=shop-db

Connecting safely

A database should rarely be reachable from the whole internet. Common options:

  • Private IP: the instance gets an address inside your VPC network (VPC Networks, Subnets and Firewall Rules), so only resources on that network (VMs, GKE, Cloud Run through a connector or direct VPC egress) can reach it.
  • Public IP with authorized networks: allows chosen IP ranges. Easy for testing, riskier in production.
  • Cloud SQL Auth Proxy and language connectors: a small helper or library that opens an encrypted tunnel and authenticates with IAM, so you do not manage IP allow-lists or SSL certificates by hand. Cloud Run has a built-in Cloud SQL connection option that uses it.
  • IAM database authentication: log in with a service account or user identity instead of a stored password, for engines that support it.
Terminal
# Run the Auth Proxy on your own machine (read-only), then connect to localhost
./cloud-sql-proxy my-project:europe-west1:shop-db --port 5432
psql "host=127.0.0.1 port=5432 dbname=shop user=app_user"

Applications should use a connection pool, because each database connection costs memory and an instance allows only a limited number. Many serverless instances opening many connections at once is a classic way to exhaust that limit.

Day-to-day operations

A few tasks you will repeat. All commands are read-only examples; they need a real project and cost money to run.

Terminal
# create an instance, a database and a user
gcloud sql instances create myinstance --database-version=POSTGRES_16 \
  --tier=db-custom-2-7680 --region=europe-west1
gcloud sql databases create guestbook --instance=myinstance
gcloud sql users create app_user --instance=myinstance --password='<from Secret Manager>'

# look at instances
gcloud sql instances list
gcloud sql instances describe myinstance

# restart (changing some flags needs one; expect a short outage)
gcloud sql instances restart myinstance

# connect with a SQL client (opens a temporary allow-listed connection)
gcloud sql connect myinstance --user=app_user --database=guestbook

Configuring SSL/TLS. Cloud SQL presents a server certificate signed by a CA specific to the instance, so clients can verify they reach the right server. You can make encryption mandatory with gcloud sql instances patch myinstance --ssl-mode=ENCRYPTED_ONLY, or go further with TRUSTED_CLIENT_CERTIFICATE_REQUIRED, which also requires each client to present a certificate you created for it (gcloud sql ssl client-certs create). The Auth Proxy and the language connectors handle all of this for you, which is the main reason to prefer them.

Executing queries and transactions. Once connected, the SQL is the same as on any server of that engine. Group statements that must succeed or fail together in a transaction (BEGIN, then COMMIT or ROLLBACK) so a failure part-way never leaves half-written data, which is how a relational database keeps its data integrity through constraints, foreign keys and atomic commits.

Limits to know. A Cloud SQL instance scales up (bigger tier) rather than out for writes, so one primary has a ceiling, and read replicas only help reads. For global scale or horizontally scaled writes, look at Spanner and AlloyDB. You also get no operating-system access or true superuser, and moving away later means a dump and restore, which is a form of lock-in you should plan for.

The next block is a "guestbook" in the SQLite stand-in below. It shows the pattern of an application writing entries and then retrieving information with a query.

SQL A guestbook table

Press Run (or Ctrl + Enter) to see the result here.

Practicing the SQL

Cloud SQL speaks ordinary SQL, so queries you write for MySQL or PostgreSQL work there. The stand-in below runs in your browser on SQLite (via sql.js), not on Cloud SQL, so it matches Cloud SQL's behavior only for common, portable SQL. It shows the queries a shop application might send, and an index that a read-heavy workload would want. Learn the language itself in the SQL tutorial.

SQL Orders per customer (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

An index speeds up reads on a column but slows writes slightly and uses storage. The next stand-in asks SQLite how it will run a query before and after an index. Real engines have their own EXPLAIN output, so the wording differs.

SQL Before and after an index

Press Run (or Ctrl + Enter) to see the result here.

What is real and what is the stand-in: the gcloud commands and the feature descriptions are real Cloud SQL concepts but cannot run here; the two SQL examples run on a different engine (SQLite in your browser) only to practice portable SQL.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does high availability on a Cloud SQL instance protect against?

    HA keeps a synchronized standby in another zone and fails over to it. It does not undo bad writes; backups and point-in-time recovery do that.
  2. 2. Why can a read replica return slightly old data?

    A replica applies changes after the primary has committed them, so it can briefly be behind.
  3. 3. Which approach avoids managing IP allow-lists and certificates when connecting?

    The proxy and connectors create an encrypted, IAM-authenticated connection, so you do not rely on open networks.
  4. 4. Which feature lets you restore the database to the minute before a bad deployment?

    PITR replays the transaction logs to a chosen moment. It must be enabled before the mistake happens.

Summary

  • Cloud SQL is managed MySQL, PostgreSQL and SQL Server: Google runs the servers; you design the schema and queries.
  • An instance is sized by vCPUs, memory and storage, and bills while it exists.
  • HA survives zone failures, replicas scale reads, backups and PITR undo mistakes: three different tools.
  • Prefer private IP, the Auth Proxy or connectors, IAM authentication and Secret Manager over open networks and shared passwords.
  • Use connection pools, and delete practice instances.

Next: Firestore and Bigtable