Design, migrate, and run scalable, highly available databases on Google Cloud across relational and NoSQL services, and track your readiness for the Professional Cloud Database Engineer exam.
Prepare and test your skills
Prepare and test your skills
Assessing version compatibility and pre-upgrade requirements is the process of confirming that a Cloud SQL (opens in a new tab) instance can move safely to a target major version. The assessment checks the allowed upgrade path, reviews release notes for breaking changes and flag changes, and creates recovery points and staging tests before production downtime begins. A complete assessment prevents failed upgrades, application errors, and data loss.
A valid upgrade target is a major version that Cloud SQL lists as upgradable for the specific instance. Run gcloud sql instances describe INSTANCE_NAME and locate the upgradableDatabaseVersions section, or call the instances.get method of the Cloud SQL Admin API. Each entry shows majorVersion for the target major, name for the database version string, and displayName for the readable version name, while Cloud SQL automatically provisions the instance on the default minor version.
The upgrade path depends on the database engine. Cloud SQL for MySQL (opens in a new tab) does not support skipping major versions, so an instance must move one major at a time and cannot move directly from MySQL 5.7 to MySQL 8.4. Cloud SQL for SQL Server (opens in a new tab) allows a combined change where the major version and the edition change together, such as SQL Server 2017 Standard to SQL Server 2019, 2022, or 2025 Enterprise, with license pricing reviewed before the change. Confirming the exact target first avoids requesting an unsupported in-place upgrade.
Release-note analysis is the review of target-version notes to find incompatible changes, discontinued features, and changed defaults that may require application, schema, or setting changes. New major versions introduce incompatible changes, so the target release notes must be reviewed before the upgrade can proceed. For PostgreSQL, review versions 10 through 18; for MySQL, review 5.7, 8.0, 8.4, and 9.7; for SQL Server, review discontinued features and breaking changes.
This review also covers defaults and compatibility behavior that survive the upgrade. In Cloud SQL for SQL Server, existing database compatibility levels are preserved after the engine upgrade, so applications continue to run with earlier Transact-SQL and Query Optimizer behavior until the compatibility level is raised separately. In Cloud SQL for MySQL, some system variable defaults change by the MySQL community, such as character_set_server changing from utf8 in 5.6 and 5.7 to utf8mb4 in 8.0. If the old behavior is required, the database flag must be changed back manually after the upgrade.
Verifying flags, extensions, and instance settings ensures that the instance passes the automatic pre-upgrade check that Cloud SQL runs when an upgrade is requested. The upgrade request fails with guidance if the instance is not ready, so incompatible settings must be fixed beforehand. Confirmation includes the Cloud SQL Owner or Cloud SQL Admin role, sufficient disk space, and supported flags and extensions.
LC_COLLATE value for the template and postgres databases must be en_US.UTF8, or the major version upgrade fails.chkpass when moving to PostgreSQL 11 or later, and manually update PostGIS and related extensions while clearing leftover objects and deprecated-function warnings verified with SELECT PostGIS_full_version();.datallowconn field shows t for allowed and f for blocked.partial_revokes=ON as the default in MySQL 8.0, which removes wildcard characters in database GRANT commands and requires updating user privileges to full schema names.Recovery points and staging validation provide a safe fallback and proof that the application works on the new version. Cloud SQL creates two on-demand upgrade backups around the upgrade, where the pre-upgrade backup is taken immediately before the upgrade starts and the post-upgrade backup is taken immediately after new writes are allowed. The pre-upgrade backup supports restoring the instance to its state on the previous version, while both backups persist as On-demand backups until deleted with the instance.
Validation follows an ordered sequence that reduces production risk:
Exam tip: Cloud SQL for MySQL cannot skip a major version in one upgrade, so move one major at a time while PostgreSQL and SQL Server targets are selected from the instance-specific upgradable versions list.
Upgrade methodologies are the planned ways to move a managed database to a new major version while keeping applications running. In Google Cloud, the two main paths are an in-place version upgrade of the existing Cloud SQL (opens in a new tab) instance or AlloyDB for PostgreSQL (opens in a new tab) cluster, or migrating data to a new instance with a new version. In-place upgrades keep the same instance name, IP address, and settings, so application connection strings do not change and data files are not moved. Migrating data creates a separate target that applications must cut over to, which can allow more testing but usually means more coordination and longer cutover work.
In-place upgrades in Cloud SQL update the existing primary instance to a supported later major version without creating a new instance. The operation uses the engine upgrade mechanism for each engine, such as pg_upgrade for PostgreSQL, the MySQL server upgrade process or mysql_upgrade for older MySQL, and the SQL Server in-place upgrade utility for SQL Server. Because the instance identity is preserved, this path is simpler and faster than a data migration, and in some cases its downtime is shorter.
Careful planning controls risk before a Cloud SQL major version upgrade:
upgradableDatabaseVersions field returned by instance describe or get calls.When a single-instance upgrade starts, Cloud SQL runs an ordered sequence where each step depends on the prior step:
An upgrade that includes replicas extends the Cloud SQL flow from one instance to a primary-plus-replica topology. Cloud SQL first checks the primary instance and all replicas, then makes the primary unavailable, takes the pre-upgrade backup, and stops replication to all replicas. Cloud SQL upgrades the primary instance first, then makes the primary available, restarts replication, takes the post-upgrade backup, and proceeds to upgrade all replicas, including cascading and cross-region replicas. Even if a replica upgrade fails, the upgraded primary instance continues to stay available.
In-place upgrades in AlloyDB for PostgreSQL upgrade the whole cluster rather than a single Cloud SQL instance. An AlloyDB cluster upgrade can take 40 minutes to 48 hours depending on database size, schema size, and the number of read pool instances, while primary instance downtime is typically 20 minutes to one hour and depends mainly on schema complexity. AlloyDB performs pre-upgrade checks first and reports staged progress through states such as IN_PROGRESS, NOT_STARTED, and completed, across stages for prechecks, preparation, primary instance upgrade, and cleanup. Before starting, drop or promote cross-region replicas because in-place upgrades do not support them, and if the cluster is a logical replication source, disable downstream subscriptions and drop logical replication slots for re-creation after the upgrade.
| Approach | Identity and connections | Data movement and downtime |
|---|---|---|
| Cloud SQL in-place upgrade | Keeps instance name, IP address, and settings; no connection-string change | No data-file move; instance becomes unavailable for several minutes during upgrade |
| AlloyDB for PostgreSQL in-place upgrade | Keeps cluster identity; applications reconnect to the same cluster endpoint | No data-file move; longer total operation with 20-minute to one-hour primary downtime |
| Migrate data to a new versioned instance | Creates a new instance with a new name and address; applications must switch connections | Requires export, transfer, and restore or replication; downtime depends on cutover window and data size |
Maintenance controls in Cloud SQL and AlloyDB for PostgreSQL limit when disruptive updates can occur and keep the instance healthy for upgrades. A configured maintenance window controls when disruptive updates may run, while at least 20% free storage should remain for maintenance operations, with a metrics-based alert on disk utilization to warn before space runs low. Cloud SQL automatically provisions the default minor version on upgrade and creates labeled pre-upgrade and post-upgrade on-demand backups that persist until deleted or until point-in-time recovery retention allows deletion.
High availability and application resilience reduce the impact of the required unavailable period. Read replicas can offload read-heavy traffic, a high-availability configuration with regional failover and disaster recovery supports mission-critical recovery time and recovery point objectives, and Cloud SQL Enterprise Plus edition provides increased availability and near-zero downtime planned maintenance. Applications should use connection pools so only pooler-to-database connections drop, limit long-running transactions, and retry failed connections and queries with exponential backoff. After cutover, monitor application health, database connectivity, and CPU, memory, connections, and replication lag, run smoke tests of critical features, and keep a fallback path such as reverse replication of writes back to the source so rollback loses minimal data.
Exam tip: MySQL in Cloud SQL cannot skip major versions, so move one major version at a time, while AlloyDB for PostgreSQL can upgrade directly from POSTGRES_14 to POSTGRES_15, POSTGRES_16, POSTGRES_17, or POSTGRES_18.
Managing maintenance windows and post-upgrade validation is the practice of scheduling disruptive database updates for low-impact times and then confirming that data, connections, and performance are healthy. In Google Cloud, Cloud SQL (opens in a new tab) and AlloyDB use maintenance windows, maintenance timing, and deny periods to control when updates occur. They rely on monitoring plus acceptance testing after the update to confirm operational continuity.
Maintenance timing in Cloud SQL controls how long after a maintenance notification the update is applied to an instance. A notification is sent before each rollout, and instances are then updated in weekly groups so staging can be updated before production. Maintenance timing is set when you configure a maintenance window, and changes made while maintenance is scheduled or in progress apply only to future events.
The maintenance window is the one-hour day-and-time slot in which Cloud SQL schedules the update. In the console you choose Any window or a specific day from Saturday through Friday plus a one-hour interval in local time; with gcloud you set --maintenance-window-day, --maintenance-window-hour in UTC, and --maintenance-release-channel with values preview, production, or week5.
| Timing option | Update occurs after notification |
|---|---|
| Any | Any time, but typically within Week 1 |
Week 1 (preview, canary) | 7 to 14 days |
Week 2 (production, stable) | 15 to 21 days |
| Week 5 | 35 to 42 days |
A common pattern is to set staging to Week 1 and production to Week 2 with the same weekly window, such as Sunday 12:00 AM to 1:00 AM. Data flows from the notification to the staging update first, then to the production update at least seven days later, so the team can run operational acceptance tests in staging and set a deny period for production if staging fails.
A deny maintenance period in Cloud SQL is a block of days, up to 90 days long, during which Cloud SQL does not schedule maintenance. The deny period protects peak business periods, such as November 1 through January 15 for holiday shopping, while the weekly window handles normal low-traffic periods. If no window is set, Cloud SQL uses default windows in the instance time zone: 10 PM to 6 AM Monday through Friday on weekdays, and Friday 10 PM to Monday 6 AM on weekends.
Rescheduling controls (opens in a new tab) let you move a scheduled update up to 24 hours before it occurs, but rescheduling is limited to keep rollouts from overlapping the next release. Rescheduling applies to one instance at a time, and if an operation is already in progress the reschedule waits until that operation completes. You can also reschedule into a deny period or outside the normal window as long as the new time stays within the timing limit.
Exam tip: Week 1 or Week 2 timing allows rescheduling up to 28 days after the original date, while Week 5 timing allows only up to 7 days after the original date.
When you reschedule, you choose among three peer options:
Maintenance notifications in Cloud SQL are opt-in email alerts about upcoming, started, completed, and canceled maintenance. Notifications are sent at least one week before scheduled maintenance to the email address associated with your Google Account, with one notification per instance and no notifications for read replicas. A custom team alias cannot be used, and you must select a maintenance window before you can receive notifications.
Upcoming maintenance can also be viewed in the Google Cloud console without email. The Instances list shows a Maintenance column with the scheduled start time only when maintenance is scheduled, the Instance details page shows the date under Maintenance and Upcoming, and Logs Explorer shows the instance and start time when you filter for maintenance-events. The time in the notification is approximate: maintenance never starts before that time but may start several minutes after.
During the window, AlloyDB and Cloud SQL depend on active monitoring because brief downtime clears caches and drops connections. Application health is monitored through application logs and health checks, database connectivity is confirmed by checking that applications can reconnect after the interruption, and Google Cloud Monitoring is used to observe CPU, memory, connections, and replication lag until they return to expected levels. In very rare cases, time-sensitive maintenance for severe stability or security issues runs outside these settings and counts as downtime against the service level agreement (SLA).
Reducing application impact means making applications resilient to transient errors such as dropped connections and failed in-flight transactions. Connections between the application and a connection pooler are preserved while connections between the pooler and the database are dropped, so the pooler transparently reestablishes database connections instead of the application. Long-running transactions are shortened and queries are rewritten to be smaller, which reduces both downtime and failure rates.
Recovery depends on retry logic and performance diagnosis working together. Applications and poolers build in connection and query retry logic with exponential back-off, where the wait grows from a few seconds on the first retry to up to a minute by the fourth retry so failures clear without overloading the service. Query Insights identifies slow queries to rewrite before maintenance, and scripts can warm the database cache after maintenance because the cache is cleared when the disk and static IP move to the updated virtual machine (VM).
Self-service maintenance lets you accept an update outside the normal scheduled flow when you need it sooner or want to catch up after skipping an update. When self-service maintenance runs on a primary instance with read replicas, Cloud SQL updates all read replicas first and then updates the primary instance as the final step; more than three replicas are divided into groups updated in parallel. After maintenance, the instance keeps the same static IP address, zone, and data on the shared disk, with crash recovery restoring any incomplete transactions to a usable state.
Post-upgrade validation is the ordered check that confirms data integrity, application connectivity, and query performance after a maintenance or major version upgrade. Cloud SQL automatically creates a pre-upgrade backup immediately before the upgrade and a post-upgrade backup immediately after new writes are allowed, both listed as type On-demand until deleted. The pre-upgrade backup supports restore to the previous version, while the post-upgrade backup captures the upgraded state, so validation compares the upgraded instance against a known-good starting point.
Validation moves from function to connections to performance in order:
Cleanup and configuration lock in the validated state and keep the fallback path open. Before deleting a source environment, create a final backup of each source database and consider replicating writes from the target back to the source so rollback causes minimal data loss. For Cloud SQL, configure a maintenance window on the primary instance, do not start a new administrative operation before the previous operation completes, and note that changing some database flags may require an instance restart.
Last updated