STT - Upgrading Qlik Sense Repository Database

[Troy_Raney]

Environment

• Qlik Sense Enterprise on Windows

Transcript

Hello everyone and welcome to the May 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 Mario Petre. Mario, why don't you tell us a little bit about yourself?
Hi everyone! Good to be back here with you once more, trying to bring you some interesting and exciting product news. I’ve been working with Qlik going on seven-eight years now, currently holding the role of Principal Technical Engineer in the Signature team. My specialty from the beginning has been Qlik Sense Enterprise on Windows, also known as Qlik Sense Client Managed now; and have been working very closely with R&D in the last couple of years trying to improve certain aspects of our deployment of the product on Windows servers as well as better management solution for standalone Postgres Database.
Excellent.
And as a result, we have this new installer that I would love to talk about and show it off a little bit.
Yeah. So, that brings us to our agenda for today. We're going to be talking about the end of life for Postgres 9.6; big reason why this tool has become necessary or at least definitely helpful. So, we’ll look at some new functionality with that upgrading tool, and the whole creating a new standalone setup. We're going to be demoing the upgrading process and outlining some important Postgres details there. Hopefully talk about some troubleshooting, what to do when things aren't going as expected, and we'll be highlighting some resources for everybody. So, Mario, can we take a look at what we mean by the end of life for 9.6, and what the current version of Postgres is that we should be using?
Sure. Postgres themselves have decided to cut off support for our Postgres 9.6 version that we've been shipping with Qlik Sense for a long time now, ever since versions in 2017 I believe.
And (high level) basically, Qlik Sense uses a Postgres Database for a Repository Service, right?
That's correct. We use it specifically for the Qlik Sense Repository Database Service and this may be installed on the same machine as your central node or elsewhere as a standalone setup.
So, what version of Postgres is Qlik currently supporting?
Our supported versions in Qlik Sense Client Managed today go up to 12.5.
Okay. So, the versions we see here in this matrix are supported, right?
Mm-hmm. Supported by Postgres.
So, if someone goes to a download site on Qlik Community and downloads the latest version of Qlik Sense Enterprise Client Managed, what's the release of Postgres that comes with that?
So, that already comes with 12.5 by default.
Okay.
We've been shipping it this way since the May 2021 release.
Great. And we can see Postgres comes out and supports that version for five years, and 9.6 hasn't been supported for a while now. So, it's time to upgrade if you're still running an old version.
Indeed. And we've received numerous cases and questions from customers who are still running these older versions, and wondering: what is the best path to upgrade? Up until now, we had a rather manual process for doing all of this. So, through collaboration with R&D, we put together an easier deployment method, one that also allows you to set up a brand new server wherever you may choose.
Okay. So, what is this new Postgres installer?
It's a standalone package that is delivered on the side of the regular Qlik Sense installer.
Okay.
The regular Qlik Sense installer still contains Postgres 12.5, and you can use it to set up a brand new service cluster; however, it will also by default install all of the Qlik Sense services. You can use this new Postgres installer if all you want to do is either set up a brand new Qlik Sense Repository Database for your central host (either on that same machine or on a separate host), or you wish to upgrade an existing Qlik Sense Client Managed setup that is running Postgres 9.6 to a supported version, in this case 12.5.
Right. So, this installer is for people really just focusing on upgrading their Postgres Database.
And keeping in a in a supported version, yes.
So, would this installer be for someone who's already been running Qlik Sense for a while and upgraded to a recent version of Qlik Sense Client Managed?
If you already have a modern version of Qlik Sense running that was upgraded from an older installation where 9.6 was deployed initially, the upgrade of Qlik Sense itself is not going to upgrade the database. It will still be using that 9.6. So, in order to bring the Postgres setup itself up to a supported version, you will need to use this dedicated installer.
And what's an example of a version of Qlik Sense that came with 9.6?
February 2021 for example.
Okay.
We still ship that with 9.6 by default. The May release of 2021 installed 12.5 for brand new installs, but (up to this point) even the May 2022 release will continue to use 9.6 if that's what you had installed before.
Okay. So, if you're trying to upgrade from February 2021 of Qlik Sense, you might need to run this installer to upgrade your Postgres?
That's correct.
Okay. Today we're going to be demoing that whole upgrade process. Are there any important details we need to take a look at before we jump into the demo?
Sure. This will only install a Postgres Database Service. It will not contain all the rest of the services that you would expect on a Qlik Sense install.
Right.
We also assume that you will be logging in with a service account that is currently running the rest of the services. Would want to make sure that you have sufficient disk storage, because part of the new installer is a backup step that will create a dump of the current database, so that it is easier to restore or roll back.
That's a great built-in feature there. Nice.
By default, we will place this in the same location where the Postgres binaries are installed, in Program Data; however, if you do choose to put it elsewhere, keep in mind that network paths and virtual disks may cause some performance issues during installation; it may even cause it to fail.
Okay. So, you're recommending the backup location to be a local disk just to avoid any potential issues?
Yep, that's correct.
It's good to get those prerequisites out of the way. So, in this demo environment you've got Qlik Sense May 2021 installed. What version of Postgres will you be upgrading from?
We are upgrading the current database version is 9.6. So, all we will be doing here is (for part of the demo) is to upgrade the existing database to 12.5.
Okay, you've already downloaded the installer. How should we get started?
First things, first we need to make sure that we stop all services other than the existing Qlik Sense Repository Database. In a multi-node environment, you will need to schedule a maintenance period, as we assume that all services in all other nodes that may be contacting the database back-end are going to be stopped during this operation.
Right.
As you would do with any other type of database maintenance.
Okay. So, make sure all the Qlik Sense services are stopped. In this situation, it's all installed on a single node, right?
Yes. I only have a central node here, yes.
Okay.
Again, it's only designed to upgrade a Postgres Database or a Qlik Sense Repository Database that was shipped with Qlik Sense. It will not detect a manually installed Postgres version, if you just grab that from Postgres download site, as we have recommended before.
Right, if someone set up a customized thing, yeah.
Yes.
So, you're stopping the Repository Service and then stopping the database?
We'll need the database to…
Running…
To run, to stay up and running, yes. The upgrade installer connects to the database and it will attempt to take a backup first, and then migrate that data into the new installation.
So, everything is turned off except for the database?
That's correct, and we can go ahead and run the installer. The first screen already tells us that the installer has detected an existing setup and presents us with the upgrade option only.
So, if nothing had been previously installed, it would prompt for an installation?
Yes, but in this case, we are going to just upgrade. So, we can see how easy that is. It will ask you for the database super user password that you've defined in the initial setup. This is essentially the password for the Qlik Sense Repository Database user.
Right.
Click Next. Here you can see it's asking us to choose a database backup path; in this case, I will leave the default.
Okay.
And the new version will be installed right along 9.6.
Yeah.
Some confirmations; just hit Upgrade, and we'll see some progress updating at the bottom. It will run through a number of file operations, extracting, copying, etc. We just have to wait for it to be done.
All right. So, it's backing up.
And take a look at that folder location, we'll see the database dump showing up.
There's the new folder.
We can see the backup file here. It will be growing in size as we finish. And here we go. It's started installing, unpacking.
And usually how long does this take?
It will very much depend on the size of your database. It could be from just a few seconds to a couple of minutes.
Aesthetically, I love that it's actually telling you what it's doing, instead of just giving you a progress bar.
Yeah, an interesting change from the regular installer.
So, where can people find this installer?
Right now, we're getting a very-very early look at this. Eventually it will be available here.
Okay. So, this is the article on Qlik Community. It has all the steps that we're talking about here, some important details, and this will be where to find the link to download the installer, great.
And it looks like it's done.
Awesome.
Let's take a look at what happened in services. The original Qlik Sense Repository Database Service has been switched to Manual in order to avoid any conflicts. We will go ahead and stop this immediately after the upgrade has finished. The Qlik Sense Repository Service has also been changed to Manual. This is to avoid automatic spin up and interaction with the database during the process, but in order to keep uptime, we should change this back to Automatic Delayed.
Okay.
And if we scroll a little bit further up…
And is that the service running the new version? Okay.
This is running now the new version. The Qlik Sense Repository Database will be running the old version, and we can actually verify that that's the case by looking at the service definitions themselves, and what paths it points to. Here we can see that it's…
Okay.
We're going into the 9.6 folder, and if we look at the properties of the new service, it will be using 12.5.
So, how can we confirm that the database is indeed running?
Via PgAdmin.
Okay, and again, PgAdmin is just a free utility to take a look at databases, right?
That's correct. Okeydoke. So, here we can see two connections have been created. Go ahead and try to connect to the Postgres 12 connection that was just generated. And there we are.
Okay. What about a scenario where we use the installer to set up a new installation of 12.5, then restore the old database to the new location? How would that work?
Sure, we can demo that now.
Okay, and what are we looking at?
We have the Postgres setup installer, the dedicated installer separately, and a database backup taken from our existing central node.
Okay, and this is just a clean virtual environment, nothing's been installed here?
Yep, no Qlik products, no database backend, nothing. Just a clean Windows server machine ready to roll.
By running this installer, it should install Postgres 12.5, and we can go from there?
That's correct.
Okay.
It doesn't detect anything. So, the only option that we'll will be exposed to is install. We'll need to provide a database password. So, by default, this is configured to allow connections from all addresses, all ranges, with a maximum of 100 incoming connections. If you don't know yet how big your environment will be, just keep in mind that you may have to come back to the Postgres configuration files on this node and adjust this value to something that makes sense.
Okay.
For instructions on how to come up with a correct scaling of Repository Service and database communications and worker threads, you can refer back to our Optimization webinar from a couple of months ago.
Right.
So, we'll assume the defaults. We will be connecting to this node with just one central node, so we can leave all of these values. If you wish to know a little bit more about what these things do, you can always hover over the tooltips.
Okay.
And let's hit Next. The super user password for the Postgres user. This is the master password for the database itself.
Okay.
And we can click Install. With this installer, PgAdmin 4 comes along as well. It will be deployed in the same location where you've chosen to install the database itself.
Okay. So, it's unpacking everything it needs, setting it up to be able to work with Qlik Sense Client Managed, and it will have that PgAdmin 4 utility. All right. So, we’ve finished.
Ta-da! All installed.
So, what now?
First, let's take a look and see what we're left with in terms of services.
Okay.
Here we go. PostgreSQL. It is running.
All right. So, we see it's running.
And we can see that it's actually using the Qlik Sense path to run the service. So, it's not just a random service but it is the one that we've just installed.
Okay, great. So, where can we find PgAdmin, so we can restore the copy of the older database to this new version?
Let's go ahead and do that now just again for clarity we are in Program Files, not Program Data. This will be your binary installation path that was chosen during installation. PgAdmin 4, and inside the Bin folder there is the little executable here. All right. So, again master password for PgAdmin; this was defined during installation. Let's go ahead and create a new server connection. Call this local QSR.
Okay. So, you're connecting to the new installation?
Yep, and in this case it's localhost. Change the port from the default that Postgres suggests to the one that was defined during installation, in our case 4432, this is the standard Sense Repository Database port. Let's go ahead and provide the password. We'll connect with a super user, and here we are.
Okay.
We have a number of databases that are being created from scratch. Right now, all of these will be empty.
Great. So, how do we restore?
The first thing that you want to do is drop the existing database, and create it again; only the QRS. It's always a good idea (since you are restoring data from a different machine) to start with a completely clean slate.
Okay.
And starting from scratch will allow the restore process to create the schema, plus tables, plus their relationships, according to the backup definition of course. One last step is to choose template 0.
Okay.
And now we can right-click on it, and restore it.
This is something you already downloaded to a local file?
That's correct. We have it here. I’m going to select it and restore.
Okay.
And this, again depending on the size of that database backup, might take a little bit, you may see some warnings here; that's often perfectly normal. If the process itself did not finish with a drastic error message, you should be fine to continue.
Okay. So, PgAdmin is reading from that backup of our 9.6 database, and it's setting it up in our brand new 12.5 database through a restore function. And once this is finished, is it necessary to restart it? Or is just good to go?
It's good to go. You should be able to connect to it from an existing Qlik Sense machine, or entering the correct connection details in the Qlik Sense Client Managed installer on another server.
Okay, can we take a look at that? Setting up the Qlik Sense Repository Service to the new database?
Yeah. Let's assume we're on a server where we already have the product installed. We just want to change what database backend we are connecting to.
Okay.
There is a little utility and delivered with the product inside the Utils folder that we can use to change the connection string for the Repository Service to instruct it to talk to a different database backend. Look for the Repository folder, Util, Qlik Sense Util. Go straight away to the connection string editor. Make sure that this path is correct, point it to wherever your repository.exe.config file is residing.
Okay.
And hit Read.
I see everything currently is set to “localhost” on that port.
QSR is the only database that you would be restoring from a different environment. All the other ones are generated on the fly and will depend on the feature set that your license has enabled. But all of these will need to be modified in order to point to the new database host.
And what's the important detail in that string? The host name?
Pretty much, just the host name and the port if that changed.
If that changed, yeah. Does that need to be a fully qualified domain name or IP address or what's the best recommended path?
The best would be a host name or a fully qualified domain name.
Okay.
So, in our case this would be “QlikServer5.domain.local”.
And we need that on each of those lines? Okay, and do we need to save that?
Yep, we can Save the Values. In order to confirm, I like to Select All here, just hit Delete, you hit Read again. Confirm that the host is persisted in the file.
Great.
And that should be that. Once we have confirmation that the backup has been restored. It finished with warnings. That shouldn't be a problem.
Okay.
We’ll again go real quick into the schemas here, and confirm that we not only have tables, but that there is some content.
Okay. So, there is data in that database.
And here you can see when this was first deployed. Essentially the system was first created on the 2nd of February 2021.
Well, that was pretty quick and painless.
Indeed, very fast. Fast to execute, faster deploy, it enables a quicker and safer upgrade, I would say. The process that the installer replicates is essentially the same as the manual instructions that we've had available for customers that were eager to get to a supported version of Postgres on our Community, and maybe we can link to that as well.
Great, now assuming people run into any issues that they're not expecting, things aren't working as they did so smoothly in our demo, what steps could they take to try and troubleshoot or what would you recommend?
All right. So, that is an excellent question. Let's go back to the old machine, and back into the services. The old Database Service is still in place as well as the binaries necessary to run this, as well as the data. The data has been backed up and then restored onto a new database. It has not been moved.
Right.
So, there are two locations now containing the same data. We have two different versions of the database. Say something goes wrong, you don't understand what's happening, simply Stop, set it to Manual or Disable it altogether. Let's go ahead and Enable this, and we can Start it.
Okay. So, then you're just right back where you were before the upgrade!
So, this is the easiest way we could think to enable a rollback scenario. Of course, the next step would be to interact with Support a little bit through a Community post or a Support case. We may even ask you for a copy of that generated backup file. That you can find again right here, or wherever you chose to place it. We would ask you to go into Old, 9.6, and get us this file.
And Support can take that and investigate further?
And we'll be able to recommend some additional maintenance types to be performed on the database before you attempt the upgrade again. Uninstalling should be very quick as well, and if something happens in the middle of the installation, there is a Resume functionality I am told as well. So, you don't have to start over, like you would have to in Qlik Sense installer.
Fantastic. Okay, great. Now it's time for Q&A. Please submit your questions through the Q&A panel on the left side of your On24 console. Mario, we've got some questions coming in; should we just take it from the top?
Excellent. Yeah, let's go.
All right. First question: how to run a backup on a physical machine (non-virtual), and what should be backed up?
The first thing that I would like to say here is: in terms of Qlik Sense assets to backup for a migration or just for backup purposes and rollback, there is no major difference in what you should do between a virtual machine and a physical one. There is a detailed instruction set available on Qlik Help. This would be the section that I would recommend starting with. It gives a good overview of what needs to be done and the different aspects of the backup procedure, and where these are stored. There are corresponding instructions on backing up and restoring as well. And if this doesn't cover it, do let us know. We are always trying to keep this information and instructions as up to date and relevant as possible, but this should have you covered as you can see there's lots of instructions on what needs to be extracted, what needs to be restored; so, this should cover it.
Yeah, I think you've demoed and gone through these steps in another Techspert Talk: Migrating Like a Boss.
Yes.
I remember. All right, okay. Next question: what are important details in Qlik Sense installation and migration for multi-node servers?
I would like to refer that back to the same recording, I think we covered quite a lot. So, namely make sure that you keep your certificates in a safe place, and everything that has to do with the central node itself of course, because that will hold to the configuration for the rest of the environment. You should not have any custom settings locally applied to each of the nodes, but I would say that is one aspect that needs a little extra care. Go through the local configuration files on each machine, and note down any values that deviate from default. As you will have to re-apply these like when migrating. If you're just moving all the files across, that shouldn't be a problem.
Okay, great. Next question: whether Qlik has any plans to include the feature of upgrading bundled Postgres in the installer itself? Currently, it's not included in the installer and the Postgres upgrade process is manual and risky.
But I would say, it's only risky if you don't take the necessary backups as recommended. But it is cumbersome I’ll give you that. So, during luck! This is the tool that will help you run that Postgres DB upgrade in a simpler way without a lot of manual processing. And as we've seen during the session itself, it does take care of the backup process itself, and then restores it onto the new database. So, it should be a lot simpler to use.
Yeah, and so pain-free. If you ever need to roll-back, you just switch to the old version and…
Yeah, that's right. You're back. Yes, you're back up and running in just a few seconds if something does go wrong.
Next question is: how to know details of the installation tree of an existing Qlik Sense site? I’m not quite sure I understand that one.
If it's referring to whatever assets are part of the ecosystem and the environment itself, you can see those in the in the QMC, in the node section, in the services sections, depending on what exactly you're looking for. So, again I’m also not sure what, how to interpret this question. If there is any anything specific that a customer wishes to know about their environment, there are all sorts of config files and ways of probing the system for that particular information, ranging from APIs, to collecting a log package with the log collector, and then looking through the JSON files that are part of the archive; but that provides us from Support a summary of what is present on the system. So, a list of the nodes, their names, their roles, stuff like that.
Great. Next question is: where can we find some guidelines for upgrading Postgres? Because we're looking for some documentation.
All right. So, we have this very nice and detailed article on how to perform a standalone Postgres upgrade using the PostgreSQL installer packages themselves. We will have a follow-up to this that will detail instructions for the new Postgres upgrade tool that Qlik is shipping as of this webinar. Going back to this previous question, this process is cumbersome, a little bit more tedious, and it does require a full reinstallation of the product while using this new tool that we demonstrated today does not . So, we recommend you use that instead.
Yeah, it's much simpler. Okay, next question: if the original password is lost is there any way to reset it?
Excellent question! We have a wonderful Qlik Fix video on this very topic.
Look at that. I’ve done so many videos, I forget the ones that are out there. That has very clear instructions and even demonstrates exactly how it's done. So, that's a nice resource to use.
That's right.
Okay, moving on…
Even though this is from June 2019, these steps are still valid for sure.
Great. If you upgrade Qlik Sense September 19 to February 2022, will that try to upgrade the database as well?
No. The answer to that is: No. The database will still be 9.6. Qlik Sense installer will set up Postgres 12.5 for new installations, not upgrades. Again, for upgrades you will have to use the tool that we demonstrated today.
Great. It's good to be clear about that, in case there's any confusion still. The next question: which order would you recommend if upgrading both Qlik Sense and Postgres. Postgres from 9.6 to 12.5 then Qlik Sense 2019 to Qlik Sense 2022; or Qlik Senses to 2022 then Postgres?
I would say the order itself doesn't much matter. However, I don't believe the Qlik Sense 2019 version supported 12.5 for Postgres. The latest version currently available for Qlik Sense Client Managed (which is the May 2022 release) is still retroactively compatible with Postgres 9.6.
Right.
However, a version of Qlik Sense 2019 might not support 12.5 just yet. I believe we've started supporting this with the May release of 2021.
Okay.
So, in this particular case, coming from a 2019 version, we would recommend upgrading Qlik Sense first, and then upgrading the database.
Okay, next question: how do you change the port the database uses? Can you please outline everywhere that setting needs to be changed?
I think we would have to take this question in two parts, Troy.
Okay.
If the product is already installed, the service definition for the original Qlik Sense Repository Database will have a setting flag that configures the port. In order to change that, you would have to modify the port with SC Edit, which is a command line tool in CMD or PowerShell on Windows servers and client machines that allow you to modify the service definitions, or simply remove the “-p” flag, and move to using the Postgres configuration file, that can be found by default in Program Data. And in this file, one of the first few options is Port. So, we would have to uncomment that, and change the desired port 4432 (is the default). It could be 4433 for example. Save this, restart the Repository Database.
The second aspect is of course what the question itself is asking: how do we modify the product then to talk to the database over the new desired port? I believe this article is a great go-to place “How to Configure Qlik Sense to Use a Dedicated Postgres.” The part about changing the connection string to the QSR and the Qlik Sense Messaging Queue database is really the important step here. So, no matter what process you follow, in order to change which port the database uses to listen to, you will still have to refer back to the connection string editor in order to modify that port.
And that's using the Qlik Sense Util that you demonstrated?
That's correct. Now, because each node holds its own repository configuration, you will have to configure every node in a multi-node environment with the new connection details; whether the host name changed for the database, or the port, the same applies.
Okay, but you mentioned earlier that this was if you're already installed. But wouldn't it be simpler if you're creating a new installation to change the port, right? So, for rim nodes for example, if changing the service definition is deemed risky, or not practical (although it's just a single line), you can simply uninstall the product and reinstall; and as part of the setup that allows you to connect to an existing service cluster (also known as a database backend), you will be able to define which port you wish to use.
Okay.
But it can very easily be modified once the product is installed without needing a reinstall.
Well, great. Moving on to the next question: any help when the Repository Service won't start with an error ‘QSR failed: FATAL’ the database system is starting up?
The first check of course is still valid: see if the Repository Service is running, and you can confirm that the database is actually reachable by trying to connect to it via PgAdmin or any other database connection utility.
Okay.
Just so you confirm that it is up and running and listening over the desired port. In a restart procedure after a maintenance window, if the restart order of the services is not respected, it could be that Repository Service comes up and tries to connect to the Database before the database itself has had a chance to spin up and become available. If that process takes longer than 5 minutes, the Repository Service will give up trying to connect. That is the default timeout, I believe (300 seconds).
Okay.
So, if it doesn't manage to connect after 300 seconds, all you have to do is just restart the Repository Service again, and given that the Repository Database should be up and running. By now it would be able to connect. But there is a built-in retry mechanism. So, if the database backend does become available while the Repository Service is trying to reconnect, it will eventually be able to do so.
Right. Well, that's helpful; it gives some things a check and some places to start troubleshooting. All right. We have time for one last question: will the latest version of Qlik Sense work with the latest version of Postgres?
Excellent question. We officially support Postgres 12.5; however, there are newer versions of Postgres out there. I don't believe they have been officially tested with the latest version that is May 2022, but newer database engine versions from Postgres may be supported in the future; as we have moved from originally 9.2 to 9.6, now to 12.5, purely because of the End of Life of the previous version. I know we have had some customers that used a transition version 11.something from Postgres, but (again) officially 12.5. Newer versions may be supported in the future officially. Check out community if you do try a higher version, and you do see issues. We would love to capture those, and at least give our R&D a heads-up so that we can maybe validate higher versions. We'd love to do that.
Yeah. Well Mario, thank you very much for this. It's exciting that this tool is out there, and I think it will prove helpful and make things a lot easier for people.
Thank you, Troy. And thanks to everyone that showed up today. It's been a blast as always, and looking forward to our next one, and looking forward to seeing you on Qlik Community.
Thank you everyone! We hope you enjoyed this session. Thank you to Mario for presenting. We appreciate getting experts like Mario to share with us. Here's our legal disclaimer. Thank you once again. Have a great rest of your day!