Re: log_truncate_on_rotation=on is not truncating

Hello Tom and all,

Thank you for following up on this.

1. log_timezone:

admin_datamart=# show log_timezone;
log_timezone
--------------
US/Pacific
(1 row)

I have been playing around with changing log_rotation_age = 7h, +/- 1 hour
to see if I can get the elapse timing right. The shutdown/startup time is
still at 1600/18:30. At 8h, it was still appending. But recently, I've
change it to 7h. Some day the log will truncate and some day it didn't. I'm
guessing if its based on elapsed time since startup for truncate to happen
or not, then it make sense that truncate didn't always happen because
startup time can varies (some day tar runs longer).

2. To answer your question as why we are restarting daily is that these
servers are hot standby and we are taking cold backups from there. The plan
is to do hot backup (now that we are on 9.1 and can pause recovery) but we
just haven't gotten time to implement the hot backup yet.

Thanks,

Chan

On Sun, Jul 15, 2012 at 10:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > The relevant bit of the code is
>
> > /*
> > * The requirements here are to choose the next time > now that is a
> > * "multiple" of the log rotation interval. "Multiple" can be
> interpreted
> > * fairly loosely. In this version we align to log_timezone rather
> than
> > * GMT.
> > */
>
> Oh, no, scratch that --- there's nothing wrong with that calculation.
> Actually I think the issue is with the logic that decides whether to
> truncate or append during rotation:
>
> * 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.
> *
> * 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.
>
> This dates from around 2004, and at the time we did not think it was
> very important if the first rotation opportunity failed to truncate.
> Of course, if you restart the postmaster so often that it never gets to
> the second rotation opportunity, that theory falls down. But I guess
> it's worth asking why you think it's a good plan to reboot so often.
> Most people running Postgres are appalled by the idea that they might
> have to have any downtime at all.
>
> regards, tom lane
>

--
*

Chan Pham
Database Administrator

Direct: 503.943.6773
Fax: 503.224.1581 // AIM: ioChanny // Twitter: iovation
www.iovation.com

<http://www.iovation.com/eml-msg/>
*