Обсуждение: HD is flooded by Error Log info

Hello

Recently an application endless loop crashed the PG server.
I am looking for the safest way  to limit the growth of the all the files in the “pg_log” directory,
While still  maintain reasonable period of log.

Any suggestions?

Our current setup is:

#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
redirect_stderr = on
log_line_prefix = '%m'
client_min_messages = error
log_min_messages = error

log_filename = 'CTMD-%d.log
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB


Thanks


Danny Abraham
BMC Software
CTM&D Business Unit
972-52-4286-513
danny_abraham@bmc.com

what i do is setup a cron job which runs daily or every second day, creates a tar.gz and copies them to a seperate location, and then another cron job which clears up archives more than one month old. That seems to be the way to go, i don't think you will find a postgresql.conf parameter which does that






--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

On Thu, 2007-11-29 at 09:53 -0600, Abraham, Danny wrote:

> Recently an application endless loop crashed the PG server.
>
> I am looking for the safest way  to limit the growth of the all the
> files in the “pg_log” directory,
>
> While still  maintain reasonable period of log.

Sounds like we should have these features

- pg_log file archiving when we reach a certain volume/number of logs

- ability to set limits on the number of messages a single backend can
emit within a certain time period.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

On Thu, Nov 29, 2007 at 04:20:30PM +0000, Simon Riggs wrote:
>
> - pg_log file archiving when we reach a certain volume/number of logs

IMO, by the time we get that complicated, admins need to start doing some
work.  That is, I think, work that should be nowhere near the main
PostgreSQL code.  If someone wants to set up a foundry project for nice
crontab suggestions and Windows scheduler suggestions (however that works),
that'd be rather another matter.

> - ability to set limits on the number of messages a single backend can
> emit within a certain time period.

Please, no.  That sounds like a way to suppress useful error messages when
something Really Bad is happening.  As it happens, that's when the flood of
messages is most important.

The general tone in this thread sounds like, "I don't have time to
administer this; help me."  Providing such help is a bad idea.

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

As far as automatic archiving is concerned the easiest would be to provide something similar to "archive_command" , however i can't imagine how the second suggestion could be useful, that sounds like pretty dangerous to me.







--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Andrew Sullivan wrote:
> On Thu, Nov 29, 2007 at 04:20:30PM +0000, Simon Riggs wrote:
>> - pg_log file archiving when we reach a certain volume/number of logs
>
> IMO, by the time we get that complicated, admins need to start doing some
> work.  That is, I think, work that should be nowhere near the main
> PostgreSQL code.  If someone wants to set up a foundry project for nice
> crontab suggestions and Windows scheduler suggestions (however that works),
> that'd be rather another matter.

Surely this is what logrotate and syslog-ng etc are for. This is a
sysadmin problem not a DBA problem - any service can generate a lot of logs.

--
   Richard Huxton
   Archonet Ltd

On Thu, Nov 29, 2007 at 04:50:45PM +0000, Richard Huxton wrote:
> Surely this is what logrotate and syslog-ng etc are for. This is a
> sysadmin problem not a DBA problem - any service can generate a lot of logs.

Yes.  But some have complained those things aren't Postgres-centric enough.
I don't see how, but some have argued strongly.  So I say, if there's a
demand, I've no objection to an add-on product (which could be especially
relevant if we get something like CPgAN); but not anything like additional
extensions to the existing logging infrastructure (what is there is nice,
because it provides things that the poastmaster alone may know; but
additions would be gilding the lily, except with pyrites).

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 29 Nov 2007 11:58:36 -0500
Andrew Sullivan <ajs@crankycanuck.ca> wrote:

> On Thu, Nov 29, 2007 at 04:50:45PM +0000, Richard Huxton wrote:
> > Surely this is what logrotate and syslog-ng etc are for. This is a 
> > sysadmin problem not a DBA problem - any service can generate a lot
> > of logs.
> 
> Yes.  But some have complained those things aren't Postgres-centric
> enough. I don't see how, but some have argued strongly.  So I say, if
> there's a demand, I've no objection to an add-on product (which could
> be especially relevant if we get something like CPgAN); but not
> anything like additional extensions to the existing logging
> infrastructure (what is there is nice, because it provides things
> that the poastmaster alone may know; but additions would be gilding
> the lily, except with pyrites).

The only argument I have ever heard that seemed like a realistic issue
with using logrotate or syslog was tgl's. You can loose data in the log
when rotating if you aren't using the PostgreSQL logging facility.

Other than that I think our current logging situation is a bit
ridiculous. [1]

Joshua D. Drake

[1]
http://www.commandprompt.com/blogs/joshua_drake/2007/11/postgresql_logging_time_for_a_change/


> 
> A
> 


- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTvEeATb/zqfZUUQRAn5gAKCEVbnJ3AND8uoxOyH10wMamgg76wCfUO9m
wKMFNGEyH25TFgAQe2xTuu8=
=DAHh
-----END PGP SIGNATURE-----

Andrew Sullivan <ajs@crankycanuck.ca> writes:
> The general tone in this thread sounds like, "I don't have time to
> administer this; help me."  Providing such help is a bad idea.

Actually, can't it be solved easily enough with suitable configuration
of the existing logging parameters?  Just set things up so that log
files are recycled fairly quickly.

            regards, tom lane

On Thu, Nov 29, 2007 at 12:48:35PM -0500, Tom Lane wrote:
> Andrew Sullivan <ajs@crankycanuck.ca> writes:
> > The general tone in this thread sounds like, "I don't have time to
> > administer this; help me."  Providing such help is a bad idea.
>
> Actually, can't it be solved easily enough with suitable configuration
> of the existing logging parameters?  Just set things up so that log
> files are recycled fairly quickly.

If recycling is allowed, yes.  I had the impression that some weren't
willing to do the recycle bits, though.

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke