MySQL → PostgreSQL Migration Guide#

df -h - shows disk space usage in human-readable format

• Ensure that free space is at least 2 times larger than the size of your database

free -h - shows information about RAM

• At least 4 GB RAM is required for pgloader
• Insufficient memory may cause migration to fail with "Heap exhausted" error

docker --version - checks the Docker version

• Docker 20.10+ is required for all features to work correctly

docker compose version - checks the Docker Compose version

• Ensure that compose supports modern syntax

docker ps - shows running containers

• mysql/postgres containers should be visible for data access

curl -fSL -OJ - downloads files from the server

• -f - quietly exits on server errors
• -S - shows errors even when using -f
• -L - follows redirects
• -O - saves the file with the name from the server
• -J - uses the filename provided by the server

pgloader_image.tar - Docker image with the pgloader tool for data transfer alpine_image.tar - lightweight Linux image for file operations

sudo mkdir -p - creates a directory with parent folders

• -p prevents an error if the directory already exists

sudo mv - moves files to the new directory

• Moving to /opt/SherpaAIServerNew isolates migration files from the current installation

docker compose stop - stops specified services without removing them

• orchestrator - main orchestration service
• embed - service for working with embeddings
• nginx - web server and proxy
• vllm - language model service

Two commands - both syntax versions are executed for compatibility Remaining services - pgembeding (PostgreSQL with embeddings) and orchestrator-db (MySQL) should continue to run for data access

docker volume list - shows all Docker volumes in the system

• DRIVER - type of driver (usually local)
• VOLUME NAME - unique name of the volume

Name prefixes - depend on the project folder name:

• For the folder SherpaAIServer → prefix sherpaaiserver_
• For the folder myproject → prefix myproject_

Main volumes:

• *orchestrator-mysql-data - MySQL database data
• *pgdata - PostgreSQL database data
• *storage - file storage

docker run --rm - runs a container and automatically removes it after execution

• --rm prevents accumulation of stopped containers

Mounting volumes:

• -v VOLUME_NAME:/data - mounts Docker volume in the container
• -v $(pwd)/backup_mysql:/backup - mounts a local folder to save the archive

tar czf - creates a compressed archive

• c - create archive
• z - compress using gzip
• f - specify filename
• -C /data . - changes directory to /data before archiving

pgembeding - uses the existing PostgreSQL image with tools

First volume: sherpaaiserver_orchestrator-mysql-data

• Contains MySQL data of the orchestrator database
• Archive is saved in backup_mysql/mysql_backup.tar.gz

Second volume: sherpaaiserver_pgdata

• Contains PostgreSQL data (if any)
• Archive is saved in backup_pg/pg_backup.tar.gz

First volume: myproject_orchestrator-mysql-data

• Similar, but with the prefix myproject_
• Use the actual name of your project folder

Second volume: myproject_pgdata

• PostgreSQL data with the corresponding prefix

• Create compressed archives of all data from Docker volumes
• Archives are saved in local directories backup_mysql and backup_pg

ls -lh - shows detailed information about files

• -l - long format
• -h - sizes in human-readable form
• Ensure that the archives have reasonable sizes

tar -tzf - shows the contents of the archive without unpacking

• -t - list contents
• -z - unpack using gzip
• -f - specify filename
• | head -20 - shows the first 20 files

Recommendation: Ensure that the archives are not empty and contain the expected database files

tar -xvzf - unpacks the archive

• -x - extract
• -v - verbose output
• -z - unpack using gzip
• -f - specify filename

Subcommand for file selection:

• ls client_files_*.tgz - finds all client-files archives
• sort -V - sorts by version (1.0, 1.1, 2.0, etc.)
• tail -n 1 - takes the last (most recent) file

Result: A folder pgloader is created with migration tools

cd pgloader - navigates to the folder with migration tools

• All subsequent commands are executed from this directory
• Contains scripts and configurations for pgloader

./load_pgloader.sh - script to load the Docker image

• Imports pgloader_image.tar into the local Docker daemon
• After execution, the image becomes available as pgloader:latest
• May take a few minutes depending on the size of the image

./run.sh build - builds the Docker container for migration

• Creates a container based on the loaded pgloader image
• Sets up all necessary dependencies and tools
• After building, the container is ready to perform the migration

MIGRATE_MYSQL - URI for connecting to the source MySQL database

• Format: mysql://username:password@host:port/database
• Used by pgloader to read data

MIGRATE_PG - URI for connecting to the target PostgreSQL database

• Format: postgres://username:password@host:port/database
• Must be accessible for writing data

DUMP_ / RESTORE_ ** - optional parameters for creating/restoring dumps

• Useful for debugging or phased migration

MySQL parameters:

• Host: 91.206.149.183 (external server)
• Port: 3306 (standard for MySQL)
• Database: orchestrator
• User: root (with full rights)

PostgreSQL parameters:

• Host: the same server
• Port: 5432 (standard for PostgreSQL)
• Database: postgres (system database)
• User: postgres (administrator)

Important: Passwords should be taken from the existing config.ini

./run.sh dry-run - simulates migration without making changes

• Connects to both databases
• Analyzes structure and data
• Shows migration plan and potential issues
• Does not make any changes to PostgreSQL

Checks:

• Availability and correctness of connections
• Compatibility of data types MySQL → PostgreSQL
• Presence of all tables and relationships
• Expected volume of data to be transferred

Recommendation: Always perform a dry-run before the actual migration

./run.sh migrate - performs the actual data migration

• Creates the orchestrator schema in PostgreSQL
• Transfers the table structure with type conversion
• Copies all data from MySQL to PostgreSQL
• Creates indexes and integrity constraints

Execution steps:

1. Checking connections and access rights
2. Creating the database schema
3. Migrating structure (tables, data types)
4. Transferring data with performance optimization
5. Creating indexes and constraints

Important: The process may take a long time for large databases

./run.sh rmi - removes the pgloader Docker image

• Frees up disk space
• Removes temporary images and containers
• Optional - can be left for reuse

cd .. - returns to the parent directory

• Exits the pgloader folder
• Returns to the root of the SherpaAIServer project

Recommendation: Perform cleanup only after successful completion and testing of the migration

docker load -i - imports a Docker image from a tar archive

• -i - read from a locally saved tar archive
• Loads the image into the local Docker daemon

Alpine Linux - a minimalist Linux distribution

• Image size ~5-10 MB (compared to Ubuntu ~100+ MB)
• Contains only necessary tools (cp, ls, tar, etc.)
• Ideal for file operations in containers

Usage: For copying data between Docker volumes

docker volume create - creates a new named Docker volume

• aiserver-storage - name for the new volume
• Volume is created empty and ready for use

Volume characteristics:

• Managed by Docker automatically
• Available to all containers on the host
• Preserved when the Docker daemon is restarted
• Supports snapshot and backup operations

Purpose: Storage for Sherpa AIServer files (uploads, cache, logs, etc.)

docker volume list - rechecks the list of volumes

• Ensures that all volumes are available
• Checks the correctness of names before copying data

Important: Compare with the results of the first check

• Ensure that volumes have not been accidentally deleted
• Check the availability of all necessary data

docker run --rm - runs a temporary Alpine container

• --rm - the container will be removed after execution

Mounting volumes:

• -v STORAGE_DATA:/from - source volume is mounted to /from
• -v aiserver-storage:/to - target volume is mounted to /to

ash -c - executes the command in the Alpine shell

• ash - Almquist shell (lightweight)
• -c - execute the command

cp -av /from/. /to/ - copies all files

• -a - archive mode (preserves permissions, owners, timestamps)
• -v - verbose output
• /from/. - copies the contents, not the /from folder itself
• /to/ - to the target directory

Example replacement: STORAGE_DATA → sherpaaiserver_storage

docker run --rm -v aiserver-storage:/data alpine ls -la /data

• Checks the contents of the new volume
• ls -la shows all files with permissions and sizes
• Ensures that files have been copied correctly

docker run --rm -v STORAGE_DATA:/data alpine du -sh /data

• Checks the size of the source volume
• du -sh - disk usage summary, human-readable format
• Compares sizes to check the completeness of the copy

Expectations:

• The number of files should match
• Sizes should be approximately equal
• The directory structure should be identical

If sizes differ: Check the copy logs for errors

• ls client_files_*.tgz - finds all archive files
• sort -V - sorts versions naturally (1.0 < 1.1 < 1.10)
• tail -n 1 - selects the most recent file
• tar -xvzf - unpacks the archive with output of contents

Expected result: A directory sh_scripts/ will be created with executable scripts and other necessary files.

• chmod +x *.sh - sets execution rights for all shell scripts
• This is necessary for running scripts in the following installation stages

1. Loads all Docker images from downloaded .tar.gz files
2. Imports images into the local Docker registry
3. Checks the success of the loading

1. Unpacks the Whisper model for speech recognition
2. Unpacks the BGE Reranker model for improved search
3. Unpacks models for generating embeddings
4. Creates necessary directories
5. Checks the success of the unpacking

1. Unpacks the Llama 3 model for language modeling
2. Removes the model-store/ prefix from file paths
3. Places files directly into the models directory
4. Checks the contents after unpacking

docker compose up -d - starts services in the background

• -d - detached mode (runs in the background)
• Starts all services from docker-compose.yml
• Includes PostgreSQL 17 in the aiserver-pg container

Important:

• The old database should continue to run
• The new version runs on a different port (5433)
• The initialization process may take several minutes

docker logs aiserver - shows container logs

• Monitors the PostgreSQL initialization process
• Looks for messages about successful startup and migration applications
• Can be interrupted with Ctrl+C

docker ps | grep aiserver - checks status

• docker ps - shows running containers
• grep aiserver - filters by name
• Should show the aiserver-pg container in the Up state

docker compose stop - stops specified services

• Both syntax versions are executed for compatibility
• Stops all Sherpa AIServer services except the database

Stopped services:

• aiserver - main web server
• aiserver-code_interpreter - code interpreter
• aiserver-llm-server - language model
• aiserver-embed - embedding service
• aiserver-whisper - speech recognition service
• aiserver-bge_reranker - ranking service

Result: Only aiserver-pg (new PostgreSQL) continues to run

Step 1: Create a dump from the old database

• docker exec -t postgres - executes a command in the postgres container (version 16)
• pg_dump -U postgres -d postgres -Fc - creates a special format dump
• -Fc - compressed special format (better for large databases)
• Saved in /tmp/postgres.dump inside the container

Step 2: Copy the dump to the host

• docker cp postgres:/tmp/postgres.dump ./postgres.dump
• Transfers the dump from the container to the local disk
• Necessary for transferring between containers

Step 3: Delete the old database in the new container

• Connects to the system database template1
• Deletes the existing postgres database (if any)

Step 4: Create a new database

• Creates a clean postgres database in the aiserver-pg container

Step 5: Create extensions

• embedding - extension for vector embeddings
• uuid-ossp - UUID generation (needed for some tables)

Step 6: Copy the dump to the new container

• Reverse copy operation
• Places the dump in the aiserver-pg container

Step 7: Restore the data

• pg_restore - restores from the special format dump
• -t - allocates TTY for progress
• Automatically creates tables, indexes, and data

docker exec -it aiserver-pg psql -U postgres -d postgres

• Connects to PostgreSQL in interactive mode
• -it - interactive terminal
• -U postgres - user postgres
• -d postgres - database postgres

\dt orchestrator.* - shows all tables in the orchestrator schema

• \dt - list of tables
• orchestrator.* - filter by schema

SELECT ... FROM pg_stat_user_tables - statistics on tables

• n_tup_ins - number of inserted rows (approximate number of records)
• Sorting in descending order shows the largest tables

\dx - shows installed extensions

• Should show embedding and uuid-ossp

\q - exit from psql

tail -f migrate.log - monitors logs in real-time

• -f - follow (updates as new lines are added)
• Shows the progress of pgloader migration
• Looks for errors or warnings

docker stats - monitors resource usage by containers

• Shows CPU, memory, network, disk for all containers
• Useful for tracking system load
• Interrupt with Ctrl+C

watch -n 30 - repeats the command every 30 seconds

• Counts the total number of records in all orchestrator tables
• watch - utility for periodically executing commands
• -n 30 - interval of 30 seconds

Usage: Keep these commands in separate terminals during migration