Thread: Problem getting sql statement logging to work
Hi
I am trying to log my sql statements to the syslog file.
I have the following entries in my postgresql.conf file yet it doesn't seem
to log anything.
The OS is Solaris 9
I am looking at /var/adm/messages and can't see anything from Postgres and I
have run lots of sql.
I saw one article on the web which suggested setting debug_print_query = on.
Is this an alternative way of setting logging ?
Thanks for any help.
David Klugmann
# - Syslog -
syslog = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
#log_min_duration_statement = -1 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero prints all queries.
# Minus-one disables.
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
log_statement = true
#log_timestamp = false
#log_hostname = false
#log_source_port = false
The problem may be in your /etc/syslog.conf file. It directs different
classes of messages different places, and can direct them to another
machine.
Rick
"David Klugmann"
<dklugmann@hotmail.com> To: pgsql-general@postgresql.org
Sent by: cc:
pgsql-general-owner@pos Subject: [GENERAL] Problem getting sql statement logging to work
tgresql.org
01/18/2005 10:05 AM
Hi
I am trying to log my sql statements to the syslog file.
I have the following entries in my postgresql.conf file yet it doesn't seem
to log anything.
The OS is Solaris 9
I am looking at /var/adm/messages and can't see anything from Postgres and
I
have run lots of sql.
I saw one article on the web which suggested setting debug_print_query =
on.
Is this an alternative way of setting logging ?
Thanks for any help.
David Klugmann
# - Syslog -
syslog = 2 # range 0-2; 0=stdout; 1=both;
2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing
detail:
# debug5, debug4,
debug3, debug2, debug1,
# log, info, notice,
warning, error
#log_min_messages = notice # Values, in order of decreasing
detail:
# debug5, debug4,
debug3, debug2, debug1,
# info, notice, warning,
error, log, fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4,
debug3, debug2, debug1,
# info, notice,
warning, error, panic(off)
#log_min_duration_statement = -1 # Log all statements whose
# execution time exceeds
the value, in
# milliseconds. Zero
prints all queries.
# Minus-one disables.
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
log_statement = true
#log_timestamp = false
#log_hostname = false
#log_source_port = false
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Many thanks for the help Michael I did restart the postmaster a few times. I will give the other suggestions a go and let you know what happens. David >From: Michael Fuhr <mike@fuhr.org> >To: David Klugmann <dklugmann@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Problem getting sql statement logging to work >Date: Tue, 18 Jan 2005 09:51:27 -0700 > >On Tue, Jan 18, 2005 at 03:05:34PM +0000, David Klugmann wrote: > > > > I have the following entries in my postgresql.conf file yet it doesn't >seem > > to log anything. > >Did you restart the backend after modifying postgresql.conf? > > > syslog_facility = 'LOCAL0' > >Have you configured /etc/syslog.conf to send local0 messages anywhere? >You might need to add an appropriate line to /etc/syslog.conf, touch >the desired log file if it doesn't already exist, and send a HUP >signal to syslogd. > >You can test your syslog configuration with the "logger" program. >I think log_statement sends messages to the LOG_INFO syslog level, >so run the following command from the shell prompt and see if the >message gets logged: > >logger -p local0.info "test 1 to local0.info" > >Verify that your syslog configuration is working, make sure you've >restarted the PostgreSQL backend so it knows about the configuration >changes, and try again. > >-- >Michael Fuhr >http://www.fuhr.org/~mfuhr/
On Tue, Jan 18, 2005 at 03:05:34PM +0000, David Klugmann wrote: > > I have the following entries in my postgresql.conf file yet it doesn't seem > to log anything. Did you restart the backend after modifying postgresql.conf? > syslog_facility = 'LOCAL0' Have you configured /etc/syslog.conf to send local0 messages anywhere? You might need to add an appropriate line to /etc/syslog.conf, touch the desired log file if it doesn't already exist, and send a HUP signal to syslogd. You can test your syslog configuration with the "logger" program. I think log_statement sends messages to the LOG_INFO syslog level, so run the following command from the shell prompt and see if the message gets logged: logger -p local0.info "test 1 to local0.info" Verify that your syslog configuration is working, make sure you've restarted the PostgreSQL backend so it knows about the configuration changes, and try again. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Many thanks. Configuring the syslog.conf and using logger to check it was working did the trick. Previously I had no local0 entries in the syslog.conf which was my problem. David >From: Michael Fuhr <mike@fuhr.org> >To: David Klugmann <dklugmann@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Problem getting sql statement logging to work >Date: Tue, 18 Jan 2005 09:51:27 -0700 > >On Tue, Jan 18, 2005 at 03:05:34PM +0000, David Klugmann wrote: > > > > I have the following entries in my postgresql.conf file yet it doesn't >seem > > to log anything. > >Did you restart the backend after modifying postgresql.conf? > > > syslog_facility = 'LOCAL0' > >Have you configured /etc/syslog.conf to send local0 messages anywhere? >You might need to add an appropriate line to /etc/syslog.conf, touch >the desired log file if it doesn't already exist, and send a HUP >signal to syslogd. > >You can test your syslog configuration with the "logger" program. >I think log_statement sends messages to the LOG_INFO syslog level, >so run the following command from the shell prompt and see if the >message gets logged: > >logger -p local0.info "test 1 to local0.info" > >Verify that your syslog configuration is working, make sure you've >restarted the PostgreSQL backend so it knows about the configuration >changes, and try again. > >-- >Michael Fuhr >http://www.fuhr.org/~mfuhr/