Horton

Thread

  1. rotatelog / logrotate with PostgreSQL

    Johnson, Shaunn <sjohnson6@bcbsm.com> — 2002-09-23T14:51:14Z

    Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7.

Can someone send an example of how they have the 
logrotate / rotatelog script working with PostgreSQL?
I'm looking at a few things and I can't seem to
get it to work.

Also, is there an easy way to tell if PostgreSQL have
been configured with "--enable-syslog"?  If I have that,
then I can just use the syslog.conf and collect logs 
that way.

Thanks!

-X

  2. Re: rotatelog / logrotate with PostgreSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-23T15:15:55Z

    "Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> Can someone send an example of how they have the 
> logrotate / rotatelog script working with PostgreSQL?

Should work to just pipe the postmaster's stderr into it, eg

	postmaster ... 2>&1 | logrotate ...  &

> Also, is there an easy way to tell if PostgreSQL have
> been configured with "--enable-syslog"?

I think pg_config would tell you, but the most reliable way is to try to
set the postgresql.conf setting and see if the postmaster will take
it...

			regards, tom lane

  3. Re: rotatelog / logrotate with PostgreSQL

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-09-24T13:48:35Z

    Hello!

On Mon, 23 Sep 2002, Johnson, Shaunn wrote:

> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7.
>
> Can someone send an example of how they have the
> logrotate / rotatelog script working with PostgreSQL?
> I'm looking at a few things and I can't seem to
> get it to work.

As usually, nothing special (though we are not using vanilla RH 7.2,
just clone - ASPLinux). BTW, it is very useful to use logrotate
to do DB dumps on a regular basis. Hint.=) If there is an interest, I can
contribute the script to public.:)

>
> Also, is there an easy way to tell if PostgreSQL have
> been configured with "--enable-syslog"?  If I have that,

hack your pg.src.rpm, then rpm --rebuild&&rpm -U
enjoy. btw, maybe --enable-syslog is already there.

> then I can just use the syslog.conf and collect logs

Sure.

-- 
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

  4. Re: rotatelog / logrotate with PostgreSQL

    Roland Roberts <roland@astrofoto.org> — 2002-09-24T14:36:42Z

    >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> "Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
    >> Can someone send an example of how they have the 
    >> logrotate / rotatelog script working with PostgreSQL?

    Tom> Should work to just pipe the postmaster's stderr into it, eg

    Tom> 	postmaster ... 2>&1 | logrotate ...  &

Huh?  logrotate moves a log file, it doesn't log anything.

Does the postmaster close the log after writing?  logrotate can move
the log and create a new (empty) one, but if the postmaster has the
log open, it will continue to write to the old one, defeating the
purpose of rotating the log.  Also, if logrotate is configured to
compress the log after rotating, an open connection will result in the
tail of the file being "corrupted" since uncompressed data will be
appended.

So I'm curious too as to how it works; I haven't tried anything at
this point....

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

  5. Re: rotatelog / logrotate with PostgreSQL

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-09-24T15:05:06Z

    Hello!

On 24 Sep 2002, Roland Roberts wrote:

> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>     Tom> "Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
>     >> Can someone send an example of how they have the
>     >> logrotate / rotatelog script working with PostgreSQL?
>
>     Tom> Should work to just pipe the postmaster's stderr into it, eg
>
>     Tom> 	postmaster ... 2>&1 | logrotate ...  &
>
> Huh?  logrotate moves a log file, it doesn't log anything.

Agreed.=)

>
> Does the postmaster close the log after writing?  logrotate can move

IIRC it closes file only on HUP or so, don't wanna to seek in sources.
So if you don't use syslog, place killall -HUP `tail -n1 /pidpath/postmaster.pid`
to postrotate clause and better use delaycompress if any.

> the log and create a new (empty) one, but if the postmaster has the
> log open, it will continue to write to the old one, defeating the
> purpose of rotating the log.  Also, if logrotate is configured to
> compress the log after rotating, an open connection will result in the
> tail of the file being "corrupted" since uncompressed data will be
> appended.

Try this:

./configure blah-blah-blah --enable-syslog
make&&make install and so on

then

postgresql.conf:
# requires ENABLE_SYSLOG
syslog = 2 # range 0-2
syslog_facility = 'LOCAL2'
syslog_ident = 'postgres'

logrotate.d/postgres:
/path/postgres.log
{
    daily
    compress
    delaycompress
    rotate 10
    notifempty
    create 640 root postgres
}

syslogd.conf:
local2.*                                                /path/postgres.log

-- 
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

  6. Re: rotatelog / logrotate with PostgreSQL

    Oliver Elphick <olly@lfix.co.uk> — 2002-09-24T17:28:39Z

    On Tue, 2002-09-24 at 16:05, Yury Bokhoncovich wrote:
> > Does the postmaster close the log after writing?  logrotate can move
> 
> IIRC it closes file only on HUP or so, don't wanna to seek in sources.
> So if you don't use syslog, place killall -HUP `tail -n1 /pidpath/postmaster.pid`
> to postrotate clause and better use delaycompress if any.

> > the log and create a new (empty) one, but if the postmaster has the
> > log open, it will continue to write to the old one, defeating the
> > purpose of rotating the log.  Also, if logrotate is configured to
> > compress the log after rotating, an open connection will result in the
> > tail of the file being "corrupted" since uncompressed data will be
> > appended.

Use logrotate's copytruncate option to rotate an open file (when not
using syslog).  There is a small possibility of losing some log data in
between copying the log file and truncating it.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And we know that all things work together for good to 
      them that love God, to them who are the called  
      according to his purpose."            
                                   Romans 8:28

  7. Re: rotatelog / logrotate with PostgreSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-24T18:09:01Z

    Roland Roberts <roland@astrofoto.org> writes:
>     Tom> Should work to just pipe the postmaster's stderr into it, eg
>     Tom> 	postmaster ... 2>&1 | logrotate ...  &

> Huh?  logrotate moves a log file, it doesn't log anything.

My mistake; I was assuming that that script did something else.
What PG is able to work with is a script that absorbs input on
stdin and sends it to a rotatable logfile.  Surely there are some
of those out there.

> Does the postmaster close the log after writing?

If it did, how would it reopen it?  It doesn't know where its stderr
points...

			regards, tom lane

  8. Re: rotatelog / logrotate with PostgreSQL

    Thomas Beutin <tyrone@laokoon.in-berlin.de> — 2002-09-24T19:01:09Z

    On Tue, Sep 24, 2002 at 02:09:01PM -0400, Tom Lane wrote:
> Roland Roberts <roland@astrofoto.org> writes:
> >     Tom> Should work to just pipe the postmaster's stderr into it, eg
> >     Tom> 	postmaster ... 2>&1 | logrotate ...  &
> 
> > Huh?  logrotate moves a log file, it doesn't log anything.
> 
> My mistake; I was assuming that that script did something else.
> What PG is able to work with is a script that absorbs input on
> stdin and sends it to a rotatable logfile.  Surely there are some
> of those out there.
Are You looking for cronolog (http://www.cronolog.org/)? It sucks the output
of a daemon and manages the logfiles.

Greetings,
-tb
-- 
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

  9. Re: rotatelog / logrotate with PostgreSQL

    scott.marlowe <scott.marlowe@ihs.com> — 2002-09-24T20:52:11Z

    On Mon, 23 Sep 2002, Johnson, Shaunn wrote:

> Howdy:
> 
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7.
> 
> Can someone send an example of how they have the 
> logrotate / rotatelog script working with PostgreSQL?
> I'm looking at a few things and I can't seem to
> get it to work.
> 
> Also, is there an easy way to tell if PostgreSQL have
> been configured with "--enable-syslog"?  If I have that,
> then I can just use the syslog.conf and collect logs 
> that way.

My rc.local line looks something like this:

su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&'

  10. Re: rotatelog / logrotate with PostgreSQL

    Gaetano Mendola <mendola@bigfoot.com> — 2002-09-25T09:06:35Z

    ""Johnson, Shaunn"" <SJohnson6@bcbsm.com> wrote in message
news:73309C2FDD95D11192E60008C7B1D5BB04C73F9B@snt452.corp.bcbsm.com...
> This message is in MIME format. Since your mail reader does not understand
> this format, some or all of this message may not be legible.
>
> ------_=_NextPart_001_01C26310.AA038860
> Content-Type: text/plain;
> charset="iso-8859-1"
>
> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7.
>
> Can someone send an example of how they have the
> logrotate / rotatelog script working with PostgreSQL?
> I'm looking at a few things and I can't seem to
> get it to work.

Hi, I seen a lot of reply, but I don't understand why all
of us should crambling on the mirror for resolve a tipical
problem already well solved on Linux box?

Anyway how alread I suggested times ago the following
way ( why don't put this in the documentation or in
the FAQ ? ):


Insert in your postgresql.conf the following lines:

# File postgresql.conf
syslog = 2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

Create a file postgres under your  /etc/logrotate/postgres
( the path depend on your installation )

#File postgres
/var/log/postgresql.log {
    compress
    rotate 2
    size=10000k
    create 0664 postgres postgres
    daily
    postrotate
        /usr/bin/killall -HUP syslogd
    endscript
}

Take a look at the option that you need.

in the syslog.conf add the following lines:

# Save postgresql logs
LOCAL0.*   /var/log/postgresql.log


be sure that the file /var/log/postgresql.log is owned by
postgres.


Send a SIGHUP to postmaster and syslog after that setting.
For test if the logrotate is working well force a rotation:


$> logrotate -f /etc/logrotate/postgres


Ciao
Gaetano

  11. Re: rotatelog / logrotate with PostgreSQL

    scott.marlowe <scott.marlowe@ihs.com> — 2002-09-25T14:58:22Z

    Well, I've found the syslog facility to be noticeable slower than 
rotatelogs with pgsql doing its own logging.  So that's why I do it the 
rotatelogs way.  Choice is good.

On Wed, 25 Sep 2002, Gaetano Mendola wrote:

> ""Johnson, Shaunn"" <SJohnson6@bcbsm.com> wrote in message
> news:73309C2FDD95D11192E60008C7B1D5BB04C73F9B@snt452.corp.bcbsm.com...
> > This message is in MIME format. Since your mail reader does not understand
> > this format, some or all of this message may not be legible.
> >
> > ------_=_NextPart_001_01C26310.AA038860
> > Content-Type: text/plain;
> > charset="iso-8859-1"
> >
> > Howdy:
> >
> > Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7.
> >
> > Can someone send an example of how they have the
> > logrotate / rotatelog script working with PostgreSQL?
> > I'm looking at a few things and I can't seem to
> > get it to work.
> 
> Hi, I seen a lot of reply, but I don't understand why all
> of us should crambling on the mirror for resolve a tipical
> problem already well solved on Linux box?
> 
> Anyway how alread I suggested times ago the following
> way ( why don't put this in the documentation or in
> the FAQ ? ):
> 
> 
> Insert in your postgresql.conf the following lines:
> 
> # File postgresql.conf
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
> 
> Create a file postgres under your  /etc/logrotate/postgres
> ( the path depend on your installation )
> 
> #File postgres
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     create 0664 postgres postgres
>     daily
>     postrotate
>         /usr/bin/killall -HUP syslogd
>     endscript
> }
> 
> Take a look at the option that you need.
> 
> in the syslog.conf add the following lines:
> 
> # Save postgresql logs
> LOCAL0.*   /var/log/postgresql.log
> 
> 
> be sure that the file /var/log/postgresql.log is owned by
> postgres.
> 
> 
> Send a SIGHUP to postmaster and syslog after that setting.
> For test if the logrotate is working well force a rotation:
> 
> 
> $> logrotate -f /etc/logrotate/postgres
> 
> 
> Ciao
> Gaetano
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

  12. Re: rotatelog / logrotate with PostgreSQL

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-25T16:45:18Z

    "scott.marlowe" <scott.marlowe@ihs.com> writes:
> Well, I've found the syslog facility to be noticeable slower than 
> rotatelogs with pgsql doing its own logging.  So that's why I do it the 
> rotatelogs way.  Choice is good.

Other reasons: syslog is rumored to drop messages under sufficiently
heavy load (at least on some platforms); syslog inherently can't capture
all messages that might appear on stderr.  For example, on most
platforms a link failure in loading a dynamic library is going to be
reported by the dynamic linker on stderr --- we have no way to reroute
it to syslog.  If you're trying to debug a problem like "why doesn't
plperl work", those messages are priceless.

			regards, tom lane

  13. Re: rotatelog / logrotate with PostgreSQL

    Martijn van Oosterhout <kleptog@svana.org> — 2002-09-26T00:47:57Z

    On Wed, Sep 25, 2002 at 08:58:22AM -0600, scott.marlowe wrote:
> Well, I've found the syslog facility to be noticeable slower than 
> rotatelogs with pgsql doing its own logging.  So that's why I do it the 
> rotatelogs way.  Choice is good.

One thing to be careful of is to put a hyphen before the filename in the
syslog config. This will stop syslog doing a sync() after *every* message,
which quite noticably kills performance. Also make sure it's only logging to
the logs you want and not to multiple files.

Hope this helps,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

  14. Re: rotatelog / logrotate with PostgreSQL

    Gaetano Mendola <mendola@bigfoot.com> — 2002-09-26T08:07:15Z

    "Martijn van Oosterhout" <kleptog@svana.org> wrote in message
news:20020926004757.GC5706@svana.org...
> On Wed, Sep 25, 2002 at 08:58:22AM -0600, scott.marlowe wrote:
> > Well, I've found the syslog facility to be noticeable slower than
> > rotatelogs with pgsql doing its own logging.  So that's why I do it the
> > rotatelogs way.  Choice is good.
>
> One thing to be careful of is to put a hyphen before the filename in the
> syslog config. This will stop syslog doing a sync() after *every* message,
> which quite noticably kills performance. Also make sure it's only logging
to
> the logs you want and not to multiple files.

That's right, but I think that all here knew this, or not ?


Ciao
Gaetano

  15. Re: rotatelog / logrotate with PostgreSQL

    scott.marlowe <scott.marlowe@ihs.com> — 2002-09-26T14:38:00Z

    On Thu, 26 Sep 2002, Martijn van Oosterhout wrote:

> On Wed, Sep 25, 2002 at 08:58:22AM -0600, scott.marlowe wrote:
> > Well, I've found the syslog facility to be noticeable slower than 
> > rotatelogs with pgsql doing its own logging.  So that's why I do it the 
> > rotatelogs way.  Choice is good.
> 
> One thing to be careful of is to put a hyphen before the filename in the
> syslog config. This will stop syslog doing a sync() after *every* message,
> which quite noticably kills performance. Also make sure it's only logging to
> the logs you want and not to multiple files.

Thanks, I'll try that.