1PG_AMCHECK(1)            PostgreSQL 16.1 Documentation           PG_AMCHECK(1)
2
3
4

NAME

6       pg_amcheck - checks for corruption in one or more PostgreSQL databases
7

SYNOPSIS

9       pg_amcheck [option...] [dbname]
10

DESCRIPTION

12       pg_amcheck supports running amcheck's corruption checking functions
13       against one or more databases, with options to select which schemas,
14       tables and indexes to check, which kinds of checking to perform, and
15       whether to perform the checks in parallel, and if so, the number of
16       parallel connections to establish and use.
17
18       Only ordinary and toast table relations, materialized views, sequences,
19       and btree indexes are currently supported. Other relation types are
20       silently skipped.
21
22       If dbname is specified, it should be the name of a single database to
23       check, and no other database selection options should be present.
24       Otherwise, if any database selection options are present, all matching
25       databases will be checked. If no such options are present, the default
26       database will be checked. Database selection options include --all,
27       --database and --exclude-database. They also include --relation,
28       --exclude-relation, --table, --exclude-table, --index, and
29       --exclude-index, but only when such options are used with a three-part
30       pattern (e.g.  mydb*.myschema*.myrel*). Finally, they include --schema
31       and --exclude-schema when such options are used with a two-part pattern
32       (e.g.  mydb*.myschema*).
33
34       dbname can also be a connection string.
35

OPTIONS

37       The following command-line options control what is checked:
38
39       -a
40       --all
41           Check all databases, except for any excluded via
42           --exclude-database.
43
44       -d pattern
45       --database=pattern
46           Check databases matching the specified pattern, except for any
47           excluded by --exclude-database. This option can be specified more
48           than once.
49
50       -D pattern
51       --exclude-database=pattern
52           Exclude databases matching the given pattern. This option can be
53           specified more than once.
54
55       -i pattern
56       --index=pattern
57           Check indexes matching the specified pattern, unless they are
58           otherwise excluded. This option can be specified more than once.
59
60           This is similar to the --relation option, except that it applies
61           only to indexes, not to other relation types.
62
63       -I pattern
64       --exclude-index=pattern
65           Exclude indexes matching the specified pattern. This option can be
66           specified more than once.
67
68           This is similar to the --exclude-relation option, except that it
69           applies only to indexes, not other relation types.
70
71       -r pattern
72       --relation=pattern
73           Check relations matching the specified pattern, unless they are
74           otherwise excluded. This option can be specified more than once.
75
76           Patterns may be unqualified, e.g.  myrel*, or they may be
77           schema-qualified, e.g.  myschema*.myrel* or database-qualified and
78           schema-qualified, e.g.  mydb*.myschema*.myrel*. A
79           database-qualified pattern will add matching databases to the list
80           of databases to be checked.
81
82       -R pattern
83       --exclude-relation=pattern
84           Exclude relations matching the specified pattern. This option can
85           be specified more than once.
86
87           As with --relation, the pattern may be unqualified,
88           schema-qualified, or database- and schema-qualified.
89
90       -s pattern
91       --schema=pattern
92           Check tables and indexes in schemas matching the specified pattern,
93           unless they are otherwise excluded. This option can be specified
94           more than once.
95
96           To select only tables in schemas matching a particular pattern,
97           consider using something like --table=SCHEMAPAT.*
98           --no-dependent-indexes. To select only indexes, consider using
99           something like --index=SCHEMAPAT.*.
100
101           A schema pattern may be database-qualified. For example, you may
102           write --schema=mydb*.myschema* to select schemas matching myschema*
103           in databases matching mydb*.
104
105       -S pattern
106       --exclude-schema=pattern
107           Exclude tables and indexes in schemas matching the specified
108           pattern. This option can be specified more than once.
109
110           As with --schema, the pattern may be database-qualified.
111
112       -t pattern
113       --table=pattern
114           Check tables matching the specified pattern, unless they are
115           otherwise excluded. This option can be specified more than once.
116
117           This is similar to the --relation option, except that it applies
118           only to tables, materialized views, and sequences, not to indexes.
119
120       -T pattern
121       --exclude-table=pattern
122           Exclude tables matching the specified pattern. This option can be
123           specified more than once.
124
125           This is similar to the --exclude-relation option, except that it
126           applies only to tables, materialized views, and sequences, not to
127           indexes.
128
129       --no-dependent-indexes
130           By default, if a table is checked, any btree indexes of that table
131           will also be checked, even if they are not explicitly selected by
132           an option such as --index or --relation. This option suppresses
133           that behavior.
134
135       --no-dependent-toast
136           By default, if a table is checked, its toast table, if any, will
137           also be checked, even if it is not explicitly selected by an option
138           such as --table or --relation. This option suppresses that
139           behavior.
140
141       --no-strict-names
142           By default, if an argument to --database, --table, --index, or
143           --relation matches no objects, it is a fatal error. This option
144           downgrades that error to a warning.
145
146       The following command-line options control checking of tables:
147
148       --exclude-toast-pointers
149           By default, whenever a toast pointer is encountered in a table, a
150           lookup is performed to ensure that it references apparently-valid
151           entries in the toast table. These checks can be quite slow, and
152           this option can be used to skip them.
153
154       --on-error-stop
155           After reporting all corruptions on the first page of a table where
156           corruption is found, stop processing that table relation and move
157           on to the next table or index.
158
159           Note that index checking always stops after the first corrupt page.
160           This option only has meaning relative to table relations.
161
162       --skip=option
163           If all-frozen is given, table corruption checks will skip over
164           pages in all tables that are marked as all frozen.
165
166           If all-visible is given, table corruption checks will skip over
167           pages in all tables that are marked as all visible.
168
169           By default, no pages are skipped. This can be specified as none,
170           but since this is the default, it need not be mentioned.
171
172       --startblock=block
173           Start checking at the specified block number. An error will occur
174           if the table relation being checked has fewer than this number of
175           blocks. This option does not apply to indexes, and is probably only
176           useful when checking a single table relation. See --endblock for
177           further caveats.
178
179       --endblock=block
180           End checking at the specified block number. An error will occur if
181           the table relation being checked has fewer than this number of
182           blocks. This option does not apply to indexes, and is probably only
183           useful when checking a single table relation. If both a regular
184           table and a toast table are checked, this option will apply to
185           both, but higher-numbered toast blocks may still be accessed while
186           validating toast pointers, unless that is suppressed using
187           --exclude-toast-pointers.
188
189       The following command-line options control checking of B-tree indexes:
190
191       --heapallindexed
192           For each index checked, verify the presence of all heap tuples as
193           index tuples in the index using amcheck's heapallindexed option.
194
195       --parent-check
196           For each btree index checked, use amcheck's bt_index_parent_check
197           function, which performs additional checks of parent/child
198           relationships during index checking.
199
200           The default is to use amcheck's bt_index_check function, but note
201           that use of the --rootdescend option implicitly selects
202           bt_index_parent_check.
203
204       --rootdescend
205           For each index checked, re-find tuples on the leaf level by
206           performing a new search from the root page for each tuple using
207           amcheck's rootdescend option.
208
209           Use of this option implicitly also selects the --parent-check
210           option.
211
212           This form of verification was originally written to help in the
213           development of btree index features. It may be of limited use or
214           even of no use in helping detect the kinds of corruption that occur
215           in practice. It may also cause corruption checking to take
216           considerably longer and consume considerably more resources on the
217           server.
218
219           Warning
220           The extra checks performed against B-tree indexes when the
221           --parent-check option or the --rootdescend option is specified
222           require relatively strong relation-level locks. These checks are
223           the only checks that will block concurrent data modification from
224           INSERT, UPDATE, and DELETE commands.
225
226       The following command-line options control the connection to the
227       server:
228
229       -h hostname
230       --host=hostname
231           Specifies the host name of the machine on which the server is
232           running. If the value begins with a slash, it is used as the
233           directory for the Unix domain socket.
234
235       -p port
236       --port=port
237           Specifies the TCP port or local Unix domain socket file extension
238           on which the server is listening for connections.
239
240       -U
241       --username=username
242           User name to connect as.
243
244       -w
245       --no-password
246           Never issue a password prompt. If the server requires password
247           authentication and a password is not available by other means such
248           as a .pgpass file, the connection attempt will fail. This option
249           can be useful in batch jobs and scripts where no user is present to
250           enter a password.
251
252       -W
253       --password
254           Force pg_amcheck to prompt for a password before connecting to a
255           database.
256
257           This option is never essential, since pg_amcheck will automatically
258           prompt for a password if the server demands password
259           authentication. However, pg_amcheck will waste a connection attempt
260           finding out that the server wants a password. In some cases it is
261           worth typing -W to avoid the extra connection attempt.
262
263       --maintenance-db=dbname
264           Specifies a database or connection string to be used to discover
265           the list of databases to be checked. If neither --all nor any
266           option including a database pattern is used, no such connection is
267           required and this option does nothing. Otherwise, any connection
268           string parameters other than the database name which are included
269           in the value for this option will also be used when connecting to
270           the databases being checked. If this option is omitted, the default
271           is postgres or, if that fails, template1.
272
273       Other options are also available:
274
275       -e
276       --echo
277           Echo to stdout all SQL sent to the server.
278
279       -j num
280       --jobs=num
281           Use num concurrent connections to the server, or one per object to
282           be checked, whichever is less.
283
284           The default is to use a single connection.
285
286       -P
287       --progress
288           Show progress information. Progress information includes the number
289           of relations for which checking has been completed, and the total
290           size of those relations. It also includes the total number of
291           relations that will eventually be checked, and the estimated size
292           of those relations.
293
294       -v
295       --verbose
296           Print more messages. In particular, this will print a message for
297           each relation being checked, and will increase the level of detail
298           shown for server errors.
299
300       -V
301       --version
302           Print the pg_amcheck version and exit.
303
304       --install-missing
305       --install-missing=schema
306           Install any missing extensions that are required to check the
307           database(s). If not yet installed, each extension's objects will be
308           installed into the given schema, or if not specified into schema
309           pg_catalog.
310
311           At present, the only required extension is amcheck.
312
313       -?
314       --help
315           Show help about pg_amcheck command line arguments, and exit.
316

NOTES

318       pg_amcheck is designed to work with PostgreSQL 14.0 and later.
319

SEE ALSO

321       amcheck
322
323
324
325PostgreSQL 16.1                      2023                        PG_AMCHECK(1)
Impressum