In shared persistence deployments the network traffic between the servers, the database and the file share are not encrypted by default after an installation. You may also need to consider setting up replication of the database to handle cases where the central database fails.
Maintaining database password integrity
Here are some guidelines to maintain password integrity in a Qlik Sense shared persistence deployment.
- It is important that you disable the Store password option for your user in PostgreSQL. If this option is enabled, the password is stored in a file, and incoming connections without a password will be able to connect to the database.
-
Change password by executing this query in the PostgreSQL database:
ALTER USER <user> WITH PASSWORD '<newpassword>';
ALTER ROLE is displayed after successfully changing the password.
Do not change password in the PostgreSQL user interface for the same reasons as above.
-
Use MD5 (default) password encryption.
- Do not set your password to PASSWORD '', that is, an empty string, since this is not handled well in PostgreSQL.
Changing from MD5 to SCRAM encryption
The default password encryption for the PostgreSQL database is MD5. You can change this to SCRAM after installation from the pgAdmin desktop app. The encryption method is always appended to the database user password and PostgreSQL superuser password, for example, md5password or SCRAM-SHA-256password.
Run the following commands as superuser in pgAdmin desktop:
-
Check that encryption is currently set to MD5.
show password_encryption; -
Verify that the database user password is encrypted with MD5.
select passwd from pg_shadow where usename = 'qliksenserepository'; -
Verify that the PostgreSQL superuser password is encrypted with MD5.
select passwd from pg_shadow where usename = 'postgres'; -
Change the encryption to SCRAM.
alter system set password_encryption = 'scram-sha-256'; -
Reload the config file to show encryption change.
select pg_reload_conf(); -
Verify password encryption has changed to SCRAM.
show password_encryption; -
Reset the database user password so it's saved using SCRAM encryption.
alter user qliksenserepository with password '*******'; -
Reset the PostgreSQL superuser password so it's saved using SCRAM encryption.
alter user postgres with password '*******'; -
Verify the encryption method for the user.
select passwd from pg_shadow where usename = 'qliksenserepository'; -
Verify the encryption method for the superuser.
select passwd from pg_shadow where usename = 'postgres';
Manually update the pg_hba.conf file.
-
Go to %ProgramData%\Qlik\Sense\Repository\PostgreSQL\12.5.
-
Open pg_hba.conf.
-
Change md5 to scram-sha-256 and save the file.
-
Restart the Qlik Sense Repository Database service.
Database traffic encryption
Qlik Sense supports database traffic encryption using SSL, but you need to perform some manual configuration to setup SSL and password protection, for example, MD5 or SCRAM-SHA-256, in a shared persistence deployment.
Do the following:
-
Edit the following values in postgresql.conf:
listen_addresses = '*' port = 4432 ssl = on ssl_cert_file = 'server.pem' ssl_key_file = 'server_key.pem' #ssl_ca_file = '' #ssl_crl_file = '' -
Add one of the following lines in pg_hba.conf depending on the authentication method used.
hostssl all all all md5hostssl all all all scram-sha-256 - Remove any other lines starting with hostssl or host in pg_hba.conf.
- Copy server.pem, and server_key.pem from %PROGRAMDATA%\Qlik\Sense\Repository\Exported Certificates\.Local Certificates to %PROGRAMDATA%\Qlik\Sense\Repository\PostgreSQL\12.x.
- Use the Connection String Editor to add the following setting to the repository.exe.config on the central node, and all rim nodes that belong to the cluster. To open the Connection String Editor, navigate to C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil and open the QlikSenseUtil.exe file as an administrator.
- In the Connection String Editor tab, click Read to open the Repository.exe file connection string.
-
Add ‘Ssl Mode=Require;’ to the connection string:
<add name="QSR" connectionString="User ID=qliksenserepository;Ssl Mode=Require;Host='fullhostname.com';Port='4432';Database=QSR;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='randompass';" providerName="Devart.Data.PostgreSql" /><add name="QSMQ" connectionString="User ID=qliksenserepository;Ssl Mode=Require;Host='fullhostname.com';Port='4432';Database=QSMQ;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;Password='randompass';" providerName="Devart.Data.PostgreSql" /> - Click Save value in config file encrypted to save your changes.
- Start all Qlik Sense services and verify that everything works.
-
Verify the authentication using the pgAdmin tool in PostgreSQL:
Users postgres and qliksenserepository must enter a valid password to connect.
Forcing the database connection to use TLS 1.2 only
You can configure the database connection to support TLS 1.2 only, and block connections using TLS 1.1 or lower.
Do the following:
- Add the following parameter to the connection string: "SSL TLS Protocol=1.2"
We recommend these additional configuration changes to maintain database integrity:
- Configure the database to only accept connections from servers where the repository is running.
-
Configure SSL to reject weak cipher suites by adding this line to the file postgresql.conf:
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!eNULL:!aNULL:!MD5:!RC2:!RC4:!DES:@STRENGTH'
Encrypting database connection for services controlled by the Qlik Sense Service Dispatcher
The following code snippets can be used to enable database encryption for the following services controlled by the Qlik Sense Service Dispatcher.
Licenses service
In C:\Program Files\Qlik\Sense\Licenses\appsettings.json:
{
"licenses": {
"host": "localhost",
"port": 4432,
"dbName": "Licenses",
"user": "qliksenserepository",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAABuvYPntQ2k+cR8K7frd+MQQAAAACAAAAAAAQZgAAAAEAACAAAAD8/TGvNzoDOPCleEynZCIfw+q/cpFaHRLcsRuR2cXjSgAAAAAOgAAAAAIAACAAAABSZavuu/lRWW2s92wdDbOeUW2sHSZP8sXI0PfPyAT7ZSAAAAD4GqZdVQacn/SzaN03617zNLfzg1owMethVPGOp2bv2UAAAADsFbcNkIOY4CEBJ/jh2djgfVEWu0L2Q8nipfWxyMg3NO5xLEGxUTpZ0riJ+J9LRX9WyW84tkAToP4pexntagZ+",
"sslMode": "require"
},
"messageQueue": {
"host": "localhost",
"port": 4432,
"dbName": "QSMQ",
"user": "qliksenserepository",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAABuvYPntQ2k+cR8K7frd+MQQAAAACAAAAAAAQZgAAAAEAACAAAAA78d6YdDM+L1OGg0C/d1irzf3Ml4/cskYQxB4A/DvyfwAAAAAOgAAAAAIAACAAAACTpVvY32teeFMJbZNsSSC/4xqaOF5j5BT7TlCA/RWkgiAAAADaOOtbEjL6DpP1sPh8optOF+diHuM2gpxFzmmfDtubF0AAAAD9ujXzsYyW53yVVUQUMtJNfoZnz6y40wdU0LcSoMACuCSt4W5vryetKdRAQF7jn1P1b5RNt4+xONi17d4bPJsl",
"sslMode": "require"
}
}
App distribution service
In C:\Program Files\Qlik\Sense\AppDistributionService\appsettings.json:
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAABuvYPntQ2k+cR8K7frd+MQQAAAACAAAAAAAQZgAAAAEAACAAAACEws1dK+PEB5TNRkrMpmMguUuMYKQx/StRpcT08T4mSgAAAAAOgAAAAAIAACAAAAD9CE26tQn2no6qttNjzyqeBZQkgIYl49lw98Fvy6TyriAAAAA2LiBpizUuEgfSlXKZHgrD4bdy12ErkG3zD3afabBmBkAAAAAZGqqheCccUlCnhEMiMjCbIEcyPfLQKmtJ5cXHNHSN2S9kTdAJjnZi5N9DiQi+0PhxgHFFPapwsqvSvJbDrgXs",
"ConnectionRetryPolicy": {
"MaxRetries": 10,
"RetryTimeMs": 100
},
"Security": {
"Enable": true,
"ServerCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\server.pem",
"PrivateKeyPath": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\server_key.pem"
},
"RootCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
},Hybrid deployment service
In C:\Program Files\Qlik\Sense\HybridDeploymentService\appsettings.json:
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAABuvYPntQ2k+cR8K7frd+MQQAAAACAAAAAAAQZgAAAAEAACAAAADKcv4roLbsaB0VW9XBLAyHp+d/+C7m31sSQg0vhBIKdAAAAAAOgAAAAAIAACAAAADce1TO9aFSv0NgUHYt5fjvKd/W+vTEnsfXT4uXAcKpUiAAAAAHZVoGx2tMg/zUVqykZVtAVngR2BtNcrklz0zG2z90QUAAAACQUSC0gv71htU90HA51n1VVXSTUBlGfVTo0nc/zqoIujyAcMi8svRQHJLZlaE9OhQM+SnKUTlYvs7JkQ4FquSg",
"Security": {
"Enable": true,
"ServerCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\server.pem",
"PrivateKeyPath": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\server_key.pem"
},
"RootCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
},
Notifier service
In C:\Program Files\Qlik\Sense\NotifierService\appsettings.json:
{
"qsmq": {
"host": "localhost",
"port": 4432,
"database": "QSMQ",
"user": "qliksenserepository",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAr/UQ7Qw2UkKeUZc0tKzpuAQAAAACAAAAAAAQZgAAAAEAACAAAACH6Y8cTrKGnDeaCwnDdIG5GVZyVs8FWozTBMJdysKTzQAAAAAOgAAAAAIAACAAAADTJstqSpIU9o6n3xzLXRqJFHgx3chZqxnssHJvV7bkdRAAAABP7QcqZrgEe9F4K5AoAGBZQAAAAP+8Sewi+NlB6TOBS+pSlxMKyTKJD1vqa8TzcOdep54sBJfiEjLu2qlq0YKN4DnI/KCMMLMVHdaMm1qzk9wlolM=",
"ssl": "true"
},
"senseServices": {
"host": "localhost",
"port": 4432,
"database": "SenseServices",
"user": "qliksenserepository",
"password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAr/UQ7Qw2UkKeUZc0tKzpuAQAAAACAAAAAAAQZgAAAAEAACAAAABHh7YWG9F996GbE1Jbry6B7Jiytn8432DsQ0VmgIxKGQAAAAAOgAAAAAIAACAAAACYxIKEvBO7aXFgGINUuWLD76jskNNK6DbiBaBvnRUkGBAAAABqzh9FMFbJDxWd532nEukBQAAAAHjGKDYs+/BNlFhMqBd77GOtXN/i5LAc96mWZahRZ4hE/Ve7aa2Uqx2/SwdwMUIr6g8xhu9CJ56QwRkukj7pRXc=",
"ssl": "true"
},Mobility registrar service
In C:\Program Files\Qlik\Sense\MobilityRegistrarService\appsettings.json:
{
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Schema": "qlik_mobility_registrar_service",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAr/UQ7Qw2UkKeUZc0tKzpuAQAAAACAAAAAAAQZgAAAAEAACAAAAA6L9dGr9oeIaqpdxz9W4BP2QmUHtxaFzGfzx051sUrnQAAAAAOgAAAAAIAACAAAAAoXU1esPxGwBi+Xs4eH3qB3WXUDPm4QbWbiAWBnlfW9hAAAADamUq8qBtA6qhQUzmcPl2MQAAAAHYky7wdQgBw20cXPN6wK0Oxnp+Iizw+MeMhqDQPH0iUnnkcLQo40jCFlijHwXeDcxVEGirCje1xCvBv/Itf94k=",
"SSL": 0
}
}NL broker service
In C:\Program Files\Qlik\Sense\NLBroker\appsettings.json:
You must use the common name (CN) for the host value. To get the CN, run the following commands in an elevated PowerShell.
$CertPath = [Environment]::GetFolderPath('CommonApplicationData') + "\Qlik\Sense\Repository\PostgreSQL\12.x\server.pem"
$Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $CertPath
$Cert | Select-Object -Property SubjectTake the output from the commands above and replace "localhost" in the snippet below.
{
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Schema": "nl_broker",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sAAAAnq+F3zxlakeZ96CexCiJJwQAAAACAAAAAAAQZgAAAAEAACAAAAAnIrak2CdHUivvPchAMP8d0053ZGmaG3WFnuNnqSxaFAAAAAAOgAAAAAIAACAAAACQSam6rRrQrLsgSq+IKUeEZBzpPYDNhKC+ss2uAA4I8CAAAAAIj29QiQoCXEKFzHPxzOhas2MOtC/tYm+QJbUtSC7SY0AAAACdXaXWr688zj1DciHYx/h79vXX6ll+G0U5AigLWrSwsCNtJTh1clp0gGr2YSyS54ESdkqqD+fi+vEKHoOO+2wH",
"Security": {
"comment": "See sslmode descriptions at https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-string#tcp-connections and https://www.postgresql.org/docs/12/libpq-ssl.html#LIBPQ-SSL-PROTECTION",
"Enable": true,
"Mode": "require",
"Certificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"Key": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem",
"RootCertificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
}NL app search service
In C:\Program Files\Qlik\Sense\NLAppSearch\appsettings.json
You must use the common name (CN) for the host value. To get the CN, run the following commands in an elevated PowerShell.
$CertPath = [Environment]::GetFolderPath('CommonApplicationData') + "\Qlik\Sense\Repository\PostgreSQL\12.x\server.pem"
$Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $CertPath
$Cert | Select-Object -Property SubjectTake the output from the commands above and replace "localhost" in the snippet below.
{
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "QSMQ",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAnq+F3zxlakeZ96CexCiJJwQAAAACAAAAAAAQZgAAAAEAACAAAAC7N047wjSCfF7zII62y1DMRh6ijHaj57BNW+asBRisqgAAAAAOgAAAAAIAACAAAAC/FuJ1IavkKFodWnIoeRLO8RpiysHPop4Dyqz8PFyaoSAAAACxdHYmcrnNTYM73q0FccSeQVWW3dZ1y/gz3Q4PRGOEAAAABK5mNZcMFf68nReI7oFhLJXw7oZl3u2PoD8O4hMqNJHicsOvzufdDDzM8YNCcrq/YVYakhiOceReEbiehxm0Jh",
"Username": "qliksenserepository",
"Security": {
"comment": "See sslmode descriptions at https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-string#tcp-connections and https://www.postgresql.org/docs/12/libpq-ssl.html#LIBPQ-SSL-PROTECTION",
"Enable": true,
"Mode": "require",
"Certificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"Key": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem",
"RootCertificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
},
"QRS": {
"Url": "https://localhost:4242/qrs",
"ClientCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"PrivateKeyPath": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem"
},
"RootCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
}DataPrep service
In C:\Program Files\Qlik\Sense\DataPrepService\appsettings.json
You must use the common name (CN) for the host value. To get the CN, run the following commands in an elevated PowerShell.
$CertPath = [Environment]::GetFolderPath('CommonApplicationData') + "\Qlik\Sense\Repository\PostgreSQL\12.x\server.pem"
$Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $CertPath
$Cert | Select-Object -Property SubjectTake the output from the commands above and replace "localhost" in the snippet below.
{
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Schema": "dataprep_service",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sAAAAnq+F3zxlakeZ96CexCiJJwQAAAACAAAAAAAQZgAAAAEAACAAAAAnIrak2CdHUivvPchAMP8d0053ZGmaG3WFnuNnqSxaFAAAAAAOgAAAAAIAACAAAACQSam6rRrQrLsgSq+IKUeEZBzpPYDNhKC+ss2uAA4I8CAAAAAIj29QiQoCXEKFzHPxzOhas2MOtC/tYm+QJbUtSC7SY0AAAACdXaXWr688zj1DciHYx/h79vXX6ll+G0U5AigLWrSwsCNtJTh1clp0gGr2YSyS54ESdkqqD+fi+vEKHoOO+2wH",
"Security": {
"comment": "See sslmode descriptions at https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-string#tcp-connections and https://www.postgresql.org/docs/12/libpq-ssl.html#LIBPQ-SSL-PROTECTION",
"Enable": true,
"Mode": "require",
"Certificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"Key": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem",
"RootCertificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
}Precedents service
In C:\Program Files\Qlik\Sense\PrecedentsService\appsettings.json
You must use the common name (CN) for the host value. To get the CN, run the following commands in an elevated PowerShell.
$CertPath = [Environment]::GetFolderPath('CommonApplicationData') + "\Qlik\Sense\Repository\PostgreSQL\12.x\server.pem"
$Cert = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 -ArgumentList $CertPath
$Cert | Select-Object -Property SubjectTake the output from the commands above and replace "localhost" in the snippet below.
{
"Postgres": {
"Host": "localhost",
"Port": 4432,
"Database": "SenseServices",
"Schema": "precedents_service",
"Username": "qliksenserepository",
"Password": "AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAnq+F3zxlakeZ96CexCiJJwQAAAACAAAAAAAQZgAAAAEAACAAAACI2GnlPHqAG8iKXI+Nv92uE5DeGKaDpoMLSLjSX3M3BgAAAAAOgAAAAAIAACAAAADo8y30KfZ517PHilkR+9SQA9uYGnnZjKLM8eBfrzW2UCAAAAB+Mrbs74uJo4lMr+Jr8gSbxxa2ZkrNEKqI1WRDo5PpWkAAAADw2QwbQktqRT23B9N5jBJtqw/7BqVKVyLw1vCZyW0FqHHMBxyD9Gn6ajMFmekMRjxjWB2eREt5HXLM7EUE0slW",
"Security": {
"comment": "See sslmode descriptions at https://github.com/brianc/node-postgres/tree/master/packages/pg-connection-string#tcp-connections and https://www.postgresql.org/docs/12/libpq-ssl.html#LIBPQ-SSL-PROTECTION",
"Enable": true,
"Mode": "require",
"Certificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"Key": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem",
"RootCertificate": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
},
"QRS": {
"Url": "https://localhost:4242/qrs",
"RetryPolicy": {
"MaxRetries": 3,
"RetryTimeMs": 100
},
"Headers": {
"X-Qlik-User": "UserDirectory=INTERNAL; UserId=sa_api"
},
"ClientCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client.pem",
"PrivateKeyPath": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\client_key.pem"
},
"RootCertificate": {
"Path": "C:\\ProgramData\\Qlik\\Sense\\Repository\\Exported Certificates\\.Local Certificates\\root.pem"
}
}
}Additional configuration
For each node.js service, it is also possible to configure https communication with the service with additional --ssl parameter in the C:\Program Files\Qlik\Sense\ServiceDispatcher\services.conf. In the following example, TLS 1.2 is configured for Resource distribution service:
[resource-distribution]
Identity=Qlik.resource-distribution
DisplayName=Resource Distribution
ExePath=Node\node.exe
Script=..\ResourceDistributionService\server.js
[resource-distribution.parameters]
--secure
--wes-port=${WESPort}
--mode=server
--log-path=${LogPath}
--log-level=info
--ssl=369098752The following node.js code generates the number 369098752 to be used to configure OpenSSL according to OpenSSL Options:
const crypto = require("crypto");
console.log(crypto.constants.SSL_OP_NO_SSLv2 |
crypto.constants.SSL_OP_NO_SSLv3 | crypto.constants.SSL_OP_NO_TLSv1 |
crypto.constants.SSL_OP_NO_TLSv1_1); Database replication and failover
This section describes how to set up database replication and failover in a shared persistence environment. Additionally, the file storage content will also need to be replicated. To fail over to a standby node in case the central database or node is lost, one or more standby databases can be configured for streaming replication from the database on the primary node.
When editing text files related to the Qlik Sense installation, do the following:
- Copy the file to another location on the server.
- Edit the file and save the changes.
- Copy the updated file back to its original location.
Setting up replication to standby nodes for failover
The instructions in this section describe how to set up asynchronous streaming replication to one or more standby nodes. Before starting, ensure that the environment is configured and running, and install PostgreSQL on a standby machine.
Configure the primary database server
On the primary database server, do the following:
-
Open the file %ProgramFiles%\PostgreSQL\<version>\data
Locate and set the following settings
wal_level = replica max_wal_senders = 3 wal_keep_segments = 8 hot_standby = on -
Create a user account that can be used for replication. To do so from a command prompt, run the following command. Adjust the hostname as needed, and specify a suitable password. You may be prompted for a password, this is the password that was specified during installation.
"C:\Program Files\PostgreSQL\<version>\bin\psql.exe" -h <machinename> -p 4432 -W -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'secretpassword';" -
Open the file %ProgramFiles%\PostgreSQL\<version>\data\pg_hba.conf.
At the bottom of the file add:
host replication replicator 0.0.0.0/0 md5You can restrict the subnet access further, if required.
- Restart the PostgreSQL service.
Configure the standby database server (PostgreSQL 12.x)
On the standby PostgreSQL database server, do the following:
- Stop the PostgreSQL service.
- Delete all content from %ProgramFiles%\PostgreSQL\<version>\data.
-
From the command line run the following command adjusted to use the name of the primary server:
"C:\Program Files\PostgreSQL\<version>\bin\pg_basebackup.exe" -h <primaryServer> -D "C:\Program Files\PostgreSQL\<version>\data" -U replicator -v -P -p 4432You can ignore any warnings about copying files manually.
-
In a text editor, create a file called standby.signal (to indicate the server should start up as a hot standby) or recovery.signal (to indicate the server should start up in targeted recovery mode) and place it in %ProgramFiles%\PostgreSQL\<version>\data.
Information noteIf standby.signal and recovery.signal are both present, standby.signal takes precedence. - Start the PostgreSQL service.
Configure the standby database server (PostgreSQL 11.x )
On the standby PostgreSQL database server, do the following:
- Stop the PostgreSQL service.
- Delete all content from %ProgramFiles%\PostgreSQL\<version>\data.
-
From the command line run the following command adjusted to use the name of the primary server:
"C:\Program Files\PostgreSQL\<version>\bin\pg_basebackup.exe" -h <primaryServer> -D "C:\Program Files\PostgreSQL\<version>\data" -U replicator -v -P -p 4432You can ignore any warnings about copying files manually.
- In a text editor, create a file called recovery.conf and place it in %ProgramFiles%\PostgreSQL\<version>\data.
-
Open recovery.conf and add the following text, adjusting the hostname and port:
standby_mode = 'on' primary_conninfo = 'host=< primaryServer > port=4432 user=replicator password=secretpassword' trigger_file = 'failover' recovery_target_timeline = 'latest' - Start the PostgreSQL service.
You should now be able to connect to the database and view the data being streamed over from the primary node in read only mode.
Manual database failover
If the database on primary node is lost, a standby node needs to take over.
Do the following:
-
On the standby node that is to become the new primary node, create a file called failover in the folder %ProgramFiles%\PostgreSQL\<version>\data
Information noteThe failover file should have no file extension.The file triggers PostgreSQL to cease recovery and enter read/write mode.
-
On each node, change the repository database connection string to point to the hostname or IP address of the new database node. As the connection string is encrypted in the config file, you need to use the Connection String Editor to decrypt the string, edit it, and write back an encrypted string.
- To open the Connection String Editor, navigate to C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil and open the QlikSenseUtil.exe file as an administrator.
-
In the Connection String Editor tab, click Read to open the Repository.exe file connection string.
The decrypted database connection string is displayed.
-
Replace the value for Host with the hostname or IP address of the new database node.
-
Click Save value in config file encrypted to save your changes.