Installing and configuring PostgreSQL
To improve performance in a Qlik Sense multi-node deployment, you have the option to install your repository (QSR), SenseServices, QSMQ, QLogs logging, and Licenses databases on a dedicated, remote PostgreSQL server.
The Qlik Sense repository database (QSR)
The QSR is the primary database in your Qlik Sense deployment.
If you want to install the QSR database on a dedicated PostgreSQL server, you must install and configure PostgreSQL before you install Qlik Sense, as you will need to enter the PostgreSQL server/host details in the Qlik Sense installer.
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.
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.
When you install the QLogs database as a standalone logging database, you can configure it either before or after you install Qlik Sense.
- If you install the QLogs database before the Qlik Sense installation, then you will need to create the QLogs database login roles manually.
- If you install the QLogs database after installing Qlik Sense, use the Qlik.Logging.Service.exe setup command. When you run this command, you specify a remote host and the script will automatically create the QLogs database and login roles for you. For more information, see: Qlik Logging Service
The licenses service database (Licenses)
The licenses database contains a local copy of license data to allow faster response times and more robustness. It is only accessed by the licenses service.
To install a dedicated PostgreSQL server with QSR, SenseServices, QSMQ, QLogs, and Licenses database:
- Install PostgreSQL
- Create the PostgreSQL databases, and configure login roles.
- Edit the configuration files to allow access from Qlik Sense nodes.
- Verify that the database has installed and is running correctly.
Before installing a dedicated PostgreSQL server instance, check that your server fulfills the system requirements on www.postgresql.org.
To install PostgreSQL on a dedicated server:
- Log in to the server where you want to install PostgreSQL as an administrator.
See: User accounts
Download PostgreSQL EnterpriseDB version 9.6 from the PostgreSQL website.
- Run the PostgreSQL setup wizard.
- On the Installation Directory and Data Directory screens, accept the default paths.
- On the Password screen, create a password for the PostgreSQL superuser.
You will use this password when you connect to the PostgreSQL database and you will also be prompted for it when you run the Qlik Sense setup.
On the Port screen, specify port 4432. This port is required for communication between all the nodes in a site.
- In the Advanced Options screen, accept the default locale.
In the Ready to Install screen, click Next to run the setup.
- After running the setup, you have the option to install Stack Builder. Clear the check box if you want to install this later.
- Click Finish to complete the installation.
When you install PostgreSQL EnterpriseDB, the pgAdmin tool is included.
Creating a PostgreSQL database
You can create a repository QSR, SenseServices, QSMQ, QLogs (logging), and Licenses database manually with the pgAdmin tool or using a script.
To create a new, empty PostgreSQL database using the pgAdmin tool:
Open the pgAdmin tool.
- In the pgAdmin Browser, under Servers, right-click the PostgreSQL node and then click Connect Server.
- Enter your PostgreSQL superuser password to make a connection. A green status bar appears in the lower right corner of your screen when the server connection is successful.
- Right-click the Databases node, click Create, and then click Database.
- Enter the name of the database you are creating, and then click Save.
To create a new, empty PostgreSQL database by running a script in the pgAdmin tool:
- Open the Query Tool. First select an existing database, such as postgres, to display the Query Tool option in the Tools menu.
- Execute the following script:
CREATE DATABASE "<databasename>" ENCODING = 'UTF8'; --creates an empty database.
Replace <databasename> with QSR for the repository database, SenseServices for the SenseServices database, QSMQ for the message queue database, Licenses for the license service, and QLogs if you are creating a logging database.
Creating login roles
You need to create login roles for users when you create a PostgreSQL database. You can create login roles using the pgAdmin tool or by running a script.
The QSR, SenseServices, QSMQ, and Licenses login role
To create login roles using the pgAdmin tool:
- Right-click the Login/Group Roles node. To create a new database user, click Create, and then click Login/Group Role.
- In the Create - Login/Group Role window, in the General tab, enter the name qliksenserepository.
- In the Privileges tab, enable Can login? and leave the other default privileges unchanged.
- In the Definition tab, enter a password of your choice, and click Save.
When you run the Qlik Sense setup, in the Shared persistence database connections settings screen, you are asked to enter the Database user password that you created here so that Qlik Sense can connect to the repository database.
- Make qliksenserepository the owner of the QSR, SenseServices, and QSMQ databases. To do this, right-click the QSR, SenseServices, QSMQ, and Licenses databases you created earlier, and then click Properties.
- In the General tab, in the Owner drop-down, select qliksenserepository as Owner of the QSR, SenseServices, QSMQ, and Licenses databases and click Save.
To create login roles by running a script in the pgAdmin tool:
Open the Query Tool. Select an existing database, to display the Query Tool option in the Tools menu.
Run the following script:
CREATE ROLE "qliksenserepository" WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; -- change <qliksenserepository_user_pass> to your password for the repository service user ALTER ROLE "qliksenserepository" WITH ENCRYPTED PASSWORD '<qliksenserepository_user_pass>'; GRANT qliksenserepository TO postgres; ALTER DATABASE "QSR" OWNER TO "qliksenserepository"; ALTER DATABASE "SenseServices" OWNER TO "qliksenserepository"; ALTER DATABASE "QSMQ" OWNER TO "qliksenserepository"; ALTER DATABASE "Licenses" OWNER TO qliksenserepository; GRANT TEMPORARY, CONNECT ON DATABASE "QSMQ" TO PUBLIC; GRANT ALL ON DATABASE "QSMQ" TO postgres; GRANT CREATE ON DATABASE "QSMQ" TO "qliksenserepository"; GRANT TEMPORARY, CONNECT ON DATABASE "SenseServices" TO PUBLIC; GRANT ALL ON DATABASE "SenseServices" TO postgres; GRANT CREATE ON DATABASE "SenseServices" TO "qliksenserepository"; GRANT TEMPORARY, CONNECT ON DATABASE "Licenses" TO PUBLIC; GRANT ALL ON DATABASE "Licenses" TO postgres; GRANT CREATE ON DATABASE "Licenses" TO qliksenserepository;
The QLogs login role
To create login roles for the QLogs database by running a script in the pgAdmin tool:
- Open the Query Tool. Select an existing database, to display the Query Tool option in the Tools menu.
- Run the following script:
CREATE ROLE qlogs_users WITH NOLOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; CREATE ROLE qlogs_reader WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; CREATE ROLE qlogs_writer WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; --creates users and assigns privileges ALTER ROLE qlogs_reader WITH ENCRYPTED PASSWORD '<qlogs_reader_password>'; --assigns password to qlogs_reader ALTER ROLE qlogs_writer WITH ENCRYPTED PASSWORD '<qlogs_writer_password>'; --assigns password to qlogs_writer GRANT qlogs_users TO qlogs_reader; GRANT qlogs_users TO qlogs_writer; --adds qlogs_reader and qlogs_writer to qlogs_users group ALTER DATABASE "QLogs" OWNER TO qlogs_writer; --sets qlogs_writer as an owner of QLogs database
To allow communication between your PostgreSQL repository database and your Qlik Sense nodes, edit the pga_hba.conf and postgresql.conf configuration files.
The postgresql.conf file enables you to specify general parameters for your PostgreSQL server, such as for auditing, authentication, and encryption. Edit this file to control which Qlik Sense nodes can access your PostgreSQL database server.
To edit the postgresql.conf file:
- Navigate to the postgresql.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
- Open the file in a text editor as an administrator.
- Make the following configuration changes:
Setting Value Description listen_addresses *
Enter the IP address(es) to listen on. If entering multiple listen addresses, use a comma separated list.
Enter * to listen for connections from all IP addresses.
Defines the maximum number of client connections allowed.
To calculate this value, multiply by 100 the number of nodes in your deployment.
- Save your changes.
For more detailed information about setting these parameters, see the PostgreSQL documentation.
The pg_hba.conf file handles client authentication. Each record specifies a connection type, such as a client IP address range, database name, user name, and the authentication method used.
To edit the pg_hba.conf file:
- Navigate to the pg_hba.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
- Open the file in a text editor as an administrator.
Locate the following line:host all all 127.0.0.1/32 md5
This line determines which servers can access the repository database server. The default address setting, 127.0.0.1/32, only allows local host to access the database.
Replace 127.0.0.1/32 with a sub net specification that covers all the IP addresses of the nodes in your site.
When specifying these settings, add one row for each node, using /32 as a suffix for each address, or add a sub net that covers all addresses using, for example, /24 as a suffix:
- IPv4 (32-bit addresses):
- To specify a single address: 192.168.1.0/24, or 172.20.143.89/32
For a small network: 172.20.143.0/24, or 10.6.0.0/16 for a larger one.
To allow access from all IPv4 addresses: 0.0.0.0/0
- IPv6 (128-bit numeric addresses):
- For a single host: ::1/128 (in this case the IPv6 loopback address)
- For a small network: fe80::7a31:c1ff:0000:0000/96
- To allow access from all IPv6 addresses: ::/0
- IPv4 (32-bit addresses):
- Save your changes.
For more information on how to set a more restrictive IP address, see the PostgreSQL documentation.
You have installed and configured a PostgreSQL database on a separate server. You are now ready to resume your installation of Qlik Sense.