Обсуждение: Re(2): Postgres/Postmaster logging and log rotation
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;
Thank you anyway.
I have solve my problem in 6.5.3 (too old server) by
creating pg_options
verbose=2
query=1
syslog=0
hostlookup
showportnumber
I
I set syslog=0 'coz I can't make it log to /var/log/postgresql.
I removed "-d 2" in /etc/rc.d/init.d/postgresql:
su -l postgres -c '/usr/bin/postmaster -B 2048 -o "-S 1024" -i -D
/var/lib/pgsql | /root/logger.pl >/dev/null 2>&1 &'
and retained
local0.* /var/log/postgresql
in syslog.conf.
>
Sherwin
pgsql-admin@fc.emc.com.ph writes:
>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;
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)