Re: Logging behavior not changing on reload

Tom,

I did the reload like this...

/usr/pgsql-9.1/bin/pg_ctl -D /db_data reload

I have examined the postgresql.conf file numerous times, and even had
someone else look at it for me. We can't see anything wrong in our syntax.
The following is the logging section of our conf file...

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a-%H.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 0 # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 10000000 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

silent_mode = on # Run server silently.
# DO NOT USE without syslog or
# logging_collector
# (change requires restart)

# - When to Log -

#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error

log_min_messages = fatal # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic

log_min_error_statement = fatal # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)

log_min_duration_statement = 5000 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds

# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
#log_line_prefix = 'user=%u,db=%d ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
log_lock_waits = on # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
log_temp_files = 0 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'

I can't seem to catch what might be wrong in our log for the very reason
that I'm needing to get these settings changed. I have a script running
that is migrating a database to a new schema. The old schema has a lot of
redundant data in it, so I'm migrating to normalized tables. The result of
migrating all of this redundant data is LOTS of unique constraint errors

Matt

On Wed, Apr 2, 2014 at 8:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Matthew Foster - NOAA Federal <matthew(dot)foster(at)noaa(dot)gov> writes:
> > Thank you for the quick reply. SHOW indeed indicates that the parameters
> > have not actually changed. I guess my next question is: Why did they not
> > change on reload?
>
> Likely theories include:
>
> * You did the reload wrong. You did not mention exactly how
> you did it, so it's hard to speculate about this.
>
> * You did the parameter change in postgresql.conf wrong. A typical gotcha
> is forgetting to remove the comment marker '#', but any sort of syntax
> error will cause Postgres to not process the change.
>
> regards, tom lane
>