Scheduling Google SQL Instances

During this hard time, it is important to reduce the cost of our server. Currently, we have 3 running staging/development SQL instances. All of them use PostgreSQL 9.6. We already decrease the vCPU and memory to save some money, but we don't stop just right there. This article will try to explain the details of how we make a "schedule" to start and stop our staging of Google Cloud SQL instances.

Image from unsplash.com

The key concept is pretty straightforward. You only run the SQL instance when you work on it. Just like what we do on our Google Cloud Compute Engine on the previous post. Unfortunately, there is a small issue: Google Cloud SQL doesn't provide an "SDK-way" to start or stop the instance. So the workaround is using the "gcloud" command under the Compute Engine within the very same Google Cloud project.

UPDATE:
2021-Mar-17 Google has provided the way to stop or start the Cloud SQL instance through their Cloud SQL Admin API. See this article for detail.

Here is the breakdown of what we aim to do.

• Create a Compute Engine, and enable the Google Cloud SQL API access scope.
• Edit the cronjob that calls the gcloud command.
• Final check.

Let's begin.

Manage the Compute Engine

We can use any kind of machine type or specification. The Compute Engine should run first before running the gcloud command. So, if we have scheduled the Compute Engine, it should incite a few minutes earlier, just to make sure it has enough time to run the command. As for me, I set it to 5 minutes span.

Another main task to do is to enable the Google Cloud SQL API access scope. We should edit the VM instance, which requires the instance for being stopped. See the following screenshot.

Cloud API access scopes

Create a Cronjob

Go to your machine and edit the cronjob using the following command.

crontab -e

We are gonna edit the cronjob configuration file using vim as the text editor. If you are not familiar with vim, get used to it. At least, learn how to exit the application. :D

At the end of the file, add the following 2 lines.

55 8 * * 1-5 gcloud sql instances patch my-precious-db-staging --activation-policy ALWAYS
55 17 * * 1-5 gcloud sql instances patch my-precious-db-staging --activation-policy NEVER

The first line is used to start our SQL instance. Let's break it down.

55 8 * * 1-5: This indicates the exact time or schedule to start our SQL instance. Because every index starts from zero (0), and the first day of the week is Sunday, so 1 means the 2nd day, 2 means the 3rd day, and so on. So our line means, "Every 2nd to 6th day of the week (working days), at 8:55 AM". To know more details about the cron schedule expression, please visit crontab.guru.

gcloud sql instances patch my-precious-db-staging --activation-policy ALWAYS

The command above is used to start the SQL instance. Change "my-precious-db-staging" with your SQL instance ID.

gcloud sql instances patch my-precious-db-staging --activation-policy NEVER

The command above is used to stop the SQL instance. Instead of using "ALWAYS", we use "NEVER".

You can read more about the command on Google Cloud SQL documentation.

Final Check

Before editing the cronjob, we could check the gcloud command right inside the console. If you get the following error message:

PERMISSION_DENIED: Request had insufficient authentication scopes

Please make sure to enable the Google Cloud SQL API access scope for the compute engine (second step above).

Finally, we should do the final check at 08:55 AM and 5:55 PM (17:55) at the Cloud SQL instance dashboard.

Thank you for reading. Don't hesitate to give a comment if you have a better approach.