체크포인트
Create a Service Account for Cloud SQL.
/ 25
Deploy a lightweight GKE application.
/ 25
Connect the GKE application to an external load balancer
/ 25
Verify full read/write capabilities of application to database.
/ 25
Connect an App to a Cloud SQL for PostgreSQL Instance
GSP919
Overview
Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and Microsoft SQL Server that offers many services for common adminstrative tasks such as backups, replication, and logging. You can easily connect your applications to a Cloud SQL instance to leverage these useful services.
In this lab, you first create a Kubernetes cluster and deploy a simple application to that cluster. You then connect the application to a supplied Cloud SQL for PostgreSQL instance and confirm that it is able to write to and read from it.
What you'll do
- Create a Kubernetes cluster and deploy a simple application to that cluster.
- Connect the application to the supplied Cloud SQL for PostgreSQL database instance.
- Confirm that the application is able to write to and read from the database.
Setup and requirements
Before you click the Start Lab button
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.
This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
- Access to a standard internet browser (Chrome browser recommended).
- Time to complete the lab---remember, once you start, you cannot pause a lab.
How to start your lab and sign in to the Google Cloud console
-
Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:
- The Open Google Cloud console button
- Time remaining
- The temporary credentials that you must use for this lab
- Other information, if needed, to step through this lab
-
Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).
The lab spins up resources, and then opens another tab that shows the Sign in page.
Tip: Arrange the tabs in separate windows, side-by-side.
Note: If you see the Choose an account dialog, click Use Another Account. -
If necessary, copy the Username below and paste it into the Sign in dialog.
{{{user_0.username | "Username"}}} You can also find the Username in the Lab Details panel.
-
Click Next.
-
Copy the Password below and paste it into the Welcome dialog.
{{{user_0.password | "Password"}}} You can also find the Password in the Lab Details panel.
-
Click Next.
Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges. -
Click through the subsequent pages:
- Accept the terms and conditions.
- Do not add recovery options or two-factor authentication (because this is a temporary account).
- Do not sign up for free trials.
After a few moments, the Google Cloud console opens in this tab.
Activate Cloud Shell
Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.
- Click Activate Cloud Shell
at the top of the Google Cloud console.
When you are connected, you are already authenticated, and the project is set to your Project_ID,
gcloud
is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.
- (Optional) You can list the active account name with this command:
- Click Authorize.
Output:
- (Optional) You can list the project ID with this command:
Output:
gcloud
, in Google Cloud, refer to the gcloud CLI overview guide.
Set your region and zone
Certain Compute Engine resources live in regions and zones. A region is a specific geographical location where you can run your resources. Each region has one or more zones.
Run the following gcloud commands in Cloud Console to set the default region and zone for your lab:
Task 1. Initialize APIs and create a Cloud IAM service account
To complete this task you must initialize the APIs and create an IAM service account that will be used to allow your application to connect to the Cloud SQL database.
Enable the APIs
You must enable the required APIs for this lab. You will build and push a container to the Artifact Registry in a later task, so you must enable the Artifact Registry API first.
- In Cloud Shell, run the following command to enable the Artifact Registry API:
Create a Service Account for Cloud SQL
You need to configure IAM service account credentials for the application that you will deploy later. The service account must be bound to a role that allows it to create and access Cloud SQL databases.
- In Cloud Shell, create a Service Account and bind it to the Cloud SQL admin role in the lab project:
- In Cloud Shell, create and export keys to a local file:
The file will be saved to your home folder in Cloud Shell.
Click Check my progress to verify the objective.
Task 2. Deploy a lightweight GKE application
In this task you will create a Kubernetes cluster and deploy a lightweight Google Kubernetes Engine (GKE) application on that cluster. You will configure the application to have access to the supplied Cloud SQL instance.
The application provided is a simple Flask-SQLAlchemy web application called gMemegen. It creates memes by supplying a set of photographs and capturing header and footer text, storing them in the database and rendering the meme to a local folder. It runs on a single pod with two containers; one for the application and one for the Cloud SQL Auth Proxy deployed in the side-car pattern.
A load balancer will marshal requests between the app and the database through the side-car. This load balancer will expose an external Ingress IP address through which you will access the app in your browser.
Create a Kubernetes cluster
In this step, you will create a minimal Kubernetes cluster. The cluster will take a couple of minutes to be deployed.
- In Cloud Shell, create a minimal Kubernetes cluster as follows:
Create Kubernetes secrets for database access
In this step you will create a pair of Kubernetes secrets containing the credentials that are needed to connect to the Cloud SQL instance and database.
- In Cloud Shell, run the following commands to create the secrets:
Download and build the GKE application container
Before you can deploy the gMemegen application to your GKE cluster you need to build the container and push it to a repository.
- In Cloud Shell, download the provided application code and change to the application directory:
- Create environment variables for the region, Project ID and Artifact Registry repository:
- Configure Docker authentication for the Artifact Registry:
- Enter
Y
if you are asked for confirmation.
- Create the Artifact Registry repository:
- Build a local Docker image:
For the purposes of this lab, you may ignore the warning about running 'pip' as the 'root' user, although you should note that, in general, especially when working on your local machine, it is best practice to use a virtual environment.
- Push the image to the Artifact Registry:
Configure and deploy the GKE application
You must modify the Kubernetes deployment manifest for the gMemegen application to point at the correct container and configure the Cloud SQL Auth Proxy side-car with the connection string for the Cloud SQL PostgreSQL instance.
The instructions explain how to edit the file using the Cloud Shell Editor, but if you prefer you can use another editor, such as vi
or nano
, from Cloud Shell for these steps.
-
On the Cloud Shell menu bar, click Open Editor to open the Cloud Shell Editor.
-
Navigate the Explorer panel on the left hand side, expanding the
gmemegen
folder and then selectinggmemegen_deployment.yaml
to edit the file. -
On line 33, in the
image
attribute, replace${REGION}
withand ${PROJECT_ID}
with. The line should now read:
- On line 60, replace replace
${REGION}
withand ${PROJECT_ID}
with. The line should now read:
To confirm that the connection name is correct, in the Cloud Console, navigate to Databases > SQL, select the postgres-gmemegen
instance and compare with the Connection name in the Overview pane. A valid connection name is of the format PROJECT_ID:REGION:CLOUD_SQL_INSTANCE_ID
.
-
Save your changes by selecting File > Save from the Cloud Shell Editor menu.
-
In the Cloud console click the Open Terminal to re-open Cloud Shell. You may need to resize the Terminal window by dragging down the handle at the centre top of the menu bar, in order to see your Cloud Console window above.
-
In Cloud Shell, deploy the application by running the following command:
- In Cloud Shell, check that the deployment was successful by running the following command:
It may take a minute or so for the pods to start up, because they need to pull the image from the repository. Repeat the above command until you see a pod, with 2 containers, with status Running
.
Click Check my progress to verify the objective.
Task 3. Connect the GKE application to an external load balancer
In this task you will create a load balancer to marshal requests between the containers in your GKE pods and access the application using its external IP address from your browser.
Create a load balancer to make your GKE application accessible from the web
In this step you will create a Kubernetes load balancer service that will provide your application with a public IP address.
- In Cloud Shell, run the following command to create a load balancer for the application:
Test the application to generate some data
In this step you will access the gMemegen application from your web browser.
The application has a very simple interface. It launches to the application home page, which displays 6 candidate images for making memes. You can select an image by clicking on it.
The Create Meme page is displayed, where you enter two items of text, to be displayed at the top and bottom of the image. Clicking Submit renders the meme and displays it. The interface provides no navigation from the completed meme page. You will have to use the browser's back button to return to the home page.
There are two other pages, Recent and Random, which display a set of recently generated memes and a random meme, respectively. Generating memes and navigating the UI will generate database activity which you can view in the logs as described below.
Wait for the load balancer to expose an external IP, which you can retrieve as follows:
- In Cloud Shell, copy the external IP address attribute of the
LoadBalancer Ingress
from the output of:
Output:
It will take a minute or so for the LoadBalancer Ingress
attribute to be included in the output (see above), so repeat the command until it is there before performing the next step.
- In a browser, navigate to the load balancer's Ingress IP address.
You can create a clickable link to the external IP address of the load balancer in Cloud Shell using the following commands:
-
Click the link in Cloud Shell and you will see the gMemegen application running in a new tab in your browser.
-
Create a meme as follows:
- On the Home page, click on one of the presented images.
- Enter text in the Top and Bottom text boxes.
- Click the Submit button.
Your new meme is displayed.
-
To create more memes, use the browser's back buttton to navigate to the home page.
-
To view existing memes, click Recent or Random in the application menu. (Note that Random opens a new browser tab)
-
In Cloud Shell, view the application’s activity by running the following:
This queries the logs from the gmemegen
container and will display the activity of the application on the pod, including the SQL statements, which are logged to stderr as they are executed.
Click Check my progress to verify the objective.
Task 4. Verify full read/write capabilities of application to database
In this task you will verify that the application is able to write to and read from the database.
Connect to the database and query an application table
In this step you will connect to the Cloud SQL instance by running PL/SQL in Cloud Shell.
-
In Google Cloud Console, navigate to Databases > SQL and select the
postgres-gmemegen
instance. -
In the Overview pane , scroll down to Connect to this instance and click the Open Cloud Shell button.
-
Run the auto-populated command in Cloud Shell.
-
When prompted, enter the password:
supersecret!
-
At the
postgres=>
prompt enter the following command to select the gmemegen_db database:
-
When prompted, enter the password:
supersecret!
-
At the
gmemegen_db=>
prompt enter:
This will display a row for each meme you have generated through the gMemegen app.
Click Check my progress to verify the objective.
Congratulations!
In this lab, you configured an application to access a Cloud SQL for PostgreSQL database. You created a Kubernetes cluster and deployed a simple meme generation application to that cluster, as well as connected the application to the supplied Cloud SQL for PostgreSQL database instance and confirmed that it is able to write to and read from it.
Google Cloud training and certification
...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.
Manual Last Updated April 23, 2024
Lab Last Tested September 20, 2023
Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.