How to enable detailed PostgreSQL Diagnostic Information

Summary

In this article we will enable PostgreSQL detailed SQL trace logging to a plain text file. If required VSoft may request further information from your PostgreSQL installation in order to assist in progressing a support case for you. Detailed trace logging in not enabled with the default install of PostgreSQL.

NOTE: As part of this process we will be stopping the ContinuaCI Server service, as a result it is highly recommended to make the below changes during a low activity period. Enabling tracing requires a restart of the primary database. It is also recommended to ensure adequate disk space on the server where tracing will be enabled (~80 megabytes per hour or greater will be required).

What to do?

  1. Locate your PostgreSQL config file (named postgresql.conf). By default this is located within the 'C:\ProgramData\VSoft\Continua CI\PostgreSQLDB' folder
  2. Take a backup copy of this file and store it in a safe place. This file will be required in order to reverse the process.
  3. Create a folder in order to house the log files (this folder can be located on any disk volume connected to the local machine).
  4. Open the postgresql.conf file and amend the below lines (NOTE: the value for log_directory should be set to the location you created in step 3 above, path names relative to your PostgreSQL data directory are allowed here).

Changes to postgresql.conf

```
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_statement = 'all'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_hostname = on
log_duration = on
```
  1. Save the file
  2. Stop the ContinuaCI Server Service
  3. Restart the Continua PostgreSQL service on the ContinuaCI Server (this should only take a few seconds to complete)
  4. Start ContinuaCI Server Service
  5. Log files will now start recording into the designated location:

10. Once the issue has been replicated, ZIP these files up and contact VSoft support (support@finalbuilder.com) in order to arrange a file transfer.

How do I turn logging off?

  1. Locate your PostgreSQL config file backup.
  2. Replace the postgresql.conf (which you had previously modified with the above changes) with this backup file.
  3. Restart the Continua PostgreSQL service on the ContinuaCI Server
  4. Detailed trace logging is now disabled