Create and Manage Cloud SQL for PostgreSQL Instances

GSP355 — Create and Manage Cloud SQL for PostgreSQL Instances: Challenge Lab

Estimated time: 1 hour

## 📑 Step-by-Step Instructions ⚠️ **Disclaimer:** This guide is for educational purposes only, intended to help you learn Google Cloud concepts. Please ensure you follow Qwiklabs' Terms of Service and YouTube's Community Guidelines. **Credits:** DM for credit or removal request (no copyright intended). © All rights and credits for the original content belong to Google Cloud. 🙏 --- ## 🛠️ Step-by-Step Guide ### 🔹 Step 1 — VM Preparation & Create the Connection Profile (Task 1) 1. Open **

set +H
clear
CYAN='\e[1;36m'
GREEN='\e[1;32m'
BOLD='\e[1m'
RESET='\e[0m'

echo -e "${CYAN}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${CYAN}${BOLD}║  Command 1 of 2: VM Configuration (Verified Payload)       ║${RESET}"
echo -e "${CYAN}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}"

export PROJECT_ID=${DEVSHELL_PROJECT_ID:-$(gcloud config get-value project 2>/dev/null)}
gcloud config set project $PROJECT_ID --quiet 2>/dev/null
gcloud services enable datamigration.googleapis.com servicenetworking.googleapis.com sqladmin.googleapis.com --quiet

echo -e "\n${CYAN}[+] Auto-fetching exact VM Name and Zone...${RESET}"
export VM_NAME=$(gcloud compute instances list --format="value(name)" 2>/dev/null | grep postgres | head -n 1 | tr -d '\r\n' | tr -d ' ')
export ZONE=$(gcloud compute instances list --filter="name=$VM_NAME" --format="value(zone)" 2>/dev/null | head -n 1 | tr -d '\r\n' | tr -d ' ')
export INTERNAL_IP=$(gcloud compute instances describe $VM_NAME --zone=$ZONE --format="value(networkInterfaces[0].networkIP)")

echo -e "✅ VM Name:    ${GREEN}$VM_NAME${RESET}"
echo -e "✅ Zone:       ${GREEN}$ZONE${RESET}"
echo -e "✅ Internal IP: ${GREEN}$INTERNAL_IP${RESET} (Save this for the UI steps)"

echo -e "\n${CYAN}[+] Waiting for SSH keys to propagate...${RESET}"
while ! gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="echo 'SSH ready'" >/dev/null 2>&1; do sleep 5; done

echo -e "${CYAN}[+] Injecting official lab manual configuration payload...${RESET}"
cat << 'EOF' > setup_vm.sh
#!/bin/bash
sudo apt-get update && sudo apt-get install -y postgresql-14-pglogical

sudo bash -c 'cat << "CONF" >> /etc/postgresql/14/main/postgresql.conf
shared_preload_libraries = '\''pglogical'\''
output_plugin_libraries = '\''pglogical_output'\''
wal_level = logical
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
listen_addresses = '\''*'\''
CONF'

echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/14/main/pg_hba.conf
echo "host replication all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/14/main/pg_hba.conf

sudo systemctl stop postgresql
sudo systemctl start postgresql@14-main

sudo -u postgres psql << 'SQL'
CREATE USER migration_admin PASSWORD 'DMS_1s_cool!';
ALTER ROLE migration_admin WITH REPLICATION;
ALTER DATABASE orders OWNER TO migration_admin;
SQL

for db in postgres orders gmemegen_db; do
  sudo -u postgres psql -d "$db" << 'SQL'
CREATE EXTENSION IF NOT EXISTS pglogical;
GRANT USAGE ON SCHEMA pglogical TO migration_admin;
GRANT ALL ON SCHEMA pglogical TO migration_admin;
GRANT SELECT ON ALL TABLES IN SCHEMA pglogical TO migration_admin;
GRANT USAGE ON SCHEMA public TO migration_admin;
GRANT ALL ON SCHEMA public TO migration_admin;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO migration_admin;
SQL
done

sudo -u postgres psql -d orders -c "ALTER TABLE inventory_items ADD PRIMARY KEY (id);" || true

sudo -u postgres psql -d orders << 'SQL'
ALTER TABLE public.distribution_centers OWNER TO migration_admin;
ALTER TABLE public.inventory_items OWNER TO migration_admin;
ALTER TABLE public.order_items OWNER TO migration_admin;
ALTER TABLE public.products OWNER TO migration_admin;
ALTER TABLE public.users OWNER TO migration_admin;
SQL
EOF

gcloud compute scp setup_vm.sh $VM_NAME:/tmp/ --zone=$ZONE --quiet
gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="bash /tmp/setup_vm.sh"

echo -e "\n${GREEN}${BOLD}✅ VM Preparation Complete!${RESET}"
echo -e "${BOLD}Click 'Check my progress' for Task 1, then follow the UI steps below.${RESET}"

echo -e "${MAGENTA}${BOLD}Please copy and paste the following values from your lab instructions:${RESET}"
read -p "Enter Migrated Cloud SQL Instance ID: " SQL_INSTANCE
read -p "Enter Qwiklabs User Account Name (Email): " STUDENT_EMAIL
read -p "Enter the Table Name to grant access to (e.g., orders): " TABLE_NAME
read -p "Enter Point-in-time recovery retention days (e.g., 2): " RETENTION_DAYS
echo ""

export VM_NAME=$(gcloud compute instances list --format="value(name)" 2>/dev/null | grep postgres)
export ZONE=$(gcloud compute instances list --filter="name=$VM_NAME" --format="value(zone)")
export EXTERNAL_IP=$(gcloud compute instances describe $VM_NAME --zone=$ZONE --format="value(networkInterfaces[0].accessConfigs[0].natIP)")

echo -e "${CYAN}Patching Cloud SQL Instance (Enabling IAM Auth & PITR)...${RESET}"
echo -e "${YELLOW}(This triggers a database restart and will take a few minutes. Please wait.)${RESET}"
gcloud sql instances patch $SQL_INSTANCE \
    --database-flags cloudsql.iam_authentication=on \
    --authorized-networks=$EXTERNAL_IP \
    --enable-point-in-time-recovery \
    --retained-transaction-log-days=$RETENTION_DAYS \
    --quiet

echo -e "${CYAN}Creating Cloud IAM User...${RESET}"
gcloud sql users create $STUDENT_EMAIL \
    --instance=$SQL_INSTANCE \
    --type=CLOUD_IAM_USER

export SQL_IP=$(gcloud sql instances describe $SQL_INSTANCE --format="value(ipAddresses[0].ipAddress)")

cat << EOF > iam_grant.sql
GRANT SELECT ON $TABLE_NAME TO "$STUDENT_EMAIL";
EOF

echo -e "${CYAN}Applying IAM permissions on Cloud SQL via $VM_NAME...${RESET}"
gcloud compute scp iam_grant.sql $VM_NAME:/tmp/ --zone=$ZONE --quiet
gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="PGPASSWORD=supersecret! psql -h $SQL_IP -U postgres -d orders -f /tmp/iam_grant.sql"

echo -e "${CYAN}Simulating IAM User Login to trigger Grader...${RESET}"
cat << EOF > test_iam.sql
SELECT COUNT(*) FROM $TABLE_NAME;
EOF
gcloud sql connect $SQL_INSTANCE --database=orders --user=$STUDENT_EMAIL --quiet < test_iam.sql

TIME_STAMP=$(date -u --rfc-3339=ns | sed -r 's/ /T/; s/\.([0-9]{3}).*/\.\1Z/')
echo -e "${GREEN}Timestamp for PITR captured: $TIME_STAMP${RESET}"
sleep 10

cat << 'EOF' > insert_row.sql
INSERT INTO distribution_centers VALUES(-80.1918,25.7617,'Miami FL',11);
EOF

echo -e "${CYAN}Inserting dummy row for PITR test...${RESET}"
gcloud compute scp insert_row.sql $VM_NAME:/tmp/ --zone=$ZONE --quiet
gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="PGPASSWORD=supersecret! psql -h $SQL_IP -U postgres -d orders -f /tmp/insert_row.sql"

echo -e "${CYAN}Cloning instance via Point-In-Time-Recovery (This takes a while)...${RESET}"
gcloud sql instances clone $SQL_INSTANCE postgres-orders-pitr \
 --point-in-time $TIME_STAMP

MAGENTA='\e[1;35m'
BOLD='\e[1m'
RESET='\e[0m'
echo -e "\n${MAGENTA}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${MAGENTA}${BOLD}║            🎉 AUTOMATION COMPLETED SUCCESSFULLY 🎉           ║${RESET}"
echo -e "${MAGENTA}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}"

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