2 doc/src/sgml/ref/vacuumdb.sgml
3 PostgreSQL documentation
6 <refentry id="APP-VACUUMDB">
7 <indexterm zone="app-vacuumdb">
8 <primary>vacuumdb</primary>
12 <refentrytitle><application>vacuumdb</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
18 <refname id="vacuumdb">vacuumdb</refname>
19 <refpurpose>garbage-collect and analyze a <productname>PostgreSQL</productname> database</refpurpose>
24 <command>vacuumdb</command>
25 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
28 <arg choice="plain" rep="repeat">
30 <group choice="plain">
31 <arg choice="plain"><option>--table</option></arg>
32 <arg choice="plain"><option>-t</option></arg>
34 <replaceable>table</replaceable>
35 <arg choice="opt">( <replaceable class="parameter">column</replaceable> [,...] )</arg>
39 <arg choice="opt"><replaceable>dbname</replaceable></arg>
43 <command>vacuumdb</command>
44 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
45 <arg rep="repeat"><replaceable>option</replaceable></arg>
46 <group choice="plain">
47 <arg choice="plain"><option>--all</option></arg>
48 <arg choice="plain"><option>-a</option></arg>
54 <title>Description</title>
57 <application>vacuumdb</application> is a utility for cleaning a
58 <productname>PostgreSQL</productname> database.
59 <application>vacuumdb</application> will also generate internal statistics
60 used by the <productname>PostgreSQL</productname> query optimizer.
64 <application>vacuumdb</application> is a wrapper around the SQL
65 command <xref linkend="SQL-VACUUM">.
66 There is no effective difference between vacuuming and analyzing
67 databases via this utility and via other methods for accessing the
75 <title>Options</title>
78 <application>vacuumdb</application> accepts the following command-line arguments:
81 <term><option>-a</option></term>
82 <term><option>--all</option></term>
91 <term><option><optional>-d</> <replaceable class="parameter">dbname</replaceable></option></term>
92 <term><option><optional>--dbname=</><replaceable class="parameter">dbname</replaceable></option></term>
95 Specifies the name of the database to be cleaned or analyzed.
96 If this is not specified and <option>-a</option> (or
97 <option>--all</option>) is not used, the database name is read
98 from the environment variable <envar>PGDATABASE</envar>. If
99 that is not set, the user name specified for the connection is
106 <term><option>-e</></term>
107 <term><option>--echo</></term>
110 Echo the commands that <application>vacuumdb</application> generates
111 and sends to the server.
117 <term><option>-f</option></term>
118 <term><option>--full</option></term>
121 Perform <quote>full</quote> vacuuming.
127 <term><option>-F</option></term>
128 <term><option>--freeze</option></term>
131 Aggressively <quote>freeze</quote> tuples.
137 <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term>
138 <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term>
141 Execute the vacuum or analyze commands in parallel by running
142 <replaceable class="parameter">njobs</replaceable>
143 commands simultaneously. This option reduces the time of the
144 processing but it also increases the load on the database server.
147 <application>vacuumdb</application> will open
148 <replaceable class="parameter">njobs</replaceable> connections to the
149 database, so make sure your <xref linkend="guc-max-connections">
150 setting is high enough to accommodate all connections.
153 Note that using this mode together with the <option>-f</option>
154 (<literal>FULL</literal>) option might cause deadlock failures if
155 certain system catalogs are processed in parallel.
161 <term><option>-q</></term>
162 <term><option>--quiet</></term>
165 Do not display progress messages.
171 <term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
172 <term><option>--table=<replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
175 Clean or analyze <replaceable class="parameter">table</replaceable> only.
176 Column names can be specified only in conjunction with
177 the <option>--analyze</option> or <option>--analyze-only</option> options.
178 Multiple tables can be vacuumed by writing multiple
179 <option>-t</> switches.
183 If you specify columns, you probably have to escape the parentheses
184 from the shell. (See examples below.)
191 <term><option>-v</option></term>
192 <term><option>--verbose</option></term>
195 Print detailed information during processing.
201 <term><option>-V</></term>
202 <term><option>--version</></term>
205 Print the <application>vacuumdb</application> version and exit.
211 <term><option>-z</option></term>
212 <term><option>--analyze</option></term>
215 Also calculate statistics for use by the optimizer.
221 <term><option>-Z</option></term>
222 <term><option>--analyze-only</option></term>
225 Only calculate statistics for use by the optimizer (no vacuum).
231 <term><option>--analyze-in-stages</option></term>
234 Only calculate statistics for use by the optimizer (no vacuum),
235 like <option>--analyze-only</option>. Run several (currently three)
236 stages of analyze with different configuration settings, to produce
237 usable statistics faster.
241 This option is useful to analyze a database that was newly populated
242 from a restored dump or by <command>pg_upgrade</command>. This option
243 will try to create some statistics as fast as possible, to make the
244 database usable, and then produce full statistics in the subsequent
251 <term><option>-?</></term>
252 <term><option>--help</></term>
255 Show help about <application>vacuumdb</application> command line
265 <application>vacuumdb</application> also accepts
266 the following command-line arguments for connection parameters:
269 <term><option>-h <replaceable class="parameter">host</replaceable></></term>
270 <term><option>--host=<replaceable class="parameter">host</replaceable></></term>
273 Specifies the host name of the machine on which the server
274 is running. If the value begins with a slash, it is used
275 as the directory for the Unix domain socket.
281 <term><option>-p <replaceable class="parameter">port</replaceable></></term>
282 <term><option>--port=<replaceable class="parameter">port</replaceable></></term>
285 Specifies the TCP port or local Unix domain socket file
286 extension on which the server
287 is listening for connections.
293 <term><option>-U <replaceable class="parameter">username</replaceable></></term>
294 <term><option>--username=<replaceable class="parameter">username</replaceable></></term>
297 User name to connect as.
303 <term><option>-w</></term>
304 <term><option>--no-password</></term>
307 Never issue a password prompt. If the server requires
308 password authentication and a password is not available by
309 other means such as a <filename>.pgpass</filename> file, the
310 connection attempt will fail. This option can be useful in
311 batch jobs and scripts where no user is present to enter a
318 <term><option>-W</></term>
319 <term><option>--password</></term>
322 Force <application>vacuumdb</application> to prompt for a
323 password before connecting to a database.
327 This option is never essential, since
328 <application>vacuumdb</application> will automatically prompt
329 for a password if the server demands password authentication.
330 However, <application>vacuumdb</application> will waste a
331 connection attempt finding out that the server wants a password.
332 In some cases it is worth typing <option>-W</> to avoid the extra
339 <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></></term>
342 Specifies the name of the database to connect to discover what other
343 databases should be vacuumed. If not specified, the
344 <literal>postgres</literal> database will be used,
345 and if that does not exist, <literal>template1</literal> will be used.
355 <title>Environment</title>
359 <term><envar>PGDATABASE</envar></term>
360 <term><envar>PGHOST</envar></term>
361 <term><envar>PGPORT</envar></term>
362 <term><envar>PGUSER</envar></term>
366 Default connection parameters
373 This utility, like most other <productname>PostgreSQL</> utilities,
374 also uses the environment variables supported by <application>libpq</>
375 (see <xref linkend="libpq-envars">).
382 <title>Diagnostics</title>
385 In case of difficulty, see <xref linkend="SQL-VACUUM">
386 and <xref linkend="APP-PSQL"> for
387 discussions of potential problems and error messages.
388 The database server must be running at the
389 targeted host. Also, any default connection settings and environment
390 variables used by the <application>libpq</application> front-end
401 <application>vacuumdb</application> might need to connect several
402 times to the <productname>PostgreSQL</productname> server, asking
403 for a password each time. It is convenient to have a
404 <filename>~/.pgpass</> file in such cases. See <xref
405 linkend="libpq-pgpass"> for more information.
410 <title>Examples</title>
413 To clean the database <literal>test</literal>:
415 <prompt>$ </prompt><userinput>vacuumdb test</userinput>
420 To clean and analyze for the optimizer a database named
421 <literal>bigdb</literal>:
423 <prompt>$ </prompt><userinput>vacuumdb --analyze bigdb</userinput>
428 To clean a single table
429 <literal>foo</literal> in a database named
430 <literal>xyzzy</literal>, and analyze a single column
431 <literal>bar</literal> of the table for the optimizer:
433 <prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</userinput>
439 <title>See Also</title>
441 <simplelist type="inline">
442 <member><xref linkend="sql-vacuum"></member>