Thread: Trouble with query logging

I'm having difficulty getting postgresql to log queries.

I have the following set in my postgresql.conf:

-------------------------------
# - Syslog -

syslog = 2          # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

log_statement = yes

-------------------------------

I've done a:

pg_ctl reload -D /usr/local/pgsql/data/

and have even done a restart, but queries are not getting logged (to
/var/log/messages).

(Error messages are getting logged, however.)

I also tried specifying a log file using -l when doing pg_ctl restart.

Using postgresql version 7.4.

Any suggestions much appreciated!

ppi@amug.org wrote:
> I'm having difficulty getting postgresql to log queries.
>
> I have the following set in my postgresql.conf:
>
> -------------------------------
> # - Syslog -
>
> syslog = 2          # range 0-2; 0=stdout; 1=both; 2=syslog
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>
> log_statement = yes
>
> -------------------------------
>
> I've done a:
>
> pg_ctl reload -D /usr/local/pgsql/data/
>
> and have even done a restart, but queries are not getting logged (to
> /var/log/messages).
>
> (Error messages are getting logged, however.)

You have to configure your syslogd to accept messages from
LOCAL0, usualy I add to syslogd.conf:


LOCAL0.*              -/var/log/postgresql.log


don't forget to SIGHUP the syslogd

Regards
Gaetano Mendola

ppi@amug.org wrote:

> syslog_facility = 'LOCAL0'

This is the point! You have to tell syslogd to log messages sent to this
facility. I prefer to log it to a different file than /var/log/messages.

As root do:

mkdir /var/log/postgresql/

Add something like

# postgresql
local0.*                          /var/log/postgresql/postgresql.log

to the end of your /etc/syslog.conf and do a

service syslogd reload

Make sure to add /var/log/postgresql/postgresql.log to the first line of
/etc/logrotate.d/syslog which is basically the list of syslog logfiles that
have to be regularly rotated .

On production servers with heavy load place use "-/var/log/postgr..." in
syslog.conf for a better performance. The - means  to uses buffered I/O.