1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>pg_amcheck</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="app-ecpg.html" title="ecpg" /><link rel="next" href="app-pgbasebackup.html" title="pg_basebackup" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center"><span class="application">pg_amcheck</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="app-ecpg.html" title="ecpg">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><th width="60%" align="center">PostgreSQL Client Applications</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="app-pgbasebackup.html" title="pg_basebackup">Next</a></td></tr></table><hr /></div><div class="refentry" id="APP-PGAMCHECK"><div class="titlepage"></div><a id="id-1.9.4.9.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">pg_amcheck</span></span></h2><p>pg_amcheck — checks for corruption in one or more
3 <span class="productname">PostgreSQL</span> databases</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.4.9.4.1"><code class="command">pg_amcheck</code> [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>dbname</code></em>]</p></div></div><div class="refsect1" id="id-1.9.4.9.5"><h2>Description</h2><p>
4 <span class="application">pg_amcheck</span> supports running
5 <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>'s corruption checking functions against one or
6 more databases, with options to select which schemas, tables and indexes to
7 check, which kinds of checking to perform, and whether to perform the checks
8 in parallel, and if so, the number of parallel connections to establish and
11 Only ordinary and toast table relations, materialized views, sequences, and
12 btree indexes are currently supported. Other relation types are silently
15 If <code class="literal">dbname</code> is specified, it should be the name of a
16 single database to check, and no other database selection options should
17 be present. Otherwise, if any database selection options are present,
18 all matching databases will be checked. If no such options are present,
19 the default database will be checked. Database selection options include
20 <code class="option">--all</code>, <code class="option">--database</code> and
21 <code class="option">--exclude-database</code>. They also include
22 <code class="option">--relation</code>, <code class="option">--exclude-relation</code>,
23 <code class="option">--table</code>, <code class="option">--exclude-table</code>,
24 <code class="option">--index</code>, and <code class="option">--exclude-index</code>,
25 but only when such options are used with a three-part pattern
26 (e.g. <code class="option">mydb*.myschema*.myrel*</code>). Finally, they include
27 <code class="option">--schema</code> and <code class="option">--exclude-schema</code>
28 when such options are used with a two-part pattern
29 (e.g. <code class="option">mydb*.myschema*</code>).
31 <em class="replaceable"><code>dbname</code></em> can also be a
32 <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="32.1.1. Connection Strings">connection string</a>.
33 </p></div><div class="refsect1" id="id-1.9.4.9.6"><h2>Options</h2><p>
34 The following command-line options control what is checked:
36 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-a</code><br /></span><span class="term"><code class="option">--all</code></span></dt><dd><p>
37 Check all databases, except for any excluded via
38 <code class="option">--exclude-database</code>.
39 </p></dd><dt><span class="term"><code class="option">-d <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--database=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
40 Check databases matching the specified
41 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>,
42 except for any excluded by <code class="option">--exclude-database</code>.
43 This option can be specified more than once.
44 </p></dd><dt><span class="term"><code class="option">-D <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-database=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
45 Exclude databases matching the given
46 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>.
47 This option can be specified more than once.
48 </p></dd><dt><span class="term"><code class="option">-i <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--index=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
49 Check indexes matching the specified
50 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>,
51 unless they are otherwise excluded.
52 This option can be specified more than once.
54 This is similar to the <code class="option">--relation</code> option, except that
55 it applies only to indexes, not to other relation types.
56 </p></dd><dt><span class="term"><code class="option">-I <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-index=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
57 Exclude indexes matching the specified
58 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>.
59 This option can be specified more than once.
61 This is similar to the <code class="option">--exclude-relation</code> option,
62 except that it applies only to indexes, not other relation types.
63 </p></dd><dt><span class="term"><code class="option">-r <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--relation=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
64 Check relations matching the specified
65 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>,
66 unless they are otherwise excluded.
67 This option can be specified more than once.
69 Patterns may be unqualified, e.g. <code class="literal">myrel*</code>, or they
70 may be schema-qualified, e.g. <code class="literal">myschema*.myrel*</code> or
71 database-qualified and schema-qualified, e.g.
72 <code class="literal">mydb*.myschema*.myrel*</code>. A database-qualified
73 pattern will add matching databases to the list of databases to be
75 </p></dd><dt><span class="term"><code class="option">-R <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-relation=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
76 Exclude relations matching the specified
77 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>.
78 This option can be specified more than once.
80 As with <code class="option">--relation</code>, the
81 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a> may be unqualified, schema-qualified,
82 or database- and schema-qualified.
83 </p></dd><dt><span class="term"><code class="option">-s <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--schema=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
84 Check tables and indexes in schemas matching the specified
85 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>, unless they are otherwise excluded.
86 This option can be specified more than once.
88 To select only tables in schemas matching a particular pattern,
89 consider using something like
90 <code class="literal">--table=SCHEMAPAT.* --no-dependent-indexes</code>.
91 To select only indexes, consider using something like
92 <code class="literal">--index=SCHEMAPAT.*</code>.
94 A schema pattern may be database-qualified. For example, you may
95 write <code class="literal">--schema=mydb*.myschema*</code> to select
96 schemas matching <code class="literal">myschema*</code> in databases matching
97 <code class="literal">mydb*</code>.
98 </p></dd><dt><span class="term"><code class="option">-S <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-schema=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
99 Exclude tables and indexes in schemas matching the specified
100 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>.
101 This option can be specified more than once.
103 As with <code class="option">--schema</code>, the pattern may be
105 </p></dd><dt><span class="term"><code class="option">-t <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--table=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
106 Check tables matching the specified
107 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>,
108 unless they are otherwise excluded.
109 This option can be specified more than once.
111 This is similar to the <code class="option">--relation</code> option, except that
112 it applies only to tables, materialized views, and sequences, not to
114 </p></dd><dt><span class="term"><code class="option">-T <em class="replaceable"><code>pattern</code></em></code><br /></span><span class="term"><code class="option">--exclude-table=<em class="replaceable"><code>pattern</code></em></code></span></dt><dd><p>
115 Exclude tables matching the specified
116 <a class="link" href="app-psql.html#APP-PSQL-PATTERNS" title="Patterns"><em class="replaceable"><code>pattern</code></em></a>.
117 This option can be specified more than once.
119 This is similar to the <code class="option">--exclude-relation</code> option,
120 except that it applies only to tables, materialized views, and
121 sequences, not to indexes.
122 </p></dd><dt><span class="term"><code class="option">--no-dependent-indexes</code></span></dt><dd><p>
123 By default, if a table is checked, any btree indexes of that table
124 will also be checked, even if they are not explicitly selected by
125 an option such as <code class="literal">--index</code> or
126 <code class="literal">--relation</code>. This option suppresses that behavior.
127 </p></dd><dt><span class="term"><code class="option">--no-dependent-toast</code></span></dt><dd><p>
128 By default, if a table is checked, its toast table, if any, will also
129 be checked, even if it is not explicitly selected by an option
130 such as <code class="literal">--table</code> or <code class="literal">--relation</code>.
131 This option suppresses that behavior.
132 </p></dd><dt><span class="term"><code class="option">--no-strict-names</code></span></dt><dd><p>
133 By default, if an argument to <code class="literal">--database</code>,
134 <code class="literal">--table</code>, <code class="literal">--index</code>,
135 or <code class="literal">--relation</code> matches no objects, it is a fatal
136 error. This option downgrades that error to a warning.
137 </p></dd></dl></div><p>
139 The following command-line options control checking of tables:
141 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">--exclude-toast-pointers</code></span></dt><dd><p>
142 By default, whenever a toast pointer is encountered in a table,
143 a lookup is performed to ensure that it references apparently-valid
144 entries in the toast table. These checks can be quite slow, and this
145 option can be used to skip them.
146 </p></dd><dt><span class="term"><code class="option">--on-error-stop</code></span></dt><dd><p>
147 After reporting all corruptions on the first page of a table where
148 corruption is found, stop processing that table relation and move on
149 to the next table or index.
151 Note that index checking always stops after the first corrupt page.
152 This option only has meaning relative to table relations.
153 </p></dd><dt><span class="term"><code class="option">--skip=<em class="replaceable"><code>option</code></em></code></span></dt><dd><p>
154 If <code class="literal">all-frozen</code> is given, table corruption checks
155 will skip over pages in all tables that are marked as all frozen.
157 If <code class="literal">all-visible</code> is given, table corruption checks
158 will skip over pages in all tables that are marked as all visible.
160 By default, no pages are skipped. This can be specified as
161 <code class="literal">none</code>, but since this is the default, it need not be
163 </p></dd><dt><span class="term"><code class="option">--startblock=<em class="replaceable"><code>block</code></em></code></span></dt><dd><p>
164 Start checking at the specified block number. An error will occur if
165 the table relation being checked has fewer than this number of blocks.
166 This option does not apply to indexes, and is probably only useful
167 when checking a single table relation. See <code class="literal">--endblock</code>
169 </p></dd><dt><span class="term"><code class="option">--endblock=<em class="replaceable"><code>block</code></em></code></span></dt><dd><p>
170 End checking at the specified block number. An error will occur if the
171 table relation being checked has fewer than this number of blocks.
172 This option does not apply to indexes, and is probably only useful when
173 checking a single table relation. If both a regular table and a toast
174 table are checked, this option will apply to both, but higher-numbered
175 toast blocks may still be accessed while validating toast pointers,
176 unless that is suppressed using
177 <code class="option">--exclude-toast-pointers</code>.
178 </p></dd></dl></div><p>
180 The following command-line options control checking of B-tree indexes:
182 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">--checkunique</code></span></dt><dd><p>
183 For each index with unique constraint checked, verify that no more than
184 one among duplicate entries is visible in the index using <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>'s
185 <code class="option">checkunique</code> option.
186 </p></dd><dt><span class="term"><code class="option">--heapallindexed</code></span></dt><dd><p>
187 For each index checked, verify the presence of all heap tuples as index
188 tuples in the index using <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>'s
189 <code class="option">heapallindexed</code> option.
190 </p></dd><dt><span class="term"><code class="option">--parent-check</code></span></dt><dd><p>
191 For each btree index checked, use <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>'s
192 <code class="function">bt_index_parent_check</code> function, which performs
193 additional checks of parent/child relationships during index checking.
195 The default is to use <span class="application">amcheck</span>'s
196 <code class="function">bt_index_check</code> function, but note that use of the
197 <code class="option">--rootdescend</code> option implicitly selects
198 <code class="function">bt_index_parent_check</code>.
199 </p></dd><dt><span class="term"><code class="option">--rootdescend</code></span></dt><dd><p>
200 For each index checked, re-find tuples on the leaf level by performing a
201 new search from the root page for each tuple using
202 <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>'s <code class="option">rootdescend</code> option.
204 Use of this option implicitly also selects the
205 <code class="option">--parent-check</code> option.
207 This form of verification was originally written to help in the
208 development of btree index features. It may be of limited use or even
209 of no use in helping detect the kinds of corruption that occur in
210 practice. It may also cause corruption checking to take considerably
211 longer and consume considerably more resources on the server.
212 </p></dd></dl></div><p>
213 </p><div class="warning"><h3 class="title">Warning</h3><p>
214 The extra checks performed against B-tree indexes when the
215 <code class="option">--parent-check</code> option or the
216 <code class="option">--rootdescend</code> option is specified require
217 relatively strong relation-level locks. These checks are the only
218 checks that will block concurrent data modification from
219 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and
220 <code class="command">DELETE</code> commands.
222 The following command-line options control the connection to the server:
224 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-h <em class="replaceable"><code>hostname</code></em></code><br /></span><span class="term"><code class="option">--host=<em class="replaceable"><code>hostname</code></em></code></span></dt><dd><p>
225 Specifies the host name of the machine on which the server is running.
226 If the value begins with a slash, it is used as the directory for the
228 </p></dd><dt><span class="term"><code class="option">-p <em class="replaceable"><code>port</code></em></code><br /></span><span class="term"><code class="option">--port=<em class="replaceable"><code>port</code></em></code></span></dt><dd><p>
229 Specifies the TCP port or local Unix domain socket file extension on
230 which the server is listening for connections.
231 </p></dd><dt><span class="term"><code class="option">-U</code><br /></span><span class="term"><code class="option">--username=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
232 User name to connect as.
233 </p></dd><dt><span class="term"><code class="option">-w</code><br /></span><span class="term"><code class="option">--no-password</code></span></dt><dd><p>
234 Never issue a password prompt. If the server requires password
235 authentication and a password is not available by other means such as
236 a <code class="filename">.pgpass</code> file, the connection attempt will fail.
237 This option can be useful in batch jobs and scripts where no user is
238 present to enter a password.
239 </p></dd><dt><span class="term"><code class="option">-W</code><br /></span><span class="term"><code class="option">--password</code></span></dt><dd><p>
240 Force <span class="application">pg_amcheck</span> to prompt for a password
241 before connecting to a database.
243 This option is never essential, since
244 <span class="application">pg_amcheck</span> will automatically prompt for a
245 password if the server demands password authentication. However,
246 <span class="application">pg_amcheck</span> will waste a connection attempt
247 finding out that the server wants a password. In some cases it is
248 worth typing <code class="option">-W</code> to avoid the extra connection attempt.
249 </p></dd><dt><span class="term"><code class="option">--maintenance-db=<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>
250 Specifies a database or
251 <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="32.1.1. Connection Strings">connection string</a> to be
252 used to discover the list of databases to be checked. If neither
253 <code class="option">--all</code> nor any option including a database pattern is
254 used, no such connection is required and this option does nothing.
255 Otherwise, any connection string parameters other than
256 the database name which are included in the value for this option
257 will also be used when connecting to the databases
258 being checked. If this option is omitted, the default is
259 <code class="literal">postgres</code> or, if that fails,
260 <code class="literal">template1</code>.
261 </p></dd></dl></div><p>
263 Other options are also available:
265 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-e</code><br /></span><span class="term"><code class="option">--echo</code></span></dt><dd><p>
266 Echo to stdout all SQL sent to the server.
267 </p></dd><dt><span class="term"><code class="option">-j <em class="replaceable"><code>num</code></em></code><br /></span><span class="term"><code class="option">--jobs=<em class="replaceable"><code>num</code></em></code></span></dt><dd><p>
268 Use <em class="replaceable"><code>num</code></em> concurrent connections to the server,
269 or one per object to be checked, whichever is less.
271 The default is to use a single connection.
272 </p></dd><dt><span class="term"><code class="option">-P</code><br /></span><span class="term"><code class="option">--progress</code></span></dt><dd><p>
273 Show progress information. Progress information includes the number
274 of relations for which checking has been completed, and the total
275 size of those relations. It also includes the total number of relations
276 that will eventually be checked, and the estimated size of those
278 </p></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--verbose</code></span></dt><dd><p>
279 Print more messages. In particular, this will print a message for
280 each relation being checked, and will increase the level of detail
281 shown for server errors.
282 </p></dd><dt><span class="term"><code class="option">-V</code><br /></span><span class="term"><code class="option">--version</code></span></dt><dd><p>
283 Print the <span class="application">pg_amcheck</span> version and exit.
284 </p></dd><dt><span class="term"><code class="option">--install-missing</code><br /></span><span class="term"><code class="option">--install-missing=<em class="replaceable"><code>schema</code></em></code></span></dt><dd><p>
285 Install any missing extensions that are required to check the
286 database(s). If not yet installed, each extension's objects will be
287 installed into the given
288 <em class="replaceable"><code>schema</code></em>, or if not specified
289 into schema <code class="literal">pg_catalog</code>.
291 At present, the only required extension is <a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a>.
292 </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>
293 Show help about <span class="application">pg_amcheck</span> command line
295 </p></dd></dl></div><p>
296 </p></div><div class="refsect1" id="id-1.9.4.9.7"><h2>Environment</h2><p>
297 <code class="command">pg_amcheck</code>, like most other <span class="productname">PostgreSQL</span>
299 also uses the environment variables supported by <span class="application">libpq</span>
300 (see <a class="xref" href="libpq-envars.html" title="32.15. Environment Variables">Section 32.15</a>).
302 The environment variable <code class="envar">PG_COLOR</code> specifies whether to use
303 color in diagnostic messages. Possible values are
304 <code class="literal">always</code>, <code class="literal">auto</code> and
305 <code class="literal">never</code>.
306 </p></div><div class="refsect1" id="id-1.9.4.9.8"><h2>Notes</h2><p>
307 <span class="application">pg_amcheck</span> is designed to work with
308 <span class="productname">PostgreSQL</span> 14.0 and later.
309 </p></div><div class="refsect1" id="id-1.9.4.9.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="amcheck.html" title="F.1. amcheck — tools to verify table and index consistency">amcheck</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="app-ecpg.html" title="ecpg">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="reference-client.html" title="PostgreSQL Client Applications">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="app-pgbasebackup.html" title="pg_basebackup">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">ecpg</span> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_basebackup</span></td></tr></table></div></body></html>