Schedule starting/stopping a CloudSQL instance on GCP

README.md

To use this cloud function follow these steps:

1. Create a pub/sub topic which will be used to trigger the cloud function.
2. Create the cloud function and copy in the code below.
   1. Make sure to set the correct project ID in line 8.
   2. Set the trigger to Pub/Sub and choose the topic created in step 1.
3. Create a cloud scheduler job to trigger the cloud function on a regular basis.
   1. Choose the frequency when you want the cloud function to be triggered.
   2. Set the target to Pub/Sub and define the topic created in step 1.
   3. The payload should be set to start [CloudSQL instance name] or stop [CloudSQL instance name] to start or stop the specified instance (e.g. start my_cloudsql_instance will start the CloudSQL instance with the name my_cloudsql_instance)

The code is based on this code so credit goes to chris32g.

main.py

from googleapiclient import discovery
from oauth2client.client import GoogleCredentials
import base64
from pprint import pprint

credentials = GoogleCredentials.get_application_default()
service = discovery.build('sqladmin', 'v1beta4', credentials=credentials, cache_discovery=False)
project = 'INSERT PROJECT_ID HERE'

def start_stop(event, context):
  print(event)
  pubsub_message = base64.b64decode(event['data']).decode('utf-8')
  print(pubsub_message)
  command, instance_name = pubsub_message.split(' ', 1)

  if command == 'start':
    start(instance_name)
  elif command == 'stop':
    stop(instance_name)
  else:
    print("unknown command " + command)

def start(instance_name):
  print("starting " + instance_name)
  patch(instance_name, "ALWAYS")

def stop(instance_name):
  print("stopping " + instance_name)
  patch(instance_name, "NEVER")

def patch(instance, activation_policy):
  request = service.instances().get(project=project, instance=instance)
  response = request.execute()

  dbinstancebody = {
    "settings": {
      "settingsVersion": response["settings"]["settingsVersion"],
      "activationPolicy": activation_policy
    }
  }

  request = service.instances().patch(
    project=project,
    instance=instance,
    body=dbinstancebody)
  response = request.execute()
  pprint(response)

requirements.txt

# Function dependencies, for example:
# package>=version

google-api-python-client==1.10.0
google-auth-httplib2==0.0.4
google-auth==1.19.2
oauth2client==4.1.3

Worked like a charm with runtime Python 3.9.

Hi, this code really help me with the implementation I'm doing for my team, thanks!
I have a doubt about the payload, it should be in JSON format?
I'm sending:
start my_instance_name (of course I put my instancte name there)
and it's not working.
or should I have to put something like this??

{ "data": "stop my_instance_name"}

When I use this last payload example I got this message error:

File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 2529, in wsgi_app
response = self.full_dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1825, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/functions_framework/init.py", line 171, in view_func
function(data, context)
File "/workspace/main.py", line 18, in start_stop
stop(instance_name)
NameError: name 'stop' is not defined"

Thanks!

@Nofugnosis Look at the last line of your error, then look at example #2 of this article https://www.freecodecamp.org/news/nameerror-name-plot_cases_simple-is-not-defined-how-to-fix-this-python-error/

thanks!! The indentation in my code was wrong, that's why.
now I got another error, it seems it's related not enough permissions to perform the patch action. I have to check that.

"Traceback (most recent call last):
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 2529, in wsgi_app
response = self.full_dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1825, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
rv = self.dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/functions_framework/init.py", line 171, in view_func
function(data, context)
File "/workspace/main.py", line 18, in start_stop
stop(instance_name)
File "/workspace/main.py", line 28, in stop
patch(instance_name, "NEVER")
File "/workspace/main.py", line 43, in patch
response = request.execute()
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper
return wrapped(*args, **kwargs)
File "/layers/google.python.pip/pip/lib/python3.9/site-packages/googleapiclient/http.py", line 907, in execute
raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sqladmin.googleapis.com/sql/v1beta4/projects/instances/?alt=json returned "The client is not authorized to make this request.">"

I'm sorry @Nofugnosis if this sounds rude, but I can't help you debug your issues. This script is over 3 years old and I don't use it anymore, you'll have to use google or chat gpt to figure out what's the issue.

No worries! it's ok and understandable. I just mentioned my last error in case you have seen it before. Thanks for all your help, I really appreciate it!