DEV Community: Mohamed Ammar

Database Migration Guide: MySQL to PostgreSQL using pgloader and Prisma

Mohamed Ammar — Sun, 07 Dec 2025 11:06:13 +0000

Published on: 07/12/2025 | Tags: #database #migration #mysql #postgresql #prisma #devops

Introduction

Migrating databases can be daunting, but with the right tools and approach, it becomes manageable. In this guide, I'll walk you through migrating from MySQL to PostgreSQL using pgloader for data transfer and Prisma ORM for schema management and evolution.

Why This Combination?

pgloader: Excellent for bulk data migration with type casting and data transformation
Prisma ORM: Perfect for schema management, evolution, and providing type-safe database access

Prerequisites

Environment Setup

Linux environment (WSL on Windows, Ubuntu, or similar)
MySQL database with existing schema and data
PostgreSQL instance ready for migration
Both instances could be local or remote (just update connection strings)

Installation Requirements

# Install pgloader
sudo apt-get install pgloader

# Install Node.js and npm (if not already installed)
sudo apt-get install nodejs npm

# Install Prisma CLI version 6.x (not version 7)
npm install -g prisma@6.x

# Install necessary database connectors
npm install @prisma/client@6.x prisma@6.x
npm install @prisma/client-mysql@6.x @prisma/client-pg@6.x

Step 1: Initialize Prisma and Extract MySQL Schema

First, let's set up Prisma and extract our existing MySQL schema:

bash
# Initialize a new Prisma project
mkdir db-migration-project
cd db-migration-project
npx prisma init

# Configure your Prisma schema to connect to MySQL
# Update prisma/schema.prisma with MySQL connection
Your prisma/schema.prisma should look like this initially:

prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = "mysql://root:password@localhost:3306/railway"
}
Now, pull the schema from your MySQL database:

bash
# Extract schema from MySQL
npx prisma db pull

# This creates a Prisma schema file representing your MySQL structure
# Review the generated schema in prisma/schema.prisma

Step 2: Create Migration File
Create your migration file for pgloader. Let's create a file called migration.load:


bash
# Create migration configuration
nano migration.load

Add the following configuration to migration.load:

LOAD DATABASE
    FROM mysql://root:password@localhost:3306/railway
    INTO postgresql://postgres:postgres@localhost:5432/railway

    WITH include drop, 
         quote identifiers, 
         create tables,
         foreign keys, 
         create indexes,
         reset sequences,
         workers = 8,
         concurrency = 1

    CAST
        -- Map MySQL datetime to PostgreSQL timestamp without time zone
        type datetime to timestamp,

        -- Map large text types to PostgreSQL text
        type longtext to text,

        -- Map MySQL integer types appropriately
        type int to integer,
        type tinyint to boolean using tinyint-to-boolean,

        -- Handle MySQL specific types
        type year to integer

    BEFORE LOAD DO
        $$ CREATE SCHEMA IF NOT EXISTS railway; $$,
        $$ SET search_path TO railway; $$;

Key Configuration Notes:
include drop: Drops tables in PostgreSQL if they exist

quote identifiers: Ensures special characters in table/column names are handled

create tables: Creates tables in PostgreSQL

reset sequences: Resets PostgreSQL sequences to match MySQL auto-increment values

workers: Parallel workers for faster migration (adjust based on your system)

Step 3: Execute Data Migration
Now, run the migration using pgloader:

bash
# Execute the migration
pgloader migration.load

Monitor the progress - pgloader provides real-time statistics
What Happens During Migration:
pgloader connects to both databases

It reads the MySQL schema and creates equivalent PostgreSQL tables

Data is transferred with appropriate type casting

Indexes and foreign keys are recreated

Sequences are reset to maintain auto-increment values

Step 4: Handle Migration Report
After migration completes, pgloader provides a detailed report:

text

Summary report:

Total transfer time : 1m 30s
Total bytes transferred : 2.5 GB
Average transfer rate : 28.3 MB/s
Errors : 0
Warnings : 2
Review any warnings or errors and address them accordingly.

Step 5: Update Prisma to PostgreSQL
Now that your data is in PostgreSQL, update your Prisma configuration:

bash
# Update Prisma schema to use PostgreSQL
nano prisma/schema.prisma

Change the datasource provider to PostgreSQL:

prisma
datasource db {
provider = "postgresql"
url = "postgresql://postgres:postgres@localhost:5432/railway"
}
Pull the schema from PostgreSQL to ensure Prisma understands the new database:

bash
# Extract schema from PostgreSQL
npx prisma db pull

# Generate Prisma Client for PostgreSQL
npx prisma generate

# Optional: Push schema to ensure consistency
npx prisma db push

Step 6: Update Your Application
Update your application's database connection:

javascript
// In your application code
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

// Your Prisma client now connects to PostgreSQL

Benefits of Using Prisma ORM for Schema Evolution

Type Safety

typescript
// Full TypeScript/JavaScript type safety
const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' }
})
// `user` is fully typed

Easy Schema Migrations
bash
Make schema changes in Prisma schema file
Then create and apply migrations
npx prisma migrate dev --name add_new_feature
Database Agnostic

Same Prisma schema can work with different databases
Easy to switch or support multiple database backends

Built-in Migration History
Prisma maintains a migration history, making rollbacks and audits straightforward.
Developer Experience

Intuitive data modeling
Auto-completion in IDEs
Built-in best practices

Application Testing
Thoroughly test your application with the new PostgreSQL database.

Conclusion

Migrating from MySQL to PostgreSQL using pgloader and Prisma provides a robust, reliable approach. pgloader handles the heavy lifting of data transfer with proper type casting, while Prisma offers excellent schema management and evolution capabilities.

The combination gives you:

Smooth data migration
Type-safe database access
Easy future schema changes
Database abstraction

Remember to always backup both databases before migration and test thoroughly in a staging environment before production deployment.

Happy migrating!

About the Author:
Mohamed Ammar, senior data architect with expertise in database systems and data architectures. Follow for more technical guides and tutorials!

Disclaimer: This guide assumes you have appropriate backups and have tested the migration process in a non-production environment first. Always verify data integrity after migration.

Creating a Dynamic Café Website on AWS EC2: A DevOps Journey

Mohamed Ammar — Tue, 28 Oct 2025 16:24:25 +0000

In this technical walkthrough, I'll document how I built and deployed a dynamic café website across multiple AWS regions using EC2 instances, Secrets Manager, and a LAMP stack - all configured through AWS CLI and PowerShell.

Project Overview
The goal was to create a scalable web application for a café that can accept online orders, with separate development and production environments in different AWS regions.

Architecture Goals

Development Environment: US East (N. Virginia) region
Production Environment: US West (Oregon) region
Technology Stack: LAMP (Linux, Apache, MySQL, PHP)
Security: AWS Secrets Manager for sensitive configuration
Deployment: Fully automated via AWS CLI

Prerequisites Setup
AWS CLI Configuration
Before starting, I configured AWS CLI with the appropriate credentials:

powershell

# Configure AWS CLI profile
aws configure set aws_access_key_id YOUR_ACCESS_KEY
aws configure set aws_secret_access_key YOUR_SECRET_KEY
aws configure set default.region us-east-1
aws configure set default.output json

Phase 1: IAM Role Creation for Secrets Manager Access
Creating the IAM Role and Policy
powershell

# Create trust policy for EC2
$trustPolicy = @{
    "Version" = "2012-10-17"
    "Statement" = @(
        @{
            "Effect" = "Allow"
            "Principal" = @{
                "Service" = "ec2.amazonaws.com"
            }
            "Action" = "sts:AssumeRole"
        }
    )
} | ConvertTo-Json

$trustPolicy | Out-File -FilePath .\trust-policy.json -Encoding utf8

Create IAM role

aws iam create-role `
    --role-name CafeRole `
    --assume-role-policy-document file://trust-policy.json `
    --description "Role for Cafe EC2 instances to access Secrets Manager"

# Create Secrets Manager policy
$secretsManagerPolicy = @{
    "Version" = "2012-10-17"
    "Statement" = @(
        @{
            "Effect" = "Allow"
            "Action" = @(
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecrets",
                "secretsmanager:CreateSecret",
                "secretsmanager:UpdateSecret"
            )
            "Resource" = "*"
        }
    )
} | ConvertTo-Json

$secretsManagerPolicy | Out-File -FilePath .\secrets-manager-policy.json -Encoding utf8

# Create and attach policy
aws iam create-policy `
    --policy-name CafeSecretsManagerPolicy `
    --policy-document file://secrets-manager-policy.json

aws iam attach-role-policy `
    --role-name CafeRole `
    --policy-arn "arn:aws:iam::$(aws sts get-caller-identity --query Account --output text):policy/CafeSecretsManagerPolicy"

# Create instance profile
aws iam create-instance-profile `
    --instance-profile-name CafeInstanceProfile

aws iam add-role-to-instance-profile `
    --instance-profile-name CafeInstanceProfile `
    --role-name CafeRole

Phase 2: Development Environment Setup
Launching EC2 Instance in US-East-1 with IAM Role
powershell

# Create EC2 instance in us-east-1 with IAM role
aws ec2 run-instances `
    --image-id ami-0c02fb55956c7d316 `
    --instance-type t2.small `
    --key-name CafeKeyPair `
    --security-group-ids sg-cafe `
    --subnet-id subnet-123 `
    --iam-instance-profile Name=CafeInstanceProfile `
    --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=Lab IDE}]' `
    --region us-east-1

# Wait for instance to be running
$instanceId = aws ec2 describe-instances `
    --filters "Name=tag:Name,Values=Lab IDE" `
    --query 'Reservations[0].Instances[0].InstanceId' `
    --output text `
    --region us-east-1

aws ec2 wait instance-running `
    --instance-ids $instanceId `
    --region us-east-1
Connecting via SSH (PuTTY)
Since we're using Windows with PuTTY, convert the key:

powershell

Use PuTTYgen to convert .pem to .ppk

Connect via: ec2-user@ with CafeKeyPair.ppk

Phase 3: LAMP Stack Configuration
Installing and Configuring Apache Web Server
bash

# Update system packages
sudo dnf update -y

# Install Apache (httpd)
sudo dnf install -y httpd

# Configure Apache to listen on port 8000
sudo sed -i 's/Listen 80/Listen 8000/g' /etc/httpd/conf/httpd.conf

# Start and enable Apache
sudo systemctl start httpd
sudo systemctl enable httpd

# Verify Apache status
sudo service httpd status

# Install PHP
sudo dnf install -y php php-mysqli php-json
php --version

Installing and Configuring MariaDB
bash

# Install MariaDB database
sudo dnf install -y mariadb105-server

# Start and enable MariaDB
sudo systemctl start mariadb
sudo systemctl enable mariadb

# Verify installation
sudo mariadb --version
sudo service mariadb status
Setting Up Development Environment
bash
# Create symlink for IDE access
ln -s /var/www/ /home/ec2-user/environment

# Change ownership for web directory
sudo chown ec2-user:ec2-user /var/www/html

Create test webpage

echo '<html>Hello from the café web server!</html>' > /var/www/html/index.html

Phase 4: Application Deployment
Downloading and Extracting Application Files
bash

cd ~/environment

# Download application components
wget https://aws-tc-largeobjects.s3.us-west-2.amazonaws.com/CUR-TF-200-ACACAD-3-113230/03-lab-mod5-challenge-EC2/s3/setup.zip
wget https://aws-tc-largeobjects.s3.us-west-2.amazonaws.com/CUR-TF-200-ACACAD-3-113230/03-lab-mod5-challenge-EC2/s3/db.zip
wget https://aws-tc-largeobjects.s3.us-west-2.amazonaws.com/CUR-TF-200-ACACAD-3-113230/03-lab-mod5-challenge-EC2/s3/cafe.zip

# Extract files
unzip setup.zip
unzip db.zip
unzip cafe.zip -d /var/www/html/

# Install AWS SDK for PHP
cd /var/www/html/cafe/
wget https://docs.aws.amazon.com/aws-sdk-php/v3/download/aws.zip
wget https://docs.aws.amazon.com/aws-sdk-php/v3/download/aws.phar
unzip aws -d /var/www/html/cafe/

# Set appropriate permissions
chmod -R +r /var/www/html/cafe/

Phase 5: Secrets Manager Configuration

Setting Application Parameters
bash

cd ~/environment/setup/
./set-app-parameters.sh
Verifying IAM Role Access
bash
# Test IAM role access
curl http://169.254.169.254/latest/meta-data/iam/security-credentials/

# Verify Secrets Manager access
aws secretsmanager list-secrets --region us-east-1
Phase 6: Database Setup
Configuring MySQL Database
bash
# Get database password from Secrets Manager
DB_PASSWORD=$(aws secretsmanager get-secret-value --secret-id /cafe/dbPassword --region us-east-1 --query SecretString --output text)

# Set root password and create database

cd ~/environment/db/
./set-root-password.sh
./create-db.sh

Database Verification
bash

# Connect to MySQL database
mysql -u admin -p$DB_PASSWORD

# Within MySQL prompt:
show databases;
use cafe_db;
show tables;
select * from product;
exit;

PHP Configuration
bash

# Configure PHP timezone
sudo sed -i "2i date.timezone = \"America/New_York\" " /etc/php.ini

# Restart Apache to apply changes
sudo service httpd restart

Phase 7: Testing the Application
Accessing the Website
The café website should now be accessible at:

http://:8000/cafe

Testing Order Functionality

Navigate to the Menu page
Select items and submit orders
Check Order History to verify order persistence

Phase 8: Creating Production Environment
Preparing for AMI Creation
bash

# Set hostname
sudo hostname cafeserver

# Generate SSH key for future access
ssh-keygen -t rsa -f ~/.ssh/id_rsa -N ""

# Add public key to authorized keys
cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
Creating AMI via AWS CLI

powershell

# Create AMI from development instance
aws ec2 create-image `
    --instance-id $instanceId `
    --name "CafeServer" `
    --description "Cafe web server AMI" `
    --region us-east-1

# Wait for AMI to become available
aws ec2 wait image-available `
    --filters "Name=name,Values=CafeServer" `
    --region us-east-1

Launching Production Instance in US-West-2
powershell

# Switch to Oregon region for EC2 operations
aws configure set region us-west-2

# Create security group in Oregon
aws ec2 create-security-group `
    --group-name cafe-sg-oregon `
    --description "Cafe security group for Oregon region" `
    --vpc-id vpc-0zzzzzzzzzz `
    --region us-west-2

$oregonSGId = aws ec2 describe-security-groups `
    --group-names cafe-sg-oregon `
    --query 'SecurityGroups[0].GroupId' `
    --output text `
    --region us-west-2

# Configure security group rules
aws ec2 authorize-security-group-ingress `
    --group-id $oregonSGId `
    --protocol tcp `
    --port 22 `
    --cidr 0.0.0.0/0 `
    --region us-west-2

aws ec2 authorize-security-group-ingress `
    --group-id $oregonSGId `
    --protocol tcp `
    --port 8000 `
    --cidr 0.0.0.0/0 `
    --region us-west-2

# Launch production instance with IAM role

aws ec2 run-instances `
    --image-id ami-0yyyyyyyyyyy `
    --instance-type t2.small `
    --security-group-ids $oregonSGId `
    --subnet-id subnet-0yyyyyyyyyyy `
    --iam-instance-profile Name=CafeInstanceProfile `
    --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=ProdCafeServer}]' `
    --region us-west-2

# Wait for instance to be running
$prodInstanceId = aws ec2 describe-instances `
    --filters "Name=tag:Name,Values=ProdCafeServer" `
    --query 'Reservations[0].Instances[0].InstanceId' `
    --output text `
    --region us-west-2

aws ec2 wait instance-running `
    --instance-ids $prodInstanceId `
    --region us-west-2

Phase 9: Configuring Production Secrets
Creating Secrets in Oregon Region
bash

# On development instance, create Oregon region secrets
cd ~/environment/setup/

# Create modified script for Oregon
cat > set-app-parameters-oregon.sh << 'EOF'
#!/bin/bash
region="us-west-2"

publicDNS=$(aws ec2 describe-instances \
    --filters "Name=tag:Name,Values=ProdCafeServer" \
    --query 'Reservations[0].Instances[0].PublicDnsName' \
    --output text \
    --region $region)

# Create secrets in Oregon region
aws secretsmanager create-secret \
    --name /cafe/dbPassword \
    --description "Database password for cafe application" \
    --secret-string "dbPassword123" \
    --region $region

aws secretsmanager create-secret \
    --name /cafe/dbName \
    --description "Database name for cafe application" \
    --secret-string "cafe_db" \
    --region $region

echo "Secrets created successfully in $region"
EOF

chmod +x set-app-parameters-oregon.sh
./set-app-parameters-oregon.sh

Phase 10: Production Verification
Testing Production Environment
bash

# Get production instance public IP
PROD_IP=$(aws ec2 describe-instances `
    --instance-ids $prodInstanceId `
    --query 'Reservations[0].Instances[0].PublicIpAddress' `
    --output text `
    --region us-west-2)

echo "Production site URL: http://$PROD_IP:8000/cafe"

IAM Role Verification Script
bash

#!/bin/bash
# iam-role-test.sh

echo "Testing IAM Role Configuration..."
curl -s http://169.254.169.254/latest/meta-data/iam/security-credentials/
aws sts get-caller-identity
aws secretsmanager list-secrets --max-items 5
echo "IAM Role test completed."

Final Architecture
With the IAM role properly configured, our architecture includes:

Development Region (us-east-1):

EC2 instance with CafeRole attached
Access to Secrets Manager in us-east-1
Full LAMP stack application

Production Region (us-west-2):

EC2 instance with same CafeRole attached
Access to Secrets Manager in us-west-2
Identical application deployment

Global IAM Infrastructure:

CafeRole with Secrets Manager permissions
CafeInstanceProfile for EC2 attachment
Cross-region consistency in access control

Lessons Learned

IAM is Critical: Without proper IAM roles, the application cannot access Secrets Manager
Regional Considerations: AWS resources are region-specific, requiring careful planning
Security Best Practices: Using Secrets Manager significantly improves security
Automation Benefits: AWS CLI commands enable reproducible deployments

Conclusion
This project successfully demonstrated a complete DevOps workflow for deploying a dynamic web application across multiple AWS regions. The café now has a robust, scalable ordering system with proper IAM role configuration for secure Secrets Manager access, ensuring both development and production environments function correctly.

The entire infrastructure can be recreated programmatically using the AWS CLI commands and scripts documented in this post.

Deploying StarRocks in Shared Data Mode on Minikube with S3 Integration

Mohamed Ammar — Sun, 14 Sep 2025 21:11:04 +0000

The Modern Data Stack
In the world of real-time analytics, the ability to query massive datasets at lightning speed is not just a luxury—it's a necessity. StarRocks has emerged as a powerhouse in this space, renowned for its sub-second query performance on petabyte-scale data. Its native vectorized execution engine and cost-based optimizer make it a top contender for replacing complex, multi-component data architectures.

But how do you manage and scale such a high-performance database?

The de facto standard for container orchestration, Kubernetes provides the elasticity, resilience, and portability that modern applications demand. By running StarRocks on Kubernetes, you can automate deployments, scaling, and management, making your analytics infrastructure as agile as your code.

In this guide, I'll dive into a particularly powerful feature: StarRocks' Shared Data Mode. This architecture decouples compute from storage. Your compute nodes (CNs) are stateless and can be spun up or down in seconds, while your data remains safely and durably stored in a central repository like Amazon S3. This means you can scale your compute resources elastically based on query load, leading to significant cost savings and performance optimization.

We'll walk through setting this all up on a local Minikube cluster running on an EC2 machine, providing a perfect sandbox for development, testing, and learning.

📋 Prerequisites
Before we begin, ensure you have the following:

EC2 with Linux OS
AWS Account: With credentials (Access Key and Secret Key) for an S3 bucket.

🛠️ Phase 1: Setting Up Our Kubernetes playground on EC2
First, we need a machine to host our cluster. An EC2 instance is perfect for this.

Launch an EC2 Instance
Log into your AWS Console and navigate to EC2.
Launch a new instance. A t2.xlarge or c5.2xlarge (8 vCPUs, 16 GiB RAM) is recommended to ensure Minikube has enough resources.
Select an Amazon Linux
Configure security groups to allow SSH (port 22) access from your IP.
Launch the instance, ensuring you have the .pem key pair to connect.

Connect and Prepare the EC2 Instance Connect via SSH using your terminal or SSH client.

Once logged in, update the system and install necessary base packages.

sudo yum update -y

📁 Files Required
We've prepared a set of files to automate and configure our setup. Download them to your EC2 instance into the same directory.

env_setup.sh: Installs Minikube, kubectl, Helm, and other dependencies.
configmap.yaml: Contains the StarRocks configuration to enable Shared Data mode.
starrocks-cluster.yaml: The main manifest defining our StarRocks cluster (FE, BE, CN).
test-s3.py: A simple script to validate our S3 credentials before deployment.

You can create these files directly on the EC2 instance using vim or nano.

env_setup.sh:

#!/bin/bash

# Install kubectl
curl -LO "https://dl.k8s.io/release/$(curl -L -s https://dl.k8s.io/release/stable.txt)/bin/linux/amd64/kubectl"
sudo install -o root -g root -m 0755 kubectl /usr/local/bin/kubectl

# Install Minikube
curl -LO https://storage.googleapis.com/minikube/releases/latest/minikube-linux-amd64
sudo install minikube-linux-amd64 /usr/local/bin/minikube

# Install Helm
curl https://raw.githubusercontent.com/helm/helm/main/scripts/get-helm-3 | bash

# Install Docker (required by Minikube's docker driver)
sudo yum install docker -y # Use apt for Ubuntu
sudo usermod -aG docker $USER && newgrp docker
sudo systemctl start docker
sudo systemctl enable docker

echo "All tools installed! Please log out and back in for group changes to take effect, or run 'newgrp docker'."

configmap.yaml:

apiVersion: v1
kind: ConfigMap
metadata:
  name: poc-starrockscluster-fe-cm
  labels:
    cluster: starrockscluster-poc-cp
data:
  fe.conf: |
    LOG_DIR = ${STARROCKS_HOME}/log
    DATE = "$(date +%Y%m%d-%H%M%S)"
    JAVA_OPTS="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseMembar -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+PrintGCDateStamps -XX:+PrintGCDetails -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xloggc:${LOG_DIR}/fe.gc.log.$DATE"
    JAVA_OPTS_FOR_JDK_9="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xlog:gc*:${LOG_DIR}/fe.gc.log.$DATE:time"
    JAVA_OPTS_FOR_JDK_11="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseG1GC -Xlog:gc*:${LOG_DIR}/fe.gc.log.$DATE:time"
    http_port = 8030
    rpc_port = 9020
    query_port = 9030
    edit_log_port = 9010
    mysql_service_nio_enabled = true
    sys_log_level = INFO
    # config for shared-data mode
    run_mode = shared_data
    cloud_native_meta_port = 6090
    # Whether volume can be created from conf. If it is enabled, a builtin storage volume may be created.
    enable_load_volume_from_conf = true

    # GCS uses S3 Protocol
    cloud_native_storage_type = S3

    # For example, testbucket/subpath
    aws_s3_path = <YOUR-BUCKET-NAME>


    # For example: us-east1
    aws_s3_region = <YOUR-AWS-REGION>

    # For example: https://s3.amazonaws.com
    aws_s3_endpoint = https://s3.amazonaws.com
    aws_s3_access_key = "<YOUR-ACCESS-KEY>"
    aws_s3_secret_key = "<YOUR-SECRET-KEY>"

starrocks-cluster.yaml:

#This manifest deploys a StarRocks cluster running in shared data mode.
# see https://docs.starrocks.io/docs/cover_pages/shared_data_deployment/ for more information about shared-data mode.
#
# You will have to download and edit this YAML file to specify the details for your shared storage. See the
# examples in the docs, and add your customizations to the ConfigMap `starrockscluster-sample-fe-cm` at the
# bottom of this file.
# https://docs.starrocks.io/en-us/latest/deployment/deploy_shared_data#configure-fe-nodes-for-shared-data-starrocks

apiVersion: starrocks.com/v1
kind: StarRocksCluster
metadata:
  name: poc-starrocks-cluster   # change the name if needed.
spec:
  starRocksFeSpec:
    image: starrocks/fe-ubuntu:3.2.7  
    replicas: 3
    limits:
      memory: 3Gi
    requests:
      cpu: '1'
      memory: 1Gi
    configMapInfo:
      configMapName: poc-starrockscluster-fe-cm
      resolveKey: fe.conf
  starRocksCnSpec:
    image: starrocks/cn-ubuntu:3.2.7   #try 3.3-latest
    replicas: 1
    limits:
      memory: 5Gi
    requests:
      cpu: '1'
      memory: 2Gi
    autoScalingPolicy: # Automatic scaling policy of the CN cluster.
      maxReplicas: 10 # The maximum number of CNs is set to 10.
      minReplicas: 1 # The minimum number of CNs is set to 1.
      # operator creates an HPA resource based on the following field.
      # see https://kubernetes.io/docs/tasks/run-application/horizontal-pod-autoscale/ for more information.
      hpaPolicy:
        metrics: # Resource metrics
          - type: Resource
            resource:
              name: memory  # The average memory usage of CNs is specified as a resource metric.
              target:
                averageUtilization: 15
                type: Utilization
          - type: Resource
            resource:
              name: cpu # The average CPU utilization of CNs is specified as a resource metric.
              target:
                averageUtilization: 15
                type: Utilization
        behavior: #  The scaling behavior is customized according to business scenarios, helping you achieve rapid or slow scaling or disable scaling.
          scaleUp:
            policies:
              - type: Pods
                value: 1
                periodSeconds: 10
          scaleDown:
            policies:
              - type: Pods
                value: 1
                periodSeconds: 60
            stabilizationWindowSeconds: 300
            selectPolicy: Max

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: poc-starrockscluster-fe-cm
  labels:
    cluster: starrockscluster-poc-cp
data:
  fe.conf: |
    LOG_DIR = ${STARROCKS_HOME}/log
    DATE = "$(date +%Y%m%d-%H%M%S)"
    JAVA_OPTS="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseMembar -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+PrintGCDateStamps -XX:+PrintGCDetails -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xloggc:${LOG_DIR}/fe.gc.log.$DATE"
    JAVA_OPTS_FOR_JDK_9="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xlog:gc*:${LOG_DIR}/fe.gc.log.$DATE:time"
    JAVA_OPTS_FOR_JDK_11="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseG1GC -Xlog:gc*:${LOG_DIR}/fe.gc.log.$DATE:time"
    http_port = 8030
    rpc_port = 9020
    query_port = 9030
    edit_log_port = 9010
    mysql_service_nio_enabled = true
    sys_log_level = INFO
    # config for shared-data mode
    run_mode = shared_data
    cloud_native_meta_port = 6090
    # Whether volume can be created from conf. If it is enabled, a builtin storage volume may be created.
    enable_load_volume_from_conf = true

    # GCS uses S3 Protocol
    cloud_native_storage_type = S3

    # For example, testbucket/subpath
    aws_s3_path = "<YOUR-BUCKET-NAME>"

    # For example: us-east1
    aws_s3_region = us-west-2

    # For example: https://s3.amazonaws.com
    aws_s3_endpoint = https://s3.amazonaws.com

    aws_s3_access_key = "<YOUR-ACCESS-KEY>"
    aws_s3_secret_key = "<YOUR-SECRET-KEY>"

test-s3.py:

import boto3
from botocore.exceptions import ClientError

BUCKET_NAME = "<YOUR-BUCKET-NAME>"
REGION = "<YOUR-AWS-REGION>"
ACCESS_KEY = "<YOUR-ACCESS-KEY>"
SECRET_KEY = "<YOUR-SECRET-KEY>"

s3 = boto3.client(
    's3',
    region_name=REGION,
    aws_access_key_id=ACCESS_KEY,
    aws_secret_access_key=SECRET_KEY
)

try:
    response = s3.list_buckets()
    print("Connection successful! Available buckets:")
    for bucket in response['Buckets']:
        print(f'  {bucket["Name"]}')

    # Try a head bucket operation for a more specific check
    s3.head_bucket(Bucket=BUCKET_NAME)
    print(f"\nSuccessfully accessed the target bucket: {BUCKET_NAME}")

except ClientError as e:
    print(f"Error: {e}")

🔐** Phase 2: Configure S3 Access**

Our entire setup hinges on StarRocks being able to communicate with S3. Let's test this first.

Edit both test-s3.py and configmap.yaml. Replace all placeholders (<...>, your-...) with your actual S3 Bucket Name, Region, Access Key, and Secret Key.

Install the Boto3 library and run the test script:

pip3 install boto3
python3 test-s3.py

A successful output confirms your credentials and permissions are correct. Fix any errors here before proceeding.

⚙️ Phase 3: Setup Environment & Start Minikube

Now, let's turn our EC2 instance into a single-node Kubernetes cluster.

Make the setup script executable and run it:

bash

chmod +x env_setup.sh
./env_setup.sh

Start Minikube with adequate resources. The shared data mode is memory and CPU intensive.

minikube start --driver=docker --cpus=8 --memory=12288

Verify the cluster is running:

bash
kubectl get nodes

🚀** Phase 4: Install the StarRocks Kubernetes Operator**

Operators are Kubernetes-native applications that manage complex stateful services like databases. We'll use the StarRocks operator to deploy our cluster.

bash

helm repo add starrocks https://starrocks.github.io/starrocks-kubernetes-operator
helm repo update
helm install starrocks-operator starrocks/operator \
  --create-namespace --namespace starrocks

Check if the operator pod is running:

bash

kubectl get pods -n starrocks

📦 Phase 5: Deploy the StarRocks Cluster

With the operator running, we can now deploy our custom-configured StarRocks cluster.

Apply the configuration that points to our S3 bucket:

bash

kubectl apply -f configmap.yaml

Deploy the cluster itself:

bash

kubectl apply -f starrocks-cluster.yaml

Watch the pods come up. This may take a few minutes as it pulls large container images.

bash

kubectl get pods -n starrocks

Wait until you see an output similar to this:

Notice the three FE pods for high availability and the single CN pod. There are no BE pods.

🔌 Phase 6: Connect and Load Data

Let's interact with our cluster and load some sample data.

Connect to the MySQL Client We'll exec into the FE pod to use the built-in MySQL client.

bash

kubectl exec --stdin --tty poc-starrocks-cluster-fe-0 --   mysql -P9030 -h127.0.0.1 -u root --prompt="StarRocks > "

Create a Database and Tables Run these SQL commands inside the MySQL client:

sql

CREATE DATABASE IF NOT EXISTS quickstart;
USE quickstart;

CREATE TABLE IF NOT EXISTS crashdata (
    CRASH_DATE DATETIME,
    BOROUGH STRING,
    ZIP_CODE STRING,
    LATITUDE INT,
    LONGITUDE INT,
    LOCATION STRING,
    ON_STREET_NAME STRING,
    CROSS_STREET_NAME STRING,
    OFF_STREET_NAME STRING,
    CONTRIBUTING_FACTOR_VEHICLE_1 STRING,
    CONTRIBUTING_FACTOR_VEHICLE_2 STRING,
    COLLISION_ID INT,
    VEHICLE_TYPE_CODE_1 STRING,
    VEHICLE_TYPE_CODE_2 STRING
);
CREATE TABLE IF NOT EXISTS weatherdata (
    DATE DATETIME,
    NAME STRING,
    HourlyDewPointTemperature STRING,
    HourlyDryBulbTemperature STRING,
    HourlyPrecipitation STRING,
    HourlyPresentWeatherType STRING,
    HourlyPressureChange STRING,
    HourlyPressureTendency STRING,
    HourlyRelativeHumidity STRING,
    HourlySkyConditions STRING,
    HourlyVisibility STRING,
    HourlyWetBulbTemperature STRING,
    HourlyWindDirection STRING,
    HourlyWindGustSpeed STRING,
    HourlyWindSpeed STRING
);

Type exit to leave the MySQL client.

Download and Upload Sample Datasets bash

curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/datasets/NYPD_Crash_Data.csv
curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/datasets/72505394728.csv

Copy the files into the FE pod

kubectl cp ./NYPD_Crash_Data.csv poc-starrocks-cluster-fe-0:/tmp/NYPD_Crash_Data.csv -n default
kubectl cp ./72505394728.csv poc-starrocks-cluster-fe-0:/tmp/72505394728.csv -n default

Get a shell inside the FE pod to run the curl commands for loading data.

bash

kubectl exec -it poc-starrocks-cluster-fe-0 -n default -- /bin/bash

Inside the container, run the two curl commands from your outline to load data into the weatherdata and crashdata tables.

Remember, just press Enter when prompted for a password.

curl --location-trusted -u root             \
    -T /tmp/72505394728.csv                    \
    -H "label:weather-0"                    \
    -H "column_separator:,"                 \
    -H "skip_header:1"                      \
    -H "enclose:\""                         \
    -H "max_filter_ratio:1"                 \
    -H "columns: STATION, DATE, LATITUDE, LONGITUDE, ELEVATION, NAME, REPORT_TYPE, SOURCE, HourlyAltimeterSetting, HourlyDewPointTemperature, HourlyDryBulbTemperature, HourlyPrecipitation, HourlyPresentWeatherType, HourlyPressureChange, HourlyPressureTendency, HourlyRelativeHumidity, HourlySkyConditions, HourlySeaLevelPressure, HourlyStationPressure, HourlyVisibility, HourlyWetBulbTemperature, HourlyWindDirection, HourlyWindGustSpeed, HourlyWindSpeed, Sunrise, Sunset, DailyAverageDewPointTemperature, DailyAverageDryBulbTemperature, DailyAverageRelativeHumidity, DailyAverageSeaLevelPressure, DailyAverageStationPressure, DailyAverageWetBulbTemperature, DailyAverageWindSpeed, DailyCoolingDegreeDays, DailyDepartureFromNormalAverageTemperature, DailyHeatingDegreeDays, DailyMaximumDryBulbTemperature, DailyMinimumDryBulbTemperature, DailyPeakWindDirection, DailyPeakWindSpeed, DailyPrecipitation, DailySnowDepth, DailySnowfall, DailySustainedWindDirection, DailySustainedWindSpeed, DailyWeather, MonthlyAverageRH, MonthlyDaysWithGT001Precip, MonthlyDaysWithGT010Precip, MonthlyDaysWithGT32Temp, MonthlyDaysWithGT90Temp, MonthlyDaysWithLT0Temp, MonthlyDaysWithLT32Temp, MonthlyDepartureFromNormalAverageTemperature, MonthlyDepartureFromNormalCoolingDegreeDays, MonthlyDepartureFromNormalHeatingDegreeDays, MonthlyDepartureFromNormalMaximumTemperature, MonthlyDepartureFromNormalMinimumTemperature, MonthlyDepartureFromNormalPrecipitation, MonthlyDewpointTemperature, MonthlyGreatestPrecip, MonthlyGreatestPrecipDate, MonthlyGreatestSnowDepth, MonthlyGreatestSnowDepthDate, MonthlyGreatestSnowfall, MonthlyGreatestSnowfallDate, MonthlyMaxSeaLevelPressureValue, MonthlyMaxSeaLevelPressureValueDate, MonthlyMaxSeaLevelPressureValueTime, MonthlyMaximumTemperature, MonthlyMeanTemperature, MonthlyMinSeaLevelPressureValue, MonthlyMinSeaLevelPressureValueDate, MonthlyMinSeaLevelPressureValueTime, MonthlyMinimumTemperature, MonthlySeaLevelPressure, MonthlyStationPressure, MonthlyTotalLiquidPrecipitation, MonthlyTotalSnowfall, MonthlyWetBulb, AWND, CDSD, CLDD, DSNW, HDSD, HTDD, NormalsCoolingDegreeDay, NormalsHeatingDegreeDay, ShortDurationEndDate005, ShortDurationEndDate010, ShortDurationEndDate015, ShortDurationEndDate020, ShortDurationEndDate030, ShortDurationEndDate045, ShortDurationEndDate060, ShortDurationEndDate080, ShortDurationEndDate100, ShortDurationEndDate120, ShortDurationEndDate150, ShortDurationEndDate180, ShortDurationPrecipitationValue005, ShortDurationPrecipitationValue010, ShortDurationPrecipitationValue015, ShortDurationPrecipitationValue020, ShortDurationPrecipitationValue030, ShortDurationPrecipitationValue045, ShortDurationPrecipitationValue060, ShortDurationPrecipitationValue080, ShortDurationPrecipitationValue100, ShortDurationPrecipitationValue120, ShortDurationPrecipitationValue150, ShortDurationPrecipitationValue180, REM, BackupDirection, BackupDistance, BackupDistanceUnit, BackupElements, BackupElevation, BackupEquipment, BackupLatitude, BackupLongitude, BackupName, WindEquipmentChangeDate" \
    -XPUT http://127.0.0.1:8030/api/quickstart/weatherdata/_stream_load

    curl --location-trusted -u root             \
    -T /tmp/NYPD_Crash_Data.csv                \
    -H "label:crashdata-0"                  \
    -H "column_separator:,"                 \
    -H "skip_header:1"                      \
    -H "enclose:\""                         \
    -H "max_filter_ratio:1"                 \
    -H "columns:tmp_CRASH_DATE, tmp_CRASH_TIME, CRASH_DATE=str_to_date(concat_ws(' ', tmp_CRASH_DATE, tmp_CRASH_TIME), '%m/%d/%Y %H:%i'),BOROUGH,ZIP_CODE,LATITUDE,LONGITUDE,LOCATION,ON_STREET_NAME,CROSS_STREET_NAME,OFF_STREET_NAME,NUMBER_OF_PERSONS_INJURED,NUMBER_OF_PERSONS_KILLED,NUMBER_OF_PEDESTRIANS_INJURED,NUMBER_OF_PEDESTRIANS_KILLED,NUMBER_OF_CYCLIST_INJURED,NUMBER_OF_CYCLIST_KILLED,NUMBER_OF_MOTORIST_INJURED,NUMBER_OF_MOTORIST_KILLED,CONTRIBUTING_FACTOR_VEHICLE_1,CONTRIBUTING_FACTOR_VEHICLE_2,CONTRIBUTING_FACTOR_VEHICLE_3,CONTRIBUTING_FACTOR_VEHICLE_4,CONTRIBUTING_FACTOR_VEHICLE_5,COLLISION_ID,VEHICLE_TYPE_CODE_1,VEHICLE_TYPE_CODE_2,VEHICLE_TYPE_CODE_3,VEHICLE_TYPE_CODE_4,VEHICLE_TYPE_CODE_5" \
    -XPUT http://127.0.0.1:8030/api/quickstart/crashdata/_stream_load

📈 Phase 7: Test Queries and Observe Auto-Scaling
The magic of Shared Data Mode is its elasticity. Let's see it in action.

Enable Metrics Server: Minikube needs this for the Horizontal Pod Autoscaler (HPA) to work.

bash

minikube addons enable metrics-server

Run a Sample Query: Connect again with the MySQL client and run the analytical queries from the outline.

Trigger a Scale-Out: Run a heavy, full-table scan. This will spike CPU usage.

Connect to the MySQL Client

bash

kubectl exec --stdin --tty poc-starrocks-cluster-fe-0 --   mysql -P9030 -h127.0.0.1 -u root --prompt="StarRocks > "

sql

SELECT * FROM crashdata;

Watch the Autoscaler Work: Open a new terminal window on your EC2 instance and watch the pods.

bash

watch kubectl get pods -n starrocks

✅ You should observe:

After a minute or so of the heavy query, a new CN pod (e.g., plaid-starrocks-cluster-cn-1) will appear with status ContainerCreating and then Running.

The HPA automatically provisioned it to handle the load!

Wait 3-5 minutes after the query finishes. You will see the extra CN pod automatically terminate. This is the autoscaler saving resources by scaling down.

🚨 Clean-Up: Don't forget to tear down your environment to avoid unnecessary costs!

Conclusion
This modern data platform successfully decouples compute from storage, enabling you to scale horizontally and seamlessly. The benefits are clear: significant cost savings by only paying for the compute you use, blistering query performance powered by elastic resources, and the agility to handle any analytical workload on demand. You've just built the future of data analytics.

This setup is not just for learning; it mirrors the architecture used in production environments to achieve both high performance and cost efficiency. You can now stop your Minikube cluster (minikube stop) and even terminate your EC2 instance to avoid unnecessary costs, knowing you can recreate this entire environment from scratch using the code and configs you've written.

How to Upgrade Tableau Server from 2023 (CentOS 7) or equivalent to 2025 (Ubuntu 24.04 LTS) Using Blue-Green Deployment

Mohamed Ammar — Thu, 31 Jul 2025 17:40:15 +0000

Author: Mohamed Ammar, Senior Data Architect at Vodafone

Last Updated: July 2025

Why This Guide?

If you're running Tableau Server 2023 or earlier on CentOS 7, you're likely aware that:

CentOS 7 is deprecated (EOL: June 2024).
Tableau 2023 support is ending, and extended support will cost money.
You need a modern, secure, and supported environment—and fast.

This guide walks you through upgrading to Tableau Server 2025 on Ubuntu 24.04 LTS using a Blue-Green deployment strategy. This ensures zero downtime and a safe rollback path.

Prerequisites

A new VM provisioned (e.g., on AWS) with:
- 16 vCPUs
- 32 GB RAM
- 300 GB SSD
OS: Ubuntu 24.04 LTS
SSH access (e.g., via PuTTY)
Tableau license key from your current server
Access to the .tsbak backup file from your old server

Step 1: Prepare the New Environment

SSH into the VM

ssh -i your-key.pem ubuntu@your-vm-ip

Update the System

sudo apt-get update
sudo apt-get upgrade -y

Download Tableau Server

Register on Tableau’s website to download the .deb package.
Download tableau-server-<version>_amd64.deb\ to your VM.

Install Dependencies

sudo apt-get install -y gdebi-core
sudo gdebi -n tableau-server-<version>_amd64.deb

Initialize TSM

After installation, run the command printed by the installer:

sudo ./initialize-tsm --accepteula

Step 2: Activate and Register Tableau Server

Switch to Tableau User

sudo su -l tableau

Activate License

Get your license key from the old server’s TSM UI:

tsm licenses activate -k <your_license_key>

Set a Password for Tableau User

sudo -i
passwd tableau

Step 3: Register the Server

Create a registration file:

nano registration_file.json

Paste and customize:

{
    "first_name" : "Andrew",
    "last_name" : "Smith",
    "phone" : "311-555-2368",
    "email" : "andrew.smith@mycompany.com",
    "company" : "My Company",
    "industry" : "Finance",
    "company_employees" : "500",            
    "department" : "Engineering",
    "title" : "Senior Manager",
    "city" : "Kirkland",
    "state" : "WA",
    "zip" : "98034",
    "country" : "United States",
    "opt_in" : "false",
    "eula" : "accept"
}

tsm register --template > /path/to/registration_file.json

tsm register --file registration_file.json

Step 4: Configure Identity Store

If you're using local identity store, import your config:

tsm settings import -f /opt/tableau/tableau_server/packages/scripts.<version_code>/config.json
tsm pending-changes apply

Step 5: Start Tableau Server

tsm initialize --start-server --request-timeout 1800

Create the admin user (change admin password!):

tabcmd initialuser --server 'localhost:80' --username 'admin' --password '<your_password>'

Step 6: Install PostgreSQL Drivers

Download from: Tableau Driver Downloads
Copy the .jar file to: (create the path it does not exist)

/opt/tableau/tableau_driver/jdbc

Step 7: Restart TSM

tsm restart

Step 8: Migrate Data from Old Server

Backup on Old Server

tsm maintenance backup -f backup_file

This creates a .tsbak file.

Transfer Backup

Use AWS internal networking or S3 bucket to transfer the file to the new VM.

Restore on New Server

tsm maintenance restore -f backup_file.tsbak

Step 9: Reapply Topology and Configuration

Manually replicate settings from the old TSM admin panel to the new one.

Final Notes

Test dashboards and data sources before switching DNS or load balancer to the new server.
Keep the old server running until you're confident in the new setup.
This Blue-Green approach ensures minimal risk and maximum uptime.