Skip to main content

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.

A photographer is editing his work in a small workspace
Image from

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.

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
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

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.

Google 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.


Popular posts from this blog

About Prabowo Murti


Hello. My name is Prabowo Murti. This website is made to write all things about programming, business, religion (especially Islam), my family, minimalism, etc, basically anything that related to my journey in life. I will do my best to write all articles in English, not because I am good at it, but just for practice.

About Me I obtained my bachelor's degree in Computer Science from Gadjah Mada University. I love to read and write. I was born in a small town named Kubu Raya, in West Kalimantan. Now I live in Tangerang, Banten. Let's say I am proud to be a muslim and an Indonesian.

I believe that we can make this world a better place to live by our writings.

If you have any question, feel free to ask by writing an email to prabowo.murti at Gmail or submit your comment (at the end of each article). I also have some digital footprints elsewhere.

Muhajirin Dotnet It is a small company I built from 2008. It also has a subdomain named Blog Belajar Bisnis, captur…

Create a Minimalist Office at Home

This will be the last article of my series on making a home-office workstation wanna-be. The minimalist office is a trend as people tend to work at home instead of spending hours commuting to office nowadays. The reason behind this is that I don't wanna bring my heavy MacBook Pro back and forth from home to office. Besides, I only need a light-computation like typing, editing an image using Canva, or just watching some screencast on YouTube.

We already have a small desk (using dirt-cheap PVC pipes and foldable table) and a DIY tablet stand. For the peripherals, I also have a 10-inch Samsung Galaxy Tab A, a Bluetooth (and silent) keyboard, and a Bluetooth mouse. I often use some keyboard shortcuts, so we can ignore the needs of the mouse.

Let's break them down.
Samsung Galaxy Tab A with 10-inch Display This device is more than enough for typing. If you type, you type with your heart. The 'thinking' part comes after that.

For the editing, change some typos, or replace so…

DIY Table Legs with PVC for Under $5

I now have two pieces of IKEA Lerberg table legs. It's a great product and has a minimalist design. But the price was about $20 each. My pocket is not deep enough, so I decided to make a similar structure but with cheaper materials. Half inch PVC pipes come in rescue. The total cost for the entire project is about IDR 65,000 or still under 5 dollars.

For this DIY project, we need to make two table legs. Each leg needs:

1 x 21.5 cm: middle leg (bottom)
1 x 18.3 cm: middle segment
2 x 17.8 cm: lower legs (right and left)
4 x 10.9 cm: this depends on your top table's width
2 x 47 cm: upper legs (right and left)
1 x 43.9 cm: middle leg (upper)
5 x T-shape fittings
2 x L-shape fittings

Each leg needs about 2.6 metres so in total we need ~5.2 metres. That means 2 x 4 metres half inch pipes. Each 4 m pipe costs about IDR 18,000. Each fitting costs about IDR 2,000. We also need an iron saw. I get one for about IDR 5,000, but I am a little bit disappointed about the quality. The IDR 20…

DIY Tab Stand Using PVC

I think this will be the simplest tablet stand for my Samsung Galaxy Tab A with a 10-inch display. The main idea (at the end) is to make a super-duper tiny and minimalist home office consists of a desk and chair, a tab, a tablet stand, a Bluetooth keyboard, and a Bluetooth (and silent) mouse. You may already read how I made my desk using a pair of PVC table legs, and a folded top table.

Each week, I will try to post about each item needed for our Zen-wannabe home-office. This article will focus on how we make a tablet stand using a half-inch of PVC pipes, and some fittings. We use PVC pipes for almost all of our project since they are pretty cheap and flexible enough. Combined with some fittings, we can easily make any kind of shapes.

What we need:

- 2 x T-shape fittings
- 1 x straight fittings
- 2 x L-shape fittings
- 30 cm half-inch PVC pipes
- An iron saw to cut the pipe
- Some iron glue, or any similar one.

First. Cut the pipes to your preference. For example, you may cut longer if…