How to enable and collect the PostGres SQL database logs

[Zareh_T]

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.

1. Open the Windows Services Console
2. Stop the Qlik Sense Repository Database Service
   
   

    

3. In a File Explorer, navigate to C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\9.6\
4. Open postgresql.conf in a text editor of your choice.
   
   

    

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

   
   
6. Start the Qlik Sense Repository Database Service
   
   

    

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