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
-dpattern --database=pattern
-Dpattern --exclude-database=pattern
-ipattern --index=pattern
This is similar to the --relation option, except that it applies only to indexes, not to other relation types.
-Ipattern --exclude-index=pattern
This is similar to the --exclude-relation option, except that it applies only to indexes, not other relation types.
-rpattern --relation=pattern
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.
-Rpattern --exclude-relation=pattern
As with --relation, the pattern may be unqualified, schema-qualified, or database- and schema-qualified.
-spattern --schema=pattern
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*.
-Spattern --exclude-schema=pattern
As with --schema, the pattern may be database-qualified.
-tpattern --table=pattern
This is similar to the --relation option, except that it applies only to tables, materialized views, and sequences, not to indexes.
-Tpattern --exclude-table=pattern
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
--no-dependent-toast
--no-strict-names
The following command-line options control checking of tables:
--exclude-toast-pointers
--on-error-stop
Note that index checking always stops after the first corrupt page. This option only has meaning relative to table relations.
--skip=option
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
--endblock=block
The following command-line options control checking of B-tree indexes:
--checkunique
--heapallindexed
--parent-check
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
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.
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:
-hhostname --host=hostname
-pport --port=port
-U --username=username
-w --no-password
-W --password
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
Other options are also available:
-e --echo
-jnum --jobs=num
The default is to use a single connection.
-P --progress
-v --verbose
-V --version
--install-missing --install-missing=schema
At present, the only required extension is amcheck.
-? --help
ENVIRONMENT
pg_amcheck, like most other PostgreSQL utilities, also uses the environment variables supported by libpq (see Section 32.15).
The environment variable PG_COLOR specifies whether to use color in diagnostic messages. Possible values are always, auto and never.
NOTES
pg_amcheck is designed to work with PostgreSQL 14.0 and later.
SEE ALSO
amcheck