Thread: Postgres Log rotation not working in 8.0.2

Postgres Log rotation not working in 8.0.2

From
Pallav Kalva
Date:
Hi  Everybody,

       Postgres log rotation doesnt seem to behave properly, below are
the settings which I used for log rotation. I want the postgres log to
be rotated every week , its past over a week now and it still writing to
the old log. Are these settings right for what I want to achieve ? or am
I missing something ?  Please advice, we are planning to go production
soon and dont want to change my settings later.


TIA,
Pallav


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

# - Where to Log -

#log_destination = 'stderr'     # Valid values are combinations of stderr,
                                # syslog and eventlog, depending on
                                # platform.

# This is relevant when logging to stderr:
redirect_stderr = true      # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
log_directory = '/var/lib/pgsql/pg_log'    # Directory where log files
are written.
                            # May be specified absolute or relative to
PGDATA
log_filename = 'postgresql-%U.log' # Log file name pattern.
                            # May include strftime() escapes
log_truncate_on_rotation = true  # If true, any existing log file of 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 false, meaning append to existing
                            # files in all cases.
log_rotation_age = 10080    # Automatic rotation of logfiles will happen
after
                            # so many minutes.  0 to disable.
log_rotation_size = 0       # Automatic rotation of logfiles will happen
after
                            # so many kilobytes of log output.  0 to
disable.

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







Re: Postgres Log rotation not working in 8.0.2

From
Tom Lane
Date:
Pallav Kalva <pkalva@deg.cc> writes:
>        Postgres log rotation doesnt seem to behave properly, below are
> the settings which I used for log rotation. I want the postgres log to
> be rotated every week , its past over a week now and it still writing to
> the old log.

Well, that rotation setting should rotate every 7 days all right.  Are
you sure the postmaster is really using these settings?  (Check with SHOW.)
In particular, I wonder whether you remembered that changing
redirect_stderr requires an actual postmaster restart, not just SIGHUP.

            regards, tom lane

Re: Postgres Log rotation not working in 8.0.2

From
Pallav Kalva
Date:
Hi Tom,

     Postmaster is using these settings,  SHOW tells me all the settings
are right as per the postgresql.conf settings. I am checking the log
rotation after restarting the postmaster, Postgres was restarted last
monday morning and it generated a new log file postgresql-15.log since
then and up until now it is still writing to the same file , its been
over a week and 6 hours now.

Pallav.

Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> writes:
>
>
>>       Postgres log rotation doesnt seem to behave properly, below are
>>the settings which I used for log rotation. I want the postgres log to
>>be rotated every week , its past over a week now and it still writing to
>>the old log.
>>
>>
>
>Well, that rotation setting should rotate every 7 days all right.  Are
>you sure the postmaster is really using these settings?  (Check with SHOW.)
>In particular, I wonder whether you remembered that changing
>redirect_stderr requires an actual postmaster restart, not just SIGHUP.
>
>            regards, tom lane
>
>
>



Re: Postgres Log rotation not working in 8.0.2

From
Tom Lane
Date:
Pallav Kalva <pkalva@deg.cc> writes:
>      Postmaster is using these settings,  SHOW tells me all the settings
> are right as per the postgresql.conf settings. I am checking the log
> rotation after restarting the postmaster, Postgres was restarted last
> monday morning and it generated a new log file postgresql-15.log since
> then and up until now it is still writing to the same file , its been
> over a week and 6 hours now.

"Week and 6 hours"?  Try waiting a bit longer.  The switchover points
are not x-hours-since-postmaster-start, they are designed to be
reasonable integer multiples.  For instance, with rotation set for 24
hours, rotation occurs at local midnight, not whatever time of day
the postmaster was last restarted as.  With a 7-day setting it should
occur at local midnight of days that are a multiple of 7 days since
1-1-1970 ... which is Thursdays I think.

What I suspect happened is that it rotated last Thursday midnight, but
since %U hadn't changed since Monday, it overwrote the same file it was
using before.  Check to see what the oldest entry in the file is ...

            regards, tom lane

Re: Postgres Log rotation not working in 8.0.2

From
Pallav Kalva
Date:
Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> writes:
>
>
>>     Postmaster is using these settings,  SHOW tells me all the settings
>>are right as per the postgresql.conf settings. I am checking the log
>>rotation after restarting the postmaster, Postgres was restarted last
>>monday morning and it generated a new log file postgresql-15.log since
>>then and up until now it is still writing to the same file , its been
>>over a week and 6 hours now.
>>
>>
>
>"Week and 6 hours"?  Try waiting a bit longer.  The switchover points
>are not x-hours-since-postmaster-start, they are designed to be
>reasonable integer multiples.  For instance, with rotation set for 24
>hours, rotation occurs at local midnight, not whatever time of day
>the postmaster was last restarted as.  With a 7-day setting it should
>occur at local midnight of days that are a multiple of 7 days since
>1-1-1970 ... which is Thursdays I think.
>
>What I suspect happened is that it rotated last Thursday midnight, but
>since %U hadn't changed since Monday, it overwrote the same file it was
>using before.  Check to see what the oldest entry in the file is ...
>
>
>
    It didnt overwrite the file, the last log entry on the file is from
2005-04-11 which is last monday.




Re: Postgres Log rotation not working in 8.0.2

From
Tom Lane
Date:
Pallav Kalva <pkalva@deg.cc> writes:
> Tom Lane wrote:
>> What I suspect happened is that it rotated last Thursday midnight, but
>> since %U hadn't changed since Monday, it overwrote the same file it was
>> using before.  Check to see what the oldest entry in the file is ...
>>
>     It didnt overwrite the file, the last log entry on the file is from
> 2005-04-11 which is last monday.

You mean the server hasn't actually emitted any messages since last
Monday?  I think it only considers rotating the file when it actually
has something to write in it ...

            regards, tom lane

Re: Postgres Log rotation not working in 8.0.2

From
Pallav Kalva
Date:
Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> writes:
>
>
>>Tom Lane wrote:
>>
>>
>>>What I suspect happened is that it rotated last Thursday midnight, but
>>>since %U hadn't changed since Monday, it overwrote the same file it was
>>>using before.  Check to see what the oldest entry in the file is ...
>>>
>>>
>>>
>>    It didnt overwrite the file, the last log entry on the file is from
>>2005-04-11 which is last monday.
>>
>>
>
>You mean the server hasn't actually emitted any messages since last
>Monday?  I think it only considers rotating the file when it actually
>has something to write in it ...
>
>
>
I am sorry for the confusion, what i meant was the oldest entry on the
log file was on last monday and most recent was this morning and the
file didnt rotate yet.


Re: Postgres Log rotation not working in 8.0.2

From
Tom Lane
Date:
Pallav Kalva <pkalva@deg.cc> writes:
> I am sorry for the confusion, what i meant was the oldest entry on the
> log file was on last monday and most recent was this morning and the
> file didnt rotate yet.

Looking at the code, it will have decided to append not truncate because
%U hadn't changed from the preceding value:

     * Decide whether to overwrite or append.  We can overwrite if (a)
     * Log_truncate_on_rotation is set, (b) the rotation was triggered by
     * elapsed time and not something else, and (c) the computed file name
     * is different from what we were previously logging into.

There's also a funny involved in the first rotation after startup,
although in your situation case (c) would've prevented overwrite anyway:

     * Note: during the first rotation after forking off from the postmaster,
     * last_file_name will be NULL.  (We don't bother to set it in the
     * postmaster because it ain't gonna work in the EXEC_BACKEND case.)
     * So we will always append in that situation, even though truncating
     * would usually be safe.

So my advice remains "wait till this Thursday before complaining".

            regards, tom lane

Re: Postgres Log rotation not working in 8.0.2

From
Geoffrey
Date:
Pallav Kalva wrote:
> Hi Tom,
>
>     Postmaster is using these settings,  SHOW tells me all the settings
> are right as per the postgresql.conf settings. I am checking the log
> rotation after restarting the postmaster, Postgres was restarted last
> monday morning and it generated a new log file postgresql-15.log since
> then and up until now it is still writing to the same file , its been
> over a week and 6 hours now.

Long shot, has the machine date/time changed?  Assuming that's what the
process is keying off of.

If not, I'll climb back into my lurk...

--
Until later, Geoffrey

Re: Postgres Log rotation not working in 8.0.2

From
Pallav Kalva
Date:
Geoffrey wrote:

> Pallav Kalva wrote:
>
>> Hi Tom,
>>
>>     Postmaster is using these settings,  SHOW tells me all the
>> settings are right as per the postgresql.conf settings. I am checking
>> the log rotation after restarting the postmaster, Postgres was
>> restarted last monday morning and it generated a new log file
>> postgresql-15.log since then and up until now it is still writing to
>> the same file , its been over a week and 6 hours now.
>
>
> Long shot, has the machine date/time changed?  Assuming that's what
> the process is keying off of.
>
> If not, I'll climb back into my lurk...
>
No, Machine time hasnt been changed.