Migration from the bundled PostgreSQL database version to a newer version of PostgreSQL

Please follow the steps below to migrate data from the bundled PostgreSQL database version to a newer version of PostgreSQL on the same machine.

1. Stop Exchange Reporter Plus. To do this, click Start, search for Exchange Reporter Plus, and then select Stop Exchange Reporter Plus.

   Note: In case you have installed Exchange Reporter Plus as a Windows Service, stop the service. Click Start > Run, type services.msc, and click OK. In the Services window, locate the service named ManageEngine Exchange Reporter Plus, and stop it.

2. Download this ZIP file, which includes the batch files required for the migration process, and extract its contents.
3. After extracting the ZIP file, follow these steps:
   • Copy the pgsqlnew folder from the extracted ZIP file and paste it in the <installation_dir>. Here, installation_dir refers to the directory in which Exchange Reporter Plus is installed.
   • Copy the initPgsqlnew.bat and setvcommonenvnew.bat files from from the extracted ZIP file and paste them in the <installation_dir>\bin directory.

   These batch files will set the necessary permissions and initialize the new PostgreSQL database, allowing seamless migration from the old PostgreSQL database version to the new one.

4. Navigate to <installation_dir>\bin directory using the Command Prompt and run initPgsqlnew.bat



5. Once it has completed, navigate to the <installation_dir>\bin directory using the Command Prompt and run startDB.bat



6. Navigate to <installation_dir>\pgsqlnew\bin directory using the Command Prompt and execute the following command to start the new PostgreSQL database.

   pg_ctl.exe -D ..\data -o "-F -p port_number" start

   In this command, replace port_number with a port number of your choice.



7. Navigate to <installation_dir>\bin directory using the Command Prompt and run ChangeDB.bat. When prompted,
   • Choose Server Type as Postgre SQL Server.
   • Use the same Port number that was used in step 6.
   • Enter a Database Name of your choice.
   • For the Migrate Data radio option, select Yes.
   • Use postgres as the User Name, and Stonebraker as the Password.
   • Click Test Connection to verify that the connection can be successfully established.
   • Click Save to store the changes.

   

   

8. Once the process is complete , navigate to the <installation_dir>\bin directory using the Command Prompt and run stopDB.bat. Then, execute setPermission.bat and initPgsql.bat with the Command Prompt to prevent any permission issues.



9. Navigate to <installation_dir>\pgsqlnew\bin directory using the Command Prompt and execute the commands given below:
   • psql -U postgres -h 127.0.0.1 -p port_number -d database_name

     where, port_number should match the one used in step 6, and database_name matches the one used in step 7.

   • CREATE USER exchangereporter WITH PASSWORD user_password;

     where, user_password can be a password of your choice, as PostgreSQL does not impose any password complexity requirements.

   • ALTER DATABASE database_name OWNER TO exchangereporter;

     where, database_name should match the one used in step 7.

   • ALTER USER exchangereporter WITH NOSUPERUSER CREATEDB REPLICATION;
   • GRANT postgres TO exchangereporter;



10. Navigate to <installation_dir>\bin directory using the Command Prompt and run the following command:

    encrypt.bat user_password

    In this command, replace user_password with the password that was set while creating the new database user in step 9(b).

    Note: Running this command will generate a unique string corresponding to your password. Please note it down as it will be required at a later stage of the migration process.



11. Navigate to <installation_dir>\conf and open customer-config.xml with a text editor. Change the value of the StartDBServer parameter to true and save the changes.



12. In the <installation_dir>\conf directory, open database_params.conf with a text editor and change the value of the username parameter to exchangereporter, and the password parameter to the unique string that you noted down in step 10.



13. Navigate to <installation_dir> and rename the pgsql folder to pgsqlOld.
14. Navigate to <installation_dir>\pgsqlnew\bin using Command Prompt and execute the following command to stop the new PostgreSQL database.

    pg_ctl.exe -D ..\data -o "-F -p port_number" stop

    In this command, replace port_number with the port number that was used in step 6.



15. Rename the pgsqlnew folder to pgsql.
16. Start the product. Check if all the configurations and data from the old Exchange Reporter Plus instance have successfully migrated to the new one.
17. Once the integrity of the data is verified, navigate to <installation_dir> and take a backup of the pgsqlOld folder by archiving the folder into a ZIP file. Make sure to store the ZIP file in a safe location.

Once the migration steps have been completed, your Exchange Reporter Plus' PostgreSQL database will be upgraded to the newer version. If you encounter any issues or need further assistance, please contact support@exchangereporterplus.com