Debian Codegraph @

doc/src/sgml/ref/vacuumdb.sgml

raw
1 <!--
2 doc/src/sgml/ref/vacuumdb.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="app-vacuumdb">
7 <indexterm zone="app-vacuumdb">
8 <primary>vacuumdb</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>vacuumdb</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>vacuumdb</refname>
19 <refpurpose>garbage-collect and analyze a <productname>PostgreSQL</productname> database</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <cmdsynopsis>
24 <command>vacuumdb</command>
25 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
27
28 <arg choice="plain" rep="repeat">
29 <arg choice="opt">
30 <group choice="plain">
31 <arg choice="plain"><option>-t</option></arg>
32 <arg choice="plain"><option>--table</option></arg>
33 </group>
34 <replaceable>table</replaceable>
35 <arg choice="opt">( <replaceable class="parameter">column</replaceable> [,...] )</arg>
36 </arg>
37 </arg>
38
39 <arg choice="opt">
40 <group choice="plain">
41 <arg choice="plain"><replaceable>dbname</replaceable></arg>
42 <arg choice="plain"><option>-a</option></arg>
43 <arg choice="plain"><option>--all</option></arg>
44 </group>
45 </arg>
46 </cmdsynopsis>
47
48 <cmdsynopsis>
49 <command>vacuumdb</command>
50 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
51 <arg rep="repeat"><replaceable>option</replaceable></arg>
52
53 <arg choice="plain" rep="repeat">
54 <arg choice="opt">
55 <group choice="plain">
56 <arg choice="plain"><option>-n</option></arg>
57 <arg choice="plain"><option>--schema</option></arg>
58 </group>
59 <replaceable>schema</replaceable>
60 </arg>
61 </arg>
62
63 <arg choice="opt">
64 <group choice="plain">
65 <arg choice="plain"><replaceable>dbname</replaceable></arg>
66 <arg choice="plain"><option>-a</option></arg>
67 <arg choice="plain"><option>--all</option></arg>
68 </group>
69 </arg>
70 </cmdsynopsis>
71
72 <cmdsynopsis>
73 <command>vacuumdb</command>
74 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
75 <arg rep="repeat"><replaceable>option</replaceable></arg>
76
77 <arg choice="plain" rep="repeat">
78 <arg choice="opt">
79 <group choice="plain">
80 <arg choice="plain"><option>-N</option></arg>
81 <arg choice="plain"><option>--exclude-schema</option></arg>
82 </group>
83 <replaceable>schema</replaceable>
84 </arg>
85 </arg>
86
87 <arg choice="opt">
88 <group choice="plain">
89 <arg choice="plain"><replaceable>dbname</replaceable></arg>
90 <arg choice="plain"><option>-a</option></arg>
91 <arg choice="plain"><option>--all</option></arg>
92 </group>
93 </arg>
94 </cmdsynopsis>
95 </refsynopsisdiv>
96
97 <refsect1>
98 <title>Description</title>
99
100 <para>
101 <application>vacuumdb</application> is a utility for cleaning a
102 <productname>PostgreSQL</productname> database.
103 <application>vacuumdb</application> will also generate internal statistics
104 used by the <productname>PostgreSQL</productname> query optimizer.
105 </para>
106
107 <para>
108 <application>vacuumdb</application> is a wrapper around the SQL
109 command <link linkend="sql-vacuum"><command>VACUUM</command></link>.
110 There is no effective difference between vacuuming and analyzing
111 databases via this utility and via other methods for accessing the
112 server.
113 </para>
114
115 </refsect1>
116
117
118 <refsect1>
119 <title>Options</title>
120
121 <para>
122 <application>vacuumdb</application> accepts the following command-line arguments:
123 <variablelist>
124 <varlistentry>
125 <term><option>-a</option></term>
126 <term><option>--all</option></term>
127 <listitem>
128 <para>
129 Vacuum all databases.
130 </para>
131 </listitem>
132 </varlistentry>
133
134 <varlistentry>
135 <term><option>--buffer-usage-limit <replaceable class="parameter">size</replaceable></option></term>
136 <listitem>
137 <para>
138 Specifies the
139 <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
140 ring buffer size for a given invocation of <application>vacuumdb</application>.
141 This size is used to calculate the number of shared buffers which will
142 be reused as part of this strategy. See <xref linkend="sql-vacuum"/>.
143 </para>
144 </listitem>
145 </varlistentry>
146
147 <varlistentry>
148 <term><option><optional>-d</optional> <replaceable class="parameter">dbname</replaceable></option></term>
149 <term><option><optional>--dbname=</optional><replaceable class="parameter">dbname</replaceable></option></term>
150 <listitem>
151 <para>
152 Specifies the name of the database to be cleaned or analyzed,
153 when <option>-a</option>/<option>--all</option> is not used.
154 If this is not specified, the database name is read
155 from the environment variable <envar>PGDATABASE</envar>. If
156 that is not set, the user name specified for the connection is
157 used. The <replaceable>dbname</replaceable> can be a <link
158 linkend="libpq-connstring">connection string</link>. If so,
159 connection string parameters will override any conflicting command
160 line options.
161 </para>
162 </listitem>
163 </varlistentry>
164
165 <varlistentry>
166 <term><option>--disable-page-skipping</option></term>
167 <listitem>
168 <para>
169 Disable skipping pages based on the contents of the visibility map.
170 </para>
171 </listitem>
172 </varlistentry>
173
174 <varlistentry>
175 <term><option>-e</option></term>
176 <term><option>--echo</option></term>
177 <listitem>
178 <para>
179 Echo the commands that <application>vacuumdb</application> generates
180 and sends to the server.
181 </para>
182 </listitem>
183 </varlistentry>
184
185 <varlistentry>
186 <term><option>-f</option></term>
187 <term><option>--full</option></term>
188 <listitem>
189 <para>
190 Perform <quote>full</quote> vacuuming.
191 </para>
192 </listitem>
193 </varlistentry>
194
195 <varlistentry>
196 <term><option>-F</option></term>
197 <term><option>--freeze</option></term>
198 <listitem>
199 <para>
200 Aggressively <quote>freeze</quote> tuples.
201 </para>
202 </listitem>
203 </varlistentry>
204
205 <varlistentry>
206 <term><option>--force-index-cleanup</option></term>
207 <listitem>
208 <para>
209 Always remove index entries pointing to dead tuples.
210 </para>
211 </listitem>
212 </varlistentry>
213
214 <varlistentry>
215 <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term>
216 <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term>
217 <listitem>
218 <para>
219 Execute the vacuum or analyze commands in parallel by running
220 <replaceable class="parameter">njobs</replaceable>
221 commands simultaneously. This option may reduce the processing time
222 but it also increases the load on the database server.
223 </para>
224 <para>
225 <application>vacuumdb</application> will open
226 <replaceable class="parameter">njobs</replaceable> connections to the
227 database, so make sure your <xref linkend="guc-max-connections"/>
228 setting is high enough to accommodate all connections.
229 </para>
230 <para>
231 Note that using this mode together with the <option>-f</option>
232 (<literal>FULL</literal>) option might cause deadlock failures if
233 certain system catalogs are processed in parallel.
234 </para>
235 </listitem>
236 </varlistentry>
237
238 <varlistentry>
239 <term><option>--min-mxid-age <replaceable class="parameter">mxid_age</replaceable></option></term>
240 <listitem>
241 <para>
242 Only execute the vacuum or analyze commands on tables with a multixact
243 ID age of at least <replaceable class="parameter">mxid_age</replaceable>.
244 This setting is useful for prioritizing tables to process to prevent
245 multixact ID wraparound (see
246 <xref linkend="vacuum-for-multixact-wraparound"/>).
247 </para>
248 <para>
249 For the purposes of this option, the multixact ID age of a relation is
250 the greatest of the ages of the main relation and its associated
251 <acronym>TOAST</acronym> table, if one exists. Since the commands
252 issued by <application>vacuumdb</application> will also process the
253 <acronym>TOAST</acronym> table for the relation if necessary, it does
254 not need to be considered separately.
255 </para>
256 </listitem>
257 </varlistentry>
258
259 <varlistentry>
260 <term><option>--min-xid-age <replaceable class="parameter">xid_age</replaceable></option></term>
261 <listitem>
262 <para>
263 Only execute the vacuum or analyze commands on tables with a
264 transaction ID age of at least
265 <replaceable class="parameter">xid_age</replaceable>. This setting
266 is useful for prioritizing tables to process to prevent transaction
267 ID wraparound (see <xref linkend="vacuum-for-wraparound"/>).
268 </para>
269 <para>
270 For the purposes of this option, the transaction ID age of a relation
271 is the greatest of the ages of the main relation and its associated
272 <acronym>TOAST</acronym> table, if one exists. Since the commands
273 issued by <application>vacuumdb</application> will also process the
274 <acronym>TOAST</acronym> table for the relation if necessary, it does
275 not need to be considered separately.
276 </para>
277 </listitem>
278 </varlistentry>
279
280 <varlistentry>
281 <term><option>--missing-stats-only</option></term>
282 <listitem>
283 <para>
284 Only analyze relations that are missing statistics for a column, index
285 expression, or extended statistics object. When used with
286 <option>--analyze-in-stages</option>, this option prevents
287 <application>vacuumdb</application> from temporarily replacing existing
288 statistics with ones generated with lower statistics targets, thus
289 avoiding transiently worse query optimizer choices.
290 </para>
291 <para>
292 This option can only be used in conjunction with
293 <option>--analyze-only</option> or <option>--analyze-in-stages</option>.
294 </para>
295 <para>
296 Note that <option>--missing-stats-only</option> requires
297 <literal>SELECT</literal> privileges on
298 <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
299 and
300 <link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link>,
301 which are restricted to superusers by default.
302 </para>
303 </listitem>
304 </varlistentry>
305
306 <varlistentry>
307 <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
308 <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
309 <listitem>
310 <para>
311 Clean or analyze all tables in
312 <replaceable class="parameter">schema</replaceable> only. Multiple
313 schemas can be vacuumed by writing multiple <option>-n</option> switches.
314 </para>
315 </listitem>
316 </varlistentry>
317
318 <varlistentry>
319 <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
320 <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
321 <listitem>
322 <para>
323 Do not clean or analyze any tables in
324 <replaceable class="parameter">schema</replaceable>. Multiple schemas
325 can be excluded by writing multiple <option>-N</option> switches.
326 </para>
327 </listitem>
328 </varlistentry>
329
330 <varlistentry>
331 <term><option>--no-index-cleanup</option></term>
332 <listitem>
333 <para>
334 Do not remove index entries pointing to dead tuples.
335 </para>
336 </listitem>
337 </varlistentry>
338
339 <varlistentry>
340 <term><option>--no-process-main</option></term>
341 <listitem>
342 <para>
343 Skip the main relation.
344 </para>
345 </listitem>
346 </varlistentry>
347
348 <varlistentry>
349 <term><option>--no-process-toast</option></term>
350 <listitem>
351 <para>
352 Skip the TOAST table associated with the table to vacuum, if any.
353 </para>
354 </listitem>
355 </varlistentry>
356
357 <varlistentry>
358 <term><option>--no-truncate</option></term>
359 <listitem>
360 <para>
361 Do not truncate empty pages at the end of the table.
362 </para>
363 </listitem>
364 </varlistentry>
365
366 <varlistentry>
367 <term><option>-P <replaceable class="parameter">parallel_workers</replaceable></option></term>
368 <term><option>--parallel=<replaceable class="parameter">parallel_workers</replaceable></option></term>
369 <listitem>
370 <para>
371 Specify the number of parallel workers for <firstterm>parallel vacuum</firstterm>.
372 This allows the vacuum to leverage multiple CPUs to process indexes.
373 See <xref linkend="sql-vacuum"/>.
374 </para>
375 </listitem>
376 </varlistentry>
377
378 <varlistentry>
379 <term><option>-q</option></term>
380 <term><option>--quiet</option></term>
381 <listitem>
382 <para>
383 Do not display progress messages.
384 </para>
385 </listitem>
386 </varlistentry>
387
388 <varlistentry>
389 <term><option>--skip-locked</option></term>
390 <listitem>
391 <para>
392 Skip relations that cannot be immediately locked for processing.
393 </para>
394 </listitem>
395 </varlistentry>
396
397 <varlistentry>
398 <term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
399 <term><option>--table=<replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term>
400 <listitem>
401 <para>
402 Clean or analyze <replaceable class="parameter">table</replaceable> only.
403 Column names can be specified only in conjunction with
404 the <option>--analyze</option> or <option>--analyze-only</option> options.
405 Multiple tables can be vacuumed by writing multiple
406 <option>-t</option> switches.
407 </para>
408 <tip>
409 <para>
410 If you specify columns, you probably have to escape the parentheses
411 from the shell. (See examples below.)
412 </para>
413 </tip>
414 </listitem>
415 </varlistentry>
416
417 <varlistentry>
418 <term><option>-v</option></term>
419 <term><option>--verbose</option></term>
420 <listitem>
421 <para>
422 Print detailed information during processing.
423 </para>
424 </listitem>
425 </varlistentry>
426
427 <varlistentry>
428 <term><option>-V</option></term>
429 <term><option>--version</option></term>
430 <listitem>
431 <para>
432 Print the <application>vacuumdb</application> version and exit.
433 </para>
434 </listitem>
435 </varlistentry>
436
437 <varlistentry>
438 <term><option>-z</option></term>
439 <term><option>--analyze</option></term>
440 <listitem>
441 <para>
442 Also calculate statistics for use by the optimizer.
443 </para>
444 </listitem>
445 </varlistentry>
446
447 <varlistentry>
448 <term><option>-Z</option></term>
449 <term><option>--analyze-only</option></term>
450 <listitem>
451 <para>
452 Only calculate statistics for use by the optimizer (no vacuum).
453 </para>
454 </listitem>
455 </varlistentry>
456
457 <varlistentry>
458 <term><option>--analyze-in-stages</option></term>
459 <listitem>
460 <para>
461 Only calculate statistics for use by the optimizer (no vacuum),
462 like <option>--analyze-only</option>. Run three
463 stages of analyze; the first stage uses the lowest possible statistics
464 target (see <xref linkend="guc-default-statistics-target"/>)
465 to produce usable statistics faster, and subsequent stages build the
466 full statistics.
467 </para>
468
469 <para>
470 This option is only useful to analyze a database that currently has
471 no statistics or has wholly incorrect ones, such as if it is newly
472 populated from a restored dump or by <command>pg_upgrade</command>.
473 Be aware that running with this option in a database with existing
474 statistics may cause the query optimizer choices to become
475 transiently worse due to the low statistics targets of the early
476 stages.
477 </para>
478 </listitem>
479 </varlistentry>
480
481 <varlistentry>
482 <term><option>-?</option></term>
483 <term><option>--help</option></term>
484 <listitem>
485 <para>
486 Show help about <application>vacuumdb</application> command line
487 arguments, and exit.
488 </para>
489 </listitem>
490 </varlistentry>
491
492 </variablelist>
493 </para>
494
495 <para>
496 <application>vacuumdb</application> also accepts
497 the following command-line arguments for connection parameters:
498 <variablelist>
499 <varlistentry>
500 <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
501 <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
502 <listitem>
503 <para>
504 Specifies the host name of the machine on which the server
505 is running. If the value begins with a slash, it is used
506 as the directory for the Unix domain socket.
507 </para>
508 </listitem>
509 </varlistentry>
510
511 <varlistentry>
512 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
513 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
514 <listitem>
515 <para>
516 Specifies the TCP port or local Unix domain socket file
517 extension on which the server
518 is listening for connections.
519 </para>
520 </listitem>
521 </varlistentry>
522
523 <varlistentry>
524 <term><option>-U <replaceable class="parameter">username</replaceable></option></term>
525 <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
526 <listitem>
527 <para>
528 User name to connect as.
529 </para>
530 </listitem>
531 </varlistentry>
532
533 <varlistentry>
534 <term><option>-w</option></term>
535 <term><option>--no-password</option></term>
536 <listitem>
537 <para>
538 Never issue a password prompt. If the server requires
539 password authentication and a password is not available by
540 other means such as a <filename>.pgpass</filename> file, the
541 connection attempt will fail. This option can be useful in
542 batch jobs and scripts where no user is present to enter a
543 password.
544 </para>
545 </listitem>
546 </varlistentry>
547
548 <varlistentry>
549 <term><option>-W</option></term>
550 <term><option>--password</option></term>
551 <listitem>
552 <para>
553 Force <application>vacuumdb</application> to prompt for a
554 password before connecting to a database.
555 </para>
556
557 <para>
558 This option is never essential, since
559 <application>vacuumdb</application> will automatically prompt
560 for a password if the server demands password authentication.
561 However, <application>vacuumdb</application> will waste a
562 connection attempt finding out that the server wants a password.
563 In some cases it is worth typing <option>-W</option> to avoid the extra
564 connection attempt.
565 </para>
566 </listitem>
567 </varlistentry>
568
569 <varlistentry>
570 <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term>
571 <listitem>
572 <para>
573 When the <option>-a</option>/<option>--all</option> is used, connect
574 to this database to gather the list of databases to vacuum.
575 If not specified, the <literal>postgres</literal> database will be used,
576 or if that does not exist, <literal>template1</literal> will be used.
577 This can be a <link linkend="libpq-connstring">connection
578 string</link>. If so, connection string parameters will override any
579 conflicting command line options. Also, connection string parameters
580 other than the database name itself will be re-used when connecting
581 to other databases.
582 </para>
583 </listitem>
584 </varlistentry>
585 </variablelist>
586 </para>
587 </refsect1>
588
589
590 <refsect1>
591 <title>Environment</title>
592
593 <variablelist>
594 <varlistentry>
595 <term><envar>PGDATABASE</envar></term>
596 <term><envar>PGHOST</envar></term>
597 <term><envar>PGPORT</envar></term>
598 <term><envar>PGUSER</envar></term>
599
600 <listitem>
601 <para>
602 Default connection parameters
603 </para>
604 </listitem>
605 </varlistentry>
606
607 <varlistentry>
608 <term><envar>PG_COLOR</envar></term>
609 <listitem>
610 <para>
611 Specifies whether to use color in diagnostic messages. Possible values
612 are <literal>always</literal>, <literal>auto</literal> and
613 <literal>never</literal>.
614 </para>
615 </listitem>
616 </varlistentry>
617 </variablelist>
618
619 <para>
620 This utility, like most other <productname>PostgreSQL</productname> utilities,
621 also uses the environment variables supported by <application>libpq</application>
622 (see <xref linkend="libpq-envars"/>).
623 </para>
624
625 </refsect1>
626
627
628 <refsect1>
629 <title>Diagnostics</title>
630
631 <para>
632 In case of difficulty, see <xref linkend="sql-vacuum"/>
633 and <xref linkend="app-psql"/> for
634 discussions of potential problems and error messages.
635 The database server must be running at the
636 targeted host. Also, any default connection settings and environment
637 variables used by the <application>libpq</application> front-end
638 library will apply.
639 </para>
640
641 </refsect1>
642
643 <refsect1>
644 <title>Examples</title>
645
646 <para>
647 To clean the database <literal>test</literal>:
648 <screen>
649 <prompt>$ </prompt><userinput>vacuumdb test</userinput>
650 </screen>
651 </para>
652
653 <para>
654 To clean and analyze for the optimizer a database named
655 <literal>bigdb</literal>:
656 <screen>
657 <prompt>$ </prompt><userinput>vacuumdb --analyze bigdb</userinput>
658 </screen>
659 </para>
660
661 <para>
662 To clean a single table
663 <literal>foo</literal> in a database named
664 <literal>xyzzy</literal>, and analyze a single column
665 <literal>bar</literal> of the table for the optimizer:
666 <screen>
667 <prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</userinput>
668 </screen></para>
669
670 <para>
671 To clean all tables in the <literal>foo</literal> and <literal>bar</literal> schemas
672 in a database named <literal>xyzzy</literal>:
673 <screen>
674 <prompt>$ </prompt><userinput>vacuumdb --schema='foo' --schema='bar' xyzzy</userinput>
675 </screen></para>
676
677
678 </refsect1>
679
680 <refsect1>
681 <title>See Also</title>
682
683 <simplelist type="inline">
684 <member><xref linkend="sql-vacuum"/></member>
685 </simplelist>
686 </refsect1>
687
688 </refentry>