Databases upgrade made easy with in-place major version upgrades on Cloud SQL

Using Cloud SQL, our fully managed relational database service is a powerful way to streamline your database operations and focus on innovation. Cloud SQL handles the complexities of database administration for you, delivering a robust and secure relational database platform that's scalable and highly available, all the while simplifying management tasks and reducing operational costs.

As an open and fully managed database service, Cloud SQL supports multiple versions of the database engines it offers, allowing you to choose the version of MySQL, PostgreSQL, or Microsoft SQL Server that best suits your needs. While Cloud SQL offers this flexibility to maintain older versions of database engines, there are substantial advantages to staying current with the latest releases. Newer versions often bring performance enhancements, security upgrades, and expanded feature sets, empowering you to optimize your applications and safeguard your data. To maximize the benefits of Cloud SQL and ensure the long-term stability and security of your applications, it's essential to move away from database engine versions that have reached their end of life (EOL).

In this blog, we will discuss key advantages as well as best practices to transition to a newer version of MySQL and PostgreSQL by leveraging Cloud SQL’s in-place major version upgrade feature. We will also discuss strategies to successfully perform major version upgrade on your primary and replica instances..

What is Cloud SQL’s in-place major version upgrade feature?

Cloud SQL's in-place major version upgrade feature is a built-in functionality that allows you to upgrade your MySQL or PostgreSQL database instance to a newer major version directly (a.k.a. in-place upgrade) within the Cloud SQL platform. This removes the need for manual data migration, complex configuration changes, and the associated lengthy downtime. Further, one of the biggest advantages of this approach is that you can retain the name, IP address, and other settings of your current instance after the upgrade.

We recommend you plan and test major version upgrades thoroughly. One of the strategies to test includes cloning the current primary instance and performing a major version upgrade on the clone. This will help iron out issues upfront, and give you the confidence to perform a production upgrade.

Cloud SQL's major version upgrade feature varies slightly between MySQL and PostgreSQL. Please see the dedicated sections below for detailed information.

Cloud SQL for MySQL

MySQL community version 5.7 reached end of life in October 2023. If you are still running MySQL 5.6 and 5.7, we recommend upgrading to MySQL 8.0, which offers next-generation query capabilities, improved performance, and enhanced security. For example:

• MySQL 8.0's instant DDL drastically speeds up table alterations while allowing concurrent DML changes.
• InnoDB received optimizations for various workloads, including read-write, IO-bound, and high-contention scenarios.
• SKIP LOCKED and NOWAIT options prevent lock waits.
• Window functions in MySQL 8.0 simplify query logic, and CTEs enable reusable temporary result sets.
• MySQL 8.0 enhances JSON functionality and adds robust security features.
• Replication performance is significantly improved, leading to faster data synchronization. Parallel replication is enabled by default.
• New features like descending indexes and invisible indexes contribute to further performance enhancements.

Please click here for more details.

What should I do to upgrade to MySQL 8.0?

You can leverage Cloud SQL’s major version upgrade feature to upgrade to 8.0. Pre-check has already been incorporated into the workflow but you have the option to run it separately as well. You can use the Upgrade Checker Utility in the MySQL shell to run a pre-check. Before upgrading, review your current primary/replica topology and devise a plan accordingly.

Upgrade using major version upgrade: If you have a primary instance with no read replicas, you can upgrade the instance in-place with Cloud SQL’s major version upgrade feature. MySQL allows replication from lower to higher major versions. This is beneficial if you have read replicas, as you can upgrade your read replicas prior to upgrading the primary instance.

The diagram below shows the stages of a major version upgrade.

Note: In this scenario, IP addresses will be maintained.

Upgrade using cascading replicas: You can leverage cascading replicas along with major version upgrades for the scenarios below. This approach allows you to:

• fall back to the old primary with its full topology intact
• set up an entire new stack in a new zone or a new region in addition to the current deployment

For example, Everflow, a Google Cloud customer that makes a partner marketing platform, leveraged cascading replicas and in-place major version upgrade to orchestrate a smooth MySQL upgrade to 8.0, with minimal downtime or disruption for their users.

To perform the major version upgrade using cascading replicas, please refer to the diagram and perform the following steps.

1. Create a read replica from the current 5.7 primary instance either to an existing or new zone/region.

2. Upgrade the replica to 8.0 via the major version upgrade feature.

3. Enable replication and create replicas as needed under the new 8.0 read replica.

4. Prep the application for IP address changes in advance to minimize downtime.

5. Route traffic and prep the application for switching over to the new master. Cloud Load Balancing can help do this efficiently.

Note: Consider this a transition period and try to keep the time for version mismatch short.

6, When you’re ready, promote the 8.0 read replica.

7. Delete the old primary MySQL 5.7 instance.

Note: As mentioned earlier, the above process requires IP address changes to the application. Ideally, IP address changes should be done before promoting the new read replica to minimize disruption when the cutover is performed.

Cloud SQL for PostgreSQL

PostgreSQL updates major versions yearly with a five-year support window. PostgreSQL 11 reached end-of-life in November, 2023. While you can upgrade to PostgreSQL 12 or 13, considering PostgreSQL’s end-of-life policy, we recommend upgrading to PostgreSQL 14 or later versions. PostgreSQL 14 and subsequent versions introduce several new features and enhancements that provide significant benefits. Here are some of the highlights:

• Performance improvements including parallel query execution for GROUP BY and JOIN operations, and faster VACUUM and REINDEX operations.
• Enhancements to logical replication with support for filtering, row-level replay, and replication to multiple destinations, making it more flexible and scalable for various use cases.
• Security enhancements and advanced features like improved JSON functionality and enhanced table partitioning.

For additional details click here.

Considerations and strategies for upgrading to PostgreSQL 14+

If your database is on an older version, we recommend upgrading to a newer version. There are different strategies that can be used to accomplish this. Since PostgreSQL does not support cross-version replication, upgrading the primary instance while the instance is replicating to the read replicas is not possible. In addition, upgrading read replicas prior to the primary instance may not be feasible. Hence, the upgrade flow involves upgrading primary instances first. Before proceeding, replication needs to be disabled for existing replicas. After the primary has been upgraded, read replicas can be upgraded one by one and replication can be re-enabled. Alternatively, you can drop read replicas and recreate them after the primary instance has been upgraded.

Upgrade via MVU: We recommend leveraging Cloud SQL’s major version upgrade feature for upgrading to newer versions of PostgreSQL (14.0+). With in-place upgrades, you can retain the name, IP address, and other settings of your current instance after the upgrade. The Cloud SQL for PostgreSQL in-place upgrade operation uses the pg_upgrade utility. Please make sure to test upgrades on beta or staging environments first, or clone the instance as mentioned above before you proceed. Cloud SQL for PostgreSQL major version upgrade performs pre-validations steps and backups on your behalf.

Unlocking higher availability and price/performance with Enterprise Plus

Upgrading to MySQL 8.0 or PostgreSQL 14 or 15 unlocks the ability to perform a quick in-place upgrade to Cloud SQL Enterprise Plus Edition, which is a powerhouse of advanced functionality. Cloud SQL Enterprise Plus Edition offers:

• 99.99% availability SLA inclusive of maintenance
• Near-zero downtime planned maintenance with <10s instance downtime
• Up to 3x faster throughput with the optional Data Cache and faster hardware for larger scale and optimal performance
• Support for larger machine configurations with up to 128 vCPU and 864 GB of memory, compared to 96 vCPU and 624 GB in Enterprise edition
• Support for up to 35 days of Point In Time Recovery (PITR) compared to seven days in Enterprise edition

In-place upgrade to Cloud SQL Enterprise Plus edition takes just a few minutes with a downtime of less than 60 seconds. To learn more, click here.

Final thoughts

Let’s revisit why upgrading makes sense: It’s an investment in the security, performance, and capabilities of your database infrastructure. By embracing the latest advancements, you can safeguard your data, optimize your applications, and empower your organization. With Cloud SQL's in-place major version upgrade feature, you can perform a streamlined and efficient upgrade of your databases, ensuring a smooth transition to the latest version. Click here to get started.