Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
PG_AMCHECK(1)		 PostgreSQL 17.5 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:

       --checkunique
	   For each index with unique constraint checked, verify that no more
	   than	one among duplicate entries is visible in the index using
	   amcheck's checkunique option.

       --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.

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

PostgreSQL 17.5			     2025			 PG_AMCHECK(1)

Want to link to this manual page? Use this URL:
<https://man.freebsd.org/cgi/man.cgi?query=pg_amcheck&sektion=1&manpath=FreeBSD+Ports+14.3.quarterly>

home | help