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 , and Licenses databases on a dedicated, remote PostgreSQL server.

Databases

The QSR, SenseServices, QSMQ, and Licenses databases share the same login role and must be installed on the same PostgreSQL instance. If you already have a PostgreSQL database installed as part of a previous deployment, you can continue to use it.

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 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, 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.

Installing PostgreSQL

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:

1. Log in to the server where you want to install PostgreSQL as an administrator.

   See: User accounts

2. Download PostgreSQL EnterpriseDB version 12.x from the PostgreSQL website.
   To know which versions of PostgreSQL are supported in Qlik Sense, see: System requirements for Qlik Sense Enterprise

3. Run the PostgreSQL setup wizard.
4. On the Installation Directory and Data Directory screens, accept the default paths.
5. 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.

6. On the Port screen, specify port 4432. This port is required for communication between all the nodes in a site.

7. In the Advanced Options screen, accept the default locale.

8. In the Ready to Install screen, click Next to run the setup.

9. After running the setup, you have the option to install Stack Builder. Clear the check box if you want to install this later.
10. 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, and Licenses database manually with the pgAdmin tool or using a script.

To create a new, empty PostgreSQL database using the pgAdmin tool:

1. Open the pgAdmin tool.

2. In the pgAdmin Browser, under Servers, right-click the PostgreSQL node and then click Connect Server.
3. 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.
   
4. Right-click the Databases node, click Create, and then click Database.
5. 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:

1. Open the Query Tool. First select an existing database, such as postgres, to display the Query Tool option in the Tools menu.
2. 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.
   

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:

1. Right-click the Login/Group Roles node. To create a new database user, click Create, and then click Login/Group Role.
2. In the Create - Login/Group Role window, in the General tab, enter the name qliksenserepository.
3. In the Privileges tab, enable Can login? and leave the other default privileges unchanged.
4. 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.
5. Make qliksenserepository the owner of the QSR, SenseServices, QSMQ, and Licenses databases. To do this, right-click the QSR, SenseServices, QSMQ, and Licenses databases you created earlier, and then click Properties.
6. 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;

CREATE DATABASE "QSR" ENCODING = 'UTF8' TEMPLATE=template0;
CREATE DATABASE "SenseServices" ENCODING = 'UTF8' TEMPLATE=template0;
CREATE DATABASE "QSMQ" ENCODING = 'UTF8' TEMPLATE=template0;
CREATE DATABASE "Licenses" ENCODING = 'UTF8' template=template0;
CREATE ROLE "qliksenserepository" WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
--Replace `your_password` with the desired password for the repository user
ALTER ROLE "qliksenserepository" WITH ENCRYPTED PASSWORD 'your_password';
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";

GRANT TEMPORARY, CONNECT ON DATABASE "QSR" TO PUBLIC;
GRANT ALL ON DATABASE "QSR" TO postgres;
GRANT CREATE ON DATABASE "QSR" TO "qliksenserepository";

--Connect to each database and grant full permissions on the public schema to qliksenserepository
\c QSR
GRANT ALL ON SCHEMA public TO qliksenserepository;
\c Licenses
GRANT ALL ON SCHEMA public TO qliksenserepository;
\c QSMQ
GRANT ALL ON SCHEMA public TO qliksenserepository;
\c SenseServices
GRANT ALL ON SCHEMA public TO qliksenserepository;

Configuring PostgreSQL

To allow communication between your PostgreSQL repository database and your Qlik Sense nodes, edit the pga_hba.conf and postgresql.conf configuration files.

postgresql.conf

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:

1. Navigate to the postgresql.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
2. Open the file in a text editor as an administrator.
3. Make the following configuration changes:

   PostgreSQL configuration changes

   Setting	Description	Example Value
   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.	*
   max_connections	Specifies the maximum number of concurrent connections to the database. The default value for a single server is 100. In a multi-node environment, this should be adjusted to the sum of all repository connection pools + 20. By default, this value is 110 per node.

4. Save your changes.

For more detailed information about setting these parameters, see the PostgreSQL documentation.

pg_hba.conf

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:

1. Navigate to the pg_hba.conf file in C:\Program Files\PostgreSQL\<version>\data of your PostgreSQL installation.
2. Open the file in a text editor as an administrator.

3. 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.

4. 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
Warning noteWhen you add the IPv6 connections and use hostname in the address column, both the forward and reverse nslookup of the client machine must return valid values for PostgreSQL to accept the connection from the client. For more information refer to the PostgreSQL documentation.
5. 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.