Arcade Base Camp September 2026

Solution for Arcade Base Camp September 2026. 1 lab: GSP918. Fast copy-paste commands for Google Cloud.

GSP918 — Migrate to Cloud SQL for PostgreSQL Using Database Migration Service

Estimated time: 1 hour

# 🚀 Database Migration Service: PostgreSQL to Cloud SQL > ⚠️ **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 Google Cloud services and complete practical exercises. Always attempt the lab yourself first and follow Google Cloud Skills Boost / Qwiklabs Terms of Service. This walkthrough is not affiliated with or endorsed by Google,

clear
CYAN='\e[1;36m'
BLUE='\e[1;34m'
RESET='\e[0m'
BOLD='\e[1m'
GREEN='\e[1;32m'
YELLOW='\e[1;33m'

echo -e "${CYAN}${BOLD}"
echo "   ____        _     _ _            __   ___              "
echo "  / __ \      | |   (_) |          / _| / _ \             "
echo " | |  | |_ __| |__   _| |_  ___   | |_ | | | |_ __  ___   "
echo " | |  | | '__| '_ \| | __| / _ \  |  _|| | | | '_ \/ __|  "
echo " | |__| | |  | |_) | | |_ | (_) | | |  | |_| | |_) \__ \  "
echo "  \____/|_|  |_.__/|_|\__| \___/  |_|   \___/| .__/|___/  "
echo "                                             | |          "
echo "                                             |_|          "
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 "${YELLOW}Extracting dynamic Region, Zone, and Source IP...${RESET}"
export VM_NAME="postgresql-vm"
export ZONE=$(gcloud compute instances list --filter="name=$VM_NAME" --format="value(zone)" 2>/dev/null)
export REGION=${ZONE%-*}
export INTERNAL_IP=$(gcloud compute instances describe $VM_NAME --zone=$ZONE --format="value(networkInterfaces[0].networkIP)" 2>/dev/null)

echo -e "✅ Region:          ${GREEN}$REGION${RESET}"
echo -e "✅ Zone:            ${GREEN}$ZONE${RESET}"
echo -e "✅ Source IP:       ${GREEN}$INTERNAL_IP${RESET}\n"

echo -e "${CYAN}Enabling Database Migration and Service Networking APIs...${RESET}"
gcloud services enable datamigration.googleapis.com servicenetworking.googleapis.com --quiet

echo -e "${CYAN}Hardening source PostgreSQL VM for logical replication...${RESET}"
cat << 'SCRIPT_EOF' > prep.sh
#!/bin/bash
sudo apt-get update && sudo apt-get install -y postgresql-14-pglogical >/dev/null 2>&1

sudo bash -c 'cat << "CONF_EOF" >> /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_EOF'

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

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

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

for db in postgres orders gmemegen_db; do
  sudo -u postgres psql -q -d "$db" << 'SQL_EOF'
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_EOF
done

sudo -u postgres psql -q -d orders << 'SQL_EOF'
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
SCRIPT_EOF

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

echo -e "${CYAN}Creating PostgreSQL Source Connection Profile (postgres-vm)...${RESET}"
gcloud database-migration connection-profiles create postgresql postgres-vm \
    --display-name="postgres-vm" \
    --region=$REGION \
    --host=$INTERNAL_IP \
    --port=5432 \
    --username="migration_admin" \
    --password="DMS_1s_cool!" \
    --quiet

echo -e "\n${GREEN}${BOLD}✅ Command 1 Complete! Click 'Check my progress' on Task 1 and proceed to the Manual UI Steps below.${RESET}"

CYAN='\e[1;36m'
BLUE='\e[1;34m'
RESET='\e[0m'
BOLD='\e[1m'
GREEN='\e[1;32m'
YELLOW='\e[1;33m'

echo -e "${BLUE}${BOLD}╔════════════════════════════════════════════════════════════╗${RESET}"
echo -e "${BLUE}${BOLD}║    🚀 COMMAND 2 OF 2: REPLICATION TEST & PROMOTION         ║${RESET}"
echo -e "${BLUE}${BOLD}╚════════════════════════════════════════════════════════════╝${RESET}\n"

export VM_NAME="postgresql-vm"
export ZONE=$(gcloud compute instances list --filter="name=$VM_NAME" --format="value(zone)" 2>/dev/null)
export REGION=${ZONE%-*}

echo -e "${CYAN}Testing Continuous Sync: Inserting dummy record into source database on $VM_NAME...${RESET}"

# Using a secure heredoc to bypass bash exclamation mark (!) history expansion errors
cat << 'EOF' > insert_test.sh
PGPASSWORD='DMS_1s_cool!' psql -h localhost -p 5432 -d orders -U migration_admin -c "insert into distribution_centers values(-80.1918,25.7617,'Miami FL',11);"
EOF

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

echo -e "${YELLOW}Waiting 30 seconds for Database Migration Service to replicate the change...${RESET}"
sleep 30

echo -e "${CYAN}Promoting the Cloud SQL Instance to Standalone (This takes 1-2 minutes)...${RESET}"
gcloud database-migration migration-jobs promote vm-to-cloudsql --region=$REGION --quiet

echo ""
echo -e "${GREEN}${BOLD}# ─────────────────────────────────────────────────────────────${RESET}"
echo -e "${GREEN}${BOLD}#  Lab cleared? Like the video and subscribe to Orbit of Ops 🚀${RESET}"
echo -e "${GREEN}${BOLD}#  youtube.com/@orbitofops${RESET}"
echo -e "${GREEN}${BOLD}# ─────────────────────────────────────────────────────────────${RESET}"