Migrating SonarQube from MySQL to PostgreSQL using Docker

My team has been running SonarQube for a long time now, and we’ve been using MySQL this entire time. But the new versions of SonarQube no longer support MySQL, so we were forced to make the move. SonarQube does provide a tool to help out with migration, but it was a lot of steps so I decided to document them.

First I’ll describe our current situation. We’re running both SonarQube and MySQL in Docker containers (no K8s). The containers map volumes to the host for the database and SonarQube where we store the data and plugins. We were running version 7.7-community of SonarQube and version 7.5 of MySQL on RHEL. The original docker-compose file looks like this (there are variables in the file that get replaced at run time):

version: "2"
services:
  sonarqube:
    image: sonarqube:7.7-community
    command: -Dsonar.web.context=/sonar
    container_name: ds-sonarqube
    ports:
      - "9000:9000"
    links:
      - "db:database"
    restart: always
    environment:
      - sonar.jdbc.username=$DB_USER
      - sonar.jdbc.password=$DB_USER_PASS
      - sonar.jdbc.url=jdbc:mysql://database:3306/sonar?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useConfig$$
    volumes:
      - /opt/sonar/extensions/plugins:/opt/sonarqube/extensions/plugins:rw
      - /opt/sonar/logs:/opt/sonarqube/logs:rw

  db:
    image: mysql:5.7.23
    command: --max_allowed_packet=256M
    container_name: ds-sonarqube-db
    ports:
      - 3306
    volumes:
    - /opt/sonar/db:/var/lib/mysql:rw
    restart: always
    environment:
      # MYSQL ENV
      #
      - MYSQL_ROOT_PASSWORD=$DB_ROOT_PASS
      - MYSQL_DATABASE=sonar
      - MYSQL_USER=$DB_USER
      - MYSQL_PASSWORD=$DB_USER_PASS

I used a backup of the MySQL database to run this process locally on my machine before trying it on the server. Here are the steps and detail I followed:

At a minimum, backup your MySQL database before starting. Since my server is on AWS, I also took a snapshot to be safe.
Take down the current SonarQube and MySQL containers and remove them. Since the data and configuration is mapped to a volume, nothing will be lost.

Create a new standalone MySQL container:

docker run -d \
-p 3306:3306 \
-v /opt/sonar/db:/var/lib/mysql:rw \
-e MYSQL_DATABASE=sonar \
-e MYSQL_USER=$DB_USER \
-e MYSQL_ROOT_PASSWORD=$DB_ROOT_PASS \
-e MYSQL_PASSWORD=$DB_USER_PASS \
mysql:5.7.23

Note that the volume maps to the location used by SonarQube

Create a new docker-compose file for SonarQube using PostgreSQL. Make sure you use the same version of SonarQube that you were using with MySQL. The upgrade step for SonarQube comes later. Here is what the new file looks like:

version: "2"
services:
  sonarqube:
    image: sonarqube:7.7-community
    command: -Dsonar.web.context=/sonar
    container_name: ds-sonarqube
    ports:
      - "9000:9000"
    links:
      - "db:database"
    restart: always
    environment:
      - sonar.jdbc.username=$DB_USER
      - sonar.jdbc.password=$DB_USER_PASS
      - sonar.jdbc.url=jdbc:postgresql://database:5432/sonar
    volumes:
      - /opt/sonar/extensions/plugins:/opt/sonarqube/extensions/plugins:rw
      - /opt/sonar/logs:/opt/sonarqube/logs:rw
      - /opt/sonar/conf:/opt/sonarqube/conf:rw
      - /opt/sonar/extensions:/opt/sonarqube/extensions:rw
      - /opt/sonar/data:/opt/sonarqube/data:rw

  db:
    image: postgres:12.1
    container_name: ds-sonarqube-db
    ports:
      - 5432
    volumes:
    - /opt/sonar/postgres:/var/lib/postgresql/data:rw
    restart: always
    environment:
      - POSTGRES_DB=sonar
      - POSTGRES_USER=sonar
      - POSTGRES_PASSWORD=$DB_USER_PASS

Start the new containers and let SonarQube initialize the empty PostgreSQL database.
Download the migrator tool and extract it. Make sure you have a Java 1.8+ SDK (not just a JRE) installed as the SDK is required for the tool.
Stop the SonarQube container, but not the PostgreSQL container.
In the directory where you mapped “/opt/sonarqube/data” (“/opt/sonar/data’ in my case), delete the es6 directory.

In the migrator tool directory, create a file called old.properties. In that file add the following:

sonar.jdbc.url = jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useConfigs=maxPerformance&useSSL=false
sonar.jdbc.username = <sonar_user_name>
sonar.jdbc.password = <sonar_user_password>

In the migrator tool directory, create another file called new.properties. In that file add the following:
```
sonar.jdbc.url = jdbc:postgresql://localhost:32776/sonar
sonar.jdbc.username = <sonar_user_name>
sonar.jdbc.password = <sonar_user_password>
```
I got the port number from Docker. Since we use credstash for secrets, I had to get the username/passwords from there to put in these files. Make sure you get rid of these files when you are done.
Run the tool:
./run.sh -source old.properties -target new.properties
Restart the SonarQube container. If it worked, once SonarQube starts up you should be able to log back in as before.
Prepare to upgrade SonarQube. Increase the max_map_count setting on the server to handle the Elastic search version with SonarQube 7.9+:
sudo sysctl -w vm.max_map_count=262144
Take down the running SonarQube and PostgreSQL containers and remove them
In your docker-compose file, change the version of SonarQube to 7.9-community.
Restart the containers using the edited docker-compose file
When SonarQube starts, it will be in maintenance mode. You need to upgrade the database to a the new version using https://<YourSonarUrl>/setup. The database will upgrade and SonarQube should start normally.
Login as an administrator and upgrade any plugins

Now your installation should be good to go and ready to upgrade to version 8 of SonarQube when it hits LTS status.

Published by Trux