Create and Manage Cloud SQL for PostgreSQL Instances
Solution for Create and Manage Cloud SQL for PostgreSQL Instances. 1 lab: GSP355. Fast copy-paste commands for Google Cloud.
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 **
clear
CYAN='\e[1;36m'
BLUE='\e[1;34m'
RESET='\e[0m'
BOLD='\e[1m'
RED='\e[1;31m'
GREEN='\e[1;32m'
YELLOW='\e[1;33m'
MAGENTA='\e[1;35m'
echo -e "${CYAN}${BOLD}"
cat << "EOF"
____ _ _ _ __ ___
/ __ \ | | (_) | / _| / _ \
| | | |_ __| |__ _| |_ ___ | |_ | | | |_ __ ___
| | | | '__| '_ \| | __| / _ \ | _| | | | | '_ \/ __|
| |__| | | | |_) | | |_ | (_) || | | |_| | |_) \__ \
\____/|_| |_.__/|_|\__| \___/ |_| \___/| .__/|___/
| |
|_|
EOF
echo -e "${RESET}"
echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║ 🚀 BROUGHT TO YOU BY ORBIT OF OPS ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"
echo -e "${MAGENTA}${BOLD}Please check your lab instructions panel for the Migration User Name.${RESET}"
read -p "Enter Migration user name (e.g., Postgres Migration User or migration_admin): " MIGRATION_USER
echo ""
echo -e "${BOLD}${YELLOW}[Orbit of Ops] Auto-fetching Project, Zone, and Region...${RESET}"
export PROJECT_ID=$(gcloud config get-value project 2>/dev/null)
echo -e "${CYAN}Detecting PostgreSQL VM...${RESET}"
export VM_NAME=""
while [[ -z "$VM_NAME" ]]; do
export VM_NAME=$(gcloud compute instances list --format="value(name)" 2>/dev/null | grep postgres)
if [[ -z "$VM_NAME" ]]; then
echo "Still waiting for VM to provision... retrying in 5 seconds."
sleep 5
fi
done
export ZONE=$(gcloud compute instances list --filter="name=$VM_NAME" --format="value(zone)")
export REGION=${ZONE%-*}
export INTERNAL_IP=$(gcloud compute instances describe $VM_NAME --zone=$ZONE --format="value(networkInterfaces[0].networkIP)")
echo -e "✅ Project ID: ${GREEN}$PROJECT_ID${RESET}"
echo -e "✅ VM Name: ${GREEN}$VM_NAME${RESET}"
echo -e "✅ Zone: ${GREEN}$ZONE${RESET}"
echo -e "✅ Region: ${GREEN}$REGION${RESET}\n"
echo -e "${CYAN}Enabling required APIs...${RESET}"
gcloud services enable datamigration.googleapis.com servicenetworking.googleapis.com --quiet
echo -e "${CYAN}Establishing initial SSH connection (waiting for keys to propagate)...${RESET}"
while ! gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="echo 'SSH ready'"; do
echo "Retrying SSH connection in 5 seconds..."
sleep 5
done
echo -e "${CYAN}Installing pglogical and configuring PostgreSQL on VM...${RESET}"
gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="
sudo apt-get update
sudo apt-get install postgresql-14-pglogical -y
sudo su - postgres -c 'gsutil cp gs://cloud-training/gsp918/pg_hba_append.conf .'
sudo su - postgres -c 'gsutil cp gs://cloud-training/gsp918/postgresql_append.conf .'
sudo su - postgres -c 'grep -q \"pglogical\" /etc/postgresql/14/main/pg_hba.conf || cat pg_hba_append.conf >> /etc/postgresql/14/main/pg_hba.conf'
sudo su - postgres -c 'grep -q \"pglogical\" /etc/postgresql/14/main/postgresql.conf || cat postgresql_append.conf >> /etc/postgresql/14/main/postgresql.conf'
sudo systemctl restart postgresql@14-main
"
cat << EOF > sql_commands.sql
-- 1. Create pglogical extensions
\c postgres;
CREATE EXTENSION IF NOT EXISTS pglogical;
\c orders;
CREATE EXTENSION IF NOT EXISTS pglogical;
-- 2. Create user and alter roles
\c postgres;
CREATE USER "${MIGRATION_USER}" PASSWORD 'DMS_1s_cool!';
ALTER DATABASE orders OWNER TO "${MIGRATION_USER}";
ALTER ROLE "${MIGRATION_USER}" WITH REPLICATION;
-- 3. Fix primary key
\c orders;
ALTER TABLE inventory_items ADD PRIMARY KEY (id);
-- 4. Grant pglogical schema in orders
GRANT USAGE ON SCHEMA pglogical TO "${MIGRATION_USER}";
GRANT ALL ON SCHEMA pglogical TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.tables TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.depend TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.local_node TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.local_sync_status TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.node TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.node_interface TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.queue TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set_seq TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set_table TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.sequence_state TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.subscription TO "${MIGRATION_USER}";
-- 5. Grant public schema in orders
GRANT USAGE ON SCHEMA public TO "${MIGRATION_USER}";
GRANT ALL ON SCHEMA public TO "${MIGRATION_USER}";
GRANT SELECT ON public.distribution_centers TO "${MIGRATION_USER}";
GRANT SELECT ON public.inventory_items TO "${MIGRATION_USER}";
GRANT SELECT ON public.order_items TO "${MIGRATION_USER}";
GRANT SELECT ON public.products TO "${MIGRATION_USER}";
GRANT SELECT ON public.users TO "${MIGRATION_USER}";
ALTER TABLE public.distribution_centers OWNER TO "${MIGRATION_USER}";
ALTER TABLE public.inventory_items OWNER TO "${MIGRATION_USER}";
ALTER TABLE public.order_items OWNER TO "${MIGRATION_USER}";
ALTER TABLE public.products OWNER TO "${MIGRATION_USER}";
ALTER TABLE public.users OWNER TO "${MIGRATION_USER}";
-- 6. Grant pglogical schema in postgres
\c postgres;
GRANT USAGE ON SCHEMA pglogical TO "${MIGRATION_USER}";
GRANT ALL ON SCHEMA pglogical TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.tables TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.depend TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.local_node TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.local_sync_status TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.node TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.node_interface TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.queue TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set_seq TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.replication_set_table TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.sequence_state TO "${MIGRATION_USER}";
GRANT SELECT ON pglogical.subscription TO "${MIGRATION_USER}";
EOF
echo -e "${CYAN}Executing unified SQL script on $VM_NAME...${RESET}"
gcloud compute scp sql_commands.sql $VM_NAME:/tmp/ --zone=$ZONE --quiet
gcloud compute ssh $VM_NAME --zone=$ZONE --quiet --command="sudo su - postgres -c 'psql -f /tmp/sql_commands.sql'"
echo -e "${CYAN}Creating DMS Connection Profile...${RESET}"
gcloud database-migration connection-profiles create postgresql migration-profile \
--display-name="migration-profile" \
--region=$REGION \
--host=$INTERNAL_IP \
--port=5432 \
--username="${MIGRATION_USER}" \
--password="DMS_1s_cool!" || true
echo -e "${GREEN}✅ VM Preparation and Connection Profile Completed! Please proceed to the UI steps below.${RESET}"
# Subscribe to Orbit of Ops https://www.youtube.com/@orbitofops/videosecho -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