Обсуждение: 8.4.12 log truncation not working?

Hi all,

For some reason the log truncation doesn't seem to be working on my server.
I have set log_rotation_age to 1 day and log_truncate_on_rotation to on.

log_destination                 | csvlog,syslog                                     | Sets the destination for server log output.
log_directory                   | pg_log                                            | Sets the destination directory for log files.
log_filename                    | postgresql-%a.log                                 | Sets the file name pattern for log files.
log_rotation_age                | 1d                                                | Automatic log file rotation will occur after N minutes.
log_rotation_size               | 0                                                 | Automatic log file rotation will occur after N kilobytes.
log_truncate_on_rotation        | on                                                | Truncate existing log files of same name during log rotation.

server_version                  | 8.4.12                                            | Shows the server version.

Log timezone is UTC.

What is happening is that I get log files that continue to get appended with each rotation instead of getting truncated.

So, in postgresql-Fri.csv, I see this:

2012-07-20 23:59:12.146 UTC,"sysdba",5950,"[local]",5009f054.173e,173,"COMMIT",2012-07-20 23:57:08 UTC,3/0,0,LOG,00000,"duration: 0.353 ms  statement: COMMIT;;",,,,,,,,
2012-07-27 00:00:03.044 UTC,"sysdba",26539,"[local]",5011da03.67ab,1,"SELECT",2012-07-27 00:00:03 UTC,6/0,0,LOG,00000,"duration: 0.423 ms  statement: select datname, oid from pg_database",,,,,,,,

A jump from Friday the 20th to Friday the 27th with no truncation.

I don't have any periodic scheduled Postgres restarts but the server does get restarted frequently.

How can I fix this so that it gets truncated when it rolls around to the same day of the week again?

Thanks and Regards,
James

pg noob <pgnube@gmail.com> writes:
> For some reason the log truncation doesn't seem to be working on my server.
> I have set log_rotation_age to 1 day and log_truncate_on_rotation to on.
> ...
> I don't have any periodic scheduled Postgres restarts but the server does
> get restarted frequently.

There is a known issue that the server will append rather than
truncating at the first rotation opportunity after a restart.
So if "frequently" means "more than once a day", that would explain
your results.  This was complained of recently:
http://archives.postgresql.org/pgsql-bugs/2012-07/msg00116.php
but nobody's done anything about it yet.

            regards, tom lane

pg noob <pgnube@gmail.com> writes:
> Thank you Tom, that's basically the answer I was expecting (I had already
> found that thread you mentioned).
> But I had a slight hope for something better.

> If I understand this correctly then, if Postgres gets restarted and starts
> appending to the log that it would have truncated, then it has to wait a
> week before it rolls around to that day again before it will truncate the
> log?  Because that is what I am seeing, is some log files with multiple
> weeks worth of log messages appended to the same day file.

Yeah, that's how it would work.

It occurs to me that you could probably work around the issue if you
left log_filename set the way it is and reduced log_rotation_age to
something relatively small, like 10 minutes or so.  The server would
go through a rotation exercise every 10 minutes, but except at midnight,
it would decide to append because the computed filename hadn't changed.
So you'd only see the misbehavior if you restarted within 10 minutes
before midnight.  (At least I think it will work like that, haven't
tried it.)

            regards, tom lane