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.

Databases

You can improve performance in a Qlik Sense multi-node deployment by installing your repository (QSR), SenseServices, QSMQ, 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 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.

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: 12

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

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.

   Information noteWhen 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;

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 Enterprise on Windows on a single node
2. The following values must be used on 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.