How to find PostgreSQL server messages logs

08 August,2019 by Jack Vamvas

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 (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find PostgreSQL server messages logs


dba-ninja.com