Underradicals Portfolio

Appearance

Postgres Logging ​

Introduction ​

PostgreSQL offers three methods for logging server messages.

  1. stderr -- Found at log/postgresql.log
  2. csvlog -- Found at log/postgresql.csv
  3. jsonlog -- Found at log/postgresql.json

The default is stderr.

NOTE

PostgreSQL does offer an additional method on Windows, and this is eventlog

If at any point you want to interrogate PostgreSQL to find the current value of a parameter, for example, in what directory logs located, execute a SHOW command and append the parameter.

bash
postgresql=# SHOW LOG_DIRECTORY
 log_directory
---------------
 log
(1 row)

Here we see the location of LOG_DIRECTORY as log.

If you want to know where to locate your postgresql.conf file where these parameters and more are located you can execute another SHOW command and append the argument config_file.

bash
postgresql=# SHOW config_file;
-----------------------------------------------------
 C:/Program Files/PostgreSQL/18/data/postgresql.conf
(1 row)

To change the value of a parameter, the easiest way, is to open the conf file and change it manually. You will find in these docs the line number where the parameter can be found.

Then reload your configuration:

bash
postgresql=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

If you haven't noticed

I am purposefully not making these commands easy to copy and paste, because I would rather you to type them out manually.

Below is a collection of parameters that affect how PostgreSQL implements logging on the server. ​

Parameters ​

LOGGING_COLLECTOR ​

(BOOLEAN) #491 ​

This will enable the logging_collector. The PostgreSQL Docs describe this as a background process that captures log messages that are then sent to stderr by default, or whatever is the value of LOGGING_DESTINATION.

Important Notes ​

  1. It is possible to log to stderr without using the logging collector; the log messages will end up wherever the server's stderr is directed. PostgreSQL does not reccommend this for high volume logs, however.
  2. The logging collector is not designed to loose messages. This means that under high volume the server could suffer blockage while trying to send additional logs if and when the collector has fallen behind.

NOTE

The logging_collector parameter can only be set at server start.

LOGGING_DESTINATION ​

(STRING) #484 ​

PostgreSQL supports several methods for logging server messages, including stderr, csvlog, jsonlog, and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line.

If csvlog is included in log_destination, log entries are output in “comma-separated value” (CSV) format, which is convenient for loading logs into programs. See Section 19.8.4 for details. logging_collector must be enabled to generate CSV-format log output.

If jsonlog is included in log_destination, log entries are output in JSON format, which is convenient for loading logs into programs. See Section 19.8.5 for details. logging_collector must be enabled to generate JSON-format log output.

When either stderr, csvlog or jsonlog are included, the file current_logfiles is created to record the location of the log file(s) currently in use by the logging collector and the associated logging destination. This provides a convenient way to find the logs currently in use by the instance. Here is an example of this file's content:

LOG_DIRECTORY ​

(STRING) #497 ​

When logging_collector is enabled, this parameter logging_directory will determine the directory in which log files will be placed.

Important Notes ​

  1. The filename path can be specified as an absolute path, or relative to the cluster data directory.
  2. This parameter can only be set in the postgresql.conf file or on the server command line.
  3. The default is log.

NOTE

On most Unix systems, you will need to alter the configuration of your system's syslog daemon in order to make use of the syslog option for log_destination. PostgreSQL can log to syslog facilities LOCAL0 through LOCAL7 (see syslog_facility), but the default syslog configuration on most platforms will discard all such messages. You will need to add something like:

bash
local0.*    /var/log/postgresql

to the syslog daemon's configuration file to make it work.

On Windows, when you use the eventlog option for log_destination, you should register an event source and its library with the operating system so that the Windows Event Viewer can display event log messages cleanly. See Section 18.12 for details.