Thread: Postgres log help - Postgresql-8.3.5
Hi All,
I am not able to figure out how to change the logging to a single line,
My log file out puts
LOG: duration: 0.492 ms
LOG: duration: 0.397 ms
LOG: duration: 0.097 ms
LOG: execute <unnamed>: select count(*) from v_mthly_demo_rollup
LOG: duration: 211.724 ms
LOG: duration: 0.499 ms
LOG: duration: 0.082 ms
LOG: execute <unnamed>: select count(*) from v_mthly__demo_rollup where (demo_nm='test' AND rollup_eff_yr_mth='200912')
LOG: duration: 0.544 ms
LOG: duration: 0.334 ms
LOG: duration: 0.036 ms
I want my log file to output something like in the below format
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net(45872)|11683|2010-02-12 11:01:48.630 PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: 0.099 ms
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net(45872)|11683|2010-02-12 11:01:48.630 PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: 0.011 ms
Here is my postgresql.conf file log configuration information
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
logging_collector = on # changed on 04/07/2009
log_directory = '/db_log/demo/pgsql8.3.5/pg_log' # changed on 04/07/2009
log_filename = 'postgresql-v8.3.5-%Y-%m-%d_%H%M%S.log' # changed on 04/07/2009
log_truncate_on_rotation = on # changed on 04/07/2009
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 100MB # changed on 04/07/2009
log_line_prefix = '%u|%d|%r|%p|%t|%m|%i|%c|%l|%s|%x' # changes done on 05/15/2009 :
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = '' # special values:
log_lock_waits = on #changed on 04/07/2009
log_statement = 'all' # changed below 6 lines on 04/07/2009
#------------------------------------------------------------------------------
Which parameter should I need to change to output in my log in the below format
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net(45872)|11683|2010-02-12 11:01:48.630 PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: 0.099 ms
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net(45872)|11683|2010-02-12 11:01:48.630 PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: 0.011 ms
Thanks
Deepak
Did you issue the reload command using pg_ctl after editing your postgresql.conf, to tell the server to pick up the changes? If you issue the following within a psql session, it will tell you what config value the server is currently using: show log_line_prefix; Looks like either your server hasn't reloaded the updated config file, or you edited the wrong config file. Lewis DM wrote: > Hi All, > > I am not able to figure out how to change the logging to a single line, > > *My log file out puts* > > LOG: duration: 0.492 ms > LOG: duration: 0.397 ms > LOG: duration: 0.097 ms > LOG: execute <unnamed>: select count(*) from v_mthly_demo_rollup > LOG: duration: 211.724 ms > LOG: duration: 0.499 ms > LOG: duration: 0.082 ms > LOG: execute <unnamed>: select count(*) from v_mthly__demo_rollup where > (demo_nm='test' AND rollup_eff_yr_mth='200912') > LOG: duration: 0.544 ms > LOG: duration: 0.334 ms > LOG: duration: 0.036 ms > > *I want my log file to output something like in the below format* > * > * > 2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net > <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 > PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: > 0.099 ms > 2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net > <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 > PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: > 0.011 ms > > Here is my postgresql.conf file log configuration information > > # ERROR REPORTING AND LOGGING > #------------------------------------------------------------------------------ > > # - Where to Log - > > logging_collector = on # changed on 04/07/2009 > log_directory = '/db_log/demo/pgsql8.3.5/pg_log' # changed on > 04/07/2009 > log_filename = 'postgresql-v8.3.5-%Y-%m-%d_%H%M%S.log' # changed on > 04/07/2009 > log_truncate_on_rotation = on # changed on 04/07/2009 > log_rotation_age = 1d # Automatic rotation of logfiles > will > log_rotation_size = 100MB # changed on 04/07/2009 > log_line_prefix = '%u|%d|%r|%p|%t|%m|%i|%c|%l|%s|%x' # changes done on > 05/15/2009 : > log_checkpoints = on > log_connections = on > log_disconnections = on > log_duration = on > log_hostname = on > log_line_prefix = '' # special values: > log_lock_waits = on #changed on 04/07/2009 > log_statement = 'all' # changed below 6 lines on > 04/07/2009 > #------------------------------------------------------------------------------ > > Which parameter should I need to change to output in my log in the below > format > 2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net > <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 > PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: > 0.099 ms > 2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net > <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 > PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: > 0.011 ms > > Thanks > Deepak
Thank you for your answer.
I had made a mistake in my postgresql.conf having 2 entries for log_line_prefix one with log_line_prefix = '%t|%u|%d|%r|%p|%m|%i|%c|%l|%s|%x'
and another log_line_prefix = '' (end of the file)
Its working now..
thanks
Deepak
On Fri, Feb 12, 2010 at 12:53 PM, Lewis Kapell <lkapell@setonhome.org> wrote:
Did you issue the reload command using pg_ctl after editing your postgresql.conf, to tell the server to pick up the changes?
If you issue the following within a psql session, it will tell you what config value the server is currently using:
show log_line_prefix;
Looks like either your server hasn't reloaded the updated config file, or you edited the wrong config file.
Lewis
DM wrote:Hi All,2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: 0.099 ms
I am not able to figure out how to change the logging to a single line,
*My log file out puts*
LOG: duration: 0.492 ms
LOG: duration: 0.397 ms
LOG: duration: 0.097 ms
LOG: execute <unnamed>: select count(*) from v_mthly_demo_rollup
LOG: duration: 211.724 ms
LOG: duration: 0.499 ms
LOG: duration: 0.082 ms
LOG: execute <unnamed>: select count(*) from v_mthly__demo_rollup where (demo_nm='test' AND rollup_eff_yr_mth='200912')
LOG: duration: 0.544 ms
LOG: duration: 0.334 ms
LOG: duration: 0.036 ms
*I want my log file to output something like in the below format*
*
*
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: 0.011 ms2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 PST|SELECT|4b72f31d.2da3|8709|2010-02-10 09:55:41 PST|0LOG: duration: 0.099 ms
Here is my postgresql.conf file log configuration information
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
logging_collector = on # changed on 04/07/2009
log_directory = '/db_log/demo/pgsql8.3.5/pg_log' # changed on 04/07/2009
log_filename = 'postgresql-v8.3.5-%Y-%m-%d_%H%M%S.log' # changed on 04/07/2009
log_truncate_on_rotation = on # changed on 04/07/2009
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 100MB # changed on 04/07/2009
log_line_prefix = '%u|%d|%r|%p|%t|%m|%i|%c|%l|%s|%x' # changes done on 05/15/2009 :
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_line_prefix = '' # special values:
log_lock_waits = on #changed on 04/07/2009
log_statement = 'all' # changed below 6 lines on 04/07/2009
#------------------------------------------------------------------------------
Which parameter should I need to change to output in my log in the below format
2010-02-12 11:01:48 PST|usr|dbdemo|server-res0003.net <http://server-res0003.net>(45872)|11683|2010-02-12 11:01:48.630 PST|BIND|4b72f31d.2da3|8710|2010-02-10 09:55:41 PST|0LOG: duration: 0.011 ms
Thanks
Deepak