Cloud Scheduler to stop a Cloud SQL database

Today we are going to execute an exercise in a real environment that is going to:

1. make us save money,
2. show us how to execute schedulers in Google Cloud.

One of the applications that we have at mkdev is Claimora which we use for our time tracking. When we want to deploy Claimora we have two environments, staging and production, and in every environment we have a Postgres database up and running. We want to save money when we are not working in an environment, and to do that we will stop the database the working hours are over. Let's see what can we do.

To be able to schedule something in Google Cloud we first need a scheduler. This scheduler job will send a message to a pub/sub topic telling what needs to be done. In our case what we want to do is to stop or start a specific database. This pub/sub topic will call a cloud function and it will be this cloud function that stops or starts the database.

Let’s start at the end, with the database that we want to stop or start. As you can see on the screen we have in Claimora a database that is called claimora-database-staging. We have this database up and running for more than 30 days, and most of the time this Postgres is not even really needed.

So, applying our exercise and following the scenario we've just shown, we need a cloud function. We need to create a cloud function that we will call sql-up-down located in Europe-west1. The next step is to choose pub/sub as a trigger and to create a topic that we will call database-up-down.

If we click Next and we choose to go with 1.13 as our runtime, we can paste the code:

package p


import (
 "context"
 "encoding/json"
 "log"


 "golang.org/x/oauth2/google"
 sqladmin "google.golang.org/api/sqladmin/v1beta4"
)


type PubSubMessage struct {
 Data []byte `json:"data"`
}


type MessagePayload struct {
 Instance string
 Project string
 Action string
}


func ProcessPubSub(ctx context.Context, m PubSubMessage) error {
 var psData MessagePayload
 err := json.Unmarshal(m.Data, &psData)
 if err != nil {
  log.Println(err)
 }
 log.Printf("Request received for Cloud SQL instance %s action: %s, %s", psData.Action, psData.Instance, psData.Project)


 hc, err := google.DefaultClient(ctx, sqladmin.CloudPlatformScope)
 if err != nil {
  return err
 }


 service, err := sqladmin.New(hc)
 if err != nil {
  return err
 }


 action := "UNDEFINED"
 switch psData.Action {
 case "start":
  action = "ALWAYS"
 case "stop":
  action = "NEVER"
 default:
  log.Fatal("No valid action provided.")
 }


 // See more examples at:
 // https://cloud.google.com/sql/docs/sql...
 rb := &sqladmin.DatabaseInstance{
  Settings: &sqladmin.Settings{
   ActivationPolicy: action,
  },
 }


 resp, err := service.Instances.Patch(psData.Project, psData.Instance, rb).Context(ctx).Do()
 if err != nil {
  log.Fatal(err)
 }
 log.Printf("%#v\n", resp)
 return nil
}

The next step is to give permission to the cloud function to be able to stop and start the database. And to do that we go to IAM and we choose the service account [e-mail] and give permissions as Cloud SQL Admin.

Now we only need to test that our topic calls the step function and stops or starts the database. To do that we need to go to our pub/sub topic, click on Message, and publish a message like the one that you can see on the screen.

In this case the instance is Claimora-database-staging, the project is Claimora-staging, and the action in this case is stop.

If we go to Cloud SQL right after we publish the message we can see how our database claimora-database-staging is starting to shut down. The next step will be to start the database. As we did before, we go to our topic and publish a message again, but this time the action will be start. And as soon as we publish the message, we will go again to Cloud SQL and we will see how the database is starting.

We have tested that our topic and cloud function are working correctly. So the last step is to create a scheduler. This scheduler will be called stop-daily-staigng-database and will be executed, in our case, at 6 pm work days, Canaries Time zone. As execution will run our pub/sub topic database-up-down with the message that we tested before. In this case, it is to stop the database. We save and we do the same again, but this time to start the database. We change the time to start at 7 am Canaries time. Same as before, we execute our topic, but this time with a message to start the database.

And now that the scheduler is there we can go and test that it is running. We execute Run NOW for it to stop. After that we go to Cloud SQL, where we see how the database is stopping. So, to sum it up: the scheduler called the pub/sub, the pub/sub sent a message and the cloud function received the message, executed the code and stopped the database.