Migrating from MySQL database to PostgreSQL server

Please follow the below steps to migrate data from your existing Exchange Reporter Plus server, which has MySQL database to a new installation that has PostgreSQL.

• Migrate from MySQL to PostgreSQL on the same machine
• Migrate from MySQL to PostgreSQL on different machines

Steps to migrate Exchange Reporter Plus data from MySQL to PostgreSQL on the same machine.

1. Stop Exchange Reporter Plus (Click Start and search for Exchange Reporter Plus. Click Stop Exchange Reporter Plus).

Note: In case you have installed Exchange Reporter Plus as an NT Service, stop the service by clicking on Start → Run, type services.msc, and click OK. In the Services window, locate the service named ManageEngine Exchange Reporter Plus, and stop it.

2. Update your current Exchange Reporter Plus installation (which has MySQL database) to the latest version by applying the latest service pack. You can download the service pack here. Check out the service pack installation steps here.
3. Open the database_params.conf file located in: Exchange Reporter Plus installation directory\conf
4. In the URL, change the port number from 33309 to a different one, say 33319 or 33329.
5. Open the setCommonEnv.bat file located in: Exchange Reporter Plus installation directory\bin using Notepad. Find the text DB_PORT, and change the port number to the one had mentioned in Step 4.
6. Go to the InstallShield Installation Information folder (C:\Program Files (x86)\InstallShield Installation Information); add _bak as suffix to the folder {CCB34152-8A6B-4442-8713-48E2F437E548}(That-is, {CCB34152-8A6B-4442-8713-48E2F437E548}_bak).
7. Download and install the latest version of Exchange Reporter Plus. When the installation is complete, uncheck the Start Exchange Reporter Plus in console mode box, and click on Finish.
8. Open the command prompt as an administrator, navigate to the new Exchange Reporter Plus installation directory\bin, and start the DB alone by executing the startDB.bat file.
9. Now, in the old installation directory, open the command prompt as an administrator; go to Exchange Reporter Plus\bin and execute the changeDB.bat file.
• For Server Type, select PostgreSQL Server.
• For Host Name, specify localhost.
• For Port Number, specify 33309.
• Choose Yes for Migrate Data.
• For Database Name, specify erp, and click Test Connection. If the test connection is successful, click Save.

Note: If you are using builds below 5600, please skip to Step 13 directly. For builds 5600 and above, steps 10, 11 and 12 must be performed.

10. Open the Installationfolder\Exchange Reporter Plus\pgsql\bin path using the command prompt and execute the below given command:

psql.exe -U postgres -h 127.0.0.1 -p 33309

11. Once this command is executed, type the password and click Enter. Then execute, Grant postgres to exchangereporter command.



12. Change the new database's name mentioned during migration, by editing the file under the Installationfolder\Exchange Reporter Plus\conf\database_params folder.



13. Once the database migration is complete, execute stopDB.bat in the command prompt.
14. Restart the product. Check if all the configurations and data from the old Exchange Reporter Plus instance have successfully migrated to the new one.

Steps to migrate Exchange Reporter Plus from MySQL to PostgreSQL on a different machine

Note: In case SSL was enabled in the old instance, we need to copy the existing SSL configuration to the new instance. Copy the server.xml and web.xml files from the \conf folder from the old server and paste them in the same location in the new server.

1. Stop Exchange Reporter Plus (Click Start and search for Exchange Reporter Plus. Click on Stop Exchange Reporter Plus).

Note In case you installed Exchange Reporter Plus as an NT Service, stop the service (Start > Run, type services.msc and click OK. In the Services window, locate the service named ManageEngine Exchange Reporter Plus, and stop it.)

2. Update your current Exchange Reporter Plus installation (which has MySQL database - Exchange Reporter Plus build 4142 and below) to the latest version by applying the latest service pack. (You can download the service pack from here, and apply it using the steps mentioned below in the Service Pack Installation Steps section).
3. Download and install the latest version of Exchange Reporter Plus. When the installation is complete, uncheck the Start Exchange Reporter Plus in console mode option, and click Finish.
4. In the new Exchange Reporter Plus server:
• Enable remote connection in PostgreSQL database server by making the following changes in the pg_hba.conf and postgresql.conf files located in: New Exchange Reporter Plus installation directory\pgsql\data. We recommend you to backup these files before modifying them.

In pg_hba.conf file, under the heading IPv4 local connections, replace the IP address  127.0.0.1/32 with the IPs of all your servers, and save the file.

In postgresql.conf file, find this text: #listen_addresses = 'localhost'. Replace it with:  listen_addresses = 'Destination IP address' and save the file.

5. Open Command Prompt as administrator, navigate to the New Exchange Reporter Plus installation directory\bin, and start the DB alone by executing the startDB.bat file. Now, in the Old Exchange Reporter Plus server, open Command Prompt as an administrator, go to Exchange Reporter Plus installation directory/bin, and execute the changeDB.bat file.
• For Server Type, select PostgreSQL Server.
• For Host Name, specify the new Exchange Reporter Plus host name (Eg: consto-dc1).
• For Port Number, specify 33309.
• Choose Yes to migrate the data.
• For Database Name, enter erp, and click Test Connection. If the connection test is successful, click Save.

Note: If you are using builds below 5600, please skip to Step 9 directly. For builds 5600 and above, steps 6, 7 and 8 must be performed.

6. Open the Installationfolder\Exchange Reporter Plus\pgsql\bin path using the command prompt and execute the below given command:

psql.exe -U postgres -h 127.0.0.1 -p 33309

7. Once this command is executed, type the password and click Enter. Then execute, Grant postgres to exchangereporter command.



8. Change the new database's name mentioned during migration, by editing the file under the Installationfolder\Exchange Reporter Plus\conf\database_params folder.



9. Once the database migration is complete on the new server, execute stopDB.bat in the Command Prompt window opened in Step 5.
10. Revert the changes that you made in files pg_hba.conf and postgresql.conf located in New Exchange Reporter Plus installation directory\pgsql\data.
11. If the archive path configured in the old Exchange Reporter Plus machine is not a shared location, copy the archived files from the old Exchange Reporter Plus machine to the same path in the new Exchange Reporter Plus machine.
12. Start the new Exchange Reporter Plus server. Check if all the configurations and data from the old Exchange Reporter Plus server has migrated to the new one successfully.