1PG_AMCHECK(1)            PostgreSQL 14.3 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 table relations and btree indexes are currently supported. Other
19       relation types are silently skipped.
20
21       If dbname is specified, it should be the name of a single database to
22       check, and no other database selection options should be present.
23       Otherwise, if any database selection options are present, all matching
24       databases will be checked. If no such options are present, the default
25       database will be checked. Database selection options include --all,
26       --database and --exclude-database. They also include --relation,
27       --exclude-relation, --table, --exclude-table, --index, and
28       --exclude-index, but only when such options are used with a three-part
29       pattern (e.g.  mydb*.myschema*.myrel*). Finally, they include --schema
30       and --exclude-schema when such options are used with a two-part pattern
31       (e.g.  mydb*.myschema*).
32
33       dbname can also be a connection string.
34

OPTIONS

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

NOTES

316       pg_amcheck is designed to work with PostgreSQL 14.0 and later.
317

SEE ALSO

319       amcheck
320
321
322
323PostgreSQL 14.3                      2022                        PG_AMCHECK(1)
Impressum