Qlik Sense has a set of four databases.
The Qlik Sense repository database (QSR)
The QSR is the primary database in your Qlik Sense deployment.
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.
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.
And 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.
Additional instructions on how to migrate an existing system can be seen be seen below.
Backing up Qlik Sense
- 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\9.3\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
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)
- Specify the port on which PostgreSQL should run on and use port 4432.
- Specify a local for the database
Restoring the QSR database and creating a dedicated user account
- 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
- 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"
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.3\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 100 x number of nodes in the Qlik Sense site: max_connections = 200 # this example is for a site with 2 nodes
- Save the file
- Start the postgresql service
Configuring Qlik Sense to use the dedicated PostgreSQL node
- On the Qlik Sense node(s), ensure that services are still stopped
- Open up the ConnectionStringEditor tool with administrative rights. By default it is located at C:\Program Files\Qlik\Sense\Repository\Util\ConnectionStringEditor\ConnectionStringEditor.exe
- 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.
- Start up Qlik Sense services on the node where you have changed the configuration file
- Repeat, as needed, on all other Qlik Sense nodes
If you encounter issues at this step, then the issue will be logged in the Repository