<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Log File Maintenance</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Routine Database Maintenance Tasks" HREF="maintenance.html"><LINK REL="PREVIOUS" TITLE="Routine Reindexing" HREF="routine-reindex.html"><LINK REL="NEXT" TITLE="Backup and Restore" HREF="backup.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="routine-reindex.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="maintenance.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 21. Routine Database Maintenance Tasks</TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="maintenance.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="backup.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="LOGFILE-MAINTENANCE" >21.3. Log File Maintenance</A ></H1 ><A NAME="AEN21232" ></A ><P > It is a good idea to save the database server's log output somewhere, rather than just routing it to <TT CLASS="FILENAME" >/dev/null</TT >. The log output is invaluable when it comes time to diagnose problems. However, the log output tends to be voluminous (especially at higher debug levels) and you won't want to save it indefinitely. You need to <SPAN CLASS="QUOTE" >"rotate"</SPAN > the log files so that new log files are started and old ones removed after a reasonable period of time. </P ><P > If you simply direct the <SPAN CLASS="SYSTEMITEM" >stderr</SPAN > of the <TT CLASS="COMMAND" >postmaster</TT > into a file, you will have log output, but the only way to truncate the log file is to stop and restart the <TT CLASS="COMMAND" >postmaster</TT >. This may be OK if you are using <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > in a development environment, but few production servers would find this behavior acceptable. </P ><P > A better approach is to send the <TT CLASS="COMMAND" >postmaster</TT >'s <SPAN CLASS="SYSTEMITEM" >stderr</SPAN > output to some type of log rotation program. There is a built-in log rotation program, which you can use by setting the configuration parameter <TT CLASS="LITERAL" >redirect_stderr</TT > to <TT CLASS="LITERAL" >true</TT > in <TT CLASS="FILENAME" >postgresql.conf</TT >. The control parameters for this program are described in <A HREF="runtime-config.html#RUNTIME-CONFIG-LOGGING-WHERE" >Section 16.4.6.1</A >. </P ><P > Alternatively, you might prefer to use an external log rotation program, if you have one that you are already using with other server software. For example, the <SPAN CLASS="APPLICATION" >rotatelogs</SPAN > tool included in the <SPAN CLASS="PRODUCTNAME" >Apache</SPAN > distribution can be used with <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN >. To do this, just pipe the <TT CLASS="COMMAND" >postmaster</TT >'s <SPAN CLASS="SYSTEMITEM" >stderr</SPAN > output to the desired program. If you start the server with <TT CLASS="COMMAND" >pg_ctl</TT >, then <SPAN CLASS="SYSTEMITEM" >stderr</SPAN > is already redirected to <SPAN CLASS="SYSTEMITEM" >stdout</SPAN >, so you just need a pipe command, for example: </P><PRE CLASS="PROGRAMLISTING" >pg_ctl start | rotatelogs /var/log/pgsql_log 86400</PRE ><P> </P ><P > Another production-grade approach to managing log output is to send it all to <SPAN CLASS="APPLICATION" >syslog</SPAN > and let <SPAN CLASS="APPLICATION" >syslog</SPAN > deal with file rotation. To do this, set the configuration parameter <TT CLASS="LITERAL" >log_destination</TT > to <TT CLASS="LITERAL" >syslog</TT > (to log to <SPAN CLASS="APPLICATION" >syslog</SPAN > only) in <TT CLASS="FILENAME" >postgresql.conf</TT >. Then you can send a <TT CLASS="LITERAL" >SIGHUP</TT > signal to the <SPAN CLASS="APPLICATION" >syslog</SPAN > daemon whenever you want to force it to start writing a new log file. If you want to automate log rotation, the <SPAN CLASS="APPLICATION" >logrotate</SPAN > program can be configured to work with log files from <SPAN CLASS="APPLICATION" >syslog</SPAN >. </P ><P > On many systems, however, <SPAN CLASS="APPLICATION" >syslog</SPAN > is not very reliable, particularly with large log messages; it may truncate or drop messages just when you need them the most. Also, on <SPAN CLASS="PRODUCTNAME" >Linux</SPAN >, <SPAN CLASS="APPLICATION" >syslog</SPAN > will sync each message to disk, yielding poor performance. (You can use a <TT CLASS="LITERAL" >-</TT > at the start of the file name in the <SPAN CLASS="APPLICATION" >syslog</SPAN > configuration file to disable this behavior.) </P ><P > Note that all the solutions described above take care of starting new log files at configurable intervals, but they do not handle deletion of old, no-longer-interesting log files. You will probably want to set up a batch job to periodically delete old log files. Another possibility is to configure the rotation program so that old log files are overwritten cyclically. </P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="routine-reindex.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="backup.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Routine Reindexing</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="maintenance.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Backup and Restore</TD ></TR ></TABLE ></DIV ></BODY ></HTML >