rckrdmrd
45e77e9a9c
DDL schemas for Trading Platform: - User management - Authentication - Payments - Education - ML predictions - Trading data Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
147 lines
4.6 KiB
Bash
Executable File
147 lines
4.6 KiB
Bash
Executable File
#!/bin/bash
|
|
# ============================================================================
|
|
# MIGRATE 6 MAIN ASSETS FROM MYSQL TO POSTGRESQL
|
|
# ============================================================================
|
|
# Migra los 6 activos principales con datos de 5m desde MySQL remoto
|
|
# a PostgreSQL local (trading_platform.market_data)
|
|
#
|
|
# Activos: XAUUSD, EURUSD, BTCUSD, GBPUSD, USDJPY, AUDUSD
|
|
# Temporalidad: 5 minutos
|
|
# Cobertura: ~10 años (2015-2025)
|
|
# ============================================================================
|
|
|
|
set -e
|
|
|
|
# MySQL remoto (solo lectura)
|
|
MYSQL_HOST="72.60.226.4"
|
|
MYSQL_USER="root"
|
|
MYSQL_PASS="AfcItz2391,."
|
|
MYSQL_DB="db_trading_meta"
|
|
|
|
# PostgreSQL local
|
|
PG_HOST="${PG_HOST:-localhost}"
|
|
PG_PORT="${PG_PORT:-5432}"
|
|
PG_USER="${PG_USER:-trading_user}"
|
|
PG_PASS="${PG_PASS:-trading_dev_2025}"
|
|
PG_DB="${PG_DB:-trading_platform}"
|
|
|
|
# Colores
|
|
RED='\033[0;31m'
|
|
GREEN='\033[0;32m'
|
|
YELLOW='\033[1;33m'
|
|
BLUE='\033[0;34m'
|
|
NC='\033[0m'
|
|
|
|
log() { echo -e "${BLUE}[$(date +'%Y-%m-%d %H:%M:%S')]${NC} $1"; }
|
|
log_success() { echo -e "${GREEN}[✓]${NC} $1"; }
|
|
log_warning() { echo -e "${YELLOW}[!]${NC} $1"; }
|
|
log_error() { echo -e "${RED}[✗]${NC} $1"; }
|
|
|
|
# Mapeo de tickers MySQL → PostgreSQL ID
|
|
declare -A TICKERS=(
|
|
["C:XAUUSD"]="1"
|
|
["C:EURUSD"]="2"
|
|
["X:BTCUSD"]="3"
|
|
["C:GBPUSD"]="4"
|
|
["C:USDJPY"]="5"
|
|
["C:AUDUSD"]="6"
|
|
)
|
|
|
|
migrate_ticker() {
|
|
local mysql_sym=$1
|
|
local pg_id=$2
|
|
local tmpfile="/tmp/ticker_${pg_id}.tsv"
|
|
|
|
log "Migrando $mysql_sym (id=$pg_id)..."
|
|
|
|
# Export from MySQL
|
|
mysql -h $MYSQL_HOST -u $MYSQL_USER -p"$MYSQL_PASS" $MYSQL_DB \
|
|
--batch --skip-column-names \
|
|
-e "SELECT $pg_id, date_agg, open, high, low, close, volume, vwap, UNIX_TIMESTAMP(date_agg)*1000
|
|
FROM tickers_agg_data
|
|
WHERE ticker='$mysql_sym'
|
|
ORDER BY date_agg" 2>/dev/null \
|
|
> "$tmpfile"
|
|
|
|
local count=$(wc -l < "$tmpfile")
|
|
log " Exportados $count registros de MySQL"
|
|
|
|
# Truncate staging
|
|
PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB \
|
|
-c "TRUNCATE market_data.ohlcv_5m_staging;" -q
|
|
|
|
# Import to staging
|
|
PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB \
|
|
-c "\COPY market_data.ohlcv_5m_staging FROM '$tmpfile' WITH (FORMAT text, DELIMITER E'\t')" -q
|
|
|
|
# Upsert to main table
|
|
local inserted=$(PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -t -c "
|
|
INSERT INTO market_data.ohlcv_5m (ticker_id, timestamp, open, high, low, close, volume, vwap, ts_epoch)
|
|
SELECT DISTINCT ON (ticker_id, timestamp)
|
|
ticker_id, timestamp, open, high, low, close, volume, vwap, ts_epoch
|
|
FROM market_data.ohlcv_5m_staging
|
|
ON CONFLICT (ticker_id, timestamp) DO NOTHING;
|
|
SELECT COUNT(*) FROM market_data.ohlcv_5m WHERE ticker_id = $pg_id;
|
|
" | tr -d ' ')
|
|
|
|
log_success "$mysql_sym: $inserted registros en PostgreSQL"
|
|
rm -f "$tmpfile"
|
|
}
|
|
|
|
echo ""
|
|
echo "=============================================="
|
|
echo " Migración de 6 Activos Principales"
|
|
echo " MySQL → PostgreSQL (market_data)"
|
|
echo "=============================================="
|
|
echo ""
|
|
|
|
# Verificar conexiones
|
|
log "Verificando conexión a MySQL..."
|
|
mysql -h $MYSQL_HOST -u $MYSQL_USER -p"$MYSQL_PASS" $MYSQL_DB -e "SELECT 1" >/dev/null 2>&1 || {
|
|
log_error "No se puede conectar a MySQL"
|
|
exit 1
|
|
}
|
|
log_success "MySQL OK"
|
|
|
|
log "Verificando conexión a PostgreSQL..."
|
|
PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT 1" >/dev/null 2>&1 || {
|
|
log_error "No se puede conectar a PostgreSQL"
|
|
exit 1
|
|
}
|
|
log_success "PostgreSQL OK"
|
|
|
|
echo ""
|
|
log "Iniciando migración de 6 activos..."
|
|
echo ""
|
|
|
|
for mysql_sym in "C:XAUUSD" "C:EURUSD" "X:BTCUSD" "C:GBPUSD" "C:USDJPY" "C:AUDUSD"; do
|
|
pg_id=${TICKERS[$mysql_sym]}
|
|
migrate_ticker "$mysql_sym" "$pg_id"
|
|
done
|
|
|
|
echo ""
|
|
log "Generando datos de 15 minutos..."
|
|
PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "SELECT * FROM market_data.aggregate_all_15m();"
|
|
|
|
echo ""
|
|
echo "=============================================="
|
|
echo " Resumen Final"
|
|
echo "=============================================="
|
|
echo ""
|
|
|
|
PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB -c "
|
|
SELECT
|
|
t.symbol,
|
|
COUNT(o5.id) as rows_5m,
|
|
MIN(o5.timestamp) as desde,
|
|
MAX(o5.timestamp) as hasta,
|
|
EXTRACT(YEAR FROM AGE(MAX(o5.timestamp), MIN(o5.timestamp)))::INT as anos
|
|
FROM market_data.tickers t
|
|
LEFT JOIN market_data.ohlcv_5m o5 ON o5.ticker_id = t.id
|
|
GROUP BY t.symbol
|
|
ORDER BY t.symbol;
|
|
"
|
|
|
|
echo ""
|
|
log_success "Migración completada"
|