PostgreSQL supports several methods for logging server messages, including stderr, eventlog,csvlog and syslog.  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.

Typical situation – problem arises and you need to debug – if you are not familiar with the PostgresQL logs – you can spend some frustrating time locating the details required for debugging. Use the information to start investigating

Step 1 – Identify where the log entries are sent 

Logon as user to use psql

user=# show log_destination;
log_destination
—————–
stderr
(1 row)

This entry identifies the destination of the PostgreSQL log entries. Typically there are 4 potential values : SYSLOG, EVENTLOG,STDERR, CSVLOG .   It can also be a comma separated list with a combination of any of these 4 values

Some introductory notes on these value

SYSLOG

To start working with SYSLOG you’ll need to know the the “facility” and “ident”

show syslog_facility;

show syslog_ident;

EVENTLOG 

Use if you want to push log entries to Windows Event Log

STDERR

As it’s the default , it tends to be the most common method. It can get complicated – as it’s possible to redirect to all sorts of destinations. Some useful commands to start with are:

user=# show logging_collector ;
logging_collector
——————-
off
(1 row)

The show logging_collector  displays whether the logging collector is on or off , when on – there is a collection of stderr output to a configured directory. Where is this directory?

user=# show log_directory;
log_directory
—————
pg_log
(1 row)

Where is the data directory?

user=# show data_directory;
data_directory
————————————–
/xxxx/dir1/postgres_mode/as96/data
(1 row)

What is the log file naming pattern?

user=# show log_filename;
log_filename
———————————-
postgres-%Y-%m-%d_%H%M%S.log
(1 row)

CSVLOG

Logs are created based on CSV standards. logging_collector has to be turned on, and if turned on will create the csv based logs in the value found on show data_directory

Author: Jack Vamvas