How to enable and collect the PostGres SQL database logs

Zareh_T — Fri, 06 Nov 2020 12:06:51 GMT

There may be situations where the QlikSense repository PostgreSQL database stops responding due to a large number of connections. It may start facing timeout or performance issues.

At this stage, it may become necessary to look into the database logs in addition to the QlikSense logs. Same would apply to the NPrinting PostgreSQL database.

Open the Windows Services Console
Stop the Qlik Sense Repository Database Service
In a File Explorer, navigate to C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\
Open postgresql.conf in a text editor of your choice.
In this configuration file, locate the section ERROR REPORTING AND LOGGING and modify it as follows:

Remove the # in front of the logging configuration settings to enable them.
Switch logging_collector = off to logging_collector = on

An example of the changed file can be seen below. Note we do not change any default values in our example.

As we are not making any changes, the logs will be located in:

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\pg_log

#--------------------------------------------- # ERROR REPORTING AND LOGGING #--------------------------------------------- # - Where to Log - log_destination = 'stderr' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) # These are only used if logging_collector is on: log_directory = 'pg_log' # directory where log files are written, # can be absolute or relative to PGDATA log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern, # can include strftime() escapes log_file_mode = 0600 # creation mode for log files, # begin with 0 to use octal notation log_truncate_on_rotation = off # If on, an existing log file with the # same name as the new log file will be # truncated rather than appended to. # But such truncation only occurs on # time-driven rotation, not on restarts # or size-driven rotation. Default is # off, meaning append to existing files # in all cases. log_rotation_age = 1d # Automatic rotation of logfiles will # happen after that time. 0 disables. log_rotation_size = 10MB # Automatic rotation of logfiles will # happen after that much log output. # 0 disables.
Start the Qlik Sense Repository Database Service
Once the service has started, verify that logging has been correctly turned on by navigating to C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\pg_log.

Notes:

Will there be a performance impact by enabling PostgreSQL logging?

R&D did some testing with the following settings:

in the postgresql.conf file: log_destination = 'csvlog' logging_collector = on log_connections = on log_disconnections = on log_hostname = on log_statement = 'all' log_line_prefix = '%a%%%u%%%s'

R&D's statement from their testing:

Average response time:
Slightly higher with logging enabled but the biggest difference was only 4ms. Average time without logging 163ms, with logging 166ms.

Processor time:
PostgreSQL used very little CPU time. Average time without logging was 0.29%, with logging 0.33%.

Memory usage:
Here, slightly less memory was used with logging enabled. Average without logging 1.16GB, with logging 1.13GB.

Log files on disk:
With logging enabled, a number of files were created on the local disk. About 10MB of data were created every minute.

The conclusion from the tests: that it is safe to enable the logs in PostgreSQL. The only thing that needs attention is to make sure the log files do not fill up the storage.

Re: How to enable and collect the PostGres SQL database logs

atiwari — Mon, 30 Dec 2024 10:41:18 GMT

Hello @Zareh_T , I have Qlik Sense Bundelded PostgreSQL 14, (Qlik Sense Feb 2024) and I want to Enbale PostgreSQL Audit Extension (pgAudit). What Steps I need to follow to enable the pgAudit.

Currently I have the shared_preload_libraries= ' ' - in PostgreSQL conf file.

Re: How to enable and collect the PostGres SQL database logs

atiwari — Thu, 02 Jan 2025 12:39:35 GMT

@Sonja_Bauernfeind Could you please review this and suggested the Option to enable the pgAudit in Qlik Sense bundled PostgreSQL 14.

article How to enable and collect the PostGres SQL database logs in Official Support Articles

How to enable and collect the PostGres SQL database logs

Notes:

Re: How to enable and collect the PostGres SQL database logs

Re: How to enable and collect the PostGres SQL database logs