Step-by-Step Guide to Migrating SonarQube from MySQL to PostgreSQL and Upgrading to 7.9.1 LTS

Overall Approach

Prepare test data (optional in production).

Database migration (MySQL → PostgreSQL) – required from SonarQube 7.9 onward.

SonarQube version upgrade (6.7.7 → 7.9.1 LTS).

Prepare Test Data

Create User Data

Create Project Data

Install sonar-scanner (mac)

tar zxf sonar-scanner-4.0.0.1744-macosx.tar.gz -C /usr/local/
export SONAR_HOME=/usr/local/sonar-scanner-4.0.0.1744-macosx
export PATH=$PATH:$M2_HOME/bin:$SONAR_HOME/bin

Get Maven project

git clone https://github.com/zeyangli/simple-java-maven-app.git
mvn clean package
ls -l target/ | awk '{print $NF}'

classes
maven-archiver
maven-status
my-app-1.1-SNAPSHOT.jar
surefire-reports
test-classes

Scan Project

Run a loop to execute the scanner multiple times; the first run may show no issues.

#!/bin/bash

projectName="demo-service"
scanTime=`date +%Y%m%d%H%M%S`

for((i=1;i<=10;i++))
do
    sonar-scanner  -Dsonar.host.url=http://xxxxxx:9000  \
    -Dsonar.projectKey=${projectName}${i}  \
    -Dsonar.projectName=${projectName}${i}  \
    -Dsonar.projectVersion=${scanTime} \
    -Dsonar.login=admin \
    -Dsonar.password=admin \
    -Dsonar.ws.timeout=30 \
    -Dsonar.projectDescription="my first project!"  \
    -Dsonar.links.homepage=http://www.baidu.com \
    -Dsonar.sources=src \
    -Dsonar.sourceEncoding=UTF-8 \
    -Dsonar.java.binaries=target/classes \
    -Dsonar.java.test.binaries=target/test-classes \
    -Dsonar.java.surefire.report=target/surefire-reports

    echo "${projectName}  scan success!"
done

Result:

Prepare Database Migration

We are migrating from MySQL to PostgreSQL.

Start a SonarQube instance of the same version as the source to perform the migration.

Using a different version will cause errors (see FAQ 2).

Backup MySQL

[root@devops ~]# mysqldump -h 127.0.0.1 -uroot -p sonar >sonar-190803.sql
Enter password:
[root@devops ~]# du -sh sonar-190803.sql
4.6M    sonar-190803.sql

Configure PostgreSQL

# Install
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96-server postgresql96-contrib
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl start postgresql-9.6
systemctl enable postgresql-9.6

# Create Sonar database
su - postgres
psql
create user sonarqube with password 'sonarqube';
create database sonarqube owner sonarqube;
grant all  on database sonarqube to sonarqube;
\q

# Remote access settings
vi /var/lib/pgsql/9.6/data/postgresql.conf
#listen_addresses = '*'
vi /var/lib/pgsql/9.6/data/pg_hba.conf
# IPv4 local connections:
host    all             all             0.0.0.0/0            trust
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
host    all     all       0.0.0.0/0                ident

Start New Sonar Instance

# Install same‑version SonarQube
mkdir pg
unzip sonarqube-6.7.7.zip  -d pg/
cp sonarqube-6.7.7/conf/sonar.properties  pg/sonarqube-6.7.7/conf/sonar.properties

# Adjust only the DB configuration to PostgreSQL
sonar.jdbc.username=xxxx
sonar.jdbc.password=xxxx
sonar.jdbc.url=jdbc:postgresql://127.0.0.1/sonarqube

# Start the service (must start, otherwise errors about missing version info)
./sonar.sh start

MySQL Migrator Tool

Project: https://github.com/SonarSource/mysql-migrator

# Extract
wget https://binaries.sonarsource.com/Distribution/mysql-migrator/mysql-migrator-1.1.0.119.zip
unzip mysql-migrator-1.1.0.119.zip && cd bin

# source.properties
sonar.jdbc.username=xxxx
sonar.jdbc.password=xxxx
sonar.jdbc.url=jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useConfigs=maxPerformance&useSSL=false

# target.properties
sonar.jdbc.username=xxxx
sonar.jdbc.password=xxxx
sonar.jdbc.url=jdbc:postgresql://127.0.0.1/sonarqube

# Run migration
./mysql-migrator -source source.properties  -target target.properties
[main] INFO ... copying tables ...
Migration successful in 2.7 seconds

# Verify data in PostgreSQL
\c sonarqube
\dt
select * from projects;

Restart Service

# Remove ES index
rm -fr data/es5

# Restart SonarQube
./sonar.sh restart

Validate Data

Project information:

User information:

Summary

Database migration completed; next step is to upgrade SonarQube to 7.9.1 LTS.

Upgrade to 7.9.1

Install JDK 11

# JDK 11 is required for SonarQube 7.9.1. Extract without setting global variables yet.
tar zxf jdk-11_linux-x64_bin.tar.gz -C /usr/local/

Configure SonarQube 7.9.1

https://binaries.sonarsource.com/Distribution/sonarqube/sonarqube-7.9.1.zip
unzip sonarqube-7.9.1.zip
cp pg/sonarqube-6.7.7/conf/sonar.properties  sonarqube-7.9.1/conf/sonar.properties

# SonarQube 7.9.1 must use JDK 11
vim ../../conf/wrapper.conf
wrapper.java.command=/usr/local/jdk-11/bin/java

Switch Service

# Stop old version
cd pg/sonarqube-6.7.7/bin/linux-x86-64/
./sonar.sh stop

# Start new version
cd sonarqube-7.9.1/bin/linux-x86-64/
./sonar.sh start

After start you may see an initialization error; open the setup URL to finish configuration.

Initialize service at http://119.3.228.122:9000/setup

Upgrade completed, data retained.

Summary

Upgrade finished; remaining work is to install required plugins.

FAQ

1. Could not determine SonarQube version of the target database…

A newly created empty PostgreSQL instance cannot be migrated; you must first run a Sonar instance of the same version to create the necessary schema.

2. Versions in source and target database don't match: 1838 != 2804

Ensure you run a Sonar instance of the same version as the source before performing the migration.

3. Migration completed but no data appears?

Delete the Elasticsearch index before starting SonarQube (see the “Restart Service” section).

4. SonarQube is under maintenance

After upgrade you need to initialize the service as described in the “Switch Service” section.