1PG_AMCHECK(1) PostgreSQL 14.3 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 table relations and btree indexes are currently supported. Other
19 relation types are silently skipped.
20 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 dbname can also be a connection string.
34
36 The following command-line options control what is checked:
37 -a
39 --all
40 Check all databases, except for any excluded via
41 --exclude-database.
42 -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 -D pattern
50 --exclude-database=pattern
51 Exclude databases matching the given pattern. This option can be
52 specified more than once.
53 -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 This is similar to the --relation option, except that it applies
60 only to indexes, not tables.
61 -I pattern
63 --exclude-index=pattern
64 Exclude indexes matching the specified pattern. This option can be
65 specified more than once.
66 This is similar to the --exclude-relation option, except that it
68 applies only to indexes, not tables.
69 -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 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 -R pattern
82 --exclude-relation=pattern
83 Exclude relations matching the specified pattern. This option can
84 be specified more than once.
85 As with --relation, the pattern may be unqualified,
87 schema-qualified, or database- and schema-qualified.
88 -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 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 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 -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 As with --schema, the pattern may be database-qualified.
110 -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 This is similar to the --relation option, except that it applies
117 only to tables, not indexes.
118 -T pattern
120 --exclude-table=pattern
121 Exclude tables matching the specified pattern. This option can be
122 specified more than once.
123 This is similar to the --exclude-relation option, except that it
125 applies only to tables, not indexes.
126 --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 --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 --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 The following command-line options control checking of tables:
145 --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 --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 Note that index checking always stops after the first corrupt page.
158 This option only has meaning relative to table relations.
159 --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 If all-visible is given, table corruption checks will skip over
165 pages in all tables that are marked as all visible.
166 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 --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 --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 The following command-line options control checking of B-tree indexes:
188 --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 --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 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 --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 Use of this option implicitly also selects the --parent-check
208 option.
209 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 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 The following command-line options control the connection to the
225 server:
226 -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 -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 -U
239 --username=username
240 User name to connect as.
241 -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 -W
251 --password
252 Force pg_amcheck to prompt for a password before connecting to a
253 database.
254 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 --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 Other options are also available:
272 -e
274 --echo
275 Echo to stdout all SQL sent to the server.
276 -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 The default is to use a single connection.
283 -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 -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 -V
299 --version
300 Print the pg_amcheck version and exit.
301 --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 At present, the only required extension is amcheck.
310 -?
312 --help
313 Show help about pg_amcheck command line arguments, and exit.
314
316 pg_amcheck is designed to work with PostgreSQL 14.0 and later.
317
319 amcheck
320PostgreSQL 14.3 2022 PG_AMCHECK(1)