Re: rotatelog / logrotate with PostgreSQL

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

"Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)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

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(at)center-f1(dot)ru(dot)
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)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(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Hello!

On 24 Sep 2002, Roland Roberts wrote:

> >>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> Tom> "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)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(at)center-f1(dot)ru(dot)
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

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(dot)Elphick(at)lfix(dot)co(dot)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

Roland Roberts <roland(at)astrofoto(dot)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

On Tue, Sep 24, 2002 at 02:09:01PM -0400, Tom Lane wrote:
> Roland Roberts <roland(at)astrofoto(dot)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(at)laokoon(dot)IN-Berlin(dot)DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

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&'

""Johnson, Shaunn"" <SJohnson6(at)bcbsm(dot)com> wrote in message
news:73309C2FDD95D11192E60008C7B1D5BB04C73F9B(at)snt452(dot)corp(dot)bcbsm(dot)com(dot)(dot)(dot)
> 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

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(at)bcbsm(dot)com> wrote in message
> news:73309C2FDD95D11192E60008C7B1D5BB04C73F9B(at)snt452(dot)corp(dot)bcbsm(dot)com(dot)(dot)(dot)
> > 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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

"scott.marlowe" <scott(dot)marlowe(at)ihs(dot)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

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(at)svana(dot)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.

"Martijn van Oosterhout" <kleptog(at)svana(dot)org> wrote in message
news:20020926004757(dot)GC5706(at)svana(dot)org(dot)(dot)(dot)
> 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

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.