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

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

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.

Comments

Popular posts from this blog

About Prabowo Murti

Assalaamu'alaykum.

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…

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…

Learn Nested Set on Yii2 Framework

The nested set behaviour is an approach to store hierarchical data in relational databases. For example, if we have many categories for our product or items. One category can be a "parent" for other categories, means that one category consists of more than one category. The model can be drawn using a "tree" model. There are other approaches available but what we will learn in this article is specifically the NestedSetsBehavior made by  Alexander Kochetov, which utilizing the Modified Preorder Tree Traversal algorithm.

Install Yii2 framework advanced template
My assumption is that we have our standard installation of Yii2 framework advanced template. You can read more about advanced template from the repository page on Github: https://github.com/yiisoft/yii2-app-advanced. Make sure that our installation is working by calling the default URL localhost:8080 after running the following command

$ ./yii serve --docroot="backend/web"

We also need to make sure tha…