Qlik Sense Enterprise server has a set of four databases:
1. The Qlik Sense repository database (QSR):
The QSR is the primary database in your Qlik Sense deployment.
2. The Qlik Sense services database (SenseServices):
The SenseServices database contains schemas for each of the Qlik Sense services and allows growth independently of the Qlik Sense Repository Database, while still sharing the same PostgreSQL instance and login role.
3. The Qlik Sense message queue database (QSMQ):
The QSMQ database provides a light-weight method of passing messages internally between services in Qlik Sense Enterprise. The NOTIFY and LISTEN functionality in PostgreSQL allows services to be notified about new messages that have been written to the messaging table.
4. The Qlik Sense logging database (QLogs):
The QLogs database centralizes logging by collecting log messages from all Qlik Sense nodes in your deployment and stores them in a PostgreSQL database.
All of the above can be installed on a separate, stand alone PostgreSQL instance. See Installing and configuring PostgreSQL
- Qlik Sense Enterprise, all versions
Note: Additional illustrated instructions on how to migrate an existing system are found below.
Please note that illustration and some steps may not match all versions of PostgreSQL, PGadmin, and/or Qlik Sense. Please cross-reference existing steps mentioned under the documentation for the appropriate version under Installing and configuring PostgreSQL
I. Backing up Qlik Sense database:
- Stop all Qlik Sense services on all nodes except the Qlik Sense Repository Database on the Central node
- Launch command prompt as Administrator
- Change the directory to the Program Files path for the PostgreSQL database
- By default this location is C:\Program Files\Qlik\Sense\Repository\PostgreSQL\<postgreSQLVersion>\bin
- Perform a backup of the Qlik Sense database (relevant help link). Sample command: pg_dump.exe -h localhost -p 4432 -U postgres -b -F t -f "c:\QSR\QSR_backup.tar" QSR
- Note that in this sample, I am writing to a directory named QSR on the C drive. This directory may not exist on your file system and will need to be created ahead of time
II. Installing / Configuring PostgreSQL
- On the dedicated server for PostgreSQL, launch the installation file with administrative rights
- Specify the Installation Directory
- Specify the Data Directory
- Specify the password for the superuser account (for the postgres account)
III. Restoring the QSR database and creating a dedicated user account
- Specify the port on which PostgreSQL should run on and use port 4432.
- Specify a local for the database
- Open pgAdminIII (Start > pgadmin)
- If there isn’t already a connection to the database in the Servers section of pgAdminIII then create a new one.
- Click on the plug icon to create a new server registration
- Enter in the server details:
- Name: This is Arbitrary
- Host: localhost
- Port: 4432
- Username: postgres
- Password: the password supplied earlier when installing PostgreSQL
- Store password: Uncheck Store password unless you are comfortable with the password being stored in plain text on the filesystem
- Connect to the newly established server connection by double clicking on it
- Right click on Databases and select New Database
- Right click on Login Roles and select New Login Role
- Enter qliksenserepository for the Role name, which will be the username of the new account.
- On the Definition tab, enter the password in twice
- Repeat the previous steps with the accounts qlogs_reader and qlogs_writer
- Open command prompt and navigate to the installation path for PostgreSQL. By default it is C:\Program Files\PostgreSQL\9.3\bin
- Move the .TAR file backup from the Central node to the PostgreSQL node and restore the database to the newly created QSR database. Sample command: pg_restore.exe -h localhost -p 5432 -U postgres -d QSR "c:\QSR_backup.tar"
IV. Configuring PostgreSQL to accept connections from the Qlik Sense Servers
- Stop the postgresql service
- Open the pg_hba.conf file which is located by default at C:\Program Files\PostgreSQL\9.6\data\pg_hba.conf
- Locate the following line: host all all 127.0.0.1/32 md5
- This line determines which servers can access the server that the repository database runs on. The default setting, 127.0.0.1/32, only allows localhost to access the database.
- Add a subnet specification that covers the IP addresses of all nodes in your site. Either add one row for each node, using /32 as suffix for each address, or add a subnet that covers all addresses using, for example, /24 as suffix. To allow all servers to access the repository database, use 0.0.0.0/0. In the below example, we are using 0.0.0.0/0. It is ideal to add to this row rather than replacing entries.
- Save the modified pg_hba.conf file
- Open the postgres.conf file which is located by default at C:\Program Files\PostgreSQL\9.3\data\postgresql.conf
- Set the maximum number of database connections to 110 x number of nodes in the Qlik Sense site: max_connections = 220 # this example is for a site with 2 nodes
- Save the file
- Start the postgresql service
V. Configuring Qlik Sense to use the dedicated PostgreSQL node (Simple)
The simplest approach, one that is guaranteed to rewrite all connections strings required is:
- Uninstall Qlik Sense on every node, selecting to keep the certificates and data intact
- Reinstall Qlik Sense on every node, and when prompted, provide the database connection details for the standalone node.
Configuring Qlik Sense to use the dedicated PostgreSQL node (Advanced)
This approach is much more complex, and requires manually rewriting the connection strings for every single service that connects to the database
On each Qlik sense Node:On the Qlik Sense node(s),
- Stop all Qlik Sense Services
- Open up the QlikSenseUtil tool with administrative rights. By default it is located at C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil
- Read the Repository's configuration file. By default it is located at C:\Program Files\Qlik\Sense\Repository\Repository.exe
- It is a good idea to save a copy of the Repository's configuration file before editing it.
- By default it is located at C:\Program Files\Qlik\Sense\Repository\Repository.exe.config
- Either inside of the tool, or in a separate editor, alter the connection string to connect to the dedicated node.
- An example original connection string looks like this:
<add name="QSR" connectionString="User ID=qliksenserepository;Host='localhost';Port='4432';Database=QSR;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='L?44=*';" providerName="Devart.Data.PostgreSql" />
- The key elements to change here are the Host section, the Port section (if you used the default PostgreSQL port), and the password section.
- An example altered connection string looks like this, with the changed sections bolded:
<add name="QSR" connectionString="User ID=qliksenserepository;Host='qlikserver3.domain.local';Port='5432';Database=QSR;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='password';" providerName="Devart.Data.PostgreSql" />
- Once you've made the needed edits, save the file with the changes.
- There are several service dispatcher managed services that contact the Qlik Sense Database that need to be reconfigured, start by searching for Configure-Service.ps1 in the Qlik Sense install folder (Usually C:\Program Files\Qlik\Sense\)
- Run each of these Powershell scripts, providing the new database hostname, port, username and password when prompted
- Finally, if you are using the centralized logging service, that too needs to be reconfigured to use the new database host, navigate to the install folder once more, and then to the Logging subfolder
- Open a command prompt there with administrator privileges
- Run qlik.logging.service.exe update -h hostname -p port -r reader_pswd -w writer_pswd where the reader_pswd and writer_pswd are the passwords for the qlogs_reader and qlogs_writer accounts respectively.
- Start up Qlik Sense services on the node where you have changed the configuration file
If you encounter issues at this step, then the issue will be logged in the Repository
If after the DB migration you are still experiencing problems, please verify if the following article applies:Postgres database moved to new host but some Qlik Sense is still accessing the old database