Migrate data from MySQL to PostgreSQL

From qTest version 8.1, we will be using PostgreSQL instead of MySQL for qTest database. You will need to migrate your qTest database in MySQL to Postgres before upgrading qTest to newer versions.

NOTE:

• It is recommended that your local machine has good network connection to your MySQL server and PostgreSQL server
• Java Runtime Environment (JRE) version 7 should be installed on your local machine
• The backup of MySQL database will be downloaded to your local machine. Therefore, it needs to have a sufficient hard drive volume
• Make sure that your MySQL and PostgreSQL servers are running.

Linux - Migrate from MySQL to PostgreSQL

IMPORTANT: Follow instructions to backup of both your databases (PostgreSQL and MySQL)before proceeding. See this article: Legacy Backup and Restore.

1. Download the migration tool to your local machine

2. $ wget https://qtest-storage.s3.amazonaws.com/archive/qtest-onpremise-postgres-migration/0.0.1/qtest-onpremise-postgres-migration-0.0.1.tar.gz
   	

3. Extract the downloaded package

   $ tar -zxf qtest-onpremise-postgres-migration-0.0.1.tar.gz
   		

4. Access to the extracted folder above

   $ cd qtest-onpremise-postgres-migration-0.0.1
   		

5. Open file application.properties

   $ vim application.properties
   		

6. Edit the following required information

   #Replace clientdb2 with the name of your current qTest 7.7.3 MySQL database
   datasource.source.originDatabase=clientdb2
   #MySQL database host address
   datasource.source.host=localhost
   #MySQL listen port(Default 3306)
   datasource.source.port=3306
   #clientdb2clone - Name of MySQL database clone that will be created to migrate data to PostgreSQL.
   datasource.source.database=clientdb2clone
   #MySQL user, this user must be granted create database privileges.
   datasource.source.username=root
   #MySQL password
   datasource.source.password=root
   
   # PostgreSQL server host address
   datasource.destination.host=localhost
   # PostgreSQL listen port(default 5432)
   datasource.destination.port=5432
   # PostgreSQL database that will be created during this migration
   datasource.destination.database=qtest2
   # PostgreSQL User, this user must be granted create database privileges
   datasource.destination.username=postgres
   # PostgreSQL Password
   datasource.destination.password=root
   
   # This database must already exist, it is used to log into postgres and create the destination.database
   datasource.predestination.database=postgres 

   Key	Description
   datasource.source.originDatabase	Name of your qTest's database on MySQL
   datasource.source.host	IP of your MySQL server
   datasource.source.port	Port number of your MySQL server
   datasource.source.database	Enter a non-existing database name on your MySQL server. The tool will clone the datasource.source.originDatabase to datasource.source.database and it will migrate data in the clone database
   datasource.source.username	User name to log in to your MySQL server
   datasource.source.password	Password to login to your MySQL server
   datasource.destination.host	IP of your PostgreSQL server
   datasource.destination.port	Port number of your PostgreSQL server
   datasource.destination.database	Name of a non-existing PostgreSQL database where the data will be migrated to
   datasource.destination.username	User name to log in to your PostgreSQL server
   datasource.destination.password	Password to log in to your PostgreSQL server
   datasource.predestination.database	Enter any existing database which can be accessed by the above PostgreSQL credentials. It is needed so that the tool can create a new database in Postgres. The tool will not use its data

7. After you have done modifying the file, save and close it

8. Run the following command to start the migration process

   java -jar qtest-onpremise-postgres-migration-0.0.1.jar
   		

9. After the progress is completed, your qTest database in MySQL is migrated to PostgreSQL as defined in the table above and a back-up of your MySQL database is downloaded to your local machine in the same location with the executed jar file

Windows - Migrate from MySQL to PostgreSQL

IMPORTANT: Follow instructions to backup of both your databases (PostgreSQL and MySQL)before proceeding. See this article: Legacy Backup and Restore.

1. Download the migration tool to your local machine
2. Extract the downloaded package

3. Access to the extracted folder above

   
4. Open file application.properties and edit the following required information

   #Replace clientdb2 with the name of your current qTest 7.7.3 MySQL database
   datasource.source.originDatabase=clientdb2
   #MySQL database host address
   datasource.source.host=localhost
   #MySQL listen port(Default 3306)
   datasource.source.port=3306
   #clientdb2clone - Name of MySQL database clone that will be created to migrate data to PostgreSQL.
   datasource.source.database=clientdb2clone
   #MySQL user, this user must be granted create database privileges.
   datasource.source.username=root
   #MySQL password
   datasource.source.password=root
   
   # PostgreSQL server host address
   datasource.destination.host=localhost
   # PostgreSQL listen port(default 5432)
   datasource.destination.port=5432
   # PostgreSQL database that will be created during this migration
   datasource.destination.database=qtest2
   # PostgreSQL User, this user must be granted create database privileges
   datasource.destination.username=postgres
   # PostgreSQL Password
   datasource.destination.password=root
   
   # This database must already exist, it is used to log into postgres and create the destination.database
   datasource.predestination.database=postgres 

   Key	Description
   datasource.source.originDatabase	Name of your qTest's database on MySQL
   datasource.source.host	IP of your MySQL server
   datasource.source.port	Port number of your MySQL server
   datasource.source.database	Enter a non-existing database name on your MySQL server. The tool will clone the datasource.source.originDatabase to datasource.source.database and it will migrate data in the clone database
   datasource.source.username	User name to log in to your MySQL server
   datasource.source.password	Password to login to your MySQL server
   datasource.destination.host	IP of your PostgreSQL server
   datasource.destination.port	Port number of your PostgreSQL server
   datasource.destination.database	Name of a non-existing PostgreSQL database where the data will be migrated to
   datasource.destination.username	User name to log in to your PostgreSQL server
   datasource.destination.password	Password to log in to your PostgreSQL server
   datasource.predestination.database	Enter any existing database which can be accessed by the above PostgreSQL credentials. It is needed so that the tool can create a new database in Postgres. The tool will not use its data

5. After you have done modifying the file, save and close it
6. Open Console window and access to the extracted folder

7. Run the following command to start the migration process

   java -jar qtest-onpremise-postgres-migration-0.0.1.jar
   		

8. After the progress is completed, your qTest database in MySQL is migrated to PostgreSQL as defined in the table above and a back-up of your MySQL database is downloaded to your local machine in the same location with the executed jar file