Create and Manage AlloyDB Instances

Solution for Create and Manage AlloyDB Instances. 1 lab: GSP395. Fast copy-paste commands for Google Cloud.

GSP395 — Create and Manage AlloyDB Instances: Challenge Lab

Estimated time: 45 minutes

# 🚀 Create and Manage AlloyDB Instances: Challenge Lab (GSP395) > ⚠️ **Disclaimer:** This is an independent, community-made walkthrough created for educational purposes, hands-on practice, and Google Cloud certification preparation. This guide is designed to help learners understand AlloyDB, database clusters, primary instances, read pools, backups, and database operations through hands-on practice. Always attempt the lab yourself first and follow Google Cloud Skills Boost / Qwiklabs Terms of

clear
RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; BLUE=

\033[1;34m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'
set +H

echo "${CYAN}${BOLD}"
echo "   ____       _     _ _            __   ___             "
echo "  / __ \     | |   (_) |          / _| / _ \            "
echo " | |  | |_ __| |__  _| |_   ___  | |_ | | | |_ __  ___  "
echo " | |  | | '__| '_ \| | __| / _ \ |  _|| | | | '_ \/ __| "
echo " | |__| | |  | |_) | | |_ | (_) || |  | |_| | |_) \__ \ "
echo "  \____/|_|  |_.__/|_|\__| \___/ |_|   \___/| .__/|___/ "
echo "                                            | |         "
echo "                                            |_|         "
echo "${RESET}"

BAR=$(printf '═%.0s' {1..58})
echo "${BLUE}${BOLD}╔${BAR}╗${RESET}"
printf "${BLUE}${BOLD}║${RESET} %-56s ${BLUE}${BOLD}║${RESET}\n" "BROUGHT TO YOU BY ORBIT OF OPS"
printf "${BLUE}${BOLD}║${RESET} %-56s ${BLUE}${BOLD}║${RESET}\n" "GSP395 | STAGE 1 OF 3: CLUSTER + PRIMARY INSTANCE"
echo "${BLUE}${BOLD}╚${BAR}╝${RESET}"
echo

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  [ -z "$ZONE" ] && ZONE=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-zone).extract(value).flatten())" 2>/dev/null | head -n 1)
  REGION=${ZONE%-*}
  [ -z "$REGION" ] && REGION=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-region).extract(value).flatten())" 2>/dev/null | head -n 1)
  [ -z "$REGION" ] && REGION=$(gcloud compute networks subnets list --project=$PROJECT_ID --filter="network~peering-network" --format="value(region.basename())" 2>/dev/null | head -n 1)
  [ -n "$PROJECT_ID" ] && [ -n "$REGION" ]
}

orbit_wait() {
  local E=0 S i
  for i in $(seq 1 40); do
    S=$(gcloud alloydb "$1" describe "$2" --region=$REGION --project=$PROJECT_ID $3 --format="value(state)" 2>/dev/null)
    [ "$S" = "READY" ] && return 0
    [ "$S" = "FAILED" ] && return 1
    [ -z "$S" ] && E=$((E+1))
    [ $E -ge 3 ] && return 1
    sleep 30
  done
  return 1
}

orbit_stage1() {
  echo "${YELLOW}${BOLD}[Orbit of Ops] Auto-fetching Project, Zone and Region...${RESET}"
  orbit_env || { echo "${RED}${BOLD}Could not detect project or region. Make sure you are in the lab project and the lab has finished provisioning.${RESET}"; return 1; }
  gcloud config set compute/region $REGION >/dev/null 2>&1
  [ -n "$ZONE" ] && gcloud config set compute/zone $ZONE >/dev/null 2>&1
  echo "${GREEN}Project: $PROJECT_ID | Region: $REGION | Zone: ${ZONE:-n/a}${RESET}"

  echo "${CYAN}Enabling required APIs...${RESET}"
  gcloud services enable alloydb.googleapis.com compute.googleapis.com servicenetworking.googleapis.com --project=$PROJECT_ID --quiet >/dev/null 2>&1
  sleep 20

  gcloud compute networks describe peering-network --project=$PROJECT_ID --format="value(name)" >/dev/null 2>&1 || { echo "${RED}Network peering-network not found.${RESET}"; return 1; }

  echo "${CYAN}Task 1: Creating cluster lab-cluster...${RESET}"
  if gcloud alloydb clusters describe lab-cluster --region=$REGION --project=$PROJECT_ID >/dev/null 2>&1; then
    echo "${GREEN}Cluster already exists, skipping create.${RESET}"
  else
    for i in 1 2 3; do
      echo "${YELLOW}  Create attempt $i of 3 (about 5-10 min)...${RESET}"
      gcloud alloydb clusters create lab-cluster --password=Change3Me --network=peering-network --region=$REGION --project=$PROJECT_ID --quiet >/tmp/orbit_cluster.log 2>&1 && break
      tail -n 4 /tmp/orbit_cluster.log; sleep 30
    done
  fi
  orbit_wait clusters lab-cluster "" || { echo "${RED}Cluster is not READY. See /tmp/orbit_cluster.log${RESET}"; return 1; }
  echo "${GREEN}Cluster READY.${RESET}"

  echo "${CYAN}Task 1: Creating primary instance lab-instance (2 vCPU, 16 GB)...${RESET}"
  if gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID >/dev/null 2>&1; then
    echo "${GREEN}Instance already exists, skipping create.${RESET}"
  else
    for i in 1 2 3; do
      echo "${YELLOW}  Create attempt $i of 3 (about 8-15 min)...${RESET}"
      gcloud alloydb instances create lab-instance --instance-type=PRIMARY --cpu-count=2 --region=$REGION --cluster=lab-cluster --project=$PROJECT_ID --quiet >/tmp/orbit_instance.log 2>&1 && break
      tail -n 4 /tmp/orbit_instance.log; sleep 30
    done
  fi
  orbit_wait instances lab-instance "--cluster=lab-cluster" || { echo "${RED}Instance is not READY. See /tmp/orbit_instance.log${RESET}"; return 1; }
  sleep 30

  ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(ipAddress)" 2>/dev/null)
  echo "${GREEN}${BOLD}Instance READY. Private IP: $ALLOYDB_IP${RESET}"
  echo "${MAGENTA}${BOLD}>> Click 'Check my progress' for Task 1, then run Command 2 of 3.${RESET}"
}
orbit_stage1

RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'
set +H
echo "${MAGENTA}${BOLD}[ ORBIT OF OPS ] Stage 2 of 3 :: Tables & Data (Tasks 2-3)${RESET}"

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  [ -z "$ZONE" ] && ZONE=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-zone).extract(value).flatten())" 2>/dev/null | head -n 1)
  REGION=${ZONE%-*}
  [ -z "$REGION" ] && REGION=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-region).extract(value).flatten())" 2>/dev/null | head -n 1)
  [ -z "$REGION" ] && REGION=$(gcloud compute networks subnets list --project=$PROJECT_ID --filter="network~peering-network" --format="value(region.basename())" 2>/dev/null | head -n 1)
  [ -n "$PROJECT_ID" ] && [ -n "$REGION" ]
}

orbit_stage2() {
  orbit_env || { echo "${RED}Could not detect project or region.${RESET}"; return 1; }
  [ -z "$ZONE" ] && { echo "${RED}alloydb-client VM not found.${RESET}"; return 1; }
  ALLOYDB_IP=$(gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="value(ipAddress)" 2>/dev/null)
  [ -z "$ALLOYDB_IP" ] && { echo "${RED}lab-instance not found. Run Command 1 first.${RESET}"; return 1; }
  echo "${GREEN}Project: $PROJECT_ID | Zone: $ZONE | AlloyDB IP: $ALLOYDB_IP${RESET}"

  echo "${CYAN}Writing schema.sql...${RESET}"
cat << 'EOF' > schema.sql
CREATE TABLE IF NOT EXISTS regions (region_id bigint NOT NULL, region_name varchar(25), PRIMARY KEY (region_id));
CREATE TABLE IF NOT EXISTS countries (country_id char(2) NOT NULL, country_name varchar(40), region_id bigint, PRIMARY KEY (country_id));
CREATE TABLE IF NOT EXISTS departments (department_id smallint NOT NULL, department_name varchar(30), manager_id integer, location_id smallint, PRIMARY KEY (department_id));
INSERT INTO regions VALUES (1, 'Europe'), (2, 'Americas'), (3, 'Asia'), (4, 'Middle East and Africa') ON CONFLICT DO NOTHING;
INSERT INTO countries VALUES ('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) ON CONFLICT DO NOTHING;
INSERT INTO departments VALUES (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) ON CONFLICT DO NOTHING;
EOF

  echo "${CYAN}Copying schema.sql to alloydb-client...${RESET}"
  SSHX=""; OK=0
  for i in 1 2 3; do
    [ $i -eq 3 ] && SSHX="--tunnel-through-iap"
    gcloud compute scp schema.sql alloydb-client:~ --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $SSHX >/tmp/orbit_scp.log 2>&1 && OK=1 && break
    echo "${YELLOW}  Attempt $i of 3 failed, retrying...${RESET}"; tail -n 2 /tmp/orbit_scp.log; sleep 20
  done
  [ $OK -eq 1 ] || { echo "${RED}Could not reach alloydb-client over SSH. See /tmp/orbit_scp.log${RESET}"; return 1; }

  echo "${CYAN}Loading tables and data with psql...${RESET}"
  OK=0
  for i in 1 2 3; do
    gcloud compute ssh alloydb-client --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $SSHX --command="echo $ALLOYDB_IP > alloydbip.txt && PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -q -v ON_ERROR_STOP=1 -f schema.sql" >/tmp/orbit_psql.log 2>&1 && OK=1 && break
    echo "${YELLOW}  Attempt $i of 3 failed, retrying...${RESET}"; tail -n 3 /tmp/orbit_psql.log; sleep 20
  done
  [ $OK -eq 1 ] || { echo "${RED}psql load failed. See /tmp/orbit_psql.log${RESET}"; return 1; }

  RES=$(gcloud compute ssh alloydb-client --zone=$ZONE --project=$PROJECT_ID --quiet --strict-host-key-checking=no $SSHX --command="PGPASSWORD=Change3Me psql -h $ALLOYDB_IP -U postgres -t -A -c 'SELECT (SELECT count(*) FROM regions), (SELECT count(*) FROM countries), (SELECT count(*) FROM departments);'" 2>/dev/null | tail -n 1)
  echo "${GREEN}Row counts (regions|countries|departments): $RES  (expected 4|9|6)${RESET}"
  [ "$RES" = "4|9|6" ] && echo "${GREEN}${BOLD}Tables created and loaded.${RESET}" || echo "${RED}Counts do not match, re-run this block.${RESET}"
  sleep 15
  echo "${MAGENTA}${BOLD}>> Click 'Check my progress' for 'Create and load tables', then run Command 3 of 3.${RESET}"
}
orbit_stage2

RESET=

\033[0m'; BOLD=

\033[1m'; CYAN=

\033[1;36m'; GREEN=

\033[1;32m'; YELLOW=

\033[1;33m'; RED=

\033[1;31m'; MAGENTA=

\033[1;35m'
set +H
echo "${MAGENTA}${BOLD}[ ORBIT OF OPS ] Stage 3 of 3 :: Read Pool & Backup (Tasks 4-5)${RESET}"

orbit_env() {
  PROJECT_ID=$(gcloud config get-value project 2>/dev/null); [ -z "$PROJECT_ID" ] && PROJECT_ID=$DEVSHELL_PROJECT_ID
  ZONE=$(gcloud compute instances list --project=$PROJECT_ID --filter="name=alloydb-client" --format="value(zone.basename())" 2>/dev/null | head -n 1)
  [ -z "$ZONE" ] && ZONE=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-zone).extract(value).flatten())" 2>/dev/null | head -n 1)
  REGION=${ZONE%-*}
  [ -z "$REGION" ] && REGION=$(gcloud compute project-info describe --project=$PROJECT_ID --format="value(commonInstanceMetadata.items.filter(key:google-compute-default-region).extract(value).flatten())" 2>/dev/null | head -n 1)
  [ -z "$REGION" ] && REGION=$(gcloud compute networks subnets list --project=$PROJECT_ID --filter="network~peering-network" --format="value(region.basename())" 2>/dev/null | head -n 1)
  [ -n "$PROJECT_ID" ] && [ -n "$REGION" ]
}

orbit_wait() {
  local E=0 S i
  for i in $(seq 1 40); do
    S=$(gcloud alloydb "$1" describe "$2" --region=$REGION --project=$PROJECT_ID $3 --format="value(state)" 2>/dev/null)
    [ "$S" = "READY" ] && return 0
    [ "$S" = "FAILED" ] && return 1
    [ -z "$S" ] && E=$((E+1))
    [ $E -ge 3 ] && return 1
    sleep 30
  done
  return 1
}

orbit_stage3() {
  orbit_env || { echo "${RED}Could not detect project or region.${RESET}"; return 1; }
  gcloud alloydb instances describe lab-instance --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID >/dev/null 2>&1 || { echo "${RED}lab-instance not found. Run Command 1 first.${RESET}"; return 1; }
  echo "${GREEN}Project: $PROJECT_ID | Region: $REGION${RESET}"

  echo "${CYAN}Task 4: Creating read pool lab-instance-rp1 (2 nodes, 2 vCPU each)...${RESET}"
  if gcloud alloydb instances describe lab-instance-rp1 --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID >/dev/null 2>&1; then
    echo "${GREEN}Read pool already exists, skipping create.${RESET}"
  else
    for i in 1 2 3; do
      echo "${YELLOW}  Create attempt $i of 3 (about 5-10 min)...${RESET}"
      gcloud alloydb instances create lab-instance-rp1 --instance-type=READ_POOL --cpu-count=2 --read-pool-node-count=2 --region=$REGION --cluster=lab-cluster --project=$PROJECT_ID --quiet >/tmp/orbit_rp.log 2>&1 && break
      tail -n 4 /tmp/orbit_rp.log; sleep 30
    done
  fi
  orbit_wait instances lab-instance-rp1 "--cluster=lab-cluster" || { echo "${RED}Read pool is not READY. See /tmp/orbit_rp.log${RESET}"; return 1; }
  echo "${GREEN}Read pool READY.${RESET}"
  sleep 15

  echo "${CYAN}Task 5: Creating on-demand backup lab-backup...${RESET}"
  if gcloud alloydb backups describe lab-backup --region=$REGION --project=$PROJECT_ID >/dev/null 2>&1; then
    echo "${GREEN}Backup already exists, skipping create.${RESET}"
  else
    for i in 1 2 3; do
      echo "${YELLOW}  Create attempt $i of 3 (about 3-8 min)...${RESET}"
      gcloud alloydb backups create lab-backup --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --quiet >/tmp/orbit_backup.log 2>&1 && break
      tail -n 4 /tmp/orbit_backup.log; sleep 30
    done
  fi
  orbit_wait backups lab-backup "" || { echo "${RED}Backup is not READY. See /tmp/orbit_backup.log${RESET}"; return 1; }
  sleep 15

  echo "${GREEN}${BOLD}Final state:${RESET}"
  gcloud alloydb instances list --cluster=lab-cluster --region=$REGION --project=$PROJECT_ID --format="table(name.basename(),instanceType,state)" 2>/dev/null
  gcloud alloydb backups list --region=$REGION --project=$PROJECT_ID --format="table(name.basename(),state)" 2>/dev/null
  echo
  echo "${MAGENTA}${BOLD}>> Click 'Check my progress' for Tasks 4 and 5.${RESET}"
  echo "${MAGENTA}${BOLD}Congratulations. All GSP395 tasks are complete - brought to you by Orbit of Ops.${RESET}"
}
orbit_stage3

# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videos