Thread: Postgres/Postmaster logging and log rotation
Hi there: We are running PostgreSQL 7.0.2 on RedHat Linux 6.2 and have encountered a few strange problems that continue to elude us - namely INSERT and DELETE functions that end abnormally, but do not include any information as to why (the JDBC connection just seems to have gone away). Trying to further diagnose the situation, I have been successful in starting the Postgres logging (by including the following in the postgresql 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. We also have trouble doing a simple restart - I wind up having to do a stop and then a start minutes later. Can anyone explain how to get Postgres to log and to be able to rotate the log periodically? Any information would be appreciated. Thanks, Bill James I/NET, Inc. Email: mailto:bjames@inetmi.com WWW: http://www.inetmi.com
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. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut wrote: > 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 The logrotate.d script included in the RPM is intended for a syslog-generated logfile. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Be sure to stop postgres, rotate your log (or move it out of the /var/log directory and then rotate it), and restart postgresql. That will solve your problem. Hopefully you don't need a 24x7 available database as this might take a few seconds for your server to complete. On Wed, 07 Mar 2001, William L James wrote: > Hi there: > > We are running PostgreSQL 7.0.2 on RedHat Linux 6.2 and > have encountered a few strange problems that continue to > elude us - namely INSERT and DELETE functions that end > abnormally, but do not include any information as to why (the > JDBC connection just seems to have gone away). Trying to > further diagnose the situation, I have been successful in starting > the Postgres logging (by including the following in the postgresql > 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. We also have trouble doing a simple restart - I wind up > having to do a stop and then a start minutes later. Can anyone > explain how to get Postgres to log and to be able to rotate the > log periodically? Any information would be appreciated. > > Thanks, > Bill James > I/NET, Inc. > Email: mailto:bjames@inetmi.com > WWW: http://www.inetmi.com > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl -- Thanks, Rodney Hampton veritime.com 248-528-9766 ----Your analogies DO NOT need to work as long as your code DOES.
Try the copytruncate directive in the logrotate configuration file. (See 'man logrotate'). This should allow it to workwithout restarting postgresql. But as the man file says, there is a small chance of losing some log entries. On Wed, Mar 07, 2001 at 10:37:30AM -0500, root wrote: > Be sure to stop postgres, rotate your log (or move it out of the /var/log > directory and then rotate it), and restart postgresql. That will solve your > problem. Hopefully you don't need a 24x7 available database as this might take > a few seconds for your server to complete. > > On Wed, 07 Mar 2001, William L James wrote: > > Hi there: > > > > We are running PostgreSQL 7.0.2 on RedHat Linux 6.2 and > > have encountered a few strange problems that continue to > > elude us - namely INSERT and DELETE functions that end > > abnormally, but do not include any information as to why (the > > JDBC connection just seems to have gone away). Trying to > > further diagnose the situation, I have been successful in starting > > the Postgres logging (by including the following in the postgresql > > 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. We also have trouble doing a simple restart - I wind up > > having to do a stop and then a start minutes later. Can anyone > > explain how to get Postgres to log and to be able to rotate the > > log periodically? Any information would be appreciated. > > > > Thanks, > > Bill James > -- > Thanks, > > > Rodney Hampton