Re(2): Postgres/Postmaster logging and log rotation - Mailing list pgsql-admin

peter_e@gmx.net writes:
>William L James writes:
>
>> script - su -l postgresql -c "/usr/bin/pg_ctl -D $PGDATA -p
>> /usr/bin/postmaster start >>/var/log/postgresql 2>&1"), but
>> when the weekly logrotate.d function kicks in, Postgres logging
>> stops.
>
>The logging probably continues to go to the rotated-away file.  The
>typical log rotation cron job presumes that the daemons close the log
>files periodically.  This is not the case with PostgreSQL.  A good
>solution is to pipe your log output to a small program that does the log
>rotating (or simply closes the log file once in a while), like rotatelogs
>from Apache.

I also have this problem in our Posgresql 6.5.3 (too old) server.
Actually we have already migrated to 7.0.3 but there are still programs
that uses the old one.

In 7.0.3, it was no problem. I just set pg_option to
   verbose=2
   query=2
   syslog=1
and added
    local0.*                                        /var/log/postgresql
in syslog.conf.

In 6.5.3, I tried your suggestion.
I put this in /etc/rc.d/init.d/postgresql:
 su -l postgres -c '/usr/bin/postmaster -B 2048 -o "-S 1024" -i -D
/var/lib/pgsql -d 2 | /root/logger.pl >/dev/null 2>&1 &'
and added
    local0.*                                        /var/log/postgresql
in syslog.conf.

It is working but aside from writing to /var/log/postgresql it is also
writing to the console where the portmaster was started.
And what I can't  understand is that what was written in the console is
different from the log file.
The writings to the console was something like this:

% (/dev/pts/4) /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Checking postgresql installation: looks good!
Starting postgresql service: FindExec: found "/usr/bin/postgres" using
argv[0]
binding ShmemCreate(key=52e2c1, size=17994752)
postmaster [25489]
/usr/bin/postmaster: ServerLoop:         handling reading 5
/usr/bin/postmaster: ServerLoop:                handling reading 5
/usr/bin/postmaster: ServerLoop:                handling writing 5
/usr/bin/postmaster: BackendStartup: pid 25530 user root db testing socket
5
/usr/bin/postmaster child[25530]: starting with (/usr/bin/postgres -d2 -B
2048 -S 1024 -v131072 -p testing )

and in /var/log/postgresql was something like this:

Mar  8 21:08:36 tae postgres[27378]: StartTransactionCommand
Mar  8 21:08:36 tae postgres[27378]: query: SELECT * FROM office
Mar  8 21:08:36 tae postgres[27378]: ProcessQuery
Mar  8 21:08:36 tae postgres[27378]: CommitTransactionCommand
Mar  8 21:08:36 tae postgres[27378]: proc_exit(0) [#0]
Mar  8 21:08:36 tae postgres[27378]: shmem_exit(0) [#0]
Mar  8 21:08:36 tae postgres[27378]: exit(0)

Can anybody tell me what's going on here? Thanks.

Anyway here's the logger.pl (taken from Cute tricks with Perl & Apache)
   note: I forgot the whole url where I got it and I also modified it a
little bit

#!/usr/bin/perl
use Sys::Syslog;

$SERVER_NAME = shift || 'postgres';
$FACILITY = 'local0';
$PRIORITY = 'debug';

Sys::Syslog::setlogsock('unix');
openlog ($SERVER_NAME,'ndelay,pid',$FACILITY);
while (<>) {
  chomp;
  syslog($PRIORITY,$_);
}
closelog;