We have setup a Cloud sql database for development but would like to save cost by shutting it down during night. This article shows how to achieve this.

The overall architecture is Cloud function is used to hold Python code to start/stop the instance; Cloud scheduler is used to schedule these two tasks; Pub/Sub is used to bridge Cloud function and scheduler. (HTTP may be considered but it’s open to the internet so may be more suitable for open API related)

Below is how to configure the cloud function




Below is the actual code

from googleapiclient import discovery
import google.auth

#change this for a new project!!!
instance = 'pg-xxx'  # change this

#now the main script        
credentials,project  = google.auth.default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials,cache_discovery=False)
#print("service: {}".format(service))

def start_stop_instance(event, context):
    """Background Cloud Function to be triggered by Pub/Sub.
    Args:
         event (dict):  The dictionary with data specific to this type of
         event. The `data` field contains the PubsubMessage message. The
         `attributes` field will contain custom attributes if there are any.
         context (google.cloud.functions.Context): The Cloud Functions event
         metadata. The `event_id` field contains the Pub/Sub message ID. The
         `timestamp` field contains the publish time.
         
         Refer to pub/sub tutorial https://cloud.google.com/functions/docs/tutorials/pubsub
         
    """
    import base64
    #print("""This Function was triggered by messageId {} published at {}
    #""".format(context.event_id, context.timestamp))
    
    #get action: START|STOP, passed in by cloud scheduler
    policy = 'ALWAYS'  #default to start the instance
    
    if 'data' in event:
      action = base64.b64decode(event['data']).decode('utf-8')
    
    print('action received: {}'.format(action))
    if action.upper() == 'START':
      policy = 'ALWAYS'  #default to start the instance
    elif action.upper() == 'STOP':
      policy = 'NEVER'  
    else:
      policy = 'ALWAYS'  
      print('received {} while START|STOP is expected!'.format(action))
    
    #Creating a request does not actually call the API. 
    #Be aware that the API (e.g.https://cloud.google.com/sql/docs/postgres/admin-api/rest/v1beta4/instances/get) is for RESTful call,
    # which is different from the Python client where "To execute the request and get a response, call the execute() function"
    #refer to https://github.com/googleapis/google-api-python-client/blob/master/docs/start.md
    
    dbinstancebody = {
       "settings": {           
           "activationPolicy": policy
       }
    }

    request = service.instances().patch(
       project=project,
       instance=instance,
       body=dbinstancebody).execute()
    
    
Requirements.txt
google-api-python-client==1.7.8
google-auth==1.6.2

To test the function, you’ll need to pass parameter START or STOP, below shows the GUI. Note ‘STOP’ is base64-encoded string, you can use any online tool e.g. https://www.base64encode.org/

Finally we can setup Cloud scheduler. Note the topic should be the same as what you specified in Cloud function above, payload is the parameter we want to pass to our Cloud function.

All done!

Use Cloud function to start and stop Cloud sql
Tagged on:                                 

13 thoughts on “Use Cloud function to start and stop Cloud sql

  • July 23, 2020 at 4:01 pm
    Permalink

    The structuring of your article is quite good. The use of images are quite supporting in understanding the process. Thanks for sharing. For base64-encoded string, you can also refer to that link
    https://url-decode.com/tool/base64-encode where you also find more tools related to that.

    Reply
  • August 29, 2020 at 8:37 pm
    Permalink

    A very good solution to reduce my cost. I was looking for a solution for this issue and at last found it here. I did it successfully and images supported me a lot. Thanks a lot.
    I am thinking to connect it with JavaFX program. Is Java good for database?

    Reply
    • August 30, 2020 at 10:07 am
      Permalink

      I think if you’re good at Java then keep using it; For me, Java is becoming too complex and my daily language is Python

      Reply
  • April 5, 2021 at 8:05 pm
    Permalink

    is there also a way to update the node pool count to zero to save the cost.

    Reply
    • April 6, 2021 at 10:54 am
      Permalink

      you’ll have to look at Google’s doc for this, you’re welcome to share your finding here

      Reply
  • April 5, 2021 at 8:10 pm
    Permalink

    When executing the above code getting an error message Code in file main.py can’t be loaded.

    Reply
    • April 6, 2021 at 10:52 am
      Permalink

      The code really is below, the comment# and character before were actually ‘[‘

      action = base64.b64decode(event['data']).decode('utf-8')

      Reply
      • April 6, 2021 at 4:50 pm
        Permalink

        Thanks, wangts now I am able to deploy the function but while trying to test the function getting the below error

        Traceback (most recent call last): File “/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py”, line 449, in run_background_function _function_handler.invoke_user_function(event_object) File “/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py”, line 268, in invoke_user_function return call_user_function(request_or_event) File “/env/local/lib/python3.7/site-packages/google/cloud/functions/worker_v2.py”, line 265, in call_user_function event_context.Context(**request_or_event.context)) File “/user_code/main.py”, line 11, in hello_pubsub start_stop_instance(event, context); File “/user_code/main.py”, line 54, in start_stop_instance request = service.instances().patch(project=project,instance=instance,body=dbinstancebody).execute() File “/env/local/lib/python3.7/site-packages/googleapiclient/_helpers.py”, line 130, in positional_wrapper return wrapped(*args, **kwargs) File “/env/local/lib/python3.7/site-packages/googleapiclient/http.py”, line 851, in execute raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError:

        Reply
        • April 6, 2021 at 5:17 pm
          Permalink

          the error message doesn’t provide much detail but I think you can check the following:
          1. use the latest API for both google-api-python-client & google-auth, I noticed Google tends to uplift to a version that breaks existing interface. This can be achieved by not specifying version number in the requirements.txt
          2. check if your cloud function has proper authorization to access database instance

          Reply
  • April 6, 2021 at 9:20 pm
    Permalink

    wangts i have tried both the above options but still did not work,I gave all the Admin access but still did not work, Is there any specific roles that I have to provide to the service account.

    Error: function terminated. Recommended action: inspect logs for termination reason. Additional troubleshooting documentation can be found at https://cloud.google.com/functions/docs/troubleshooting#logging Details:

    Reply
    • April 6, 2021 at 10:40 pm
      Permalink

      give your cloud function’s service account the project “editor” role first, if it works, you’ll know your code works; then you can replace this editor role with a set of more specific roles, this article may be of help
      https://cloud.google.com/sql/docs/postgres/admin-api

      Reply
    • April 6, 2021 at 10:15 pm
      Permalink

      seems your cloud function is not authorised to access your sql instance, check your “Runtime service account” under “RUNTIME,BUILD AND CONNECTIONS SETTINGS” has proper authorisation, consult Google doc for details

      Reply

Leave a Reply

Your email address will not be published.

+ 85 = 93