Home Explore Blog CI



postgresql

20th chunk of `doc/src/sgml/maintenance.sgml`
215f04e7158b27d2d8532bab0d66803abce63f37c27e27a80000000100000f2c
 /var/log/pgsql_log 86400
</programlisting>
  </para>

  <para>
   You can combine these approaches by setting up <application>logrotate</application>
   to collect log files produced by <productname>PostgreSQL</productname> built-in
   logging collector.  In this case, the logging collector defines the names and
   location of the log files, while <application>logrotate</application>
   periodically archives these files.  When initiating log rotation,
   <application>logrotate</application> must ensure that the application
   sends further output to the new file.  This is commonly done with a
   <literal>postrotate</literal> script that sends a <literal>SIGHUP</literal>
   signal to the application, which then reopens the log file.
   In <productname>PostgreSQL</productname>, you can run <command>pg_ctl</command>
   with the <literal>logrotate</literal> option instead.  When the server receives
   this command, the server either switches to a new log file or reopens the
   existing file, depending on the logging configuration
   (see <xref linkend="runtime-config-logging-where"/>).
  </para>

  <note>
   <para>
    When using static log file names, the server might fail to reopen the log
    file if the max open file limit is reached or a file table overflow occurs.
    In this case, log messages are sent to the old log file until a
    successful log rotation. If <application>logrotate</application> is
    configured to compress the log file and delete it, the server may lose
    the messages logged in this time frame. To avoid this issue, you can
    configure the logging collector to dynamically assign log file names
    and use a <literal>prerotate</literal> script to ignore open log files.
    </para>
  </note>

  <para>
   Another production-grade approach to managing log output is to
   send it to <application>syslog</application> and let
   <application>syslog</application> deal with file rotation. To do this, set the
   configuration parameter <varname>log_destination</varname> to <literal>syslog</literal>
   (to log to <application>syslog</application> only) in
   <filename>postgresql.conf</filename>. Then you can send a <literal>SIGHUP</literal>
   signal to the <application>syslog</application> daemon whenever you want to force it
   to start writing a new log file.  If you want to automate log
   rotation, the <application>logrotate</application> program can be
   configured to work with log files from
   <application>syslog</application>.
  </para>

  <para>
   On many systems, however, <application>syslog</application> is not very reliable,
   particularly with large log messages; it might truncate or drop messages
   just when you need them the most.  Also, on <productname>Linux</productname>,
   <application>syslog</application> will flush each message to disk, yielding poor
   performance.  (You can use a <quote><literal>-</literal></quote> at the start of the file name
   in the <application>syslog</application> configuration file to disable syncing.)
  </para>

  <para>
   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-useful 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.
  </para>

  <para>
   <ulink url="https://pgbadger.darold.net/"><productname>pgBadger</productname></ulink>
   is an external project that does sophisticated log file analysis.
   <ulink
   url="https://bucardo.org/check_postgres/"><productname>check_postgres</productname></ulink>
   provides Nagios alerts when important messages appear in the log
   files, as well as detection of many other extraordinary conditions.
  </para>
 </sect1>
</chapter>

Title: Managing Log Output
Summary
This section discusses various methods for managing log output in PostgreSQL, including using logrotate, syslog, and other external tools. It covers topics such as log file rotation, compression, and deletion, as well as potential issues like max open file limits and file table overflows. Additionally, it mentions external projects like pgBadger and check_postgres that can be used for log file analysis and monitoring.