150 lines
4.7 KiB
Bash
150 lines
4.7 KiB
Bash
#!/bin/bash
|
||
# =============================================================================
|
||
# GCP 正式環境 → UAT 測試環境 資料同步腳本
|
||
# =============================================================================
|
||
# 用途：將 GCP PostgreSQL 資料同步到 UAT PostgreSQL
|
||
# 執行頻率：每日 02:00 (避開業務高峰)
|
||
# 同步方向：GCP (正式) → UAT (測試)
|
||
# =============================================================================
|
||
|
||
set -e
|
||
|
||
# 配置
|
||
GCP_PROJECT="astral-gateway-484913-d7"
|
||
GCP_ZONE="asia-east1-b"
|
||
GCP_VM="momo-pro-gcp"
|
||
|
||
UAT_HOST="192.168.0.110"
|
||
UAT_USER="wooo"
|
||
|
||
DB_NAME="momo_analytics"
|
||
DB_USER="momo"
|
||
|
||
# Telegram 通知
|
||
TELEGRAM_BOT_TOKEN="<TELEGRAM_BOT_TOKEN>"
|
||
TELEGRAM_CHAT_ID="5619078117"
|
||
|
||
# 暫存目錄
|
||
TEMP_DIR="/tmp/momo_sync_$(date +%Y%m%d_%H%M%S)"
|
||
mkdir -p "$TEMP_DIR"
|
||
|
||
# 需要同步的資料表（按優先順序）
|
||
SYNC_TABLES=(
|
||
"products"
|
||
"categories"
|
||
"price_records"
|
||
"daily_sales_snapshot"
|
||
"realtime_sales_monthly"
|
||
"monthly_summary_analysis"
|
||
"promo_products"
|
||
)
|
||
|
||
# 日誌函數
|
||
log() {
|
||
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
|
||
}
|
||
|
||
# 發送 Telegram 通知
|
||
send_telegram() {
|
||
local message="$1"
|
||
curl -s -X POST "https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage" \
|
||
-d chat_id="${TELEGRAM_CHAT_ID}" \
|
||
-d parse_mode="HTML" \
|
||
-d text="$message" > /dev/null 2>&1 || true
|
||
}
|
||
|
||
# 錯誤處理
|
||
error_exit() {
|
||
log "ERROR: $1"
|
||
send_telegram "🔴 <b>資料同步失敗</b>%0A%0A❌ $1%0A⏰ $(date '+%Y-%m-%d %H:%M:%S')"
|
||
rm -rf "$TEMP_DIR"
|
||
exit 1
|
||
}
|
||
|
||
# 開始同步
|
||
log "=========================================="
|
||
log "開始 GCP → UAT 資料同步"
|
||
log "=========================================="
|
||
|
||
START_TIME=$(date +%s)
|
||
|
||
# Step 1: 從 GCP 匯出資料
|
||
log "Step 1: 從 GCP 匯出資料..."
|
||
|
||
for table in "${SYNC_TABLES[@]}"; do
|
||
log " 匯出 $table..."
|
||
|
||
# 使用 gcloud 執行 pg_dump
|
||
gcloud compute ssh "$GCP_VM" \
|
||
--zone="$GCP_ZONE" \
|
||
--project="$GCP_PROJECT" \
|
||
--command="sudo kubectl exec -n momo momo-postgres-0 -- pg_dump -U $DB_USER -d $DB_NAME -t $table --data-only --column-inserts" \
|
||
> "$TEMP_DIR/${table}.sql" 2>/dev/null || error_exit "匯出 $table 失敗"
|
||
|
||
# 檢查檔案大小
|
||
size=$(du -h "$TEMP_DIR/${table}.sql" | cut -f1)
|
||
log " ✓ $table ($size)"
|
||
done
|
||
|
||
# Step 2: 傳輸到 UAT
|
||
log "Step 2: 傳輸到 UAT..."
|
||
scp -o StrictHostKeyChecking=no -r "$TEMP_DIR" "${UAT_USER}@${UAT_HOST}:/tmp/" || error_exit "傳輸失敗"
|
||
|
||
# Step 3: 在 UAT 匯入資料
|
||
log "Step 3: 在 UAT 匯入資料..."
|
||
|
||
SYNC_DIR=$(basename "$TEMP_DIR")
|
||
|
||
for table in "${SYNC_TABLES[@]}"; do
|
||
log " 匯入 $table..."
|
||
|
||
# 先清空表（使用 TRUNCATE CASCADE 處理外鍵）
|
||
ssh -o StrictHostKeyChecking=no "${UAT_USER}@${UAT_HOST}" "
|
||
export KUBECONFIG=/home/wooo/.kube/config
|
||
kubectl exec -n momo momo-postgres-0 -- psql -U $DB_USER -d $DB_NAME -c 'TRUNCATE TABLE $table CASCADE;'
|
||
" 2>/dev/null || log " 警告: 清空 $table 失敗 (可能表不存在)"
|
||
|
||
# 匯入資料
|
||
ssh -o StrictHostKeyChecking=no "${UAT_USER}@${UAT_HOST}" "
|
||
export KUBECONFIG=/home/wooo/.kube/config
|
||
cat /tmp/${SYNC_DIR}/${table}.sql | kubectl exec -i -n momo momo-postgres-0 -- psql -U $DB_USER -d $DB_NAME
|
||
" 2>/dev/null || error_exit "匯入 $table 失敗"
|
||
|
||
log " ✓ $table 匯入完成"
|
||
done
|
||
|
||
# Step 4: 清理暫存檔案
|
||
log "Step 4: 清理暫存檔案..."
|
||
rm -rf "$TEMP_DIR"
|
||
ssh -o StrictHostKeyChecking=no "${UAT_USER}@${UAT_HOST}" "rm -rf /tmp/${SYNC_DIR}" 2>/dev/null || true
|
||
|
||
# 計算耗時
|
||
END_TIME=$(date +%s)
|
||
DURATION=$((END_TIME - START_TIME))
|
||
MINUTES=$((DURATION / 60))
|
||
SECONDS=$((DURATION % 60))
|
||
|
||
# 驗證同步結果
|
||
log "Step 5: 驗證同步結果..."
|
||
|
||
# 取得 UAT 資料表統計
|
||
UAT_STATS=$(ssh -o StrictHostKeyChecking=no "${UAT_USER}@${UAT_HOST}" "
|
||
export KUBECONFIG=/home/wooo/.kube/config
|
||
kubectl exec -n momo momo-postgres-0 -- psql -U $DB_USER -d $DB_NAME -t -c \"
|
||
SELECT relname, n_live_tup FROM pg_stat_user_tables
|
||
WHERE relname IN ('products', 'price_records', 'daily_sales_snapshot', 'realtime_sales_monthly')
|
||
ORDER BY relname;
|
||
\"
|
||
" 2>/dev/null)
|
||
|
||
log "=========================================="
|
||
log "同步完成！耗時: ${MINUTES}分${SECONDS}秒"
|
||
log "=========================================="
|
||
log "UAT 資料表統計:"
|
||
echo "$UAT_STATS"
|
||
|
||
# 發送成功通知
|
||
send_telegram "✅ <b>GCP → UAT 資料同步完成</b>%0A%0A📊 同步資料表: ${#SYNC_TABLES[@]} 個%0A⏱️ 耗時: ${MINUTES}分${SECONDS}秒%0A%0A📋 <b>UAT 資料統計</b>:%0A<pre>$(echo "$UAT_STATS" | head -10)</pre>%0A%0A⏰ $(date '+%Y-%m-%d %H:%M:%S')"
|
||
|
||
log "同步完成！"
|