124 lines
4.8 KiB
Bash
Executable File
124 lines
4.8 KiB
Bash
Executable File
#!/bin/bash
|
||
# ===========================================
|
||
# 数据迁移脚本: milkydata → milkydata_dev
|
||
# 用途: 将生产库 milkydata 的数据迁移到 milkydata_dev
|
||
# 自动处理列名差异 (is_paid → is_member, paid_expires_at → membership_expires_at)
|
||
# 注意: 目标库 milkydata_dev 中已存在的数据会被清空
|
||
# ===========================================
|
||
|
||
set -euo pipefail
|
||
|
||
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
|
||
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
|
||
|
||
DB_CONTAINER="1Panel-postgresql-FtMo"
|
||
DB_USER="milkydata"
|
||
SRC_DB="milkydata"
|
||
DST_DB="milkydata_dev"
|
||
BACKUP_DIR="${PROJECT_ROOT}/backups"
|
||
|
||
mkdir -p "$BACKUP_DIR"
|
||
LOG_FILE="${BACKUP_DIR}/migration_$(date +%Y%m%d_%H%M%S).log"
|
||
|
||
log() { echo "[$(date '+%H:%M:%S')] $1" | tee -a "$LOG_FILE"; }
|
||
|
||
echo "═══════════════════════════════════════"
|
||
echo " 数据迁移: ${SRC_DB} → ${DST_DB}"
|
||
echo "═══════════════════════════════════════"
|
||
|
||
# ---------- 步骤 0: 确认 ----------
|
||
echo ""
|
||
echo "源库: ${SRC_DB}"
|
||
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$SRC_DB" -tAc "
|
||
SELECT CONCAT(' users: ', COUNT(*), ' 行') FROM users;
|
||
SELECT CONCAT(' weather_data: ', COUNT(*), ' 行') FROM weather_data;
|
||
SELECT CONCAT(' payment_orders: ', COUNT(*), ' 行') FROM payment_orders;
|
||
"
|
||
echo ""
|
||
echo "目标库: ${DST_DB}（数据将被清空并重新导入）"
|
||
echo ""
|
||
read -p "确认迁移? [y/N] " -n 1 -r; echo
|
||
[[ ! $REPLY =~ ^[Yy]$ ]] && { echo "已取消"; exit 0; }
|
||
|
||
# ---------- 步骤 1: 备份目标库 ----------
|
||
log "备份目标库 ${DST_DB}..."
|
||
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$DST_DB" -Fc \
|
||
> "${BACKUP_DIR}/${DST_DB}_pre_migration_$(date +%Y%m%d_%H%M%S).dump" 2>> "$LOG_FILE"
|
||
log " 目标库备份完成 ✅"
|
||
|
||
# ---------- 步骤 2: 清空目标库 ----------
|
||
log "清空目标库 ${DST_DB}..."
|
||
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "
|
||
TRUNCATE weather_data CASCADE;
|
||
TRUNCATE payment_orders CASCADE;
|
||
TRUNCATE notifications CASCADE;
|
||
TRUNCATE payment_audit_log CASCADE;
|
||
DELETE FROM users CASCADE;
|
||
" 2>> "$LOG_FILE"
|
||
log " 清空完成 ✅"
|
||
|
||
# ---------- 步骤 3: 导出源库数据到 SQL 文件 ----------
|
||
log "导出源库数据..."
|
||
TMP_SQL="/tmp/migrate_$$.sql"
|
||
|
||
# users 表（列名转换: is_paid → is_member, paid_expires_at → membership_expires_at）
|
||
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
|
||
--data-only --table=users --column-inserts 2>/dev/null \
|
||
| grep -v '^--' | grep -v '^$' \
|
||
| sed 's/ is_paid/ is_member/g' \
|
||
| sed 's/ paid_expires_at/ membership_expires_at/g' \
|
||
> "$TMP_SQL"
|
||
log " users 导出并转换列名 ✅"
|
||
|
||
# weather_data
|
||
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
|
||
--data-only --table=weather_data --column-inserts 2>/dev/null \
|
||
| grep -v '^--' | grep -v '^$' >> "$TMP_SQL"
|
||
log " weather_data 导出 ✅"
|
||
|
||
# payment_orders
|
||
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
|
||
--data-only --table=payment_orders --column-inserts 2>/dev/null \
|
||
| grep -v '^--' | grep -v '^$' >> "$TMP_SQL"
|
||
log " payment_orders 导出 ✅"
|
||
|
||
log " 共 $(wc -l < "$TMP_SQL") 条 SQL 语句"
|
||
|
||
# ---------- 步骤 4: 导入到目标库 ----------
|
||
log "导入数据到 ${DST_DB}..."
|
||
docker exec -i "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" < "$TMP_SQL" 2>> "$LOG_FILE"
|
||
log " 导入完成 ✅"
|
||
|
||
# ---------- 步骤 5: 重置自增序列 ----------
|
||
log "重置自增序列..."
|
||
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "
|
||
SELECT SETVAL('users_id_seq', COALESCE((SELECT MAX(id) FROM users), 1));
|
||
SELECT SETVAL('weather_data_id_seq', COALESCE((SELECT MAX(id) FROM weather_data), 1));
|
||
SELECT SETVAL('payment_orders_id_seq', COALESCE((SELECT MAX(id) FROM payment_orders), 1));
|
||
" 2>> "$LOG_FILE"
|
||
log " 序列重置完成 ✅"
|
||
|
||
# ---------- 清理 ----------
|
||
rm -f "$TMP_SQL"
|
||
|
||
# ---------- 验证 ----------
|
||
echo ""
|
||
log "═════════════════════════════════"
|
||
log "迁移完成！源库 vs 目标库数据量："
|
||
log "═════════════════════════════════"
|
||
|
||
for table in users weather_data payment_orders; do
|
||
src=$(docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$SRC_DB" -tAc "SELECT COUNT(*) FROM ${table};" 2>/dev/null)
|
||
dst=$(docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "SELECT COUNT(*) FROM ${table};" 2>/dev/null)
|
||
status="✅"
|
||
[ "$src" != "$dst" ] && status="❌ 不匹配!"
|
||
log " ${table}: ${src} → ${dst} ${status}"
|
||
done
|
||
|
||
echo ""
|
||
echo "日志: ${LOG_FILE}"
|
||
echo "备份: ${BACKUP_DIR}/${DST_DB}_pre_migration_*.dump"
|
||
echo ""
|
||
echo "⚠️ 如需回滚：
|
||
docker exec -i ${DB_CONTAINER} pg_restore -U ${DB_USER} -d ${DST_DB} --clean \n < ${BACKUP_DIR}/${DST_DB}_pre_migration_*.dump"
|