<!-- doc/src/sgml/maintenance.sgml -->

<chapter id="maintenance">
 <title>Routine Database Maintenance Tasks</title>

 <indexterm zone="maintenance">
  <primary>maintenance</primary>
 </indexterm>

 <indexterm zone="maintenance">
  <primary>routine maintenance</primary>
 </indexterm>

  <para>
   <productname>PostgreSQL</productname>, like any database software, requires that certain tasks
   be performed regularly to achieve optimum performance. The tasks
   discussed here are <emphasis>required</emphasis>, but they
   are repetitive in nature and can easily be automated using standard
   tools such as <application>cron</application> scripts or
   Windows' <application>Task Scheduler</application>.  It is the database
   administrator's responsibility to set up appropriate scripts, and to
   check that they execute successfully.
  </para>

  <para>
   One obvious maintenance task is the creation of backup copies of the data on a
   regular schedule.  Without a recent backup, you have no chance of recovery
   after a catastrophe (disk failure, fire, mistakenly dropping a critical
   table, etc.).  The backup and recovery mechanisms available in
   <productname>PostgreSQL</productname> are discussed at length in
   <xref linkend="backup"/>.
  </para>

  <para>
   The other main category of maintenance task is periodic <quote>vacuuming</quote>
   of the database.  This activity is discussed in
   <xref linkend="routine-vacuuming"/>.  Closely related to this is updating
   the statistics that will be used by the query planner, as discussed in
   <xref linkend="vacuum-for-statistics"/>.
  </para>

  <para>
   Another task that might need periodic attention is log file management.
   This is discussed in <xref linkend="logfile-maintenance"/>.
  </para>

  <para>
   <ulink
   url="https://bucardo.org/check_postgres/"><application>check_postgres</application></ulink>
   is available for monitoring database health and reporting unusual
   conditions.  <application>check_postgres</application> integrates with
   Nagios and MRTG, but can be run standalone too.
  </para>

  <para>
   <productname>PostgreSQL</productname> is low-maintenance compared
   to some other database management systems.  Nonetheless,
   appropriate attention to these tasks will go far towards ensuring a
   pleasant and productive experience with the system.
  </para>

 <sect1 id="routine-vacuuming">
  <title>Routine Vacuuming</title>

  <indexterm zone="routine-vacuuming">
   <primary>vacuum</primary>
  </indexterm>

  <para>
   <productname>PostgreSQL</productname> databases require periodic
   maintenance known as <firstterm>vacuuming</firstterm>.  For many installations, it
   is sufficient to let vacuuming be performed by the <firstterm>autovacuum
   daemon</firstterm>, which is described in <xref linkend="autovacuum"/>.  You might
   need to adjust the autovacuuming parameters described there to obtain best
   results for your situation.  Some database administrators will want to
   supplement or replace the daemon's activities with manually-managed
   <command>VACUUM</command> commands, which typically are executed according to a
   schedule by <application>cron</application> or <application>Task
   Scheduler</application> scripts.  To set up manually-managed vacuuming properly,
   it is essential to understand the issues discussed in the next few
   subsections.  Administrators who rely on autovacuuming may still wish
   to skim this material to help them understand and adjust autovacuuming.
  </para>

  <sect2 id="vacuum-basics">
   <title>Vacuuming Basics</title>

   <para>
    <productname>PostgreSQL</productname>'s
    <link linkend="sql-vacuum"><command>VACUUM</command></link> command has to
    process each table on a regular basis for several reasons:

    <orderedlist>
     <listitem>
      <simpara>To recover or reuse disk space occupied by updated or deleted
      rows.</simpara>
     </listitem>

     <listitem>
      <simpara>To update data statistics