Of everything AlpinaShop keeps on its physical server, the tienda database is the most valuable and the most fragile. It holds the orders, the customers, the stock and the prices. It runs on a single PostgreSQL that Marta installed years ago, with no replica, no failover, a nightly pg_dump nobody has ever tried to restore and a major version upgrade outstanding for two cycles because "it is not a good moment". If that disk fails on a Saturday in October, AlpinaShop does not sell.

Cloud SQL is Google Cloud's managed relational database service: PostgreSQL, MySQL and SQL Server with automatic backups, regional high availability, read replicas, managed patching and point-in-time recovery. In this lesson we will migrate the tienda database to the alpinashop-pedidos instance, connect the Flask application securely and leave Lucía a replica where she can run her reports without punishing the shop.

1. What Marta stops doing: managed versus self-managed database
2. Engines, editions and sizing
3. Creating the alpinashop-pedidos instance
4. Databases, users and passwords
5. Regional high availability and failover
6. Read replicas for Lucía's reports
7. Backups, retention and point-in-time recovery
8. Connectivity: public IP, private IP and the Auth Proxy
9. Connecting the Flask application
10. Migrating the current data
11. Scheduled maintenance and windows
12. Vertical scaling and limits
13. When Cloud SQL falls short: AlloyDB and Spanner

The most honest way to explain the value of Cloud SQL is to list the tasks and see who does them.

The last two rows are the price to pay: you lose superuser access and full control of the engine. In Cloud SQL there is no SSH to the machine, you cannot install just any extension and you cannot touch every parameter. For the vast majority of applications — AlpinaShop included — it is an excellent trade. If your product depends on an exotic extension or a convoluted pg_hba.conf, you will have to check compatibility beforehand or stay on Compute Engine.

There is also a change in cost that is worth stating plainly: a VM with PostgreSQL installed is cheaper than an equivalent Cloud SQL instance. What you buy with that difference is Marta's time and the removal of a risk that is not covered today. If you put a price on an afternoon of downtime in the middle of the campaign, the sums work themselves out.

Available engines:

AlpinaShop already uses PostgreSQL, so the choice is obvious: PostgreSQL 16, the same family as its current installation, with no changes to the application.

Editions. Cloud SQL comes in two editions that are worth distinguishing:

For AlpinaShop's start, Enterprise is enough. If in future the shop cannot tolerate even a minute of interruption, Enterprise Plus is the escape route without changing service.

Sizing. You choose a machine type just as in Compute Engine. Practical criteria for a database:

• Memory comes first. The goal is for the "hot" dataset (indexes and frequently queried tables) to fit in RAM. A database that constantly reads from disk is slow no matter how much CPU it has.
• The disk determines the IOPS. As we saw in 02-01, on persistent disks performance grows with size. A 10 GB disk has very few IOPS even if your data only takes up 8 GB.
• Enable automatic storage growth. A database stopping because the disk is full is an avoidable incident.
• Start modest and scale. Changing the machine type is an operation of minutes with a restart.

AlpinaShop's tienda database takes up around 12 GB. We choose db-custom-2-7680 (2 vCPU, 7.5 GB of RAM) with a 50 GB SSD disk and automatic growth: plenty of room so that the disk does not limit the IOPS, and enough RAM to cache the active set.

This command concentrates nearly all the decisions in the lesson, so let us break it down:

• --region=europe-west1 (not a zone): Cloud SQL is a regional service. The primary instance lives in one zone, but the choice is expressed at region level.
• --availability-type=REGIONAL enables high availability: a standby instance in another zone with synchronous replication. It is the flag that turns a single point of failure into a fault-tolerant architecture.
• --storage-auto-increase: the disk grows on its own when it fills up. It never shrinks, so keep watching its growth.
• --backup-start-time=03:00 (UTC): a daily backup at a low-traffic hour.
• --enable-point-in-time-recovery + --retained-transaction-log-days=7: keeps the WAL so you can restore to any instant in the last 7 days.
• --maintenance-window-*: Google will apply updates on Sundays at 4:00 UTC, not on a Tuesday at 11:00.
• --maintenance-release-channel=production: you get the versions that have already matured, not the most recent ones.
• --database-flags: the engine's parameters are tuned here. log_min_duration_statement=1000 logs every query taking more than a second, which is the best performance diagnosis tool there is and costs nothing to set up.

Creation takes a few minutes. When it finishes:

An instance is the server; the databases and users live inside it.

Always use --prompt-for-password: typing the password on the command line leaves it in the bash history and in the audit logs.

Creating the user does not give it permissions inside the database: that is done with SQL. Connect (section 8) and run:

The ALTER DEFAULT PRIVILEGES clause is the one that is usually missing: without it, the permissions apply only to the tables that existed at that moment, and any table created afterwards is inaccessible to the application. It is a classic source of errors after a deployment.

Cloud SQL also supports IAM authentication: instead of passwords, users and service accounts authenticate with their Google Cloud identity and a short-lived token. It is the recommended option because it removes passwords from the system. We note it here and develop it in 03-04.

With --availability-type=REGIONAL, Cloud SQL keeps a standby instance in another zone of europe-west1, with synchronous replication of the disk: every write is confirmed in both zones before the commit is accepted.

What this means in practice:

• The standby instance does not serve traffic. It is not a read replica: it exists only to take over. You pay for it and you do not use it, and that is precisely the insurance you are buying.
• Failover is automatic and keeps the connection address. The application does not change its configuration; it only sees a drop in connections of roughly a minute (less on Enterprise Plus).
• The application must know how to reconnect. This is the point people forget: if your connection pool does not retry, failover turns into an error visible to the customer. Configure retries in the pool (we will look at this in section 9).
• It roughly doubles the cost of the instance. It is the clearest economic decision in this lesson: for alpinashop-prod yes; for alpinashop-dev, no.

It can genuinely be tested, and it must be tested:

Run this command in a test environment while the application is running and watch how long it takes to recover. A high availability plan that has never been exercised is a hypothesis, not a plan.

Lucía runs aggregation queries over the orders: sales by category, monthly trends, products that are not moving. Executed against the primary database, they compete for CPU and memory with the shop, and a heavy report on a campaign Saturday can slow down the checkout.

The immediate solution is a read replica: an asynchronous copy that accepts SELECT queries.

Characteristics you need to be clear about:

• Replication is asynchronous: the replica runs slightly behind (normally milliseconds or a few seconds). For reports that is irrelevant; for reading an order right after creating it, it will not do.
• It is read-only. Any write fails.
• It can have a different machine type from the primary: if Lucía's reports need more memory, you can give it to her without touching production.
• It can be in another region, which additionally serves as a geographic disaster recovery plan.
• It can be promoted to a standalone instance with gcloud sql instances promote-replica. It is an irreversible operation that breaks replication, useful in a real recovery or to create a test environment with current data.

Keep an eye on the replication lag:

In Cloud Monitoring, the relevant metric is database/replication/replica_lag (06-04). An alert when it exceeds a few minutes avoids reports that look right but are out of date.

Where the limit lies. A read replica solves today's problem, but analytical queries against a transactional database will always be inefficient: PostgreSQL stores by rows, and a report aggregating one column over millions of orders reads whole rows. The definitive solution for Lucía is to export the data to BigQuery, with columnar storage and an engine designed to aggregate. That is module 4. For now, the replica is the correct and cheap answer.

We already configured daily backups with 14 days of retention and 7 days of PITR when creating the instance. Let us look at what each mechanism involves.

Automatic backups. Daily, incremental, stored outside the instance and with no appreciable performance cost. They are not pg_dump: they are copies of the storage.

That last command is a habit worth its weight in gold: before any schema migration, a manual backup with an explicit description.

Restoring. There are two very different scenarios:

Almost always you want the second. Restoring over the original instance destroys the current state, including the data written after the incident, which you may want to keep. Cloning to a new instance lets you compare, extract only what you need and decide calmly.

Point-in-time recovery (PITR). It is the difference between "I recover last night's backup" and "I recover the exact state at 09:14:59, one second before the script deleted the price table". It works by combining the last backup with the transaction logs (WAL). It requires PITR to be enabled before the incident; enabling it afterwards does not let you travel back in time.

A realistic example of use: at 09:15 Dani runs an UPDATE without a WHERE on productos. At 09:20 it is spotted. The correct sequence is to clone to alpinashop-pedidos-restaurada with --point-in-time at 09:14:59, check there that the prices are correct, export only the affected table and reimport it into production. The shop does not stop and not a single sale from those five minutes is lost.

Logical exports. As well as the managed backups, it is worth exporting periodically to Cloud Storage in SQL format: it is useful for migrating, for taking the data outside Google Cloud and as a copy independent of the service.

Managed backups are tied to the instance: if someone deletes the instance, they are deleted with it (final backups aside). An export in a bucket with versioning and retention is a different layer of protection. This is, incidentally, why we devoted the previous lesson to Cloud Storage before this one.

Connecting to Cloud SQL is where most people get stuck, so let us take it step by step.

Authorised networks (public IP). Only for administrative access, and never 0.0.0.0/0:

Cloud SQL Auth Proxy. This is the key piece. A binary that runs alongside your application, listens on localhost and forwards connections to Cloud SQL over a TLS tunnel, authenticating with your Google Cloud credentials. Advantages: the instance needs no public IP, there are no certificates or IP lists to manage, and access is controlled with the IAM role roles/cloudsql.client.

That project:region:instance identifier is the instance's connection name, and you will need it constantly:

With the proxy running, any PostgreSQL client points at localhost as if the database were on your machine:

And if you are in Cloud Shell, you do not even need to download it:

In production you do not run a separate proxy process: you use the Python connector, which does the same thing inside the application's process.

Aspects of the code you need to understand well:

• pool_pre_ping=True is the flag that makes you survive a failover. Before handing over a connection from the pool, it checks that it is still alive; if the failover closed it, it discards it and opens another. Without this, after a failover the application returns errors until it is restarted.
• pool_recycle=1800 avoids connections expired by the idle timeouts of network intermediaries.
• pool_size has to be multiplied by the number of processes. If gunicorn starts 4 workers with pool_size=5, that is 20 connections per instance. With 10 MIG instances at an autumn peak, 200 connections: exactly the max_connections we configured. This calculation is the number one cause of outages through connection exhaustion, and it has to be done beforehand, not afterwards.
• Parameterised queries (:sku). Never concatenate strings into SQL: that is the door to SQL injection.
• The password comes from an environment variable, and in production that variable is filled in from Secret Manager (03-06), never from a file in the repository.

If the number of connections becomes a problem, the usual pattern is to put PgBouncer in front, or to use Cloud SQL Enterprise Plus with its built-in connection manager.

The moment has come to move the real tienda database. For 12 GB, the simplest and most controlled route is pg_dump plus an import from Cloud Storage.

Step 1: dump on the source server.

--no-owner and --no-acl stop the dump trying to assign owners and permissions to users that do not exist in Cloud SQL. It is the most frequent cause of errors during an import.

Step 2: upload the dump to the bucket.

Step 3: authorise Cloud SQL to read the bucket. The instance has a service account of its own, and it needs explicit permission:

This step is always forgotten and produces an unhelpful permissions error. Remember it: the Cloud SQL instance is just another identity, and without permission it does not read your bucket.

Step 4: import.

Step 5: verify. Never take a migration on trust without cross-checking:

That final ANALYZE matters: after a bulk import the planner's statistics are empty and queries can be absurdly slow until they are recalculated.

The interruption problem. This procedure means stopping writes while dumping and importing: for 12 GB, perhaps 30-40 minutes with the shop in read-only mode. That is acceptable if it is done in the small hours.

When it is not acceptable, the tool is Database Migration Service (DMS): it creates a continuous replica from your source PostgreSQL into Cloud SQL, keeps it synchronised while the shop carries on working and allows a final cut-over of seconds. It requires the source to have logical replication enabled and connectivity with Google Cloud. For a migration of tens or hundreds of GB with an availability requirement, it is the right option; for AlpinaShop's 12 GB in the small hours of a Sunday, pg_dump is simpler and sufficient.

Google updates the engine and the infrastructure periodically. Those updates involve a brief restart, and you want to decide when it happens.

The last three flags are especially valuable for AlpinaShop: they define a maintenance deny period that blocks updates during the autumn campaign. Google will postpone them until after 15 November.

Good practice: keep alpinashop-dev on preview with the window one day earlier than production. That way changes reach development first and you have room to react.

Cloud SQL scales vertically for writes: there is no way to spread writes across several instances.

Limits and considerations you need to know:

• The disk grows but does not shrink. If you enable automatic growth and an anomalous workload inflates the disk to 2 TB, you will keep paying for 2 TB. The only way out is to export and recreate.
• Connections are a scarce resource. max_connections depends on the instance's memory; each PostgreSQL connection is a process with its own memory.
• There is no superuser. Cloud SQL's postgres user has broad privileges but is not SUPERUSER. Some extensions and operations are not available.
• Extensions are limited to the supported list. Check yours before migrating.
• Scaling has a ceiling. The largest machine type available marks the physical limit of your database. If you get close to it, that is a sign you need a different architecture.

Three signs that Cloud SQL has become too small for you: writes saturate the largest instance available; you need active writes in several regions simultaneously; or analytical queries against the same database are unavoidable and very heavy.

For AlpinaShop, Cloud SQL comfortably covers the foreseeable horizon: a Spanish shop with seasonal peaks is a long way from those limits. The natural growth path would be, first, to offload the analytics to BigQuery (module 4); then, if transactional volume demanded it, AlloyDB, which allows migration with minimal changes thanks to its PostgreSQL compatibility. Spanner is studied in detail in lesson 02-06, within the landscape of non-relational and distributed databases.

• Creating the instance without high availability, "we will enable it later". Switching to REGIONAL afterwards is possible, but it means a restart and tends to be postponed indefinitely.
• Trusting backups that have never been restored. Schedule a quarterly test restore to a cloned instance.
• Enabling PITR after the incident. It is no use: you have to have it beforehand.
• Restoring over the original instance during an incident. Clone to a new instance and decide calmly.
• Forgetting ALTER DEFAULT PRIVILEGES. Tables created after the GRANT are left inaccessible.
• Not granting read access on the bucket to the instance's service account before importing.
• Importing without --no-owner --no-acl. It fails because of users that do not exist at the destination.
• Not running ANALYZE after the import. Absurdly slow queries because of empty statistics.
• Sizing the pool without multiplying by processes and instances. Connection exhaustion at the worst possible moment.
• Omitting pool_pre_ping. The application does not survive a failover.
• Exposing the public IP with broad authorised networks. Use the Auth Proxy or the connector.
• Tip: enable log_min_duration_statement from day one. It is free diagnostics.
• Tip: take a manual backup with a description before every schema migration.
• Tip: define a maintenance deny period covering the autumn campaign.
• Tip: export periodically to a bucket with versioning, as a copy independent of the instance's lifecycle.

1. Create alpinashop-pedidos-dev with PostgreSQL 16, db-g1-small or db-custom-1-3840, in europe-west1, with zonal (not regional) availability and daily backups with 7 days of retention. Justify why we do not put high availability in development.
2. Create the tienda database and the users app_catalogo and informes_lectura.
3. Connect with gcloud sql connect and create the tienda schema with a productos table (sku, nombre, precio, stock, activo).
4. Grant app_catalogo read/write permissions and informes_lectura read-only, including the default privileges.
5. Check from informes_lectura that an INSERT fails and a SELECT works.

1. Insert three products and note the exact time.
2. Simulate an accident: DELETE FROM tienda.productos; with no WHERE.
3. Clone the instance to an instant before the deletion.
4. Verify in the clone that the data is there and explain how you would return only that table to the original instance.
5. Delete the clone and work out what it would have cost you to keep it running for a month.

1. Write a minimal Flask application that connects with the Python connector and exposes /productos.
2. Configure the pool with pool_pre_ping, pool_recycle and a pool_size justified for 4 gunicorn workers and up to 6 instances.
3. Calculate the total number of connections in the worst case and compare it with max_connections.
4. Explain what would happen during a failover with and without pool_pre_ping.
5. State where the password should come from in production and why not from a variable in the code.

Solution 1

In development we do not put high availability because it doubles the cost to protect us against a risk that has no consequences in development: if the instance goes down for a few hours, nobody loses a sale. High availability is paid for where there is revenue at stake. It is the same logic of allocating resources that we applied with the centro-coste labels in 01-04.

Solution 2

To return only that table to the original instance, you export it from the clone and import it into production, without touching the rest:

This is the advantage of cloning instead of restoring on top: you recover exactly the damaged table and keep all the correct data written after the incident.

A clone is a complete instance and is billed as such from the moment it exists: a db-custom-1-3840 with 20 GB of SSD comes to tens of dollars a month. Leaving forgotten rescue clones running is a very common silent expense; delete them as soon as the recovery is over.

Solution 3

3. The worst-case calculation:

With max_connections=200 there is room, but it is tight: 32 connections are left for maintenance tasks, Lucía's reports and administrative sessions. If the MIG could reach 10 instances, that would be 280 connections and the database would reject them. The options are to lower pool_size to 3, increase the instance's memory to raise max_connections, or introduce PgBouncer.

4. During a failover, the pool's open connections are left broken. Without pool_pre_ping, SQLAlchemy hands out those dead connections and every request fails with a connection error until the pool recycles or the application restarts: minutes of 500 errors visible to the customer. With pool_pre_ping, every connection is checked before use; broken ones are discarded and new ones opened transparently, and the user only perceives slightly higher latency for a few seconds.

5. In production the password must come from Secret Manager (lesson 03-06), injected as an environment variable or read at start-up with the application's service account. Never in the code or in the repository, because it would stay in the Git history forever, would be visible to anyone with access to the repository and could not be rotated without redeploying. The optimal option is Cloud SQL IAM authentication outright, which removes the password.

The tienda database has stopped being AlpinaShop's weak point. You have seen in a concrete table which tasks Marta stops doing — patches, backups, failover, replicas, encryption — and what is given up in exchange: the superuser and full control of the engine, a trade that for this application is clearly favourable. You have chosen PostgreSQL 16, the Enterprise edition and a reasoned sizing in which memory and disk size matter more than CPU. You have created alpinashop-pedidos in europe-west1 with a single command that concentrates the important decisions: regional high availability, daily backups with 14 days of retention, 7 days of PITR, a maintenance window early on Sunday and slow query logging from the very first minute.

You have created the tienda database with differentiated users and minimal permissions, without forgetting ALTER DEFAULT PRIVILEGES. You have understood that the high availability standby instance does not serve traffic and that its value lies in the automatic failover that keeps the connection address, and that this failover is only transparent if the application reconnects — hence pool_pre_ping. You have created a read replica so that Lucía's reports do not compete with the shop, knowing that it is a correct but temporary solution, because real analytics lives in BigQuery. You have configured backups, you have learned to clone to a point in time instead of restoring destructively, and you have seen why an export to a bucket with versioning is a different layer of protection from the managed backups. You have compared public IP, private IP, Auth Proxy and the Python connector, and you have connected the Flask catalogue with a well-sized pool and parameterised queries. And you have carried out the real migration with pg_dump, an import from the bucket, verification and ANALYZE, with Database Migration Service noted down for when the interruption is not acceptable.

AlpinaShop now has its compute on virtual machines, its images in a bucket and its data in a managed database. But Marta is still maintaining operating systems, instance templates and startup scripts. In 02-04, App Engine, we will see what happens when that layer is removed too: what a PaaS is, how the standard and flexible environments differ, how a whole application is described in a twenty-line app.yaml, how versions are deployed and traffic split to do a canary, how scaling is configured — including scaling to zero — and how the same Flask application reaches Cloud SQL and Cloud Storage without anyone administering a single server.