1PG_AMCHECK(1) PostgreSQL 16.1 Documentation PG_AMCHECK(1)
2
6 pg_amcheck - checks for corruption in one or more PostgreSQL databases
7
9 pg_amcheck [option...] [dbname]
10
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 Only ordinary and toast table relations, materialized views, sequences,
19 and btree indexes are currently supported. Other relation types are
20 silently skipped.
21 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 dbname can also be a connection string.
35
37 The following command-line options control what is checked:
38 -a
40 --all
41 Check all databases, except for any excluded via
42 --exclude-database.
43 -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 -D pattern
51 --exclude-database=pattern
52 Exclude databases matching the given pattern. This option can be
53 specified more than once.
54 -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 This is similar to the --relation option, except that it applies
61 only to indexes, not to other relation types.
62 -I pattern
64 --exclude-index=pattern
65 Exclude indexes matching the specified pattern. This option can be
66 specified more than once.
67 This is similar to the --exclude-relation option, except that it
69 applies only to indexes, not other relation types.
70 -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 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 -R pattern
83 --exclude-relation=pattern
84 Exclude relations matching the specified pattern. This option can
85 be specified more than once.
86 As with --relation, the pattern may be unqualified,
88 schema-qualified, or database- and schema-qualified.
89 -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 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 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 -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 As with --schema, the pattern may be database-qualified.
111 -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 This is similar to the --relation option, except that it applies
118 only to tables, materialized views, and sequences, not to indexes.
119 -T pattern
121 --exclude-table=pattern
122 Exclude tables matching the specified pattern. This option can be
123 specified more than once.
124 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 --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 --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 --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 The following command-line options control checking of tables:
147 --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 --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 Note that index checking always stops after the first corrupt page.
160 This option only has meaning relative to table relations.
161 --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 If all-visible is given, table corruption checks will skip over
167 pages in all tables that are marked as all visible.
168 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 --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 --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 The following command-line options control checking of B-tree indexes:
190 --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 --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 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 --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 Use of this option implicitly also selects the --parent-check
210 option.
211 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 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 The following command-line options control the connection to the
227 server:
228 -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 -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 -U
241 --username=username
242 User name to connect as.
243 -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 -W
253 --password
254 Force pg_amcheck to prompt for a password before connecting to a
255 database.
256 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 --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 Other options are also available:
274 -e
276 --echo
277 Echo to stdout all SQL sent to the server.
278 -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 The default is to use a single connection.
285 -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 -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 -V
301 --version
302 Print the pg_amcheck version and exit.
303 --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 At present, the only required extension is amcheck.
312 -?
314 --help
315 Show help about pg_amcheck command line arguments, and exit.
316
318 pg_amcheck is designed to work with PostgreSQL 14.0 and later.
319
321 amcheck
322PostgreSQL 16.1 2023 PG_AMCHECK(1)