doc/src/sgml/ref/pg_amcheck.sgml

   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>