Databases

Maintenance made flexible: Cloud SQL launches self-service maintenance

Routine maintenance is critical in the upkeep of any healthy database system. Maintenance involves updating your operating system and upgrading your database software so that you can rest assured that your system is secure, performant, and up-to-date. When you run your database on Cloud SQL, we schedule maintenance for you once every few months during your weekly maintenance window, so that you can turn your attention to more interesting matters. However, from time-to-time, you may find that Cloud SQL’s regular maintenance cadence just doesn’t work for you. Maybe you need a bug fix from the latest database minor version to address a performance issue, or maybe there’s an operating system vulnerability that your security team wants patched as soon as possible. Whatever the case, having the flexibility to update before the next scheduled maintenance event would be ideal.

Cloud SQL has now made self-service maintenance generally available. Self-service maintenance allows you the freedom to upgrade your Cloud SQL instance’s maintenance version to the latest on your own, so that you can receive the latest security patches, bug fixes, and new features on demand. When combined with deny maintenance periods, self-service maintenance gives you the flexibility to upgrade your instance according to your own maintenance schedule. You can perform self-service maintenance using just a single command through gcloud or the API.

Cloud SQL has also launched maintenance changelogs, a new section in our documentation that describes the contents of maintenance versions released by Cloud SQL. For each database engine major version, Cloud SQL publishes a running list of the maintenance versions and the changes introduced in each, such as database minor version upgrades and security patches. With maintenance changelogs, you can know what’s new with the latest maintenance version and make informed decisions about when you need to maintain your instance on your own ahead of regularly scheduled maintenance. Cloud SQL also upkeeps an RSS feed for each maintenance changelog that you can subscribe your feed reader to and receive notifications when Cloud SQL releases new maintenance versions.

How to perform self-service maintenance

Say you’re a PostgreSQL database administrator at a tax accounting software firm named Taxio. During Q1 of each year, you use a deny maintenance period to skip maintenance on your database instance named tax-services-prod in order to ensure your environment is as stable as possible during your busy season. Now that it’s May, you take a closer look at how your PostgreSQL 12.8 instance is operating on the older maintenance version.

After studying the query performance patterns using Query Insights, you realize that your queries that use regular expressions are running far slower than you expected. You check out the PostgreSQL bugs page and you see that other users reported the same performance regression in PostgreSQL 12.8. Fortunately, it looks like the issue was patched in PostgreSQL 12.9 and later minor versions.

You decide you’d like to go ahead and take care of the issue right away ahead of the next scheduled maintenance event, which is a few months away. First, you need to see what maintenance version tax-services-prod is running and what the latest maintenance version available is. You spin up gcloud and retrieve the instance’s configuration information with the following command:

  gcloud sql instances describe tax-services-prod

Cloud SQL returns the following information:

  connectionName: taxio:us-central1:tax-services-prod
createTime: '2019-03-22T03:30:48.231Z'
databaseInstalledVersion: POSTGRES_12_8
maintenanceVersion: POSTGRES_12_8.R20210922.02_00
availableMaintenanceVersions: 
- POSTGRES_12_10.R20220331.02_01

You see that there is a new maintenance version, POSTGRES_12_10.R20220331.02_01, that is much more current than your current maintenance version, POSTGRES_12_8.R20210922.02_00. From the version name, it looks like the new maintenance version runs on PostgreSQL 12.10, but you want to be sure. You navigate to the PostgreSQL 12 maintenance changelog page in the documentation and confirm that the new maintenance version upgrades the database minor version to PostgreSQL 12.10.

Cloud SQL.jpg

You decide to perform self-service maintenance. You enter the following command into gcloud:

  gcloud sql instances patch tax-services-prod \
–maintenance-version=POSTGRES_12_10.R20220331.02_01

Cloud SQL returns the following response:

  The following message will be used for the patch API method.
{"maintenanceVersion": "POSTGRES_12_10.R20220331.02_01", "name": "tax-services-prod", "project": "taxio", "settings": {}}
Patching Cloud SQL instance...working..

A few minutes later, your tax-services-prod is up-to-date, running PostgreSQL 12.10. You run some acceptance tests and you’re delighted to see that the performance for queries with regular expressions is much better.

Learn more

With self-service maintenance, you can update your instance with the latest maintenance version, outside of the flow of regularly scheduled maintenance. You can also use maintenance changelogs to review the contents of new maintenance versions. See our documentation to learn more about self-service maintenance and maintenance changelogs.