Log rotation

Fernando Nasser <fnasser@redhat.com> writes:

Please remind me again why the postmaster cannot close and open the log
file when it receives a SIGHUP (to re-read configuration)?

(a) Because it never opened it in the first place --- the log file is
whatever was passed as stderr.

(b) Because it would not be sufficient to make the postmaster itself
close and reopen the file; every child process would have to do so also.
Doing this in any sort of synchronized fashion seems impossible.

It's much cleaner to have stderr be a pipe to some separate collector
program that can handle log rotation (ie, the Apache solution).

regards, tom lane

Tom Lane wrote:

Fernando Nasser <fnasser@redhat.com> writes:

Please remind me again why the postmaster cannot close and open the log
file when it receives a SIGHUP (to re-read configuration)?

(a) Because it never opened it in the first place --- the log file is
whatever was passed as stderr.

(b) Because it would not be sufficient to make the postmaster itself
close and reopen the file; every child process would have to do so also.
Doing this in any sort of synchronized fashion seems impossible.

Now I remember. Thanks for reminding me of that.

It's much cleaner to have stderr be a pipe to some separate collector
program that can handle log rotation (ie, the Apache solution).

We could also create a pipe and start a new process ("logger") and give
it the other end of the pipe and the name of the log file. We could
send it a SIGHUP after we reread the configuration file.

But just doing a pipe on the OS level is way simpler.

I don't really care on how its done, but IMO an enterprise class
database must be able to do log rotation. Logging to Syslog is not an
option (specially with our verbosity) -- users must be able to use flat
files for logging.

I never seem some many customer complaints and bug reports about a
single item like the ones we have received here about logging. I think
this should be the number 1 item in te TODO list.

Thanks again for the clarifications.

Regards,
Fernando

On Fri, Mar 12, 2004 at 09:24:28 -0500,
Fernando Nasser <fnasser@redhat.com> wrote:

I don't really care on how its done, but IMO an enterprise class
database must be able to do log rotation. Logging to Syslog is not an
option (specially with our verbosity) -- users must be able to use flat
files for logging.

I never seem some many customer complaints and bug reports about a
single item like the ones we have received here about logging. I think
this should be the number 1 item in te TODO list.

Are you suggesting the that postgres project develop their own logger
rather than people just using one that has already been developed
by some other group?

Bruno Wolff III <bruno@wolff.to> writes:

Are you suggesting the that postgres project develop their own logger
rather than people just using one that has already been developed
by some other group?

The problem from the point of view of Red Hat is to not introduce a
dependency from the Postgres RPM to the Apache RPM ... this is no
problem for people who don't mind hand-customizing their setup, but
it is a problem if you want it to be part of the out-of-the-box setup.

regards, tom lane

Tom Lane wrote:

Bruno Wolff III <bruno@wolff.to> writes:

Are you suggesting the that postgres project develop their own logger
rather than people just using one that has already been developed
by some other group?

The problem from the point of view of Red Hat is to not introduce a
dependency from the Postgres RPM to the Apache RPM ... this is no
problem for people who don't mind hand-customizing their setup, but
it is a problem if you want it to be part of the out-of-the-box setup.

Did anything ever come from this thread?
http://archives.postgresql.org/pgsql-hackers/2003-05/msg00603.php
(Heading: "Plan B for log rotation support: borrow Apache code")

cheers

andrew

On Fri, Mar 12, 2004 at 13:17:50 -0500,
Tom Lane <tgl@sss.pgh.pa.us> wrote:

Bruno Wolff III <bruno@wolff.to> writes:

Are you suggesting the that postgres project develop their own logger
rather than people just using one that has already been developed
by some other group?

The problem from the point of view of Red Hat is to not introduce a
dependency from the Postgres RPM to the Apache RPM ... this is no
problem for people who don't mind hand-customizing their setup, but
it is a problem if you want it to be part of the out-of-the-box setup.

I can see their problem with making a dependency to all of apache or including
multilog in their distribution. But they probably could include something
that is only a logger either using some project that is only a logger or
splitting out the logger that is bundled with apache. Then it wouldn't
be unreasonable to make a dependency for postgres requiring that logging
rpm. Other services could also make use of this logging package as well.

On Friday 12 March 2004 09:24 am, Fernando Nasser wrote:

I don't really care on how its done, but IMO an enterprise class
database must be able to do log rotation. Logging to Syslog is not an
option (specially with our verbosity) -- users must be able to use flat
files for logging.

Uh, we have many many many different ways to use syslog. So my other message
on the topic.

I never seem some many customer complaints and bug reports about a
single item like the ones we have received here about logging. I think
this should be the number 1 item in te TODO list.

Uh, upgrading? I'm sure we have more reports about upgrading than logging.

But see my reply to bug 103767 for more.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
(828)862-5554
www.pari.edu

Bruno Wolff III wrote:

I can see their problem with making a dependency to all of apache or including
multilog in their distribution. But they probably could include something
that is only a logger either using some project that is only a logger or
splitting out the logger that is bundled with apache. Then it wouldn't
be unreasonable to make a dependency for postgres requiring that logging
rpm. Other services could also make use of this logging package as well.

Yes that would be nice. I have no idea how difficult it would be to extricate
the logrotate program from Apache. I also don't know if there would be any
license restrictions, would we be able to redistribute it as an independently
package? I don't know the answer.

Regards,
Fernando

Hi Lamar,

Lamar Owen wrote:

On Friday 12 March 2004 09:24 am, Fernando Nasser wrote:

I don't really care on how its done, but IMO an enterprise class
database must be able to do log rotation. Logging to Syslog is not an
option (specially with our verbosity) -- users must be able to use flat
files for logging.

Uh, we have many many many different ways to use syslog. So my other message
on the topic.

Which other message?

Anyway, Syslog is not an option for us. We need flat files.

I never seem some many customer complaints and bug reports about a
single item like the ones we have received here about logging. I think
this should be the number 1 item in te TODO list.

Uh, upgrading? I'm sure we have more reports about upgrading than logging.

Yeah, but that only comes with a full version upgrade :-)

The logging one keeps popping up as people try and use the server for production.

But see my reply to bug 103767 for more.

See my reply to your reply ;-)

Best regards,
Fernando

On Fri, Mar 12, 2004 at 15:19:29 -0500,
Fernando Nasser <fnasser@redhat.com> wrote:

Bruno Wolff III wrote:

I can see their problem with making a dependency to all of apache or
including
multilog in their distribution. But they probably could include something
that is only a logger either using some project that is only a logger or
splitting out the logger that is bundled with apache. Then it wouldn't
be unreasonable to make a dependency for postgres requiring that logging
rpm. Other services could also make use of this logging package as well.

Yes that would be nice. I have no idea how difficult it would be to
extricate the logrotate program from Apache. I also don't know if there
would be any license restrictions, would we be able to redistribute it as
an independently package? I don't know the answer.

I was suggesting this as something a distro maintainer (such as Redhat)
could do. I think that the postgres developers shouldn't be spending
time doing this. They should be just suggesting some possibilities
in the admin part of the documentation.

Andrew Dunstan <andrew@dunslane.net> writes:

Did anything ever come from this thread?
http://archives.postgresql.org/pgsql-hackers/2003-05/msg00603.php
(Heading: "Plan B for log rotation support: borrow Apache code")

Only an entry on my depressingly long personal to-do list :-(

I did take a look at the Apache rotator program, and found that it was
probably more trouble to adopt than it's worth. It seemed to depend on
a lot of configuration and library-routine infrastructure that we don't
share. (No big surprise; I suppose someone trying to pull out a random
bit of our backend code would be at least as unhappy.) I suspect it
would be less trouble, as well as legalistically cleaner, to write our
own from scratch.

Andrew Sullivan offered Afilias' rotator script awhile back also.
I think that works fine if you like a Perl script.

regards, tom lane

On Friday 12 March 2004 03:21 pm, Fernando Nasser wrote:

Lamar Owen wrote:

Uh, we have many many many different ways to use syslog. So my other
message on the topic.

Which other message?

The one I didn't post. :-)

Anyway, Syslog is not an option for us. We need flat files.

Ok, riddle me this:

If I have PostgreSQL set to log to syslog facility LOCAL0, and a local0.none
on /var/log/messages and local0.* to /var/log/pgsql (assuming only one
postmaster, unfortunately) then you get a flat file.

I can see that in a multipostmaster setting how you might want some
differentiation between postmasters, but ISTM that the tool reading these
logs should be trained in how to separate loglines out. I use a product
called SmoothWall as a firewall/VPN solution, and its log reporting modules
split out loglines in this way, so that I can have the ipsec logs in one
browser page and the l2tp logs elsewhere, and the ppp logs elsewhere, and the
kernel logs elsewhere....

Or you'll have to include some other log rotator.

Uh, upgrading? I'm sure we have more reports about upgrading than
logging.

Yeah, but that only comes with a full version upgrade :-)

Which is quite often. And people tend to upgrade as part of the OS upgrade,
which could easily be every other version (in the case of Fedora). Upgrading
from RHAS 2.1 to RHEL3 (I know it's not supported) should prove to be an
interesting one.

The logging one keeps popping up as people try and use the server for
production.

Yes, and for the vast majority of cases syslog is enough solution for the job.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
(828)862-5554
www.pari.edu

On Saturday 13 March 2004 09:36, Lamar Owen wrote:

On Friday 12 March 2004 03:21 pm, Fernando Nasser wrote:

Lamar Owen wrote:

Uh, we have many many many different ways to use syslog. So my other
message on the topic.

Which other message?

The one I didn't post. :-)

Anyway, Syslog is not an option for us. We need flat files.

Ok, riddle me this:

If I have PostgreSQL set to log to syslog facility LOCAL0, and a
local0.none on /var/log/messages and local0.* to /var/log/pgsql (assuming
only one postmaster, unfortunately) then you get a flat file.

I can see that in a multipostmaster setting how you might want some
differentiation between postmasters, but ISTM that the tool reading these
logs should be trained in how to separate loglines out. <snip>

Different postmasters = different conf files. Just set your syslog_facility
and/or your syslog_ident differently and it should be pretty easy to seperate
the logs. Actually, now that I have started using syslog fairly regularly, I
find it hard to believe it would be worth the effort to try to recreate a
logging facility as feature complete as syslog when we have syslog available.
ranks right up there with recreate cron, another feature many people think an
enterprise database "has to have". Personally I think in place upgrades are
far, far more important than either of those two, to both end users and to
the development community.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Bruno Wolff III wrote:

On Fri, Mar 12, 2004 at 15:19:29 -0500,
Fernando Nasser <fnasser@redhat.com> wrote:

Bruno Wolff III wrote:

I can see their problem with making a dependency to all of apache or
including
multilog in their distribution. But they probably could include something
that is only a logger either using some project that is only a logger or
splitting out the logger that is bundled with apache. Then it wouldn't
be unreasonable to make a dependency for postgres requiring that logging
rpm. Other services could also make use of this logging package as well.

Yes that would be nice. I have no idea how difficult it would be to
extricate the logrotate program from Apache. I also don't know if there
would be any license restrictions, would we be able to redistribute it as
an independently package? I don't know the answer.

I was suggesting this as something a distro maintainer (such as Redhat)
could do. I think that the postgres developers shouldn't be spending
time doing this. They should be just suggesting some possibilities
in the admin part of the documentation.

The distro maintainers should only pack proven solutions created by the
community. When they add man power to a project they must do it in the
community process, not in house.

A decent logging mechanism (that allows log rotation) is important to
postgres and should not require one to depend on a distro. They should
be able to get it from pgsql land.

It would be nice if there was a community recommended and community
tested logging solution, even if the instructions to set it up required
one to download and install some apache package.

But adding prodution quality logging to PostgreSQL is still a postgresql
community TODO and should not be delegated to the distros and thus
restricted to a distro only.

Regards,
Fernando

Lamar Owen wrote:

Anyway, Syslog is not an option for us. We need flat files.

Ok, riddle me this:

If I have PostgreSQL set to log to syslog facility LOCAL0, and a local0.none
on /var/log/messages and local0.* to /var/log/pgsql (assuming only one
postmaster, unfortunately) then you get a flat file.

The problem is that sysloging has more overhead than a plain append to a
file. There are some very strict response time AppServer applications
where we want to keep this things out of the picture.

The other problem is that we have some nice graphical tools for
configuring logging but the /etc/syslog.conf is a very hard one to
automate dur to the pattern matching. We can add some lines there, like
one to get local0 to a specific file, but it will probably be guesswork
and require human inspection anyway.

I can see that in a multipostmaster setting how you might want some
differentiation between postmasters, but ISTM that the tool reading these
logs should be trained in how to separate loglines out. I use a product
called SmoothWall as a firewall/VPN solution, and its log reporting modules
split out loglines in this way, so that I can have the ipsec logs in one
browser page and the l2tp logs elsewhere, and the ppp logs elsewhere, and the
kernel logs elsewhere....

It may be desirable to logrotate them at different times as well, so
they would have to be in different files.

Or you'll have to include some other log rotator.

That is what Tom is proposing.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

I can see that in a multipostmaster setting how you might want some
differentiation between postmasters, but ISTM that the tool reading these
logs should be trained in how to separate loglines out. <snip>

Different postmasters = different conf files. Just set your syslog_facility
and/or your syslog_ident differently and it should be pretty easy to seperate
the logs. Actually, now that I have started using syslog fairly regularly, I

Not that I'm volunteering, but I think the biggest issue is many users
simply don't know how to approach the problem. Some docs on using
syslog, cron, etc. with PostgreSQL to accomplish maintenace jobs would
probably be enough.

The only mention we have of syslog is related to the postgresql.conf
entries and their appropriate values.

http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-LOGGING-SYSLOG

Robert Treat wrote:

Different postmasters = different conf files. Just set your syslog_facility
and/or your syslog_ident differently and it should be pretty easy to seperate
the logs. Actually, now that I have started using syslog fairly regularly, I
find it hard to believe it would be worth the effort to try to recreate a
logging facility as feature complete as syslog when we have syslog available.
ranks right up there with recreate cron, another feature many people think an
enterprise database "has to have". Personally I think in place upgrades are
far, far more important than either of those two, to both end users and to
the development community.

The concerns with syslog were related to performance (additional load on
the server). Perhaps these concerns were unfounded.

I am not saying that upgrades are not important. I have been asking for
it and have even tried to come up with a process to keep a continuously
functional pg_upgrade program in synch with the cvs head development,
even adding bits to the catalog for one release to aloow the conversion
to be done. Ask Bruce and Tom, I've mentioned this to them 2 or 3 years ago.

But the log rotation is a relatively small task in comparison, and it is
at least as equaly visible in terms of production users (at least in
the enterprise). I am talking about benefit/effort ratio.

Regards,
Fernando

Rod Taylor wrote:

I can see that in a multipostmaster setting how you might want some
differentiation between postmasters, but ISTM that the tool reading these
logs should be trained in how to separate loglines out. <snip>

Different postmasters = different conf files. Just set your syslog_facility
and/or your syslog_ident differently and it should be pretty easy to seperate
the logs. Actually, now that I have started using syslog fairly regularly, I

Not that I'm volunteering, but I think the biggest issue is many users
simply don't know how to approach the problem. Some docs on using
syslog, cron, etc. with PostgreSQL to accomplish maintenace jobs would
probably be enough.

It is very easy to setup logging with the Control Center tool. Except
that it does not handle the /etc/syslog.conf bit (you have to add the
entry to get a specific LOCAL to a specific file by hand.

Maybe someone could make some measurements to desmistify the performance
impact of syslog. There is this generalized belief in IS departments
that should minimize it. Perhaps is an old fud that is not anylonger true.

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

On Sat, Mar 13, 2004 at 10:36:23AM -0500, Fernando Nasser wrote:

Lamar Owen wrote:

Ok, riddle me this:

If I have PostgreSQL set to log to syslog facility LOCAL0, and a
local0.none on /var/log/messages and local0.* to /var/log/pgsql (assuming
only one postmaster, unfortunately) then you get a flat file.

The problem is that sysloging has more overhead than a plain append to a
file. There are some very strict response time AppServer applications
where we want to keep this things out of the picture.

I thought it was an advantage to say "log to that box running syslog over
there and leave my disk alone" - what do you have in mind with "AppServer
applications" ?

It may be desirable to logrotate them at different times as well, so
they would have to be in different files.

syslogd with newsyslog, just like any other log file? I must be missing
something.. I don't see why postgresql is different..

Cheers,

Patrick