codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>25.3. Log File Maintenance</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="routine-reindex.html" title="25.2. Routine Reindexing" /><link rel="next" href="backup.html" title="Chapter 26. Backup and Restore" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">25.3. Log File Maintenance</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="routine-reindex.html" title="25.2. Routine Reindexing">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="maintenance.html" title="Chapter 25. Routine Database Maintenance Tasks">Up</a></td><th width="60%" align="center">Chapter 25. Routine Database Maintenance Tasks</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="backup.html" title="Chapter 26. Backup and Restore">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGFILE-MAINTENANCE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">25.3. Log File Maintenance <a href="#LOGFILE-MAINTENANCE" class="id_link">#</a></h2></div></div></div><a id="id-1.6.12.12.2" class="indexterm"></a><p>3   It is a good idea to save the database server's log output4   somewhere, rather than just discarding it via <code class="filename">/dev/null</code>.5   The log output is invaluable when diagnosing6   problems.7  </p><div class="note"><h3 class="title">Note</h3><p>8    The server log can contain sensitive information and needs to be protected,9    no matter how or where it is stored, or the destination to which it is routed.10    For example, some DDL statements might contain plaintext passwords or other11    authentication details. Logged statements at the <code class="literal">ERROR</code>12    level might show the SQL source code for applications13    and might also contain some parts of data rows. Recording data, events and14    related information is the intended function of this facility, so this is15    not a leakage or a bug. Please ensure the server logs are visible only to16    appropriately authorized people.17   </p></div><p>18   Log output tends to be voluminous19   (especially at higher debug levels) so you won't want to save it20   indefinitely.  You need to <span class="emphasis"><em>rotate</em></span> the log files so that21   new log files are started and old ones removed after a reasonable22   period of time.23  </p><p>24   If you simply direct the <span class="systemitem">stderr</span> of25   <code class="command">postgres</code> into a26   file, you will have log output, but27   the only way to truncate the log file is to stop and restart28   the server. This might be acceptable if you are using29   <span class="productname">PostgreSQL</span> in a development environment,30   but few production servers would find this behavior acceptable.31  </p><p>32   A better approach is to send the server's33   <span class="systemitem">stderr</span> output to some type of log rotation program.34   There is a built-in log rotation facility, which you can use by35   setting the configuration parameter <code class="varname">logging_collector</code> to36   <code class="literal">true</code> in <code class="filename">postgresql.conf</code>.  The control37   parameters for this program are described in <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE" title="20.8.1. Where to Log">Section 20.8.1</a>. You can also use this approach38   to capture the log data in machine readable <acronym class="acronym">CSV</acronym>39   (comma-separated values) format.40  </p><p>41   Alternatively, you might prefer to use an external log rotation42   program if you have one that you are already using with other43   server software. For example, the <span class="application">rotatelogs</span>44   tool included in the <span class="productname">Apache</span> distribution45   can be used with <span class="productname">PostgreSQL</span>.  One way to46   do this is to pipe the server's47   <span class="systemitem">stderr</span> output to the desired program.48   If you start the server with49   <code class="command">pg_ctl</code>, then <span class="systemitem">stderr</span>50   is already redirected to <span class="systemitem">stdout</span>, so you just need a51   pipe command, for example:52 53</p><pre class="programlisting">54pg_ctl start | rotatelogs /var/log/pgsql_log 8640055</pre><p>56  </p><p>57   You can combine these approaches by setting up <span class="application">logrotate</span>58   to collect log files produced by <span class="productname">PostgreSQL</span> built-in59   logging collector.  In this case, the logging collector defines the names and60   location of the log files, while <span class="application">logrotate</span>61   periodically archives these files.  When initiating log rotation,62   <span class="application">logrotate</span> must ensure that the application63   sends further output to the new file.  This is commonly done with a64   <code class="literal">postrotate</code> script that sends a <code class="literal">SIGHUP</code>65   signal to the application, which then reopens the log file.66   In <span class="productname">PostgreSQL</span>, you can run <code class="command">pg_ctl</code>67   with the <code class="literal">logrotate</code> option instead.  When the server receives68   this command, the server either switches to a new log file or reopens the69   existing file, depending on the logging configuration70   (see <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE" title="20.8.1. Where to Log">Section 20.8.1</a>).71  </p><div class="note"><h3 class="title">Note</h3><p>72    When using static log file names, the server might fail to reopen the log73    file if the max open file limit is reached or a file table overflow occurs.74    In this case, log messages are sent to the old log file until a75    successful log rotation. If <span class="application">logrotate</span> is76    configured to compress the log file and delete it, the server may lose77    the messages logged in this time frame. To avoid this issue, you can78    configure the logging collector to dynamically assign log file names79    and use a <code class="literal">prerotate</code> script to ignore open log files.80    </p></div><p>81   Another production-grade approach to managing log output is to82   send it to <span class="application">syslog</span> and let83   <span class="application">syslog</span> deal with file rotation. To do this, set the84   configuration parameter <code class="varname">log_destination</code> to <code class="literal">syslog</code>85   (to log to <span class="application">syslog</span> only) in86   <code class="filename">postgresql.conf</code>. Then you can send a <code class="literal">SIGHUP</code>87   signal to the <span class="application">syslog</span> daemon whenever you want to force it88   to start writing a new log file.  If you want to automate log89   rotation, the <span class="application">logrotate</span> program can be90   configured to work with log files from91   <span class="application">syslog</span>.92  </p><p>93   On many systems, however, <span class="application">syslog</span> is not very reliable,94   particularly with large log messages; it might truncate or drop messages95   just when you need them the most.  Also, on <span class="productname">Linux</span>,96   <span class="application">syslog</span> will flush each message to disk, yielding poor97   performance.  (You can use a <span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span> at the start of the file name98   in the <span class="application">syslog</span> configuration file to disable syncing.)99  </p><p>100   Note that all the solutions described above take care of starting new101   log files at configurable intervals, but they do not handle deletion102   of old, no-longer-useful log files.  You will probably want to set103   up a batch job to periodically delete old log files.  Another possibility104   is to configure the rotation program so that old log files are overwritten105   cyclically.106  </p><p>107   <a class="ulink" href="https://pgbadger.darold.net/" target="_top"><span class="productname">pgBadger</span></a>108   is an external project that does sophisticated log file analysis.109   <a class="ulink" href="https://bucardo.org/check_postgres/" target="_top"><span class="productname">check_postgres</span></a>110   provides Nagios alerts when important messages appear in the log111   files, as well as detection of many other extraordinary conditions.112  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="routine-reindex.html" title="25.2. Routine Reindexing">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="maintenance.html" title="Chapter 25. Routine Database Maintenance Tasks">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="backup.html" title="Chapter 26. Backup and Restore">Next</a></td></tr><tr><td width="40%" align="left" valign="top">25.2. Routine Reindexing </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 26. Backup and Restore</td></tr></table></div></body></html>