108 lines
2.7 KiB
Bash
Executable File
108 lines
2.7 KiB
Bash
Executable File
#!/bin/bash
|
|
# Database Backup Script for Coolify Migration
|
|
# Exports PostgreSQL database schema and data
|
|
|
|
set -e
|
|
|
|
# Load environment variables
|
|
if [ -f .env.local ]; then
|
|
export $(cat .env.local | grep -v '^#' | xargs)
|
|
fi
|
|
|
|
# Get DATABASE_URL
|
|
DB_URL="${RUNTIME_DATABASE_URL:-$DATABASE_URL}"
|
|
|
|
if [ -z "$DB_URL" ]; then
|
|
echo "❌ Error: DATABASE_URL or RUNTIME_DATABASE_URL not set"
|
|
exit 1
|
|
fi
|
|
|
|
# Extract connection details
|
|
DB_NAME=$(echo $DB_URL | sed -n 's/.*\/\([^?]*\).*/\1/p')
|
|
DB_USER=$(echo $DB_URL | sed -n 's/.*:\/\/\([^:]*\):.*/\1/p')
|
|
DB_PASS=$(echo $DB_URL | sed -n 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/p')
|
|
DB_HOST=$(echo $DB_URL | sed -n 's/.*@\([^:]*\):.*/\1/p')
|
|
DB_PORT=$(echo $DB_URL | sed -n 's/.*:\([0-9]*\)\/.*/\1/p' || echo "5432")
|
|
|
|
# Create backup directory
|
|
BACKUP_DIR="backups/$(date +%Y%m%d_%H%M%S)"
|
|
mkdir -p "$BACKUP_DIR"
|
|
|
|
echo "📦 Backing up database..."
|
|
echo " Host: $DB_HOST"
|
|
echo " Port: $DB_PORT"
|
|
echo " Database: $DB_NAME"
|
|
echo " User: $DB_USER"
|
|
echo " Backup directory: $BACKUP_DIR"
|
|
|
|
# Export schema only
|
|
echo "📋 Exporting schema..."
|
|
PGPASSWORD="$DB_PASS" pg_dump \
|
|
-h "$DB_HOST" \
|
|
-p "$DB_PORT" \
|
|
-U "$DB_USER" \
|
|
-d "$DB_NAME" \
|
|
--schema-only \
|
|
--no-owner \
|
|
--no-privileges \
|
|
> "$BACKUP_DIR/schema.sql"
|
|
|
|
# Export data only
|
|
echo "💾 Exporting data..."
|
|
PGPASSWORD="$DB_PASS" pg_dump \
|
|
-h "$DB_HOST" \
|
|
-p "$DB_PORT" \
|
|
-U "$DB_USER" \
|
|
-d "$DB_NAME" \
|
|
--data-only \
|
|
--no-owner \
|
|
--no-privileges \
|
|
> "$BACKUP_DIR/data.sql"
|
|
|
|
# Export full dump (schema + data)
|
|
echo "📦 Creating full dump..."
|
|
PGPASSWORD="$DB_PASS" pg_dump \
|
|
-h "$DB_HOST" \
|
|
-p "$DB_PORT" \
|
|
-U "$DB_USER" \
|
|
-d "$DB_NAME" \
|
|
--no-owner \
|
|
--no-privileges \
|
|
> "$BACKUP_DIR/full_dump.sql"
|
|
|
|
# Export Prisma migrations state
|
|
echo "🔄 Exporting Prisma migrations..."
|
|
if [ -d "prisma/migrations" ]; then
|
|
cp -r prisma/migrations "$BACKUP_DIR/"
|
|
cp prisma/schema.prisma "$BACKUP_DIR/"
|
|
fi
|
|
|
|
# Create metadata file
|
|
cat > "$BACKUP_DIR/metadata.json" << EOF
|
|
{
|
|
"backup_date": "$(date -u +%Y-%m-%dT%H:%M:%SZ)",
|
|
"database_name": "$DB_NAME",
|
|
"database_host": "$DB_HOST",
|
|
"database_port": "$DB_PORT",
|
|
"prisma_version": "$(npx prisma --version 2>/dev/null | head -1 || echo 'unknown')",
|
|
"files": {
|
|
"schema": "schema.sql",
|
|
"data": "data.sql",
|
|
"full_dump": "full_dump.sql",
|
|
"migrations": "migrations/",
|
|
"schema_prisma": "schema.prisma"
|
|
}
|
|
}
|
|
EOF
|
|
|
|
echo "✅ Backup complete!"
|
|
echo "📁 Backup location: $BACKUP_DIR"
|
|
echo ""
|
|
echo "Files created:"
|
|
echo " - schema.sql (schema only)"
|
|
echo " - data.sql (data only)"
|
|
echo " - full_dump.sql (schema + data)"
|
|
echo " - migrations/ (Prisma migrations)"
|
|
echo " - schema.prisma (Prisma schema)"
|
|
echo " - metadata.json (backup metadata)"
|