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 (QLogs) logging databases on a dedicated, remote PostgreSQL server.

Note: In Qlik Sense Enterprise, configuring all the components of a Multi-Cloud deployment is optional. However, all deployments, whether Multi-Cloud or on-premise require the installation of the SenseServices and QSMQ databases.

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.

Note: The QSR, SenseServices and QSMQ databases share the same login role and must be installed on the same PostgreSQL instance.

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
Note: If you already have a PostgreSQL database installed as part of a previous deployment, then you can continue to use it.
Warning: If Qlik Sense uses a PostgreSQL database on a dedicated infrastructure, then it can use PostgreSQL version 9.6. You can run the instance of PostgreSQL on platforms including Windows, Linux or cloud hosted services, such as Amazon RDS. However, Qlik will only offer configuration support when PostgreSQL is running on Windows. If you use Linux or Amazon RDS, it is your own responsibility to install and configure a running instance of PostgreSQL for Qlik Sense to use.

To install a dedicated PostgreSQL server with QSR, SenseServices, QSMQ and QLogs 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 9.6 from the PostgreSQL website.

  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 QLogs (logging) 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, 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, and QSMQ 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, and QSMQ databases. To do this, right-click the QSR, SenseServices, or QSMQ 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, or QSMQ databases and click Save.

To create login roles by running a script in the pgAdmin tool:

  1. Open the Query Tool. Select an existing database, to display the Query Tool option in the Tools menu.
  2. Run the following script:
  3. CREATE ROLE qliksenserepository WITH LOGIN NOINHERIT NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; --creates 'qliksenserepository' user and assigns privileges
    ALTER ROLE qliksenserepository WITH ENCRYPTED PASSWORD '<qliksenserepository_password>'; --assigns password to qliksenserepository
    ALTER DATABASE "QSR" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the QSR database
    ALTER DATABASE "SenseServices" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the SenseServices database
    ALTER DATABASE "QSMQ" OWNER TO qliksenserepository; --sets qliksenserepository as owner of the QSMQ database
    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;

Note: Include a password for qliksenserepository as you will be prompted for this when you install Qlik Sense.

The QLogs login role

To create login roles for the QLogs database by running a script in the pgAdmin tool:

  1. Open the Query Tool. Select an existing database, to display the Query Tool option in the Tools menu.
  2. Run the following script:
  3. 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

Note: Include a password for qlogs_reader and qlogs_writer as you will be prompted for these when you install Qlik Sense.

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.

Note: Make a backup copy of the postgresql.conf and pg_hba.conf files before you start, so that you have the option to revert back to the original settings.

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:
    SettingValueDescription
    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_connections600

    Defines the maximum number of client connections allowed.

    To calculate this value, multiply by 100 the number of nodes in your deployment.

  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/0
  5. Warning: When 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.
  6. 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.

Did this information help you?

Can you tell us why it did not help you and how we can improve it?