Thread: Postgres server output logfile

Hi Everybody,

I am new to postgresql. I started my postgres using the command

postmaster -D /usr/local/pgsql/data > logfile 2>&1 &

Now after running it for some time I noticed that the size of this logfile has become very large. Is this logfile used to store any important information used by the database for recovery in case of crash? If no,is there any way I can specify a different log file withouting stopping the server ?

Any help is appreciated.

Thanks,

Tarun

Mintoo Lall <tlqmail@yahoo.com> writes:
> I am new to postgresql. I started my postgres using the command
> postmaster -D /usr/local/pgsql/data > logfile 2>&1 &

> Now after running it for some time I noticed that the size of this
> logfile has become very large. Is this logfile used to store any
> important information used by the database for recovery in case of
> crash?

No, it's only messages for human consumption.

> If no,is there any way I can specify a different log file
> withouting stopping the server ?

Unfortunately not.  The recommended procedure for production servers is
not to send the postmaster's stdout/stderr directly to a disk file, but
to pipe it into some script that rotates the output.  There's a usable
script in the Apache distribution, or you can roll your own with little
effort.  See the PG admin guide for more discussion (in the routine-
maintenance chapter).

            regards, tom lane

Tom Lane wrote:

> Unfortunately not.  The recommended procedure for production servers is
> not to send the postmaster's stdout/stderr directly to a disk file, but
> to pipe it into some script that rotates the output.  There's a usable
> script in the Apache distribution, or you can roll your own with little
> effort. ...

There was some interest in such logging programs a while back, and I
wrote this one:

    ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz

I've attached a message I sent out when I packaged that up.  I had no
time back then (2001, yikes) and didn't receive any comments or bug
reports.

If there is interest now, then I'll update the documentation, format
the code per the PostgreSQL coding standards and offer it again.

A patch to the postmaster so that it starts the log program and can
re-start it if someone kills it would improve robustness even further,
but 'kill -9' on the postmaster is bad, and so is 'kill -9' on the log
program. :-)

Regards,

Giles


Hi Peter,

> I've been playing with a little program I wrote whose sole purpose is to
> write its stdin to a file and close and reopen that file when it receives
> a signal.  I figured this could work well when integrated transparently
> into pg_ctl.
>
> So, is log rotation a concern?  Is this a reasonable solution?  Other
> ideas?

There was a discussion of this over a year ago.  After I contributed
to the discussion Tom Lane suggested I write something, and in the
tradition of software development I did so but never quite finished it
... I got to the point that it works for me, then got distracted
before completing a test suite.

You may well prefer your own code, but this one supports rotation on
size and/or time basis as well as on receipt of SIGHUP, and places a
timestamp on each line written. It's also pretty careful about errors,
which is one of the things that was disliked about the Apache program
last time it was discussed.

I am happy to contribute the code using the standard PostgreSQL
license if it's wanted.  (If anyone wants it under a BSD license or
GPL for another purpose that's fine too.)

I use the code on HP-UX, and developed it on NetBSD.  There shouldn't
be too many portability problems lurking, other than the usual hassles
of what % escape to use in printf() for off_t.  I doubt anyone wants
log files larger than a couple of GB anyway? :-)

ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz

> (No Grand Unified Logging Solutions please.  And no, "use syslog" doesn't
> count.)

<grin>

One improvement I suggest is that the postmaster be taught to start
(and restart if necessary) the log program.  This avoids fragile
startup scripts and also avoids taking down PostgreSQL if someone
sends the wrong signal to the log program.

Cheers,

Giles

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4326.1043965879.1@nemeton.com.au>

[ possible duplicate -- sorry folks if so! ]

Tom Lane wrote:

> Unfortunately not.  The recommended procedure for production servers is
> not to send the postmaster's stdout/stderr directly to a disk file, but
> to pipe it into some script that rotates the output.  There's a usable
> script in the Apache distribution, or you can roll your own with little
> effort. ...

There was some interest in such logging programs a while back, and I
wrote this one:

    ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz

I've attached a message I sent out when I packaged that up.  I had no
time back then (2001, yikes) and didn't receive any comments or bug
reports.

If there is interest now, then I'll update the documentation, format
the code per the PostgreSQL coding standards and offer it again.

A patch to the postmaster so that it starts the log program and can
re-start it if someone kills it would improve robustness even further,
but 'kill -9' on the postmaster is bad, and so is 'kill -9' on the log
program. :-)

Regards,

Giles


------- =_aaaaaaaaaa0
Content-Type: message/rfc822
Content-ID: <4326.1043965879.2@nemeton.com.au>

To: Peter Eisentraut <peter_e@gmx.net>
cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Log rotation?
In-Reply-To: Message from Peter Eisentraut <peter_e@gmx.net>
   of "Wed, 05 Sep 2001 03:12:28 +0200." <Pine.LNX.4.30.0109050253250.828-100000@peter.localdomain>
Date: Sat, 08 Sep 2001 07:21:19 +1000
Message-ID: <7707.999897679@nemeton.com.au>
From: Giles Lean <giles@nemeton.com.au>


Hi Peter,

> I've been playing with a little program I wrote whose sole purpose is to
> write its stdin to a file and close and reopen that file when it receives
> a signal.  I figured this could work well when integrated transparently
> into pg_ctl.
>
> So, is log rotation a concern?  Is this a reasonable solution?  Other
> ideas?

There was a discussion of this over a year ago.  After I contributed
to the discussion Tom Lane suggested I write something, and in the
tradition of software development I did so but never quite finished it
... I got to the point that it works for me, then got distracted
before completing a test suite.

You may well prefer your own code, but this one supports rotation on
size and/or time basis as well as on receipt of SIGHUP, and places a
timestamp on each line written. It's also pretty careful about errors,
which is one of the things that was disliked about the Apache program
last time it was discussed.

I am happy to contribute the code using the standard PostgreSQL
license if it's wanted.  (If anyone wants it under a BSD license or
GPL for another purpose that's fine too.)

I use the code on HP-UX, and developed it on NetBSD.  There shouldn't
be too many portability problems lurking, other than the usual hassles
of what % escape to use in printf() for off_t.  I doubt anyone wants
log files larger than a couple of GB anyway? :-)

ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz

> (No Grand Unified Logging Solutions please.  And no, "use syslog" doesn't
> count.)

<grin>

One improvement I suggest is that the postmaster be taught to start
(and restart if necessary) the log program.  This avoids fragile
startup scripts and also avoids taking down PostgreSQL if someone
sends the wrong signal to the log program.

Cheers,

Giles


------- =_aaaaaaaaaa0--

He is my very simple solution:

8<---------------------------
/*
 * log2file - send stdin to file opening it and closing it regularly
 */

#include <stdio.h>

FILE *  output;
char    buffer[ BUFSIZ];

int
main( int argc, char * argv[ ])
    {
    if ( argc != 2)
        {
        fprintf( stderr, "Usage: %s <output file>\n", argv[ 0]);
        exit( 1);
        }

    output = fopen(( const char *) argv[ 1], "a+");
    fclose( output);
    if ( output == NULL)
        {
        fprintf( stderr, "%s: cannot write to file: %s", argv[ 0], argv[
1]);
        exit( 2);
        }

    fclose( stderr);
    fclose( stdout);

    while ( ! feof( stdin))
        {
        fgets( buffer, sizeof( buffer), stdin);
        output = fopen(( const char *) argv[ 1], "a+");
        fputs( buffer, output);
        fclose( output);
        }
    exit( 0);
    }
8<------------------------------

In /etc/rc.d/init.d/postgresl

[...]
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -D $PGDATA -p
/usr/bin/postmaster start 2>&1 < /dev/null" |\
      /usr/local/bin/log2file /var/log/postgres &
[...]



Giles Lean wrote:
>
> Tom Lane wrote:
>
> > Unfortunately not.  The recommended procedure for production servers is
> > not to send the postmaster's stdout/stderr directly to a disk file, but
> > to pipe it into some script that rotates the output.  There's a usable
> > script in the Apache distribution, or you can roll your own with little
> > effort. ...
>
> There was some interest in such logging programs a while back, and I
> wrote this one:
>
>     ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz
>
> I've attached a message I sent out when I packaged that up.  I had no
> time back then (2001, yikes) and didn't receive any comments or bug
> reports.
>
> If there is interest now, then I'll update the documentation, format
> the code per the PostgreSQL coding standards and offer it again.
>
> A patch to the postmaster so that it starts the log program and can
> re-start it if someone kills it would improve robustness even further,
> but 'kill -9' on the postmaster is bad, and so is 'kill -9' on the log
> program. :-)
>
> Regards,
>
> Giles
>
>   ------------------------------------------------------------------------
>
> Subject: Re: [HACKERS] Log rotation?
> Date: Sat, 08 Sep 2001 07:21:19 +1000
> From: Giles Lean <giles@nemeton.com.au>
> To: Peter Eisentraut <peter_e@gmx.net>
> CC: PostgreSQL Development <pgsql-hackers@postgresql.org>
>
> Hi Peter,
>
> > I've been playing with a little program I wrote whose sole purpose is to
> > write its stdin to a file and close and reopen that file when it receives
> > a signal.  I figured this could work well when integrated transparently
> > into pg_ctl.
> >
> > So, is log rotation a concern?  Is this a reasonable solution?  Other
> > ideas?
>
> There was a discussion of this over a year ago.  After I contributed
> to the discussion Tom Lane suggested I write something, and in the
> tradition of software development I did so but never quite finished it
> ... I got to the point that it works for me, then got distracted
> before completing a test suite.
>
> You may well prefer your own code, but this one supports rotation on
> size and/or time basis as well as on receipt of SIGHUP, and places a
> timestamp on each line written. It's also pretty careful about errors,
> which is one of the things that was disliked about the Apache program
> last time it was discussed.
>
> I am happy to contribute the code using the standard PostgreSQL
> license if it's wanted.  (If anyone wants it under a BSD license or
> GPL for another purpose that's fine too.)
>
> I use the code on HP-UX, and developed it on NetBSD.  There shouldn't
> be too many portability problems lurking, other than the usual hassles
> of what % escape to use in printf() for off_t.  I doubt anyone wants
> log files larger than a couple of GB anyway? :-)
>
> ftp://ftp.nemeton.com.au/pub/src/logwrite-1.0alpha.tar.gz
>
> > (No Grand Unified Logging Solutions please.  And no, "use syslog" doesn't
> > count.)
>
> <grin>
>
> One improvement I suggest is that the postmaster be taught to start
> (and restart if necessary) the log program.  This avoids fragile
> startup scripts and also avoids taking down PostgreSQL if someone
> sends the wrong signal to the log program.
>
> Cheers,
>
> Giles
>
>   ------------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Jean-Luc Lachance <jllachan@nsd.ca> writes:

> He is my very simple solution:

One of the constraints on the code that I wrote was that it try to be
robust.  Having the database go down because the log writing program
exited due to a transiently full filesystem was thought to be
undesirable -- see Tom Lane's comments in the archives.

>     if ( output == NULL)
>         {
>         fprintf( stderr, "%s: cannot write to file: %s", argv[ 0], argv[
> 1]);
>         exit( 2);
>         }

The Apache rotatelogs program is similar, or was when I looked at it
last.  Perhaps they've worked on it recently.  (Perhaps I should offer
some improvements ... in my copious spare time. :-)

Regards,

Giles

On Tue, Feb 04, 2003 at 08:39:03 +1100,
  Giles Lean <giles@nemeton.com.au> wrote:
>
> Jean-Luc Lachance <jllachan@nsd.ca> writes:
>
> > He is my very simple solution:
>
> One of the constraints on the code that I wrote was that it try to be
> robust.  Having the database go down because the log writing program
> exited due to a transiently full filesystem was thought to be
> undesirable -- see Tom Lane's comments in the archives.

multilog will block instead of exit when a file system fills up. That
may or may not be better for you.