Environment
- Qlik Sense Enterprise on Windows
Transcript
Hello and welcome to the June edition of Techspert talks. I'm Troy Raney and I'll be your host for today's session. Today's presentation is Upgrading Qlik Sense Repository Database with our own Mike Dickson. Mike, why don't you tell us a little bit about yourself?
Thanks Troy. Well, my name is Mike Dickson. I'm a Senior Technical Support Engineer and I've been working with Qlik for going on 8 years. My primary focus within Qlik is Qlik Sense Administration, Deployment and Management.
Great. All right well, today we're going to be talking all about PostgreSQLand the supported versions of PostgreSQL. Mike's going to walk us through a demonstration of that; how that upgrade process looks and generally how to resolve any comment issues that you might encounter. Now, Mike for those of us who aren't that familiar with this topic, how is PostgreSQL database used with Qlik Sense? Well, the PostgreSQL database is the primary database used by Qlik Sense to store all of its configuration data, user information, data connection details, and application data; and is essential for all of the functionality of Qlik Sense.
And that gets shipped with the product, right?
Yes, it does. PostgreSQL is included with Qlik Sense setup by default. The version of PostgreSQL that comes bundled with Qlik Sense as of May 2021 is 12.5. Prior to that was 9.6; however Qlik Sense also supports many other versions of PostgreSQL, such as 13.x 14.x which are not included with installer but can be downloaded and installed manually via the PostgreSQL website.
Okay, so why would it be good to upgrade the database?
Keeping software up to date is critical for many different reasons, including: security, performance, and compatibility. Security is often the most critical reason to keep software updated. For compatibility for example: PostgreSQL 9.6 (which was included with our old installers) is no longer going to be supported by Qlik, which is one of the main reasons why we're here today.
Right, because the the old 9.6 hit its end-of-life as far as being supported?
Correct, as of November 2022, Qlik Sense is no longer going to support version 9.6.
Okay, if people get to this point, it becomes decided that it's time to upgrade, what are the prerequisites or anything they need to know before starting this process .
Yeah, sure. The upgrade process requires administrative rights; the backup destination must have sufficient disk space to dump the existing database; the backup destination should not be a network path or virtual storage folder; it's recommended that the backup is stored on a main drive on the server; there will be downtime for this operation, so plan accordingly. If you're using a version of Qlik Sense prior to February 2022, you'll need to upgrade to at least that version before using the Qlik PostgreSQL installer which we're going to be using today.
Okay, and that's what you're going to show us. Could you tell us a little bit about the demo environment you're going to be showing us today?
Of course. Today we'll be looking at a Qlik Sense environment of February 2022, as this is the minimum requirement for using the QPI tool to upgrade a PostgreSQL database of 9.6 to the latest version of 14.8.
Okay, so this is a version where you've already upgraded Qlik Sense software, but the old 9.6 database is still there?
Correct. The reason this was done is: prior to upgrading to February 2022, if you attempt to use the QPI tool, you'll see the following error: this installer does not support upgrade to version 14.8 for the installed Qlik (since it was February 2021 when I tried to run this), so I was required to upgrade to February 2022 so that I could use the QPI tool.
All right, so to use this tool (which makes things a little simpler) if you're on an old version of Qlik Sense using 9.6, you recommend upgrading Qlik Sense first?
Yes. If you would like to use the QPI tool which simplifies the process, then we would suggest to upgrade to the minimum version which is February 2022.
All right, so how do we get started?
Okay, first we're going to need to download the QPI tool.
And where can we find that?
This can be done by either going to our Knowledge Article we have an- Upgrade Qlik Sense Repository Database Using the QPI installer. It provides all of the steps; and within this article you can find a download link here. Also, we have on our official downloads website; we can come in here to the Qlik data analytics, and then find the product itself called The Qlik PostgreSQL Installer; and you can download it from our official website.
Awesome, so once you've got that downloaded what's next?
All right, once you have it downloaded, it's going to be a simple running of the application as with -make sure you run it as admin. Once you have that up, it'll bring up the tool here. The installer will automatically recognize the existing database as long as it is the package database that comes installed with Qlik Sense.
If somebody has a separate installed PostgreSQL database it would be a different process?
Correct. if you have a separate installed version of PostgreSQL, you'll need to do this manually.
If somebody does have to go down that path, where can they find more information about that?
Sure, the same place where you find the QPI tool, we also have a link to how to manually upgrade right here; and so you can go through the process of upgrading manually.
So, it's all laid out there as well, but this is a simpler version, okay.
Yes.
So, what's next?
So, we got this running.
Okay.
Once we got the tool downloaded and installed, we're going to want to stop all of our Qlik services except for the Qlik Sense Repository Database.
Okay.
The easiest way to do this is to click on Qlik Sense Repository Service and stop, and it'll stop pretty much all of your services except for Qlik Sense Service Dispatcher, which you'll need to stop manually. And in older versions you might have a Qlik Sense Logging Service, which you also want to stop. You'll also want to make sure that none of the services are set as Disabled.
Okay, so we've still got the database running, and I guess that's necessary to be running for the upgrade to work?
Yes, because the tool is going to make a backup of the existing database prior to installing the newer version; so to make a backup, you do need to make sure everything is running.
All right, awesome. I guess just click on the big button?
Yep, you have one option: you click on Upgrade. Once you get to this screen, it's going to require you to go to a couple links: instructions on installing Qlik Sense using the tool.
Okay, I love it when the links to the documentation is right there in the installer.
And then also there's a link which talks about our Customer Agreement.
Okay, all our legal terms.
Yeah, after you've been able to go to these links and read the documentation, it'll allow you to check these boxes. On the next screen, this is where you're going to put in your database password. This is the password that was used during the install of Qlik Sense right so way back when hopefully somebody wrote that down.
Exactly, we'll hit next.
Okay, you're going to want to define the database backup path please remember that the backup destination should not be a network path or a virtual storage folder right this is one of those prerequisites .
Yep, if your environment has enough space you can leave it as default and it'll create a backup folder for you.
Okay, once you've determined the location, next It'll ask to define the install location of where you want the new version of PostgreSQL to be installed; by default it'll go to ProgramFiles, the location of the new PostgreSQL database.
Could that actually be in a Network folder?
No.
Okay,
Still needs to be local, but –
It still needs to be local?
Yes, because this is where your software is actually installed the EXE, the executables, so you want that to be local on the server.
What version will it be installing?
The QPI tool will be installing the latest version of PostgreSQL that's available which is 14.8.
Awesome.
The next, this is the directory where the data of the upgrade a database will be stored; the first location is where the executables are going to be; the next folder is where you want your data. This doesn't need to be on the same folder, but by default it's a good place to keep it, because it's easy to find.
Sure, keeping it all together that makes sense.
After you click Next, review all of the Qlik properties; these are set during the install of Qlik Sense, the ports, the listening address, the IPA range, the max number connections, Etc.
All right, and where are all these settings stored?
So, you can find all of these settings within ProgramData. You're going to look in the old location > Qlik Sense Repository > PostgreSQL, and then you'll see the old version 9.6, and then within here you can look at the configuration and the settings PG_HBA config file and the PostgreSQL config file .
Awesome, so all those settings are stored there. It's good to be aware if people are having trouble finding those things. Okay.
Afterwards, it's as simple as clicking Upgrade. We'll see it's upgrading the existing database; and down here you can see all of the different processes that are going on.
So, we don't have to sit here and wait for this to run, you've got a new machine where this process is already finished?
I do.
Awesome.
So over here, I've run the installer, and it says the database has been backed-up and upgraded successfully.
Okay.
After the upgrade's complete now you're going to want to open up Services, so that we can start Services. You'll find that the Qlik Sense Repository Database Service has been set to Manual. Please do not change this startup method. You will also find a new PostgreSQL x64-14 Service, and please do not rename this. This will be already running; if it's not, go ahead and start this. You're going to want to start up all of your Qlik Services.
Okay, so the old version 9.6 is still got that old name Qlik Sense Repository Database?
Yes.
All right, when people start these up, is there an important order for starting the services?
The first 2 Services I like to start are: making sure that the database is running.
Right.
And then the Dispatcher.
Okay.
After the Dispatch is run, then you can start up the Repository Service. Once the Repository Service is running, all of the other services can be started up in any order.
Okay, cool. You can come into your old ProgramData > Qlik Sense Repository, where you had the old 9.6 folder, the backup was created.
Awesome, so we have a PostgreSQL installer database backup file and did that installer tool rename the 9.6 folder to deprecated that is.
Exactly, .
Correct. so in here you still have your configuration files and all of your data if needed so if anything goes wrong you could just go back in there and rename the folder and you pretty much be up and running just like you were before that is .
Correct. as long as you still have the Qlik Sense Repository database still installed if things have gone wrong you can stop this rename the folder to remove the dash deprecated which was set up by the installer and then start up the Qlik Sense Repository database and you can get back to what it was prior to using the tool great what is the next step after you started all the services.
Okay.
So, once you've got your services running, you'll want to try to log into the QMC and verify that everything is correct. You want to make sure you can navigate and you can see your apps you can also open up the Qlik Sense Hub, and make sure you can access your applications and that they open.
As long as users aren't affected it's working.
Now we know that everything is working. The final step to this process now that we've verified everything is working is to remove the Qlik Sense Database Service, because upgrading to newer versions of Qlik Sense, you need to have this removed.
I can see that having two different versions of SQL can cause some problems.
Exactly; to do this, we're going to need to find the PostgreSQL MSI file that was used during the install of Qlik Sense.
Okay, where do we find that?
This can be done by opening up a Command Prompt as Admin and running the following commands. You're going to want to look in the location of C: > ProgramData > ProgramCache.
Okay, and where do we find those commands?
These commands can be found on our article.
Nice; it's all written out there, that's great.
Exactly, so once you've gotten to the location, if you're upgrading your database from 9.6, you're going to run the following command. If you're upgrading from 12.5, you're going to run this command. Since we ran the installer on 9.6, we're going to copy this command out; let me go to this location real quick. So, now that I'm in the ProgramCache, I'm going to run the following command and it's going to tell me exactly where I can find this PostgreSQL.msi file.
Okay.
Once you have the location, go to the ProgramData > ProgramCache into that location and you'll see the PostgreSQL installer right-click on it, and click uninstall.
Okay, now what is this doing?
It this is removing the Qlik Sense Repository Database Service; right here. Once that's been done, you can come back in here and you can see that Qlik Sense Repository Database Service is now no longer there.
All right, it's all nice and clean.
And so now, we are confirming that we're running on PostgreSQL 14.8 which was installed with the tool; all services are running. We can verify that you are still able to navigate through the qmc without any issues.
Awesome, now everything has run really smoothly here. I mean this is a demo, so we're kind of expecting it to, but what are some common issues that admins could encounter?
Yeah, one of the common issues is you know insufficient disk space for the backup. The backup, we left it as default to make the backup on the C: drive < ProgramData > Qlik > Sense > Repository > PostgreSQL; and I made the backup file here. My backup was relatively small, but in mature environments the backups could be larger. You want to make sure that you have enough disk space on the location that you're doing the backup.
Okay.
Also one of the common and issues using the QPI tool is that this installer does not detect a standalone PostgreSQL database; so, if you're running a environment where you already have PostgreSQL you're already running a standalone version, the QPI tool will not recognize this. You'll need to upgrade your database doing the manual process.
Right, this tool only works when upgrading the bundled versions of SQL that come with Qlik Sense Enterprise.
Yes. The official installed package with Qlik Sense.
Well, that makes sense. Is there another point you want to highlight?
Yes. If you attempt to upgrade prior to the version of at least February 2022, you'll get some messages. Also if you attempt to upgrade to a November 2022, you will see the following error message saying that we are ”no longer support 9.6,” that's why we recommend that you upgrade your database prior to upgrading Qlik Sense.
Okay, so that would be a scenario where you're attempting to upgrade Qlik Sense to a version that recognizes the existence of 9.6 PostgreSQL database is no longer supported? So it's just saying hold-on; upgrade SQL first.
Exactly, so we recommend to use the tool to upgrade your database prior to upgrading your Qlik Sense environment.
Is that a link to the article you're showing us?
Yes.
Awesome.
Right here, when you run the Qlik Sense installer, it'll provide you a link to upgrading the database using the QPI tool.
That's great. I love it when the product has links to more information like that. What if bad things happen, and the installer or the whole computer crashes during installation?
If you run into issues during the install, you will be able to find out what's going on in the temporary logs here. I've gone to Users > my user ID > AppData > Local > and the Temp folder. We've got the Qlik PostgreSQL installer log file, so we can come into this to find out what may have caused the crash during the upgrade process.
Okay. Yeah, that's kind of a worst case scenario, but if you someone does run into that, that would be the log file they need?
Yes. Exactly; and then you can always revert back by renaming the 9.6 folder to just 9.6 and removing the Deprecated from it.
Right. And that seems pretty simple. If during the verification process after upgrading, you find that things aren't perfect you can always revert back to the previous version. Great. Well now, it's time for Q&A. Please submit your questions to the Q& A panel on the left side of your On24 console. Mike, how about if we just take them from the top? First question: would it be best practices to upgrade PostgreSQLfrom 12.5 to a newer version? Or just wait until Qlik starts requiring newer versions or when 12.5 is no longer supported?
Yeah, well this is usually based on each individual company policy. By using the QPI tool we will be upgrading to PostgreSQL 14.8, but as of right now 12.5 which is included in the installer works perfectly fine; it is still packaged with Qlik Sense on all of the releases even up to the most recent release of May 2023.
Okay, the PostgreSQLinstaller tool that we're talking about today, will that always upgrade to the latest version?
I don't think so. As long as we keep updating the tool with newer versions, it will update you know; I think it's a hard-coded in there.
Okay, but that's cool that it's not just upgrading to 12.5 that's upgrading to a more recent release. Right.
Okay, next question: in which versions do you need to be careful when upgrading?
Okay, well there isn't a specific version that you need to be quote unquote careful with using the QPI tool as it only looks for the pre-installed PostgreSQL database of 9.6 or 12.5. These are the only ones that come installed with Qlik Sense. There isn't necessarily a version that needs to be careful of upgrading because there's only those two.
Okay, moving along: are there any Do's and Don'ts that generally apply when upgrading? I guess we're looking for some major roadblocks.
Right. One of the things that I always say is before attempting to upgrade is make sure you have a backup of the environment. As displayed on the screen, we have ”how to backup and restore your PostgreSQLdatabase within Qlik Sense” with a video and all of the steps to manually make a backup prior to upgrading.
Yeah, always back up I think that's pretty common for admins; it's nice to know we've got those instructions there.
Exactly, and also if possible to test using the QPI tool. If you have a Sandbox environment with Qlik Sense installed, attempt to use the QPI tool ahead of using in a Dev, Production, UAT environment so that you get familiar with using the tool.
That's a great tip. Next question: we are currently on May 2022 release of Qlik Sense Enterprise without upgrading the Repository database. How would you recommend we proceed with upgrading? What should we do first?
Okay, if your database is still on 9.6 which can be found within ProgramData > Qlik > Sense > Repository; I would suggest using the QPI tool first to upgrade the database and make sure everything is working as intended; then removing the Qlik Sense Repository Database Service. After that is done, then you can work on upgrading your Qlik Sense environment to the latest releases.
Okay, so if you already passed that threshold of May 2021, upgrade the database first and then Qlik Sense?
Yes.
All right, next question: we've noticed that after the Repository upgrade, every time we're trying to upgrade Qlik Sense to the next version, for example August 2022 to November 2022; the first time we're running a setup file it ”gets stuck upgrading the Repository.” We have to kill it, restart the server, and rerun the setup. Is there something we should do for that to keep from having that issue?
This is honestly hard to answer without looking at why it's getting stuck. If this is consistent, it might help to either make a post within the community to see if others have run into this issue, or you can create a support case providing the Qlik install logs to see if there's an error within the logs as this does sound very environmental.
Right. And that was again in the user profile, the temp log route that had the installer log?
Right, exactly.
Okay, the next question: is it possible to upgrade the Repository database without upgrading Qlik?
Yes, of course there is. Please note that Qlik did not start officially supporting the later versions of PostgreSQL IE 14.x till February 2022 version of Qlik Sense, but yes. It is possible to upgrade the database without upgrading Qlik itself.
Great, next question: by uninstalling and installing Qlik, is there any chance to lose something such as Custom Security rules, custom objects, or any other information?
Great question. If you do not uninstall the database during the uninstall of Qlik Sense, you should not lose any of your custom rules within the QMC, custom data, custom objects, or security rules; but if you have any manually modified files within the program files of Qlik Sense, then you might lose those when you do an uninstall of Qlik Sense. But as far as the configuration within the QMC, as long as you do not uninstall the database, you won't lose any of this information.
Moving on. Next question: I have inherited a Qlik Sense system with 2 different versions of PostgreSQL. How can I tell which version Qlik Sense is using?
Okay, if your environment is using the Qlik Sense Repository Database Service, then you can find the version of PostgreSQL by going into the database data location of C: ProgramData > Qlik > Sense > Repository > PostgreSQL. I think if you see both folders of 9.6 and 12.5 here, to find out which one is being used is to - start by renaming the older version folder to 9.6_old, and restarting all of your services and confirming everything is working as intended. Outside of that, there isn't a specific way with; other than getting into the database using PG Admin to find out what version is being used.
Right. That's a good tip as well, using PG admin; some sort of separate tool that kind of takes Qlik Sense out of the equation.
Exactly.
All right, so there's a couple more questions here: what is that command to remove the old version of PostgreSQLthat you showed?
I assume you mean to remove the old Qlik Sense Repository Database, and this can be done by finding the PostgreSQL.msi and uninstalling it as explained in the demo. Following these steps here to find the program cache and then uninstalling PostgreSQL MSI to remove the service from here.
Okay, but everything's documented in that article and we'll be posting that link to everything that Mike's shown here along with the recording very soon. Next question: what actions you recommend if the PostgreSQL database is already on the D drive? As opposed to the C drive, I guess.
Okay, well if PostgreSQL is already on the D drive, then that usually means that it is not installed using the Qlik Sense installer; as the installer by default will put it on the C ProgramData > Qlik > Sense > Repository location. If you would like to upgrade this, you would need to manually upgrade it by following the steps within our knowledge article of how to manually upgrade PostgreSQL.
Okay, so basically not using that tool, but you going through the manual process?
Exactly.
All right, and a similar question here: does the backup need to be on any local drive or does it have to be the C drive?
It can be on any local drive; it does not need to be on the C drive; just needs to make sure it's not a network path to a different server.
Last question: are older versions of Qlik Sense pre-2021 compatible with recent versions of PostgreSQL?
No, they are not. So, if we look at August 2021 system requirements; we can come down here, it shows that we do support 12x, but we do not support 14 for the older versions. We don't start officially supporting the version that we're using the tool until February 2022. Where we come down to the database where it shows PostgreSQL 14.x.
Right. Well that's great. I appreciate you showing where we can get all the documentation on the system requirements. We'll include the link to this as well.
Yep.
All right, well Mike; thank you very much for this. I hope it'll be useful for people going forward, especially when it comes time to upgrade either Qlik Sense or the database.
No worries. Thank you very much for everybody joining today. I do appreciate it, and I hopefully we were able to answer all the questions that needed to be talked about, and hopefully your upgrade process runs smooth and you don't run into any issues; but if you do please don't hesitate to reach out to support.
Okay, great. Thank you everyone. We hope you enjoyed this session; and thank you to Mike for presenting. We always appreciate getting experts like Mike to share with us. Here's our legal disclaimer; and thank you once again. Have a great rest of your day.