# MySQL → PostgreSQL Migration Guide ### Introduction {% hint style="warning" %} **IMPORTANT!** IF your AI Server version is 2.0.0 or lower, then your version uses both MySQL and PostgreSQL version 16 simultaneously. In the new version 2.1.0+, only PostgreSQL version 17 is used. {% endhint %} You need to migrate from MySQL to PostgreSQL, as well as perform data migration from PostgreSQL version 16 to version 17. This guide describes the process of migrating the Sherpa AI Server database from MySQL to PostgreSQL. The migration includes data transfer, configuration updates, and transitioning to a new database management system. {% hint style="info" %} **ATTENTION!** If you have the option to ABANDON DATA (IF YOU DO NOT NEED IT), we recommend skipping this guide and proceeding to the installation guide from scratch: {% endhint %} {% embed url="" %} ### System Requirements #### Minimum requirements for migration: * **Free disk space**: at least 2x the size of the database (for source data + migration) * **RAM**: at least 4 GB RAM for pgloader * **Docker**: version 20.10+ with Docker Compose support * **Network connection**: stable connection to databases #### Preliminary Checks Before starting the migration, perform the following checks: ```bash # Check available disk space df -h # Check available RAM free -h # Check Docker status docker --version docker compose version # Check database availability docker ps | grep -E "(mysql|postgres)" ```
💡 Comments on checks **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
### Preparing for Migration #### Downloading Required Files If the environment is closed, you need to download the INSTALLATION ARCHIVES in advance and additionally the following files: ```bash curl -fSL -OJ https://sherparpa.ru/downloads/private/SherpaAIServer/pgloader_image.tar curl -fSL -OJ https://sherparpa.ru/downloads/private/SherpaAIServer/alpine_image.tar ```
💡 Comments on downloading files **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
Create the directory `/opt/SherpaAIServerNew` and place all downloaded archives AND FILES FROM THE INSTALLATION GUIDE into it: ```bash # Create a new directory for migration sudo mkdir -p /opt/SherpaAIServerNew # Move downloaded files to the new directory (and files from the download preparation) sudo mv pgloader_image.tar /opt/SherpaAIServerNew/ sudo mv alpine_image.tar /opt/SherpaAIServerNew/ ```
💡 Comments on directory preparation **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
**What is being downloaded:** * `pgloader_image.tar` - Docker image with pgloader and necessary tools * `alpine_image.tar` - lightweight Alpine Linux image for working with volumes (does not affect your current operating system) #### Stopping Services 1. **Stop containers except for databases:** ```bash # go to the old directory cd /opt/SherpaAIServer/ docker compose stop orchestrator embed nginx vllm docker-compose stop orchestrator embed nginx vllm # go to the new directory cd /opt/SherpaAIServerNew/ ```
💡 Comments on stopping services **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
{% hint style="info" %} **Why we stop services:** During migration, any changes to the database must be excluded to ensure data consistency. {% endhint %} {% hint style="info" %} **Note:** There is no need to delete containers, just stop them. `pgembeding` and `orchestrator-db` should remain. {% endhint %} #### Creating Backups **Determining volume names:** First, find the correct volume names in your system: ```bash docker volume list ```
💡 Comments on determining volumes **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
Example output: ``` DRIVER VOLUME NAME local sherpaaiserver_orchestrator-mysql-data local sherpaaiserver_pgdata local sherpaaiserver_storage ``` **Creating volume backups:** Replace volume names with the actual ones from your list: ```bash # Backup MySQL data (replace VOLUME_NAME with the actual name) docker run --rm -v VOLUME_NAME:/data -v $(pwd)/backup_mysql:/backup pgembeding tar czf /backup/mysql_backup.tar.gz -C /data . # Backup PostgreSQL data (if any) (replace VOLUME_NAME with the actual name with postgres) docker run --rm -v VOLUME_NAME:/data -v $(pwd)/backup_pg:/backup pgembeding tar czf /backup/pg_backup.tar.gz -C /data . ```
💡 Comments on creating backups **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
**Examples with specific names:** For a project in the `SherpaAIServer` folder: ```bash docker run --rm -v sherpaaiserver_orchestrator-mysql-data:/data -v $(pwd)/backup_mysql:/backup pgembeding tar czf /backup/mysql_backup.tar.gz -C /data . docker run --rm -v sherpaaiserver_pgdata:/data -v $(pwd)/backup_pg:/backup pgembeding tar czf /backup/pg_backup.tar.gz -C /data . ```
💡 Example for Sherpa AI Server **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`
For a project in the `myproject` folder: ```bash docker run --rm -v myproject_orchestrator-mysql-data:/data -v $(pwd)/backup_mysql:/backup pgembeding tar czf /backup/mysql_backup.tar.gz -C /data . docker run --rm -v myproject_pgdata:/data -v $(pwd)/backup_pg:/backup pgembeding tar czf /backup/pg_backup.tar.gz -C /data . ```
💡 Example for myproject **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
💡 What these commands do * Create compressed archives of all data from Docker volumes * Archives are saved in local directories `backup_mysql` and `backup_pg`
**Checking created backups:** ```bash # Check the size of the archives ls -lh backup_mysql/ backup_pg/ # Check the contents of the archives tar -tzf backup_mysql/mysql_backup.tar.gz | head -20 ```
💡 Comments on checking backups **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
### Main Migration Logic #### Unpacking and Preparing Tools 1. **Unpack client files:** ```bash tar -xvzf "$(ls client_files_*.tgz | sort -V | tail -n 1)" ```
💡 Comments on unpacking **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
2. **Go to the pgloader directory:** ```bash cd pgloader ```
💡 Comments on changing directory **cd pgloader** - navigates to the folder with migration tools * All subsequent commands are executed from this directory * Contains scripts and configurations for pgloader
3. **Load the pgloader Docker image:** ```bash ./load_pgloader.sh ```
💡 Comments on loading the image **./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
4. **Build the migration container:** ```bash ./run.sh build ```
💡 Comments on building the container **./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
#### Configuring Migration Settings **Creating a `.env` file with connection parameters:** All passwords can be found in the file `oais/backend/config/config.ini`. ```bash cp .env.template .env nano .env # URI for the source MySQL database MIGRATE_MYSQL="mysql://user:pass@host:port/db" # URI for the target PostgreSQL database MIGRATE_PG="postgres://user:pass@host:port/db" # Parameters for creating a dump (optional) DUMP_PATH="/path/for/dump" DUMP_URI="mysql://user:pass@host/db" # Parameters for restoration (optional) RESTORE_URI="postgres://user:pass@host/db" RESTORE_PATH="/path/to/dump.sql" ```
💡 Comments on environment variables **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
**Example working configuration:** ```env # For MySQL database DUMP_URI="mysql://root:pass@91.206.149.183:3306/orchestrator" MIGRATE_MYSQL="mysql://root:pass@91.206.149.183:3306/orchestrator" # For PostgreSQL database MIGRATE_PG="postgres://postgres:pass@91.206.149.183:5432/postgres" ```
💡 Example configuration **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`
> **Important:** Ensure that ports and hosts are accessible from the Docker container. #### Executing Migration **Test run (recommended)** ```bash ./run.sh dry-run ```
💡 Comments on test run **./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
**Main migration** ```bash ./run.sh migrate ```
💡 Comments on main 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
**Migration options:** * `--force` - perform migration even if the schema already exists * `--build` - rebuild the container before running * `--rmi` - remove the image after completion {% hint style="info" %} **Execution time:** Depends on the size of the database. For databases up to 10 GB - from 30 minutes to several hours. For larger databases - it may take several hours or days. {% endhint %} #### Cleanup After Migration ```bash # Remove pgloader Docker image (optional) ./run.sh rmi # Return to the root project directory cd .. ```
💡 Comments on cleanup **./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
### Copying Data to New Volumes After successfully migrating the database, it may be necessary to transfer the file storage between Docker volumes. #### Loading the Alpine Image ```bash docker load -i alpine_image.tar ```
💡 Comments on loading Alpine **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
#### Creating a New Volume ```bash docker volume create aiserver-storage ```
💡 Comments on creating a volume **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.)
#### Determining Current Volumes **Find the names of used volumes:** ```bash docker volume list ```
💡 Rechecking volumes **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
Example output (names depend on the project folder name): ``` DRIVER VOLUME NAME local sherpaaiserver_orchestrator-mysql-data local sherpaaiserver_pgdata local sherpaaiserver_storage ``` Or, if the project folder is named differently (for example, `myproject`): ``` DRIVER VOLUME NAME local myproject_orchestrator-mysql-data local myproject_pgdata local myproject_storage ``` #### Transferring Data Between Volumes **Determine the data source:** In the command below, replace `STORAGE_DATA` with the name of your current volume for data storage (for example, `sherpaaiserver_storage`): ```bash docker run --rm \ -v STORAGE_DATA:/from \ -v aiserver-storage:/to \ alpine ash -c "cp -av /from/. /to/" ```
💡 Comments on data transfer **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`
**Checking the success of the copy:** ```bash # Check the contents of the new volume docker run --rm -v aiserver-storage:/data alpine ls -la /data # Compare sizes (approximate) docker run --rm -v STORAGE_DATA:/data alpine du -sh /data docker run --rm -v aiserver-storage:/data alpine du -sh /data ```
💡 Comments on checking the copy **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
### Migration from PostgreSQL 16 to 17 #### Preparing for PostgreSQL Version Upgrade **Preliminary actions:** 1. **Keep the old database running** - the `pgembeding` container should continue to run 2. **Unpack new client-files** - the archive should contain updated `docker-compose.yml` and `.env` 3. **Transfer configuration** - copy connection parameters from the old version `/opt/SherpaAIServer/oais/backend/config/config.ini` to the new `.env` 4. **Change the port** - temporarily set port 5433 for the new PostgreSQL to avoid conflict #### Unpacking the Archive with Client Files ```bash # Find and unpack the archive (the most recent version is automatically selected) tar -xvzf "$(ls client_files_*.tgz | sort -V | tail -n 1)" ```
💡 What this command does * `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.
#### Preparing Scripts for Execution ```bash # Go to the directory with scripts cd sh_scripts/ # Make all scripts executable chmod +x *.sh # Return to the root project directory cd .. ```
💡 What these commands do * `chmod +x *.sh` - sets execution rights for all shell scripts * This is necessary for running scripts in the following installation stages
#### Structure of the Unpacked Archive: After unpacking, you should see the following files and directories: * `sh_scripts/` - directory with installation scripts * `download_all_latest_docker_images.sh` - script for downloading Docker images * `load_all_docker_images.sh` - script for loading images into Docker * `extract_models.sh` - script for unpacking AI models * `extract_llama.sh` - script for unpacking LLM models * `docker-compose.yml` - Docker Compose configuration for client installation * `.env` - file with environment variables for system configuration #### Loading Docker Images ```bash # Run the script to load Docker images sudo ./sh_scripts/load_all_docker_images.sh ```
💡 What the script does 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
#### Unpacking AI Models ```bash # Run the script to unpack the main models sudo ./sh_scripts/extract_models.sh ```
💡 What the script does 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
```bash # Run the script to unpack the LLM model sudo ./sh_scripts/extract_llama.sh ```
💡 What the script does 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
#### Directory Structure After Unpacking (approximate): ``` ./whisper/ └── models/ ├── base.pt └── ... ./bge_reranker/ └── models/ └── bge-reranker-large/ ├── config.json ├── model.bin └── ... ./embed-server/app/ └── model-store/ └── sentence-transformers/ └── paraphrase-multilingual-MiniLM-L12-v2/ ├── config.json ├── pytorch_model.bin └── ... ./llm-server/models/ ├── meta-llama/ │ └── Meta-Llama-3-8B-Instruct/ │ ├── config.json │ ├── model-00001-of-00004.safetensors │ ├── model-00002-of-00004.safetensors │ └── ... └── tokenizer.json ``` #### Starting the New Version of PostgreSQL ```bash docker compose up -d ```
💡 Comments on starting the new version **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
**Monitoring the Startup:** ```bash # Monitor initialization logs docker logs aiserver # Check the status of containers docker ps | grep aiserver ```
💡 Comments on monitoring **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
#### Stopping Services to Transfer Data **Stop all services except for the new database:** ```bash docker-compose stop aiserver aiserver-code_interpreter aiserver-llm-server aiserver-embed aiserver-whisper aiserver-bge_reranker docker compose stop aiserver aiserver-code_interpreter aiserver-llm-server aiserver-embed aiserver-whisper aiserver-bge_reranker ```
💡 Comments on stopping services **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
#### Transferring Data Between Versions **Execute the following sequence of commands to transfer data:** ```bash # 1. Create a dump from the old database docker exec -t postgres pg_dump -U postgres -d postgres -Fc -f /tmp/postgres.dump && \ # 2. Copy the dump to the host docker cp postgres:/tmp/postgres.dump ./postgres.dump && \ # 3. Delete the old database in the new container docker exec -it aiserver-pg psql -U postgres -d template1 -c "DROP DATABASE IF EXISTS postgres;" && \ # 4. Create a new database docker exec -it aiserver-pg psql -U postgres -d template1 -c "CREATE DATABASE postgres;" && \ # 5. Create necessary extensions docker exec -it aiserver-pg psql -U postgres -d postgres -c "CREATE EXTENSION IF NOT EXISTS embedding;" && \ docker exec -it aiserver-pg psql -U postgres -d postgres -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";" && \ # 6. Copy the dump to the new container docker cp ./postgres.dump aiserver-pg:/tmp/postgres.dump && \ # 7. Restore the data docker exec -t aiserver-pg pg_restore -U postgres -d postgres /tmp/postgres.dump ```
💡 Comments on transferring between PostgreSQL versions **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
### Validating Migration Results #### Checking the Correctness of Data Transfer **Basic checks:** ```bash # Connect to the new database docker exec -it aiserver-pg psql -U postgres -d postgres # Check the number of tables \dt orchestrator.* # Check the number of records in main tables SELECT schemaname, tablename, n_tup_ins AS rows FROM pg_stat_user_tables WHERE schemaname = 'orchestrator' ORDER BY n_tup_ins DESC; # Check for extensions \dx # Exit psql \q ```
💡 Comments on basic checks **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
#### Testing Functionality 1. **Start main Sherpa AI Server services** 2. **Perform test API requests** 3. **Check the operation of the web interface** 4. **Perform basic data operations** ### Diagnostics and Troubleshooting #### Common Errors and Solutions **If migration errors occur, contact technical support, but in the meantime, you can return to the old directory /opt/SherpaAIServer and run the previous settings of Sherpa AI Server** **"Heap exhausted" or memory shortage** ``` Solution: Increase memory limit for pgloader export SBCL_DYNAMIC_SPACE_SIZE=8192 ``` **Database connection error** ``` Checks: - Check port availability: telnet host port - Check the correctness of credentials - Check network accessibility of containers ``` **Data integrity violation** ``` Actions: 1. Check pgloader logs: cat migrate.log 2. Compare the number of records in the source and target databases 3. Check for all tables and indexes ``` **Migration interruption** ``` Actions: - Do not restart the migration - Contact technical support with logs - Provide: migrate.log, .env configuration, database size ```
💡 Comments on monitoring migration **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
### Completing the Migration **After successfully completing all steps:** 1. **Start all Sherpa AI Server services** 2. **Perform final system testing** 3. **Delete temporary files and old containers** (if necessary) > **Recommendation:** Save migration logs and configuration files for future reference. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.sherparpa.ru/en/obnovleniya/obnovleniya-sherpa-ai-server/versiya-2.2.0/instrukciya-po-migracii-mysql-postgresql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.