Debian Codegraph @

doc/src/sgml/ref/pg_amcheck.sgml

raw
1 <!--
2 doc/src/sgml/ref/pg_amcheck.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="app-pgamcheck">
7 <indexterm zone="app-pgamcheck">
8 <primary>pg_amcheck</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pg_amcheck</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pg_amcheck</refname>
19 <refpurpose>checks for corruption in one or more
20 <productname>PostgreSQL</productname> databases</refpurpose>
21 </refnamediv>
22
23 <refsynopsisdiv>
24 <cmdsynopsis>
25 <command>pg_amcheck</command>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
27 <arg><replaceable>dbname</replaceable></arg>
28 </cmdsynopsis>
29 </refsynopsisdiv>
30
31 <refsect1>
32 <title>Description</title>
33
34 <para>
35 <application>pg_amcheck</application> supports running
36 <xref linkend="amcheck"/>'s corruption checking functions against one or
37 more databases, with options to select which schemas, tables and indexes to
38 check, which kinds of checking to perform, and whether to perform the checks
39 in parallel, and if so, the number of parallel connections to establish and
40 use.
41 </para>
42
43 <para>
44 Only ordinary and toast table relations, materialized views, sequences, and
45 btree indexes are currently supported. Other relation types are silently
46 skipped.
47 </para>
48
49 <para>
50 If <literal>dbname</literal> is specified, it should be the name of a
51 single database to check, and no other database selection options should
52 be present. Otherwise, if any database selection options are present,
53 all matching databases will be checked. If no such options are present,
54 the default database will be checked. Database selection options include
55 <option>--all</option>, <option>--database</option> and
56 <option>--exclude-database</option>. They also include
57 <option>--relation</option>, <option>--exclude-relation</option>,
58 <option>--table</option>, <option>--exclude-table</option>,
59 <option>--index</option>, and <option>--exclude-index</option>,
60 but only when such options are used with a three-part pattern
61 (e.g. <option>mydb*.myschema*.myrel*</option>). Finally, they include
62 <option>--schema</option> and <option>--exclude-schema</option>
63 when such options are used with a two-part pattern
64 (e.g. <option>mydb*.myschema*</option>).
65 </para>
66
67 <para>
68 <replaceable>dbname</replaceable> can also be a
69 <link linkend="libpq-connstring">connection string</link>.
70 </para>
71 </refsect1>
72
73 <refsect1>
74 <title>Options</title>
75
76 <para>
77 The following command-line options control what is checked:
78
79 <variablelist>
80 <varlistentry>
81 <term><option>-a</option></term>
82 <term><option>--all</option></term>
83 <listitem>
84 <para>
85 Check all databases, except for any excluded via
86 <option>--exclude-database</option>.
87 </para>
88 </listitem>
89 </varlistentry>
90
91 <varlistentry>
92 <term><option>-d <replaceable class="parameter">pattern</replaceable></option></term>
93 <term><option>--database=<replaceable class="parameter">pattern</replaceable></option></term>
94 <listitem>
95 <para>
96 Check databases matching the specified
97 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>,
98 except for any excluded by <option>--exclude-database</option>.
99 This option can be specified more than once.
100 </para>
101 </listitem>
102 </varlistentry>
103
104 <varlistentry>
105 <term><option>-D <replaceable class="parameter">pattern</replaceable></option></term>
106 <term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term>
107 <listitem>
108 <para>
109 Exclude databases matching the given
110 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>.
111 This option can be specified more than once.
112 </para>
113 </listitem>
114 </varlistentry>
115
116 <varlistentry>
117 <term><option>-i <replaceable class="parameter">pattern</replaceable></option></term>
118 <term><option>--index=<replaceable class="parameter">pattern</replaceable></option></term>
119 <listitem>
120 <para>
121 Check indexes matching the specified
122 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>,
123 unless they are otherwise excluded.
124 This option can be specified more than once.
125 </para>
126 <para>
127 This is similar to the <option>--relation</option> option, except that
128 it applies only to indexes, not to other relation types.
129 </para>
130 </listitem>
131 </varlistentry>
132
133 <varlistentry>
134 <term><option>-I <replaceable class="parameter">pattern</replaceable></option></term>
135 <term><option>--exclude-index=<replaceable class="parameter">pattern</replaceable></option></term>
136 <listitem>
137 <para>
138 Exclude indexes matching the specified
139 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>.
140 This option can be specified more than once.
141 </para>
142 <para>
143 This is similar to the <option>--exclude-relation</option> option,
144 except that it applies only to indexes, not other relation types.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><option>-r <replaceable class="parameter">pattern</replaceable></option></term>
151 <term><option>--relation=<replaceable class="parameter">pattern</replaceable></option></term>
152 <listitem>
153 <para>
154 Check relations matching the specified
155 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>,
156 unless they are otherwise excluded.
157 This option can be specified more than once.
158 </para>
159 <para>
160 Patterns may be unqualified, e.g. <literal>myrel*</literal>, or they
161 may be schema-qualified, e.g. <literal>myschema*.myrel*</literal> or
162 database-qualified and schema-qualified, e.g.
163 <literal>mydb*.myschema*.myrel*</literal>. A database-qualified
164 pattern will add matching databases to the list of databases to be
165 checked.
166 </para>
167 </listitem>
168 </varlistentry>
169
170 <varlistentry>
171 <term><option>-R <replaceable class="parameter">pattern</replaceable></option></term>
172 <term><option>--exclude-relation=<replaceable class="parameter">pattern</replaceable></option></term>
173 <listitem>
174 <para>
175 Exclude relations matching the specified
176 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>.
177 This option can be specified more than once.
178 </para>
179 <para>
180 As with <option>--relation</option>, the
181 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> may be unqualified, schema-qualified,
182 or database- and schema-qualified.
183 </para>
184 </listitem>
185 </varlistentry>
186
187 <varlistentry>
188 <term><option>-s <replaceable class="parameter">pattern</replaceable></option></term>
189 <term><option>--schema=<replaceable class="parameter">pattern</replaceable></option></term>
190 <listitem>
191 <para>
192 Check tables and indexes in schemas matching the specified
193 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>, unless they are otherwise excluded.
194 This option can be specified more than once.
195 </para>
196 <para>
197 To select only tables in schemas matching a particular pattern,
198 consider using something like
199 <literal>--table=SCHEMAPAT.* --no-dependent-indexes</literal>.
200 To select only indexes, consider using something like
201 <literal>--index=SCHEMAPAT.*</literal>.
202 </para>
203 <para>
204 A schema pattern may be database-qualified. For example, you may
205 write <literal>--schema=mydb*.myschema*</literal> to select
206 schemas matching <literal>myschema*</literal> in databases matching
207 <literal>mydb*</literal>.
208 </para>
209 </listitem>
210 </varlistentry>
211
212 <varlistentry>
213 <term><option>-S <replaceable class="parameter">pattern</replaceable></option></term>
214 <term><option>--exclude-schema=<replaceable class="parameter">pattern</replaceable></option></term>
215 <listitem>
216 <para>
217 Exclude tables and indexes in schemas matching the specified
218 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>.
219 This option can be specified more than once.
220 </para>
221 <para>
222 As with <option>--schema</option>, the pattern may be
223 database-qualified.
224 </para>
225 </listitem>
226 </varlistentry>
227
228 <varlistentry>
229 <term><option>-t <replaceable class="parameter">pattern</replaceable></option></term>
230 <term><option>--table=<replaceable class="parameter">pattern</replaceable></option></term>
231 <listitem>
232 <para>
233 Check tables matching the specified
234 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>,
235 unless they are otherwise excluded.
236 This option can be specified more than once.
237 </para>
238 <para>
239 This is similar to the <option>--relation</option> option, except that
240 it applies only to tables, materialized views, and sequences, not to
241 indexes.
242 </para>
243 </listitem>
244 </varlistentry>
245
246 <varlistentry>
247 <term><option>-T <replaceable class="parameter">pattern</replaceable></option></term>
248 <term><option>--exclude-table=<replaceable class="parameter">pattern</replaceable></option></term>
249 <listitem>
250 <para>
251 Exclude tables matching the specified
252 <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link>.
253 This option can be specified more than once.
254 </para>
255 <para>
256 This is similar to the <option>--exclude-relation</option> option,
257 except that it applies only to tables, materialized views, and
258 sequences, not to indexes.
259 </para>
260 </listitem>
261 </varlistentry>
262
263 <varlistentry>
264 <term><option>--no-dependent-indexes</option></term>
265 <listitem>
266 <para>
267 By default, if a table is checked, any btree indexes of that table
268 will also be checked, even if they are not explicitly selected by
269 an option such as <literal>--index</literal> or
270 <literal>--relation</literal>. This option suppresses that behavior.
271 </para>
272 </listitem>
273 </varlistentry>
274
275 <varlistentry>
276 <term><option>--no-dependent-toast</option></term>
277 <listitem>
278 <para>
279 By default, if a table is checked, its toast table, if any, will also
280 be checked, even if it is not explicitly selected by an option
281 such as <literal>--table</literal> or <literal>--relation</literal>.
282 This option suppresses that behavior.
283 </para>
284 </listitem>
285 </varlistentry>
286
287 <varlistentry>
288 <term><option>--no-strict-names</option></term>
289 <listitem>
290 <para>
291 By default, if an argument to <literal>--database</literal>,
292 <literal>--table</literal>, <literal>--index</literal>,
293 or <literal>--relation</literal> matches no objects, it is a fatal
294 error. This option downgrades that error to a warning.
295 </para>
296 </listitem>
297 </varlistentry>
298 </variablelist>
299 </para>
300
301 <para>
302 The following command-line options control checking of tables:
303
304 <variablelist>
305 <varlistentry>
306 <term><option>--exclude-toast-pointers</option></term>
307 <listitem>
308 <para>
309 By default, whenever a toast pointer is encountered in a table,
310 a lookup is performed to ensure that it references apparently-valid
311 entries in the toast table. These checks can be quite slow, and this
312 option can be used to skip them.
313 </para>
314 </listitem>
315 </varlistentry>
316
317 <varlistentry>
318 <term><option>--on-error-stop</option></term>
319 <listitem>
320 <para>
321 After reporting all corruptions on the first page of a table where
322 corruption is found, stop processing that table relation and move on
323 to the next table or index.
324 </para>
325 <para>
326 Note that index checking always stops after the first corrupt page.
327 This option only has meaning relative to table relations.
328 </para>
329 </listitem>
330 </varlistentry>
331
332 <varlistentry>
333 <term><option>--skip=<replaceable class="parameter">option</replaceable></option></term>
334 <listitem>
335 <para>
336 If <literal>all-frozen</literal> is given, table corruption checks
337 will skip over pages in all tables that are marked as all frozen.
338 </para>
339 <para>
340 If <literal>all-visible</literal> is given, table corruption checks
341 will skip over pages in all tables that are marked as all visible.
342 </para>
343 <para>
344 By default, no pages are skipped. This can be specified as
345 <literal>none</literal>, but since this is the default, it need not be
346 mentioned.
347 </para>
348 </listitem>
349 </varlistentry>
350
351 <varlistentry>
352 <term><option>--startblock=<replaceable class="parameter">block</replaceable></option></term>
353 <listitem>
354 <para>
355 Start checking at the specified block number. An error will occur if
356 the table relation being checked has fewer than this number of blocks.
357 This option does not apply to indexes, and is probably only useful
358 when checking a single table relation. See <literal>--endblock</literal>
359 for further caveats.
360 </para>
361 </listitem>
362 </varlistentry>
363
364 <varlistentry>
365 <term><option>--endblock=<replaceable class="parameter">block</replaceable></option></term>
366 <listitem>
367 <para>
368 End checking at the specified block number. An error will occur if the
369 table relation being checked has fewer than this number of blocks.
370 This option does not apply to indexes, and is probably only useful when
371 checking a single table relation. If both a regular table and a toast
372 table are checked, this option will apply to both, but higher-numbered
373 toast blocks may still be accessed while validating toast pointers,
374 unless that is suppressed using
375 <option>--exclude-toast-pointers</option>.
376 </para>
377 </listitem>
378 </varlistentry>
379 </variablelist>
380 </para>
381
382 <para>
383 The following command-line options control checking of B-tree indexes:
384
385 <variablelist>
386 <varlistentry>
387 <term><option>--checkunique</option></term>
388 <listitem>
389 <para>
390 For each index with unique constraint checked, verify that no more than
391 one among duplicate entries is visible in the index using <xref linkend="amcheck"/>'s
392 <option>checkunique</option> option.
393 </para>
394 </listitem>
395 </varlistentry>
396
397 <varlistentry>
398 <term><option>--heapallindexed</option></term>
399 <listitem>
400 <para>
401 For each index checked, verify the presence of all heap tuples as index
402 tuples in the index using <xref linkend="amcheck"/>'s
403 <option>heapallindexed</option> option.
404 </para>
405 </listitem>
406 </varlistentry>
407
408 <varlistentry>
409 <term><option>--parent-check</option></term>
410 <listitem>
411 <para>
412 For each btree index checked, use <xref linkend="amcheck"/>'s
413 <function>bt_index_parent_check</function> function, which performs
414 additional checks of parent/child relationships during index checking.
415 </para>
416 <para>
417 The default is to use <application>amcheck</application>'s
418 <function>bt_index_check</function> function, but note that use of the
419 <option>--rootdescend</option> option implicitly selects
420 <function>bt_index_parent_check</function>.
421 </para>
422 </listitem>
423 </varlistentry>
424
425 <varlistentry>
426 <term><option>--rootdescend</option></term>
427 <listitem>
428 <para>
429 For each index checked, re-find tuples on the leaf level by performing a
430 new search from the root page for each tuple using
431 <xref linkend="amcheck"/>'s <option>rootdescend</option> option.
432 </para>
433 <para>
434 Use of this option implicitly also selects the
435 <option>--parent-check</option> option.
436 </para>
437 <para>
438 This form of verification was originally written to help in the
439 development of btree index features. It may be of limited use or even
440 of no use in helping detect the kinds of corruption that occur in
441 practice. It may also cause corruption checking to take considerably
442 longer and consume considerably more resources on the server.
443 </para>
444 </listitem>
445 </varlistentry>
446 </variablelist>
447 </para>
448
449 <warning>
450 <para>
451 The extra checks performed against B-tree indexes when the
452 <option>--parent-check</option> option or the
453 <option>--rootdescend</option> option is specified require
454 relatively strong relation-level locks. These checks are the only
455 checks that will block concurrent data modification from
456 <command>INSERT</command>, <command>UPDATE</command>, and
457 <command>DELETE</command> commands.
458 </para>
459 </warning>
460
461 <para>
462 The following command-line options control the connection to the server:
463
464 <variablelist>
465 <varlistentry>
466 <term><option>-h <replaceable class="parameter">hostname</replaceable></option></term>
467 <term><option>--host=<replaceable class="parameter">hostname</replaceable></option></term>
468 <listitem>
469 <para>
470 Specifies the host name of the machine on which the server is running.
471 If the value begins with a slash, it is used as the directory for the
472 Unix domain socket.
473 </para>
474 </listitem>
475 </varlistentry>
476
477 <varlistentry>
478 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
479 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
480 <listitem>
481 <para>
482 Specifies the TCP port or local Unix domain socket file extension on
483 which the server is listening for connections.
484 </para>
485 </listitem>
486 </varlistentry>
487
488 <varlistentry>
489 <term><option>-U</option></term>
490 <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
491 <listitem>
492 <para>
493 User name to connect as.
494 </para>
495 </listitem>
496 </varlistentry>
497
498 <varlistentry>
499 <term><option>-w</option></term>
500 <term><option>--no-password</option></term>
501 <listitem>
502 <para>
503 Never issue a password prompt. If the server requires password
504 authentication and a password is not available by other means such as
505 a <filename>.pgpass</filename> file, the connection attempt will fail.
506 This option can be useful in batch jobs and scripts where no user is
507 present to enter a password.
508 </para>
509 </listitem>
510 </varlistentry>
511
512 <varlistentry>
513 <term><option>-W</option></term>
514 <term><option>--password</option></term>
515 <listitem>
516 <para>
517 Force <application>pg_amcheck</application> to prompt for a password
518 before connecting to a database.
519 </para>
520 <para>
521 This option is never essential, since
522 <application>pg_amcheck</application> will automatically prompt for a
523 password if the server demands password authentication. However,
524 <application>pg_amcheck</application> will waste a connection attempt
525 finding out that the server wants a password. In some cases it is
526 worth typing <option>-W</option> to avoid the extra connection attempt.
527 </para>
528 </listitem>
529 </varlistentry>
530
531 <varlistentry>
532 <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term>
533 <listitem>
534 <para>
535 Specifies a database or
536 <link linkend="libpq-connstring">connection string</link> to be
537 used to discover the list of databases to be checked. If neither
538 <option>--all</option> nor any option including a database pattern is
539 used, no such connection is required and this option does nothing.
540 Otherwise, any connection string parameters other than
541 the database name which are included in the value for this option
542 will also be used when connecting to the databases
543 being checked. If this option is omitted, the default is
544 <literal>postgres</literal> or, if that fails,
545 <literal>template1</literal>.
546 </para>
547 </listitem>
548 </varlistentry>
549 </variablelist>
550 </para>
551
552 <para>
553 Other options are also available:
554
555 <variablelist>
556 <varlistentry>
557 <term><option>-e</option></term>
558 <term><option>--echo</option></term>
559 <listitem>
560 <para>
561 Echo to stdout all SQL sent to the server.
562 </para>
563 </listitem>
564 </varlistentry>
565
566 <varlistentry>
567 <term><option>-j <replaceable class="parameter">num</replaceable></option></term>
568 <term><option>--jobs=<replaceable class="parameter">num</replaceable></option></term>
569 <listitem>
570 <para>
571 Use <replaceable>num</replaceable> concurrent connections to the server,
572 or one per object to be checked, whichever is less.
573 </para>
574 <para>
575 The default is to use a single connection.
576 </para>
577 </listitem>
578 </varlistentry>
579
580 <varlistentry>
581 <term><option>-P</option></term>
582 <term><option>--progress</option></term>
583 <listitem>
584 <para>
585 Show progress information. Progress information includes the number
586 of relations for which checking has been completed, and the total
587 size of those relations. It also includes the total number of relations
588 that will eventually be checked, and the estimated size of those
589 relations.
590 </para>
591 </listitem>
592 </varlistentry>
593
594 <varlistentry>
595 <term><option>-v</option></term>
596 <term><option>--verbose</option></term>
597 <listitem>
598 <para>
599 Print more messages. In particular, this will print a message for
600 each relation being checked, and will increase the level of detail
601 shown for server errors.
602 </para>
603 </listitem>
604 </varlistentry>
605
606 <varlistentry>
607 <term><option>-V</option></term>
608 <term><option>--version</option></term>
609 <listitem>
610 <para>
611 Print the <application>pg_amcheck</application> version and exit.
612 </para>
613 </listitem>
614 </varlistentry>
615
616 <varlistentry>
617 <term><option>--install-missing</option></term>
618 <term><option>--install-missing=<replaceable class="parameter">schema</replaceable></option></term>
619 <listitem>
620 <para>
621 Install any missing extensions that are required to check the
622 database(s). If not yet installed, each extension's objects will be
623 installed into the given
624 <replaceable class="parameter">schema</replaceable>, or if not specified
625 into schema <literal>pg_catalog</literal>.
626 </para>
627 <para>
628 At present, the only required extension is <xref linkend="amcheck"/>.
629 </para>
630 </listitem>
631 </varlistentry>
632
633 <varlistentry>
634 <term><option>-?</option></term>
635 <term><option>--help</option></term>
636 <listitem>
637 <para>
638 Show help about <application>pg_amcheck</application> command line
639 arguments, and exit.
640 </para>
641 </listitem>
642 </varlistentry>
643 </variablelist>
644 </para>
645 </refsect1>
646
647 <refsect1>
648 <title>Environment</title>
649
650 <para>
651 <command>pg_amcheck</command>, like most other <productname>PostgreSQL</productname>
652 utilities,
653 also uses the environment variables supported by <application>libpq</application>
654 (see <xref linkend="libpq-envars"/>).
655 </para>
656
657 <para>
658 The environment variable <envar>PG_COLOR</envar> specifies whether to use
659 color in diagnostic messages. Possible values are
660 <literal>always</literal>, <literal>auto</literal> and
661 <literal>never</literal>.
662 </para>
663 </refsect1>
664
665 <refsect1>
666 <title>Notes</title>
667
668 <para>
669 <application>pg_amcheck</application> is designed to work with
670 <productname>PostgreSQL</productname> 14.0 and later.
671 </para>
672 </refsect1>
673
674 <refsect1>
675 <title>See Also</title>
676
677 <simplelist type="inline">
678 <member><xref linkend="amcheck"/></member>
679 </simplelist>
680 </refsect1>
681 </refentry>