doc/src/sgml/ref/vacuumdb.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="APP-VACUUMDB">
   7  <refmeta>
   8   <refentrytitle id="APP-VACUUMDB-TITLE"><application>vacuumdb</application></refentrytitle>
   9   <manvolnum>1</manvolnum>
  10   <refmiscinfo>Application</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname id="vacuumdb">vacuumdb</refname>
  15   <refpurpose>garbage-collect and analyze a <productname>PostgreSQL</productname> database</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="app-vacuumdb">
  19   <primary>vacuumdb</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23   <cmdsynopsis>
  24    <command>vacuumdb</command>
  25    <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
  26    <group><arg>--full</arg><arg>-f</arg></group>
  27    <group><arg>--verbose</arg><arg>-v</arg></group>
  28    <group><arg>--analyze</arg><arg>-z</arg></group>
  29    <group><arg>--freeze</arg><arg>-F</arg></group>
  30    <arg>--table | -t <replaceable>table</replaceable>
  31     <arg>( <replaceable class="parameter">column</replaceable> [,...] )</arg>
  32    </arg>
  33    <arg><replaceable>dbname</replaceable></arg>
  34    <sbr>
  35    <command>vacuumdb</command>
  36    <arg rep="repeat"><replaceable>connection-options</replaceable></arg>
  37    <group><arg>--all</arg><arg>-a</arg></group>
  38    <group><arg>--full</arg><arg>-f</arg></group>
  39    <group><arg>--verbose</arg><arg>-v</arg></group>
  40    <group><arg>--analyze</arg><arg>-z</arg></group>
  41    <group><arg>--freeze</arg><arg>-F</arg></group>
  42   </cmdsynopsis>
  43  </refsynopsisdiv>
  44
  45
  46  <refsect1>
  47   <title>Description</title>
  48
  49   <para>
  50    <application>vacuumdb</application> is a utility for cleaning a
  51    <productname>PostgreSQL</productname> database.
  52    <application>vacuumdb</application> will also generate internal statistics
  53    used by the <productname>PostgreSQL</productname> query optimizer.
  54   </para>
  55
  56   <para>
  57    <application>vacuumdb</application> is a wrapper around the SQL
  58    command <xref linkend="SQL-VACUUM" endterm="SQL-VACUUM-title">.
  59    There is no effective difference between vacuuming databases via
  60    this utility and via other methods for accessing the server.
  61   </para>
  62
  63  </refsect1>
  64
  65
  66  <refsect1>
  67   <title>Options</title>
  68
  69    <para>
  70     <application>vacuumdb</application> accepts the following command-line arguments:
  71
  72     <variablelist>
  73      <varlistentry>
  74       <term><option>-a</option></term>
  75       <term><option>--all</option></term>
  76       <listitem>
  77        <para>
  78         Vacuum all databases.
  79        </para>
  80       </listitem>
  81      </varlistentry>
  82
  83      <varlistentry>
  84       <term><option><optional>-d</> <replaceable class="parameter">dbname</replaceable></option></term>
  85       <term><option><optional>--dbname</> <replaceable class="parameter">dbname</replaceable></option></term>
  86       <listitem>
  87        <para>
  88         Specifies the name of the database to be cleaned or analyzed.
  89         If this is not specified and <option>-a</option> (or
  90         <option>--all</option>) is not used, the database name is read
  91         from the environment variable <envar>PGDATABASE</envar>.  If
  92         that is not set, the user name specified for the connection is
  93         used.
  94        </para>
  95       </listitem>
  96      </varlistentry>
  97
  98      <varlistentry>
  99       <term><option>-e</></term>
 100       <term><option>--echo</></term>
 101       <listitem>
 102        <para>
 103         Echo the commands that <application>vacuumdb</application> generates
 104         and sends to the server.
 105        </para>
 106       </listitem>
 107      </varlistentry>
 108
 109      <varlistentry>
 110       <term><option>-f</option></term>
 111       <term><option>--full</option></term>
 112       <listitem>
 113        <para>
 114         Perform <quote>full</quote> vacuuming.
 115        </para>
 116       </listitem>
 117      </varlistentry>
 118
 119      <varlistentry>
 120       <term><option>-q</></term>
 121       <term><option>--quiet</></term>
 122       <listitem>
 123        <para>
 124         Do not display progress messages.
 125        </para>
 126       </listitem>
 127      </varlistentry>
 128
 129      <varlistentry>
 130       <term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
 131       <term><option>--table <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
 132       <listitem>
 133        <para>
 134         Clean or analyze <replaceable class="parameter">table</replaceable> only.
 135         Column names can be specified only in conjunction with
 136         the <option>--analyze</option> option.
 137        </para>
 138        <tip>
 139         <para>
 140          If you specify columns, you probably have to escape the parentheses
 141          from the shell.  (See examples below.)
 142         </para>
 143        </tip>
 144       </listitem>
 145      </varlistentry>
 146
 147      <varlistentry>
 148       <term><option>-v</option></term>
 149       <term><option>--verbose</option></term>
 150       <listitem>
 151        <para>
 152         Print detailed information during processing.
 153        </para>
 154       </listitem>
 155      </varlistentry>
 156
 157      <varlistentry>
 158       <term><option>-z</option></term>
 159       <term><option>--analyze</option></term>
 160       <listitem>
 161        <para>
 162         Calculate statistics for use by the optimizer.
 163        </para>
 164       </listitem>
 165      </varlistentry>
 166
 167      <varlistentry>
 168       <term><option>-F</option></term>
 169       <term><option>--freeze</option></term>
 170       <listitem>
 171        <para>
 172         Aggressively <quote>freeze</quote> tuples.
 173        </para>
 174       </listitem>
 175      </varlistentry>
 176     </variablelist>
 177    </para>
 178
 179    <para>
 180     <application>vacuumdb</application> also accepts
 181     the following command-line arguments for connection parameters:
 182
 183     <variablelist>
 184      <varlistentry>
 185       <term><option>-h <replaceable class="parameter">host</replaceable></></term>
 186       <term><option>--host <replaceable class="parameter">host</replaceable></></term>
 187       <listitem>
 188        <para>
 189         Specifies the host name of the machine on which the
 190         server
 191         is running.  If the value begins with a slash, it is used
 192         as the directory for the Unix domain socket.
 193        </para>
 194       </listitem>
 195      </varlistentry>
 196
 197      <varlistentry>
 198       <term><option>-p <replaceable class="parameter">port</replaceable></></term>
 199       <term><option>--port <replaceable class="parameter">port</replaceable></></term>
 200       <listitem>
 201        <para>
 202         Specifies the TCP port or local Unix domain socket file
 203         extension on which the server
 204         is listening for connections.
 205        </para>
 206       </listitem>
 207      </varlistentry>
 208
 209      <varlistentry>
 210       <term><option>-U <replaceable class="parameter">username</replaceable></></term>
 211       <term><option>--username <replaceable class="parameter">username</replaceable></></term>
 212       <listitem>
 213        <para>
 214         User name to connect as.
 215        </para>
 216       </listitem>
 217      </varlistentry>
 218
 219      <varlistentry>
 220       <term><option>-w</></term>
 221       <term><option>--no-password</></term>
 222       <listitem>
 223        <para>
 224         Never issue a password prompt.  If the server requires
 225         password authentication and a password is not available by
 226         other means such as a <filename>.pgpass</filename> file, the
 227         connection attempt will fail.  This option can be useful in
 228         batch jobs and scripts where no user is present to enter a
 229         password.
 230        </para>
 231       </listitem>
 232      </varlistentry>
 233
 234      <varlistentry>
 235       <term><option>-W</></term>
 236       <term><option>--password</></term>
 237       <listitem>
 238        <para>
 239         Force <application>vacuumdb</application> to prompt for a
 240         password before connecting to a database.
 241        </para>
 242
 243        <para>
 244         This option is never essential, since
 245         <application>vacuumdb</application> will automatically prompt
 246         for a password if the server demands password authentication.
 247         However, <application>vacuumdb</application> will waste a
 248         connection attempt finding out that the server wants a password.
 249         In some cases it is worth typing <option>-W</> to avoid the extra
 250         connection attempt.
 251        </para>
 252       </listitem>
 253      </varlistentry>
 254     </variablelist>
 255    </para>
 256  </refsect1>
 257
 258
 259  <refsect1>
 260   <title>Environment</title>
 261
 262   <variablelist>
 263    <varlistentry>
 264     <term><envar>PGDATABASE</envar></term>
 265     <term><envar>PGHOST</envar></term>
 266     <term><envar>PGPORT</envar></term>
 267     <term><envar>PGUSER</envar></term>
 268
 269     <listitem>
 270      <para>
 271       Default connection parameters
 272      </para>
 273     </listitem>
 274    </varlistentry>
 275   </variablelist>
 276
 277   <para>
 278    This utility, like most other <productname>PostgreSQL</> utilities,
 279    also uses the environment variables supported by <application>libpq</>
 280    (see <xref linkend="libpq-envars">).
 281   </para>
 282
 283  </refsect1>
 284
 285
 286  <refsect1>
 287   <title>Diagnostics</title>
 288
 289   <para>
 290    In case of difficulty, see <xref linkend="SQL-VACUUM"
 291    endterm="sql-vacuum-title"> and <xref linkend="APP-PSQL"> for
 292    discussions of potential problems and error messages.
 293    The database server must be running at the
 294    targeted host.  Also, any default connection settings and environment
 295    variables used by the <application>libpq</application> front-end
 296    library will apply.
 297   </para>
 298
 299  </refsect1>
 300
 301
 302  <refsect1>
 303   <title>Notes</title>
 304
 305   <para>
 306    <application>vacuumdb</application> might need to connect several
 307    times to the <productname>PostgreSQL</productname> server, asking
 308    for a password each time. It is convenient to have a
 309    <filename>~/.pgpass</> file in such cases. See <xref
 310    linkend="libpq-pgpass"> for more information.
 311   </para>
 312  </refsect1>
 313
 314  <refsect1>
 315   <title>Examples</title>
 316
 317    <para>
 318     To clean the database <literal>test</literal>:
 319 <screen>
 320 <prompt>$ </prompt><userinput>vacuumdb test</userinput>
 321 </screen>
 322    </para>
 323
 324    <para>
 325     To clean and analyze for the optimizer a database named
 326     <literal>bigdb</literal>:
 327 <screen>
 328 <prompt>$ </prompt><userinput>vacuumdb --analyze bigdb</userinput>
 329 </screen>
 330    </para>
 331
 332    <para>
 333     To clean a single table
 334     <literal>foo</literal> in a database named
 335     <literal>xyzzy</literal>, and analyze a single column
 336     <literal>bar</literal> of the table for the optimizer:
 337 <screen>
 338 <prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table 'foo(bar)' xyzzy</userinput>
 339 </screen>
 340    </para>
 341
 342  </refsect1>
 343
 344  <refsect1>
 345   <title>See Also</title>
 346
 347   <simplelist type="inline">
 348    <member><xref linkend="sql-vacuum" endterm="sql-vacuum-title"></member>
 349   </simplelist>
 350  </refsect1>
 351
 352 </refentry>