PG_AMCHECK(1) PostgreSQL 15.7 Documentation PG_AMCHECK(1)
NAME
pg_amcheck - checks for corruption in one or more PostgreSQL databases
SYNOPSIS
pg_amcheck [option...] [dbname]
DESCRIPTION
pg_amcheck supports running amcheck's corruption checking functions
against one or more databases, with options to select which schemas,
tables and indexes to check, which kinds of checking to perform, and
whether to perform the checks in parallel, and if so, the number of
parallel connections to establish and use.
Only ordinary and toast table relations, materialized views, sequences,
and btree indexes are currently supported. Other relation types are
silently skipped.
If dbname is specified, it should be the name of a single database to
check, and no other database selection options should be present.
Otherwise, if any database selection options are present, all matching
databases will be checked. If no such options are present, the default
database will be checked. Database selection options include --all,
--database and --exclude-database. They also include --relation,
--exclude-relation, --table, --exclude-table, --index, and
--exclude-index, but only when such options are used with a three-part
pattern (e.g. mydb*.myschema*.myrel*). Finally, they include --schema
and --exclude-schema when such options are used with a two-part pattern
(e.g. mydb*.myschema*).
dbname can also be a connection string.
OPTIONS
The following command-line options control what is checked:
-a
--all
Check all databases, except for any excluded via
--exclude-database.
-d pattern
--database=pattern
Check databases matching the specified pattern, except for any
excluded by --exclude-database. This option can be specified more
than once.
-D pattern
--exclude-database=pattern
Exclude databases matching the given pattern. This option can be
specified more than once.
-i pattern
--index=pattern
Check indexes matching the specified pattern, unless they are
otherwise excluded. This option can be specified more than once.
This is similar to the --relation option, except that it applies
only to indexes, not to other relation types.
-I pattern
--exclude-index=pattern
Exclude indexes matching the specified pattern. This option can be
specified more than once.
This is similar to the --exclude-relation option, except that it
applies only to indexes, not other relation types.
-r pattern
--relation=pattern
Check relations matching the specified pattern, unless they are
otherwise excluded. This option can be specified more than once.
Patterns may be unqualified, e.g. myrel*, or they may be
schema-qualified, e.g. myschema*.myrel* or database-qualified and
schema-qualified, e.g. mydb*.myschema*.myrel*. A
database-qualified pattern will add matching databases to the list
of databases to be checked.
-R pattern
--exclude-relation=pattern
Exclude relations matching the specified pattern. This option can
be specified more than once.
As with --relation, the pattern may be unqualified,
schema-qualified, or database- and schema-qualified.
-s pattern
--schema=pattern
Check tables and indexes in schemas matching the specified pattern,
unless they are otherwise excluded. This option can be specified
more than once.
To select only tables in schemas matching a particular pattern,
consider using something like --table=SCHEMAPAT.*
--no-dependent-indexes. To select only indexes, consider using
something like --index=SCHEMAPAT.*.
A schema pattern may be database-qualified. For example, you may
write --schema=mydb*.myschema* to select schemas matching myschema*
in databases matching mydb*.
-S pattern
--exclude-schema=pattern
Exclude tables and indexes in schemas matching the specified
pattern. This option can be specified more than once.
As with --schema, the pattern may be database-qualified.
-t pattern
--table=pattern
Check tables matching the specified pattern, unless they are
otherwise excluded. This option can be specified more than once.
This is similar to the --relation option, except that it applies
only to tables, materialized views, and sequences, not to indexes.
-T pattern
--exclude-table=pattern
Exclude tables matching the specified pattern. This option can be
specified more than once.
This is similar to the --exclude-relation option, except that it
applies only to tables, materialized views, and sequences, not to
indexes.
--no-dependent-indexes
By default, if a table is checked, any btree indexes of that table
will also be checked, even if they are not explicitly selected by
an option such as --index or --relation. This option suppresses
that behavior.
--no-dependent-toast
By default, if a table is checked, its toast table, if any, will
also be checked, even if it is not explicitly selected by an option
such as --table or --relation. This option suppresses that
behavior.
--no-strict-names
By default, if an argument to --database, --table, --index, or
--relation matches no objects, it is a fatal error. This option
downgrades that error to a warning.
The following command-line options control checking of tables:
--exclude-toast-pointers
By default, whenever a toast pointer is encountered in a table, a
lookup is performed to ensure that it references apparently-valid
entries in the toast table. These checks can be quite slow, and
this option can be used to skip them.
--on-error-stop
After reporting all corruptions on the first page of a table where
corruption is found, stop processing that table relation and move
on to the next table or index.
Note that index checking always stops after the first corrupt page.
This option only has meaning relative to table relations.
--skip=option
If all-frozen is given, table corruption checks will skip over
pages in all tables that are marked as all frozen.
If all-visible is given, table corruption checks will skip over
pages in all tables that are marked as all visible.
By default, no pages are skipped. This can be specified as none,
but since this is the default, it need not be mentioned.
--startblock=block
Start checking at the specified block number. An error will occur
if the table relation being checked has fewer than this number of
blocks. This option does not apply to indexes, and is probably only
useful when checking a single table relation. See --endblock for
further caveats.
--endblock=block
End checking at the specified block number. An error will occur if
the table relation being checked has fewer than this number of
blocks. This option does not apply to indexes, and is probably only
useful when checking a single table relation. If both a regular
table and a toast table are checked, this option will apply to
both, but higher-numbered toast blocks may still be accessed while
validating toast pointers, unless that is suppressed using
--exclude-toast-pointers.
The following command-line options control checking of B-tree indexes:
--heapallindexed
For each index checked, verify the presence of all heap tuples as
index tuples in the index using amcheck's heapallindexed option.
--parent-check
For each btree index checked, use amcheck's bt_index_parent_check
function, which performs additional checks of parent/child
relationships during index checking.
The default is to use amcheck's bt_index_check function, but note
that use of the --rootdescend option implicitly selects
bt_index_parent_check.
--rootdescend
For each index checked, re-find tuples on the leaf level by
performing a new search from the root page for each tuple using
amcheck's rootdescend option.
Use of this option implicitly also selects the --parent-check
option.
This form of verification was originally written to help in the
development of btree index features. It may be of limited use or
even of no use in helping detect the kinds of corruption that occur
in practice. It may also cause corruption checking to take
considerably longer and consume considerably more resources on the
server.
Warning
The extra checks performed against B-tree indexes when the
--parent-check option or the --rootdescend option is specified
require relatively strong relation-level locks. These checks are
the only checks that will block concurrent data modification from
INSERT, UPDATE, and DELETE commands.
The following command-line options control the connection to the
server:
-h hostname
--host=hostname
Specifies the host name of the machine on which the server is
running. If the value begins with a slash, it is used as the
directory for the Unix domain socket.
-p port
--port=port
Specifies the TCP port or local Unix domain socket file extension
on which the server is listening for connections.
-U
--username=username
User name to connect as.
-w
--no-password
Never issue a password prompt. If the server requires password
authentication and a password is not available by other means such
as a .pgpass file, the connection attempt will fail. This option
can be useful in batch jobs and scripts where no user is present to
enter a password.
-W
--password
Force pg_amcheck to prompt for a password before connecting to a
database.
This option is never essential, since pg_amcheck will automatically
prompt for a password if the server demands password
authentication. However, pg_amcheck will waste a connection attempt
finding out that the server wants a password. In some cases it is
worth typing -W to avoid the extra connection attempt.
--maintenance-db=dbname
Specifies a database or connection string to be used to discover
the list of databases to be checked. If neither --all nor any
option including a database pattern is used, no such connection is
required and this option does nothing. Otherwise, any connection
string parameters other than the database name which are included
in the value for this option will also be used when connecting to
the databases being checked. If this option is omitted, the default
is postgres or, if that fails, template1.
Other options are also available:
-e
--echo
Echo to stdout all SQL sent to the server.
-j num
--jobs=num
Use num concurrent connections to the server, or one per object to
be checked, whichever is less.
The default is to use a single connection.
-P
--progress
Show progress information. Progress information includes the number
of relations for which checking has been completed, and the total
size of those relations. It also includes the total number of
relations that will eventually be checked, and the estimated size
of those relations.
-v
--verbose
Print more messages. In particular, this will print a message for
each relation being checked, and will increase the level of detail
shown for server errors.
-V
--version
Print the pg_amcheck version and exit.
--install-missing
--install-missing=schema
Install any missing extensions that are required to check the
database(s). If not yet installed, each extension's objects will be
installed into the given schema, or if not specified into schema
pg_catalog.
At present, the only required extension is amcheck.
-?
--help
Show help about pg_amcheck command line arguments, and exit.
NOTES
pg_amcheck is designed to work with PostgreSQL 14.0 and later.
SEE ALSO
amcheck
PostgreSQL 15.7 2024 PG_AMCHECK(1)
Generated by dwww version 1.15 on Sat Jun 29 02:14:55 CEST 2024.