Upgrade Bundled PostgreSQL Using QPI Tool
Backup, Upgrade, and Recover Qlik Sense
Overview
In this lesson, you will learn how to get your bundled installation of PostgreSQL server upgraded using the Qlik PostgreSQL Installer (QPI) tool. This process consists of four parts, all of which must be completed in order to successfully upgrade bundled PostgreSQL server. As a quick reference, the four parts are:
Unbundle and Upgrade Qlik Sense Repository Using QPI Tool
Uninstall Qlik Sense Repository Database Service
Reinstall PostgreSQL Binaries
Update Path Environment Variable
Let’s get started!
76
STEPS
56
SCREENSHOTS
HIGH
COMPLEXITY
01. Unbundle and Upgrade Qlik Sense Repository Using QPI Tool
This part consists largely of downloading the QPI tool, running the QPI tool, and providing the QPI tool few pieces of information for it to use to upgrade PostgreSQL server.
Step 01.
Go to Qlik’s Downloads page.
Step 02.
In the Products filter, click the search icon.
Step 03.
Type the word PostgreSQL in the search input box.
Step 04.
Select the Qlik PostgreSQL Installer option.
Step 05.
Click the Qlik_PostgreSQL_setup.exe download link to download the QPI tool.
Step 06.
Stop all Qlik Sense services on rim nodes, if you have any.
Step 07.
Stop all Qlik Sense services except for Qlik Sense Repository Database service in the same order on central node.
Step 08.
Go to the Downloads folder or wherever you downloaded the install file to.
Step 09.
Right-click the Qlik_PostgreSQL_setup_v1.4.0.exe file and select Run as administrator.
Step 10.
Click the Yes button in the User Account Control window.
Step 11.
Click the Upgrade button.
Step 12.
Click the Qlik PostgreSQL Installer instructions link to open documentation explaining how to use the QPI tool.
Step 13.
Click the Qlik Customer Agreement link.
Step 14.
Check both checkboxes to confirm that you’ve read the instructions and agree to Qlik Customer Agreement, if you do.
Step 15.
Click the Next button.
Step 16.
Enter the password of postgres user.
Step 17.
Click the Next button.
Step 18.
Specify the directory where you would like the QPI tool to save the backup of current version of PostgreSQL server contents.
Step 19.
Click the Next button.
Step 20.
Specify where you would like the QPI to install the new, standalone PostgreSQL server.
Step 21.
Click the Next button.
Step 22.
Specify where you would like the new, standalone PostgreSQL server to store its data.
Step 23.
Click the Next button.
Step 24.
Review specified settings and click Upgrade to being unbundling and upgrade process.
Step 25.
Wait for the QPI file to complete.
Step 26.
Notice the note about uninstalling Qlik Sense Repository Database. We will do that next.
Step 27.
Click the Close button to complete the unbundling and upgrade of PostgreSQL process.
02. Review and Confirm Successful Upgrade and Unbundling
Now that PostgreSQL has been upgraded and QPI tool switched Qlik Sense to use the new version of PostgreSQL server, it’s a good idea to launch QMC and the Hub to make sure Qlik Sense is fully operational. In this part, we will make sure that Qlik Sense works as expected and take a note of few things like where to find a backup of old version of PostgreSQL if you will need it.
Step 01.
Launch QMC and confirm that it is operational.
Step 02.
Launch the Hub and confirm that it is operational.
Step 03.
Launch the Services app and confirm that the old Qlik Sense Repository Database service is not running, and the Setup Type is set to Manual.
Step 04.
Confirm that the new postgresql-x64-14 service has been installed and is running.
Step 05.
Go to installation directory of old version of PostgreSQL and notice that the old PostgreSQL directory has been renamed.
Step 06.
Notice also that the backup of the old repository database has been created and saved to the backup folder.
Step 07.
If you open the backup folder and the folder inside it, you’ll see the actual backup file of your old PostgreSQL server.
Step 08.
Right-click the file and select Open with Code option to see contents of the backup file.
Step 09.
Notice that the file contains SQL script that can be run to rebuild original PostgreSQL server’s contents if needed.
Step 10.
Lastly, notice also that the new, standalone PostgreSQL server has been installed in the directory you’ve specified in step 20 above.
This confirms that QPI tool successfully:
Upgraded PostgreSQL server to a newer version.
Converted PostgreSQL into a standalone installation.
And copied the old database to the new, standalone database.
We are not done yet, though. We’re only halfway through. Stopping here will cause issues upgrading Qlik Sense in the future. The last two steps that must be completed are:
Uninstalling Qlik Sense Repository Database service.
And reinstalling PostgreSQL binaries.
03. Uninstall Qlik Sense Repository Database (QSRD) Service
Now that you’ve confirmed that copying of data and upgrade of PostgreSQL server went successfully, the next step is to remove the Qlik Sense Repository Database service that used to run the old PostgreSQL server. Qlik Sense gets “confused” when there are multiple PostgreSQL servers and services installed on one server and that confusion leads to failure in upgrading Qlik Sense in the future.
To ensure that you won’t have issues upgrading Qlik Sense in the future, Qlik Sense Repository Database service must be uninstalled. This is a mandatory step.
What Will Uninstalling QSRD Service Do?
When we will complete this section, two things will happen:
Qlik Sense Repository Database service no longer will be listed as one of the Qlik Sense services in Services app:
PostgreSQL folder and the binaries inside of it will be deleted from Qlik Sense install directory.
Default Qlik Sense install directory is C:\Program Files\Qlik\Sense\Repository .
Let’s begin!
Step 01.
Go to C:\ProgramData\Package Cache directory.
Step 02.
Type the word PostgreSQL in the search bar.
Step 03.
Hit the Search button to search for PostgreSQL msi file.
Step 04.
Right-click the appropriate msi file.
Step 05.
Select Uninstall from the context menu.
Step 06.
Click Yes on the popup window that’s asking you to confirm that you want to uninstall Qlik Sense Repository Database service and corresponding binary files.
Step 07.
Click the Yes button in the User Account Control window to being uninstallation process.
Step 08.
Launch the Services app and confirm that Qlik Sense Repository Database service has been uninstalled.
Step 09.
Confirm that PostgreSQL folder has been removed from Qlik Sense install directory.
Step 10.
Confirm that QMC is operational by logging in to QMC and exploring tasks, apps, users, etc.
Step 11.
Confirm that the Hub is operational.
04. Reinstall PostgreSQL Binaries
The last mandatory step is to reinstall PostgreSQL binaries. PostgreSQL binary files have been deleted when Qlik Sense Repository Database service was uninstalled. Although we needed to uninstall the QSRD service, we do still need the PostgreSQL binary files.
Why Do We Need PostgreSQL Binaries?
We need them because there are several Qlik Sense service configuration PowerShell scripts that use them. An example is Configure-Service.ps1 file which is used to configure App Distribution Service and is located here by default:
If you open that file with a text editor (I’m using Visual Studio Code in this example), you will see that this configuration file relies on psql.exe file that is supposed to be in C:\Program Files\Qlik\Sense\Repository\PostgreSQL\14 directory.
Since that directory and the file have been deleted when Qlik Sense Repository Database service was uninstalled, this configuration script will fail to run.
Steps in this section will show you how to reinstall the binary files without reinstalling the Qlik Sense Repository Database service. Let’s begin!
Step 01.
Go to C:\ProgramData\Package Cache directory.
Step 02.
Type the word PostgreSQL in the search bar.
Step 03.
Hit the Search button to search for PostgreSQL msi file.
Step 04.
Right-click appropriate MSI file.
Step 05.
Select the Open file location option from the context menu.
Step 06.
Right-click the address bar and select Copy address option.
Step 07.
Launch the Command Prompt as administrator.
Step 08.
Click the Yes button in the User Account Control window to allow Command Prompt to be run with admin privileges.
Step 09.
Change the directory to the directory that you copied in Step 05 above.
Step 10.
If you currently have a version of Qlik Sense that is August 2023 or newer installed, run the following command to get the PostgreSQL binary files reinstalled:
If you currently have a version of Qlik Sense that is May 2023 or older installed, copy and paste the following command into Command Prompt:
Step 11.
If you have installed Qlik Sense in non-default directory, change the path for INSTALLDIR parameter to point to your Qlik Sense installation directory.
Step 12.
Hit the Enter key on your keyboard to run the command and get PostgreSQL binary files reinstalled.
Step 13.
Once the reinstallation process is complete, open the Repository folder in Qlik Sense install directory and confirm that PostgreSQL folder has been reinstalled.
05. Update Path Environment Variable
As a reminder, in step 20 in 01. Unbundle and Upgrade Qlik Sense Repository Using QPI Tool section above, we specified the new installation directory for updated, standalone version of PostgreSQL. Since PostgreSQL now lives in a new folder, we’ll also need to update the Path environment variable to point to the new PostgreSQL directory so that you would be able to continue running script to backup PostgreSQL without issues. Let’s get that done and wrap up this lesson and the PostgreSQL upgrade process.
Step 01.
Open Windows Explorer and go to the new PostgreSQL installation directory and look for bin folder.
Step 02.
Open the bin folder.
Step 03.
Right-click the address bar and select Copy address option.
Step 04.
Go to This PC in Navigation pane.
Step 05.
In the Computer tab, click on System properties button.
Step 06.
Click the Advanced system settings link in the list of Related settings links.
Step 07.
In the System Properties window, click the Environment Variables… button.
Step 08.
In the System variables section, select the Path variable.
Step 09.
Click the Edit… button.
Step 10.
Select the path to the old PostgreSQL bin directory.
Step 11.
Click the Edit button.
Step 12.
Paste the path to the new PostgreSQL bin directory that you’ve copied in step 03 above.
Step 13.
Click the OK button to save changes to Path environment variable.
Step 14.
Click the OK button in the Environment variables window.
Step 15.
Click the OK button in the System Properties window.
Guide
Summary
Done!
That was a whole lot! But you made it. You now have Qlik Sense server that is using newer, standalone PostgreSQL server, with old PostgreSQL fully uninstalled, PostgreSQL binary files that are needed for Qlik Sense service configuration scripts reinstalled and ready to resume operations as usual, and Path environment variable updated to point to new PostgreSQL bin folder to ensure that you won’t run into issues running PostgreSQL backup script. Congrats for making it through this process!
Completing this process enabled you and me to have greater control and more flexibility going forward for upgrading PostgreSQL.
Now that you have effectively converted PostgreSQL to a standalone installation, you will need to follow different steps going forward when upgrading PostgreSQL server. The next set of lessons show how to upgrade a standalone installation of PostgreSQL server. I’ll see you in the next lesson!
References
Up Next
Standalone PostgreSQL Upgrade Overview
Copyright © 2023 howdash LLC