Обсуждение: Postgres server output log

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

This is someones' elses' posting that i have preserved
shud be useful.

regds
mallah.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

Well,
the most correct way to do a logrotate is ( Redhat ):

1) Put on your postgresql.conf the following lines:

syslog = 2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

2)  Put on the directory /etc/logrotate.d a file called
'postgres' with the following lines:

/var/log/postgresql.log {
    compress
    rotate 2
    size=10000k
    errors mendola@bigfoot.com
    create 0664 postgres postgres
    daily
    postrotate
             /usr/bin/killall -HUP syslogd
    endscript
}

change the email address of course :-)

3) Put the following line on your /etc/syslog.conf

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


Ciao
Gaetano

-------------------------------------------------------


On Thursday 30 January 2003 10:21 am, Mintoo Lall wrote:
> 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
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now

--


--------------------------------------------
                                Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

On Thu, 2003-01-30 at 05:22, Rajesh Kumar Mallah. wrote:
>
> This is someones' elses' posting that i have preserved
> shud be useful.
...
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
>
> Well,
> the most correct way to do a logrotate is ( Redhat ):
...
> 2)  Put on the directory /etc/logrotate.d a file called
> 'postgres' with the following lines:
>
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     errors mendola@bigfoot.com
>     create 0664 postgres postgres
>     daily
>     postrotate
>              /usr/bin/killall -HUP syslogd
>     endscript
> }
...

This won't work as it stands with PostgreSQL, because the hangup signal
does not make the postmaster reopen the log file.  Instead of that
postrotate section, you need to use logrotate's copytruncate option,
which will copy the logfile to another file before zeroing the logfile.

Depending on what is being logged, you may want to change the
permissions to 660; there may be stuff in the logs that you don't want
to reveal to the world.  (With copytruncate, I think the create option
applies to the copy files rather than to the original logfile.)

--
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
                 ========================================
     "The Lord knoweth how to deliver the godly out of
      temptations, and to reserve the unjust unto the day of
      judgment to be punished;"            II Peter 2:9

Hmm.. thank you for pointing out!.

i have marked a Cc to  Gaetano Mendola [ the original sinner ;-) ]

regds
mallah.

On Thursday 30 January 2003 06:10 pm, Oliver Elphick wrote:
> On Thu, 2003-01-30 at 05:22, Rajesh Kumar Mallah. wrote:
> > This is someones' elses' posting that i have preserved
> > shud be useful.
>
> ...
>
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
> >
> > Well,
> > the most correct way to do a logrotate is ( Redhat ):
>
> ...
>
> > 2)  Put on the directory /etc/logrotate.d a file called
> > 'postgres' with the following lines:
> >
> > /var/log/postgresql.log {
> >     compress
> >     rotate 2
> >     size=10000k
> >     errors mendola@bigfoot.com
> >     create 0664 postgres postgres
> >     daily
> >     postrotate
> >              /usr/bin/killall -HUP syslogd
> >     endscript
> > }
>
> ...
>

> This won't work as it stands with PostgreSQL, because the hangup signal
> does not make the postmaster reopen the log file.  Instead of that
> postrotate section, you need to use logrotate's copytruncate option,
> which will copy the logfile to another file before zeroing the logfile.
>
> Depending on what is being logged, you may want to change the
> permissions to 660; there may be stuff in the logs that you don't want
> to reveal to the world.  (With copytruncate, I think the create option
> applies to the copy files rather than to the original logfile.)

--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

This is OK. The script isn't HUPping PostgreSQL, it is HUPping syslog.

I think configuring PostgreSQL to write to syslog is the correct way to go.

Once you decide to route the messages to syslogy you get everything that
syslog offers such as:
directing output to files, pipes or central log servers (in any combination)
selectively dropping or writing messages based on severity
many ready-made management tools like logrotate
many ready-made monitoring tools like swatch and logwatch

You can even choose alternate versions of the syslog daemon - I have one
server runing msyslogd (Modular Syslog Daemon) with the PostgreSQL module
installed so I can write syslog entries for a project directly into a
PostgreSQL database for retrieval or analysis.

So my advice is to complie/configure PostgreSQL to use syslog and use the
myriad of available syslog tools for management and analysis.

Cheers,
Steve



On Thursday 30 January 2003 4:40 am, Oliver Elphick wrote:
> On Thu, 2003-01-30 at 05:22, Rajesh Kumar Mallah. wrote:
> > This is someones' elses' posting that i have preserved
> > shud be useful.
>
> ...
>
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
> >
> > Well,
> > the most correct way to do a logrotate is ( Redhat ):
>
> ...
>
> > 2)  Put on the directory /etc/logrotate.d a file called
> > 'postgres' with the following lines:
> >
> > /var/log/postgresql.log {
> >     compress
> >     rotate 2
> >     size=10000k
> >     errors mendola@bigfoot.com
> >     create 0664 postgres postgres
> >     daily
> >     postrotate
> >              /usr/bin/killall -HUP syslogd
> >     endscript
> > }
>
> ...
>
> This won't work as it stands with PostgreSQL, because the hangup signal
> does not make the postmaster reopen the log file.  Instead of that
> postrotate section, you need to use logrotate's copytruncate option,
> which will copy the logfile to another file before zeroing the logfile.
>
> Depending on what is being logged, you may want to change the
> permissions to 660; there may be stuff in the logs that you don't want
> to reveal to the world.  (With copytruncate, I think the create option
> applies to the copy files rather than to the original logfile.)

On Thu, 2003-01-30 at 17:10, Steve Crawford wrote:
> This is OK. The script isn't HUPping PostgreSQL, it is HUPping syslog.

Yes; I assumed this was a template to be adapted to PostgreSQL.  My
comments assume that logrotate is managing the file specified by -l to
the postmaster.

--
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
                 ========================================
     "The Lord knoweth how to deliver the godly out of
      temptations, and to reserve the unjust unto the day of
      judgment to be punished;"            II Peter 2:9

Steve Crawford <scrawford@pinpointresearch.com> writes:
> I think configuring PostgreSQL to write to syslog is the correct way to go.

syslog is a good alternative, but not perfect.  I know of two strikes
against it:

1. It can't catch everything that could go to a stderr log.  While I
think we've cleaned up all the backend code (there used to be lots of
random fprintf's), there are messages that are not under our control.
In particular, on most platforms any dynamic-linker error messages go
to stderr --- when you're trying to debug why the backend can't load
a .so file, this info is invaluable, but syslog can't capture it.

2. On some platforms, syslog is reputed to drop or truncate messages
under heavy load.

You have to figure out whether the many advantages of syslog outweigh
these problems for your use.

            regards, tom lane

Bernstein's multilog program (part of the daemontools package) may be
useful. It is designed to read input from standard input and will
do log rotation. Documentation is available at:
http://cr.yp.to/daemontools/multilog.html

Thanks guys for the replies.

I am using redhat linux 8.0 and postgres 7.2.3. I
started my postgresql server with the following
command after making the changes
pg_ctl start -D /usr/local/pgsql/data   -o "-i"

If I understood correctly the syslog should have
created a log file at /var/log/postgresql.log but it
didnt do it. I am not sure what is the correct way of
starting the postgres after making the changes so as
to use the syslog and the log rotation script. Any
help is appreciated.

Thanks,
Tarun


--- "Rajesh Kumar Mallah." <mallah@trade-india.com>
wrote:
>
>
>
> This is someones' elses' posting that i have
> preserved
> shud be useful.
>
> regds
> mallah.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
>
> Well,
> the most correct way to do a logrotate is ( Redhat
> ):
>
> 1) Put on your postgresql.conf the following lines:
>
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>
> 2)  Put on the directory /etc/logrotate.d a file
> called
> 'postgres' with the following lines:
>
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     errors mendola@bigfoot.com
>     create 0664 postgres postgres
>     daily
>     postrotate
>              /usr/bin/killall -HUP syslogd
>     endscript
> }
>
> change the email address of course :-)
>
> 3) Put the following line on your /etc/syslog.conf
>
> # Save postgresql logs
> LOCAL0.*
> /var/log/postgresql.log
>
>
> Ciao
> Gaetano
>
>
-------------------------------------------------------
>
>
> On Thursday 30 January 2003 10:21 am, Mintoo Lall
> wrote:
> > 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
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now
>
> --
>
>
> --------------------------------------------
>                                 Regds Mallah
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)26152172 (221) (L) 9811255597 (M)
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

Thanks guys for the replies.

I am using redhat linux 8.0 and postgres 7.2.3. I
started my postgresql server with the following
command after making the changes 
pg_ctl start -D /usr/local/pgsql/data   -o "-i"

If I understood correctly the syslog should have
created a log file at /var/log/postgresql.log but it
didnt do it. I am not sure what is the correct way of
starting the postgres after making the changes so as
to use the syslog and the log rotation script. Any
help is appreciated.

Thanks,
Tarun


--- "Rajesh Kumar Mallah." <mallah@trade-india.com>
wrote:
> 
> 
> 
> This is someones' elses' posting that i have
> preserved
> shud be useful.
> 
> regds
> mallah.
> 
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
> 
> Well,
> the most correct way to do a logrotate is ( Redhat
> ):
> 
> 1) Put on your postgresql.conf the following lines:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
> 
> 2)  Put on the directory /etc/logrotate.d a file
> called
> 'postgres' with the following lines:
> 
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     errors mendola@bigfoot.com
>     create 0664 postgres postgres
>     daily
>     postrotate
>              /usr/bin/killall -HUP syslogd
>     endscript
> }
> 
> change the email address of course :-)
> 
> 3) Put the following line on your /etc/syslog.conf
> 
> # Save postgresql logs
> LOCAL0.*
> /var/log/postgresql.log
> 
> 
> Ciao
> Gaetano
> 
>
-------------------------------------------------------
> 
> 
> On Thursday 30 January 2003 10:21 am, Mintoo Lall
> wrote:
> > 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
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now
> 
> -- 
> 
> 
> --------------------------------------------
>                                 Regds Mallah
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)26152172 (221) (L) 9811255597 (M)
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
> 
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of 
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thanks guys for the replies.

I am using redhat linux 8.0 and postgres 7.2.3. I
started my postgresql server with the following
command after making the changes 
pg_ctl start -D /usr/local/pgsql/data   -o "-i"

If I understood correctly the syslog should have
created a log file at /var/log/postgresql.log but it
didnt do it. I am not sure what is the correct way of
starting the postgres after making the changes so as
to use the syslog and the log rotation script. Any
help is appreciated.

Thanks,
Tarun


--- "Rajesh Kumar Mallah." <mallah@trade-india.com>
wrote:
> 
> 
> 
> This is someones' elses' posting that i have
> preserved
> shud be useful.
> 
> regds
> mallah.
> 
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
> 
> Well,
> the most correct way to do a logrotate is ( Redhat
> ):
> 
> 1) Put on your postgresql.conf the following lines:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
> 
> 2)  Put on the directory /etc/logrotate.d a file
> called
> 'postgres' with the following lines:
> 
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     errors mendola@bigfoot.com
>     create 0664 postgres postgres
>     daily
>     postrotate
>              /usr/bin/killall -HUP syslogd
>     endscript
> }
> 
> change the email address of course :-)
> 
> 3) Put the following line on your /etc/syslog.conf
> 
> # Save postgresql logs
> LOCAL0.*
> /var/log/postgresql.log
> 
> 
> Ciao
> Gaetano
> 
>
-------------------------------------------------------
> 
> 
> On Thursday 30 January 2003 10:21 am, Mintoo Lall
> wrote:
> > 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
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up
> now
> 
> -- 
> 
> 
> --------------------------------------------
>                                 Regds Mallah
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)26152172 (221) (L) 9811255597 (M)
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
> 
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of 
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Is all this stuff in our docs?

---------------------------------------------------------------------------

Rajesh Kumar Mallah. wrote:
>
>
>
> This is someones' elses' posting that i have preserved
> shud be useful.
>
> regds
> mallah.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
>
> Well,
> the most correct way to do a logrotate is ( Redhat ):
>
> 1) Put on your postgresql.conf the following lines:
>
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>
> 2)  Put on the directory /etc/logrotate.d a file called
> 'postgres' with the following lines:
>
> /var/log/postgresql.log {
>     compress
>     rotate 2
>     size=10000k
>     errors mendola@bigfoot.com
>     create 0664 postgres postgres
>     daily
>     postrotate
>              /usr/bin/killall -HUP syslogd
>     endscript
> }
>
> change the email address of course :-)
>
> 3) Put the following line on your /etc/syslog.conf
>
> # Save postgresql logs
> LOCAL0.*
> /var/log/postgresql.log
>
>
> Ciao
> Gaetano
>
> -------------------------------------------------------
>
>
> On Thursday 30 January 2003 10:21 am, Mintoo Lall wrote:
> > 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
> >
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now
>
> --
>
>
> --------------------------------------------
>                                 Regds Mallah
> Rajesh Kumar Mallah,
> Project Manager (Development)
> Infocom Network Limited, New Delhi
> phone: +91(11)26152172 (221) (L) 9811255597 (M)
> Visit http://www.trade-india.com ,
> India's Leading B2B eMarketplace.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian writes:

> Is all this stuff in our docs?

At least I didn't find it, while I was searching for "how to logrotate the
postmaster logfile properly" last week (IIRC only the "pipe postmasters
output into a logrotate script" possibility is discussed, not the way like 2)
below). Finally I had a look how SuSE does it and it's very similar to 2)
shown below (but they have the "copytruncate" option).

Best regards,

    Tilo

>
> ---------------------------------------------------------------------------
>
> Rajesh Kumar Mallah. wrote:
> > This is someones' elses' posting that i have preserved
> > shud be useful.
> >
> > regds
> > mallah.
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
> >
> > Well,
> > the most correct way to do a logrotate is ( Redhat ):
> >
> > 1) Put on your postgresql.conf the following lines:
> >
> > syslog = 2
> > syslog_facility = 'LOCAL0'
> > syslog_ident = 'postgres'
> >
> > 2)  Put on the directory /etc/logrotate.d a file called
> > 'postgres' with the following lines:
> >
> > /var/log/postgresql.log {
> >     compress
> >     rotate 2
> >     size=10000k
> >     errors mendola@bigfoot.com
> >     create 0664 postgres postgres
> >     daily
> >     postrotate
> >              /usr/bin/killall -HUP syslogd
> >     endscript
> > }
> >
> > change the email address of course :-)
> >
> > 3) Put the following line on your /etc/syslog.conf
> >
> > # Save postgresql logs
> > LOCAL0.*
> > /var/log/postgresql.log
> >
> >
> > Ciao
> > Gaetano
> >
> > -------------------------------------------------------
> >
> > On Thursday 30 January 2003 10:21 am, Mintoo Lall wrote:
> > > 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
> > >
> > >
> > >
> > > ---------------------------------
> > > Do you Yahoo!?
> > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now
> >
> > --
> >
> >
> > --------------------------------------------
> >                                 Regds Mallah
> > Rajesh Kumar Mallah,
> > Project Manager (Development)
> > Infocom Network Limited, New Delhi
> > phone: +91(11)26152172 (221) (L) 9811255597 (M)
> > Visit http://www.trade-india.com ,
> > India's Leading B2B eMarketplace.
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org