← back to Watches

database/deploy-database.sh

397 lines

#!/bin/bash

################################################################################
# PostgreSQL Database Deployment Script
################################################################################
# Complete database setup with all enterprise features
#
# Features deployed:
#   - Enhanced schema with partitioning
#   - Full-text search indices
#   - Materialized views
#   - ETL pipeline execution
#   - Performance optimization
#   - Monitoring setup
################################################################################

set -e

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m'

# Configuration
DB_NAME="${DB_NAME:-omega_watches}"
DB_USER="${DB_USER:-postgres}"
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

echo -e "${GREEN}"
cat << "EOF"
╔════════════════════════════════════════════════════════════════╗
║     PostgreSQL Database Deployment - Enterprise Edition        ║
║     Omega Watches Price History System                         ║
╚════════════════════════════════════════════════════════════════╝
EOF
echo -e "${NC}"

print_step() {
    echo -e "\n${BLUE}==>${NC} ${GREEN}$1${NC}"
}

print_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

print_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

print_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

check_postgres() {
    print_step "Checking PostgreSQL installation..."

    if ! command -v psql &> /dev/null; then
        print_error "PostgreSQL is not installed"
        exit 1
    fi

    print_info "PostgreSQL version: $(psql --version)"

    # Check if PostgreSQL is running
    if ! sudo systemctl is-active --quiet postgresql; then
        print_warn "PostgreSQL is not running. Starting..."
        sudo systemctl start postgresql
    fi

    print_info "PostgreSQL is running"
}

check_database_exists() {
    print_step "Checking database status..."

    if sudo -u postgres psql -lqt | cut -d \| -f 1 | grep -qw "$DB_NAME"; then
        print_info "Database '$DB_NAME' exists"
        return 0
    else
        print_warn "Database '$DB_NAME' does not exist"
        return 1
    fi
}

create_database() {
    print_step "Creating database..."

    sudo -u postgres psql << EOF
-- Create database
CREATE DATABASE ${DB_NAME}
    WITH ENCODING 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- Connect to database and add comment
\c ${DB_NAME}
COMMENT ON DATABASE ${DB_NAME} IS 'Omega Watch Price History Tracking System - Enterprise Edition';
EOF

    print_info "Database created successfully"
}

deploy_schema() {
    print_step "Deploying enhanced schema..."

    if [ -f "${SCRIPT_DIR}/enhanced-schema.sql" ]; then
        sudo -u postgres psql -d ${DB_NAME} -f "${SCRIPT_DIR}/enhanced-schema.sql"
        print_info "Schema deployed successfully"
    else
        print_error "Schema file not found: ${SCRIPT_DIR}/enhanced-schema.sql"
        exit 1
    fi
}

run_etl_pipeline() {
    print_step "Running ETL pipeline..."

    if [ -f "${SCRIPT_DIR}/etl-pipeline.js" ]; then
        cd "$PROJECT_ROOT"
        node "${SCRIPT_DIR}/etl-pipeline.js"
        print_info "ETL pipeline completed"
    else
        print_error "ETL pipeline script not found"
        exit 1
    fi
}

optimize_database() {
    print_step "Optimizing database..."

    sudo -u postgres psql -d ${DB_NAME} << EOF
-- Refresh all materialized views
SELECT omega.refresh_all_materialized_views();

-- Analyze all tables for query planner
ANALYZE omega.watches;
ANALYZE omega.specifications;
ANALYZE omega.features;
ANALYZE omega.watch_features;
ANALYZE omega.price_history;

-- Vacuum for optimal performance
VACUUM ANALYZE omega.watches;
VACUUM ANALYZE omega.price_history;

-- Create additional monitoring views
CREATE OR REPLACE VIEW omega.database_stats AS
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_live_tup as row_count,
    n_dead_tup as dead_rows,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'omega'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage statistics
CREATE OR REPLACE VIEW omega.index_usage AS
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'omega'
ORDER BY idx_scan DESC;
EOF

    print_info "Database optimized"
}

create_backup_script() {
    print_step "Creating backup script..."

    cat > /tmp/backup-omega-db.sh << 'BACKUP_EOF'
#!/bin/bash

# PostgreSQL Backup Script for Omega Watches
BACKUP_DIR="/var/backups/postgresql/omega_watches"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DB_NAME="omega_watches"

mkdir -p "$BACKUP_DIR"

echo "Starting backup: $TIMESTAMP"

# Full database dump
pg_dump -U postgres -Fc "$DB_NAME" > "${BACKUP_DIR}/omega_watches_${TIMESTAMP}.dump"

# Schema only backup
pg_dump -U postgres -s "$DB_NAME" > "${BACKUP_DIR}/schema_${TIMESTAMP}.sql"

# Data only backup
pg_dump -U postgres -a "$DB_NAME" > "${BACKUP_DIR}/data_${TIMESTAMP}.sql"

# Compress old backups
find "$BACKUP_DIR" -name "*.dump" -mtime +7 -exec gzip {} \;
find "$BACKUP_DIR" -name "*.sql" -mtime +7 -exec gzip {} \;

# Remove backups older than 30 days
find "$BACKUP_DIR" -name "*.gz" -mtime +30 -delete

echo "Backup completed: ${BACKUP_DIR}/omega_watches_${TIMESTAMP}.dump"
BACKUP_EOF

    chmod +x /tmp/backup-omega-db.sh
    sudo mv /tmp/backup-omega-db.sh /usr/local/bin/backup-omega-db

    print_info "Backup script installed: /usr/local/bin/backup-omega-db"
}

setup_monitoring() {
    print_step "Setting up monitoring..."

    sudo -u postgres psql -d ${DB_NAME} << EOF
-- Enable pg_stat_statements extension for query monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Create monitoring function
CREATE OR REPLACE FUNCTION omega.get_system_stats()
RETURNS TABLE (
    metric VARCHAR,
    value TEXT
) AS \$\$
BEGIN
    RETURN QUERY
    SELECT 'Total Watches'::VARCHAR, COUNT(*)::TEXT FROM omega.watches WHERE is_active = true
    UNION ALL
    SELECT 'Total Price Points', COUNT(*)::TEXT FROM omega.price_history
    UNION ALL
    SELECT 'Active Series', COUNT(DISTINCT series)::TEXT FROM omega.watches WHERE is_active = true
    UNION ALL
    SELECT 'Database Size', pg_size_pretty(pg_database_size(current_database()))
    UNION ALL
    SELECT 'Largest Table',
           (SELECT tablename FROM omega.database_stats LIMIT 1)
    UNION ALL
    SELECT 'Total Queries (Session)',
           (SELECT SUM(calls)::TEXT FROM pg_stat_statements)
    UNION ALL
    SELECT 'Cache Hit Ratio',
           ROUND(100.0 * SUM(blks_hit) / NULLIF(SUM(blks_hit + blks_read), 0), 2)::TEXT || '%'
    FROM pg_stat_database WHERE datname = current_database();
END;
\$\$ LANGUAGE plpgsql;
EOF

    print_info "Monitoring setup complete"
}

display_summary() {
    print_step "Deployment Summary"

    sudo -u postgres psql -d ${DB_NAME} << EOF
\echo '==================================================================='
\echo 'Database Deployment Summary'
\echo '==================================================================='
\echo ''

SELECT * FROM omega.get_system_stats();

\echo ''
\echo 'Top 5 Watch Collections:'
SELECT series, COUNT(*) as watches
FROM omega.watches
WHERE is_active = true
GROUP BY series
ORDER BY watches DESC
LIMIT 5;

\echo ''
\echo 'Price Statistics:'
SELECT
    MIN(price)::NUMERIC(12,2) as min_price,
    MAX(price)::NUMERIC(12,2) as max_price,
    AVG(price)::NUMERIC(12,2) as avg_price,
    COUNT(*) as total_price_points
FROM omega.price_history;

\echo ''
\echo 'Materialized Views:'
SELECT
    schemaname,
    matviewname,
    pg_size_pretty(pg_relation_size(schemaname||'.'||matviewname)) as size
FROM pg_matviews
WHERE schemaname = 'omega';

\echo ''
\echo '==================================================================='
EOF
}

create_env_file() {
    print_step "Creating environment file..."

    if [ ! -f "$PROJECT_ROOT/.env.db" ]; then
        cat > "$PROJECT_ROOT/.env.db" << ENVEOF
# PostgreSQL Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=${DB_NAME}
DB_USER=${DB_USER}
DB_PASSWORD=

# Connection Pool Settings
DB_POOL_MAX=20
DB_POOL_MIN=5
DB_STATEMENT_TIMEOUT=10000

# Replica Configuration (optional)
DB_REPLICA_HOST=
DB_REPLICA_PORT=5432
ENVEOF
        print_info "Environment file created: $PROJECT_ROOT/.env.db"
        print_warn "Please update DB_PASSWORD in .env.db"
    else
        print_info "Environment file already exists"
    fi
}

################################################################################
# MAIN EXECUTION
################################################################################

main() {
    echo ""
    print_info "Starting deployment at $(date)"
    echo ""

    # Pre-flight checks
    check_postgres

    # Database setup
    if check_database_exists; then
        read -p "Database exists. Drop and recreate? (y/N): " -n 1 -r
        echo
        if [[ $REPLY =~ ^[Yy]$ ]]; then
            print_warn "Dropping existing database..."
            sudo -u postgres psql -c "DROP DATABASE IF EXISTS ${DB_NAME};"
            create_database
            deploy_schema
            run_etl_pipeline
        else
            print_info "Using existing database"
            deploy_schema
        fi
    else
        create_database
        deploy_schema
        run_etl_pipeline
    fi

    # Post-deployment optimization
    optimize_database
    setup_monitoring
    create_backup_script
    create_env_file

    # Summary
    display_summary

    echo ""
    print_info "✅ Deployment completed successfully at $(date)"
    echo ""

    print_info "Next Steps:"
    echo "  1. Update database password in .env.db"
    echo "  2. Run backup: sudo /usr/local/bin/backup-omega-db"
    echo "  3. Setup replication (optional): ./database/setup-replication.sh master"
    echo "  4. Start application with PostgreSQL enabled"
    echo ""

    print_info "Useful Commands:"
    echo "  - Monitor replication: monitor-replication"
    echo "  - Database stats: psql -d ${DB_NAME} -c 'SELECT * FROM omega.get_system_stats()'"
    echo "  - Refresh views: psql -d ${DB_NAME} -c 'SELECT omega.refresh_all_materialized_views()'"
    echo ""
}

# Run main function
main "$@"