GSP395

Overview

In a challenge lab, you’re given a scenario and a set of tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the course to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.

When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.

To score 100% you must successfully complete all tasks within the time period!

This lab is recommended for students who have enrolled in the Create and Manage AlloyDB Databases skill badge. Are you ready for the challenge?

Topics tested

In this challenge lab, you are required to perform the following tasks:

• Create a cluster and instance
• Create tables in your instance
• Load simple datasets into tables
• Create a Read Pool instance
• Create a backup

Target Audience

The content of this challenge lab will be most applicable to AlloyDB for PostgreSQL DBAs. This lab is designed to test the abilities of students who have completed the Create and Manage AlloyDB Databases quest.

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.

What you need

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).
• Time to complete the lab.

Note: If you have a personal Google Cloud account or project, do not use it for this lab. Note: If you are using a Pixelbook, open an Incognito window to run this lab.

Challenge scenario

In your role as the corporate Database Administrator, you have been tasked with standing up a new AlloyDB for PostgreSQL database for your company's HR Operations group. You have been provided a list specifcations for this database related to tables to create and data to load.

Task 1. Create a cluster and instance

1. Your first task is to create a cluster.

2. You may complete this step using the Cloud Console or the gcloud CLI. If you use the glcoud CLI you must refer to and use the GCP Region value that is provided on your lab launch page.

3. Your cluster must have following attributes. You can keep the default values for all other settings:

Item	Value
Cluster ID	lab-cluster
Password	Change3Me
Network	peering-network

Note: The private services access connection option was configured for this project when you started the lab. This step was required to allow access to the AlloyDB cluster.

If you are using the gcloud CLI, an example command to create a cluster is as follows.

Note: You must substitute the required values or your configuration will be invalid and you will not pass this challenge lab. gcloud beta alloydb clusters create SAMPLE-CLUSTER-ID \ --password=SAMPLE_PASSWORD \ --network=SAMPLE_NETWORK \ --region=GCP_REGION_VALUE \ --project=QWIKLABS_PROJECT_ID

4. Next you must create an instance. If you following the Cloud Console wizard, your cluster's instance must have following attributes:

Item	Value
Instance ID	lab-instance
Machine Type	2 vCPU, 16 GB

If you are using the gcloud CLI, an example command to create an instance is as follows.

Note: You must substitute the required values or your configuration will be invalid and you will not pass this challenge lab. gcloud beta alloydb instances create SAMPLE-INSTANCE-ID \ --instance-type=PRIMARY \ --cpu-count=2 \ --region=GCP_REGION_VALUE \ --cluster=SAMPLE-CLUSTER-ID \ --project=QWIKLABS_PROJECT_ID

5. When you are on the Overview page for the new cluster you created, please make note of the Private IP address in the instances section. Copy the Private IP address to a text file so that you can paste the value in a later step.

6. Click Check my progress to verify the objective.

Create a cluster and instance

Task 2. Create tables in your instance

1. Next you must create tables in your AlloyDB instance. A VM named, alloydb-client, containing the PostgreSQL client was provisioned for you at the start of the lab.

2. On the Navigation menu (), under Compute Engine click VM instances.

3. For the instance named alloydb-client, in the Connect column, click SSH to open a terminal window.

4. Set the following environment variable, replacing ALLOYDB_ADDRESS with the Private IP address of the AlloyDB instance from the previous task.

export ALLOYDB=ALLOYDB_ADDRESS

5. Run the following command to store the Private IP address of the AlloyDB instance on the AlloyDB client VM so that it will persist throughout the challenge lab.

echo $ALLOYDB > alloydbip.txt

6. Use the following command to launch the PostgreSQL (psql) client. You will be prompted to provide the postgres user's password (Change3Me) which you entered when you created the cluster.

psql -h $ALLOYDB -U postgres

7. Your database must have a total of three (3) tables - regions, countries, and departments.

8. The tables must be defined as listed below.

An example DDL command to create an AlloyDB table is as follows:

CREATE TABLE sample ( sample_id bigint NOT NULL, sample_name varchar(25) ) ; ALTER TABLE sample ADD PRIMARY KEY (sample_id);

Table: regions

Primary Key: region_id

Column	Datatype
region_id	bigint NOT NULL
region_name	varchar(25)

Table: countries

Primary Key: country_id

Column	Datatype
country_id	char(2) NOT NULL
country_name	varchar(40)
region_id	bigint

Table: departments

Primary Key: department_id

Column	Datatype
department_id	smallint NOT NULL
department_name	varchar(30)
manager_id	integer
location_id	smallint

Task 3. Load simple datasets into tables

1. Next load the tables you just created with the following simple, low-volume datasets.

2. You must use the psql client to load these tables. Use the alloydb-client VM as outlined in the previous task to access the psql client.

Note: The data elements provided are written in the order of the columns of the corresponding table.

An example DML command to load a single row into an AlloyDB table is as follows.

INSERT INTO Sample VALUES (1, 'Sample Value');

Table: regions

1, 'Europe' 2, 'Americas' 3, 'Asia' 4, 'Middle East and Africa'

Table: countries

'IT', 'Italy', 1 'JP', 'Japan', 3 'US', 'United States of America', 2 'CA', 'Canada', 2 'CN', 'China', 3 'IN', 'India', 3 'AU', 'Australia', 3 'ZW', 'Zimbabwe', 4 'SG', 'Singapore', 3

Table: departments

10, 'Administration', 200, 1700 20, 'Marketing', 201, 1800 30, 'Purchasing', 114, 1700 40, 'Human Resources', 203, 2400 50, 'Shipping', 121, 1500 60, 'IT', 103, 1400

3. Click each Check my progress to verify the objectives.

Create and Load Tables

Task 4. Create a Read Pool instance

1. Next create a Read Pool instance for your cluster. You may create the Read Pool instance using the Cloud Console or the gcloud CLI. If you use the glcoud CLI you must refer to and use the GCP Region value that is provided on your lab launch page.

2. Your Read Pool instance must have following attributes. You can keep the default values for all other settings:

Item	Value
Read pool instance ID	lab-instance-rp1
Node count	2
Machine Type	2 vCPU, 16 GB

If you are using the gcloud CLI, an example command to create a Read Pool instance is as follows.

Note: You must substitute the required values or your configuration will be invalid and you will not pass this challenge lab. gcloud beta alloydb instances create SAMPLE-READ-POOL-INSTANCE-ID \ --instance-type=READ_POOL \ --cpu-count=2 \ --read-pool-node-count=2 \ --region=GCP_REGION_VALUE \ --cluster=SAMPLE-CLUSTER-ID \ --project=QWIKLABS_PROJECT_ID

3. Click Check my progress to verify the objective.

Create a read pool instance

Task 5. Create a backup

1. Next create an on-demand backup of your instance. You may create the backup using the Cloud Console or the gcloud CLI. If you use the glcoud CLI you must refer to and use the GCP Region value that is provided on your lab launch page.

2. On the Cloud Console Navigation menu (), under Databases click AlloyDB for PostgreSQL then Backups to launch the Backups page.

3. Your backup must be named lab-backup.

If you are using the gcloud CLI, an example command to create a backup is as follows.

Note: You must substitute the required values or your configuration will be invalid and you will not pass this challenge lab. gcloud beta alloydb backups create SAMPLE-BACKUP_ID \ --cluster=SAMPLE-CLUSTER-ID \ --region=GCP_REGION_VALUE \ --project=QWIKLABS_PROJECT_ID

4. Click Check my progress to verify the objective.

Create a backup

Congratulations!

In this challenge lab, you created a Cloud Spanner instance and database, created tables, loaded data, and performed DDL operations on a table.

Earn your next skill badge

This self-paced lab is part of the Create and Manage AlloyDB Databases skill badge. Completing this skill badge earns you the badge above to recognize your achievement. Share your badge on your resume and social platforms, and announce your accomplishment using #GoogleCloudBadge.

This skill badge is part of Google Cloud’s Database Engineer learning path. Continue your learning journey by enrolling in the Migrate MySQL Data to Cloud SQL Using Database Migration Service skill badge or the Manage PostgreSQL Databases on Cloud SQL skill badge.

Manual Last Updated April 25, 2024

Lab Last Tested February 26, 2024

Copyright 2022 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.