Installing and configuring PostgreSQL on Azure

This topic describes how to install and configure PostgreSQL on Microsoft Azure. For more general instructions regarding PostgreSQL installation and configuration, see 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.

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

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.

Note: The QSR, SenseServices, QSMQ, and Licenses databases share the same login role and must be installed on the same PostgreSQL instance.
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.

Setting up a PostgreSQL database in Azure

Before you install Qlik Sense, you need to set up a database in Azure.

Do the following:

  1. Go to the Azure portal: https://portal.azure.com.

  2. Search for Azure Database for PostgreSQL.

  3. For the PostgreSQL server input fields, enter your values. The following three values must be filled in:

    Server name: <your unique instance name, example: qliksensedemo>

    Server admin login name: postgres

    Version: 9.6

  4. Under Connection security, click Add client IP to whitelist the connection.

  5. Disable SSL encryption.

Connecting to the database using pgadmin 4.x

Do the following:

  1. If not already installed, download and install the pgAdmin tool from the following site: https://www.pgadmin.org/download/pgadmin-4-windows/.

  2. Create a connection to the instance you checked out, in this case: qliksensedemo.postgres.database.azure.com.

  3. Enter user: postgres@qliksensedemo

  4. Enter the password that you used when setting up the database.

    Note: When installing the Sense database, you need to specify the user as qliksenserepository@dbinstance, while remaining as qliksenserepository in the Azure PostgreSQL instance.
  5. Once connected to the Azure instance, open up a database and open the query tool.

  6. In the Query Editor, add the following lines of code:

    -- one by one, for creating the DB CREATE DATABASE "QSR" ENCODING = 'UTF8'; CREATE DATABASE "SenseServices" ENCODING = 'UTF8'; CREATE DATABASE "QSMQ" ENCODING = 'UTF8'; CREATE DATABASE "Licenses" ENCODING = 'UTF8'; //one at a time -- from here the whole 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;
Note: The new script does not include Qlogs, because Qlik Sense logging service does not support PostgreSQL in cloud deployments.

Installing Qlik Sense

Now that you have set up the PostgreSQL database on Azure, you can install Qlik Sense.

Do the following:

  1. Follow the installation instructions in Installing Qlik Sense on a single nodeThe following values must be used on the appropriate pages:

  2. On the page Shared persistence database connection settings:

    Database host name: qliksensedemo.postgres.database.azure.com

    Database port: 5432

    Database user: qliksenserepository@qliksensedemo

When you have installed Qlik Sense your setup is complete.

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

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