Thread: Logging behavior not changing on reload
I changed a few logging parameters on our 9.1 server (e.g. log_rotation_age, log_rotation_size, log_min_messages). After a reload, the logging behavior has not changed. Is a restart required for these? The documentation doesn't indicate that it is.
Thanks.
Matt
You could try show command (http://www.postgresql.org/docs/9.3/static/sql-show.html) and see if the changes have taken effect. Usually there are comments against each parameter which mention if a restart is required. Example,
port = 5432 # (change requires restart)
port = 5432 # (change requires restart)
On Wed, Apr 2, 2014 at 6:36 PM, Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> wrote:
I changed a few logging parameters on our 9.1 server (e.g. log_rotation_age, log_rotation_size, log_min_messages). After a reload, the logging behavior has not changed. Is a restart required for these? The documentation doesn't indicate that it is.Thanks.Matt
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?
Matt
On Wed, Apr 2, 2014 at 8:29 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:
You could try show command (http://www.postgresql.org/docs/9.3/static/sql-show.html) and see if the changes have taken effect. Usually there are comments against each parameter which mention if a restart is required. Example,
port = 5432 # (change requires restart)On Wed, Apr 2, 2014 at 6:36 PM, Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> wrote:I changed a few logging parameters on our 9.1 server (e.g. log_rotation_age, log_rotation_size, log_min_messages). After a reload, the logging behavior has not changed. Is a restart required for these? The documentation doesn't indicate that it is.Thanks.Matt
Hmm, usually when you do a pg_ctl reload, the log files has entries of that event plus changes in parameter values. Check you PostgreSQL log file? May be do a tail -f on it and try pg_ctl reload once again?
On Wed, Apr 2, 2014 at 7:09 PM, Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> wrote:
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?MattOn Wed, Apr 2, 2014 at 8:29 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:You could try show command (http://www.postgresql.org/docs/9.3/static/sql-show.html) and see if the changes have taken effect. Usually there are comments against each parameter which mention if a restart is required. Example,
port = 5432 # (change requires restart)On Wed, Apr 2, 2014 at 6:36 PM, Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> wrote:I changed a few logging parameters on our 9.1 server (e.g. log_rotation_age, log_rotation_size, log_min_messages). After a reload, the logging behavior has not changed. Is a restart required for these? The documentation doesn't indicate that it is.Thanks.Matt
On 04/02/2014 03:39 PM, Matthew Foster - NOAA Federal wrote: > 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? > > Matt > Hi Matt, Changing paramaters relative to logging_collector requires restart not just reload. Cheers
Matthew Foster - NOAA Federal <matthew.foster@noaa.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
Tovo Rabemanantsoa <tovo.rabemanantsoa@bordeaux.inra.fr> writes:
> On 04/02/2014 03:39 PM, Matthew Foster - NOAA Federal wrote:
>> 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?
> Changing paramaters relative to logging_collector requires restart not
> just reload.
Not the ones he asked about. It's true that running the logging collector
at all (ie turning logging_collector on or off) requires a restart to
reconfigure the server process interconnections, but adjusting the log
rotation parameters doesn't need that.
regards, tom lane
On 04/02/2014 03:54 PM, Tom Lane wrote: > Tovo Rabemanantsoa <tovo.rabemanantsoa@bordeaux.inra.fr> writes: >> On 04/02/2014 03:39 PM, Matthew Foster - NOAA Federal wrote: > > Not the ones he asked about. It's true that running the logging collector > at all (ie turning logging_collector on or off) requires a restart to > reconfigure the server process interconnections, but adjusting the log > rotation parameters doesn't need that. > > regards, tom lane > > Okay, This is a misundestanding from me then. Thanks for the precision
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@sss.pgh.pa.us> wrote:
Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> writes:Likely theories include:
> 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?
* 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
Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> writes:
> 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.
It looks okay to me too. So we need to approach this a bit more
systematically.
1. When you do the pg_ctl reload, do you see anything about "received
SIGHUP, reloading configuration files" appear in the postmaster log?
If not, the problem is something about pg_ctl not getting its job done.
(Notably, are you sure that's the right -D value? It does not look like
where installations using the type of path you show above would usually
put the data directory.)
2. If the postmaster is getting the SIGHUP signal, then the wires are
crossed somewhere in terms of what you're doing with postgresql.conf.
You wouldn't be the first person to have edited the wrong copy of
postgresql.conf, for instance. Try "SHOW config_file" to verify where
the server thinks its config is coming from.
3. It also seems possible that the value you're setting is getting
overridden from somewhere else. Try something like
SELECT name,source,sourcefile,sourceline FROM pg_settings
WHERE name like 'log%';
to see where it says it got the active values of these variables from.
It's especially easy to get these sorts of things confused if you've
got more than one Postgres installation on the machine, or if you're
using an arrangement where the config files are not in the data
directory proper.
regards, tom lane
Tom,
I just found it. I had to set my terminal's scrollback buffer to unlimited and search for SIGHUP in the tail output. The log_rotation_size value was too large. ~2GB is the maximum. I was trying to go for 10GB, and it didn't like it. I changed the value to 2000000, and it worked.
Thanks, everyone, for the assistance!
Matt
On Wed, Apr 2, 2014 at 9:53 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matthew Foster - NOAA Federal <matthew.foster@noaa.gov> writes:> I did the reload like this...It looks okay to me too. So we need to approach this a bit more
> /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.
systematically.
1. When you do the pg_ctl reload, do you see anything about "received
SIGHUP, reloading configuration files" appear in the postmaster log?
If not, the problem is something about pg_ctl not getting its job done.
(Notably, are you sure that's the right -D value? It does not look like
where installations using the type of path you show above would usually
put the data directory.)
2. If the postmaster is getting the SIGHUP signal, then the wires are
crossed somewhere in terms of what you're doing with postgresql.conf.
You wouldn't be the first person to have edited the wrong copy of
postgresql.conf, for instance. Try "SHOW config_file" to verify where
the server thinks its config is coming from.
3. It also seems possible that the value you're setting is getting
overridden from somewhere else. Try something like
SELECT name,source,sourcefile,sourceline FROM pg_settings
WHERE name like 'log%';
to see where it says it got the active values of these variables from.
It's especially easy to get these sorts of things confused if you've
got more than one Postgres installation on the machine, or if you're
using an arrangement where the config files are not in the data
directory proper.
regards, tom lane