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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
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!
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.
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?
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