codekingpro/portable-devtools
115k
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 16.3 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 more3 <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 running5 <a class="xref" href="amcheck.html" title="F.2. amcheck — tools to verify table and index consistency">amcheck</a>'s corruption checking functions against one or6 more databases, with options to select which schemas, tables and indexes to7 check, which kinds of checking to perform, and whether to perform the checks8 in parallel, and if so, the number of parallel connections to establish and9 use.10 </p><p>11 Only ordinary and toast table relations, materialized views, sequences, and12 btree indexes are currently supported. Other relation types are silently13 skipped.14 </p><p>15 If <code class="literal">dbname</code> is specified, it should be the name of a16 single database to check, and no other database selection options should17 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 include20 <code class="option">--all</code>, <code class="option">--database</code> and21 <code class="option">--exclude-database</code>. They also include22 <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 pattern26 (e.g. <code class="option">mydb*.myschema*.myrel*</code>). Finally, they include27 <code class="option">--schema</code> and <code class="option">--exclude-schema</code>28 when such options are used with a two-part pattern29 (e.g. <code class="option">mydb*.myschema*</code>).30 </p><p>31 <em class="replaceable"><code>dbname</code></em> can also be a32 <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.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:35 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 via38 <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 specified41 <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 given46 <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 specified50 <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.53 </p><p>54 This is similar to the <code class="option">--relation</code> option, except that55 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 specified58 <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.60 </p><p>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 specified65 <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.68 </p><p>69 Patterns may be unqualified, e.g. <code class="literal">myrel*</code>, or they70 may be schema-qualified, e.g. <code class="literal">myschema*.myrel*</code> or71 database-qualified and schema-qualified, e.g.72 <code class="literal">mydb*.myschema*.myrel*</code>. A database-qualified73 pattern will add matching databases to the list of databases to be74 checked.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 specified77 <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.79 </p><p>80 As with <code class="option">--relation</code>, the81 <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 specified85 <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.87 </p><p>88 To select only tables in schemas matching a particular pattern,89 consider using something like90 <code class="literal">--table=SCHEMAPAT.* --no-dependent-indexes</code>.91 To select only indexes, consider using something like92 <code class="literal">--index=SCHEMAPAT.*</code>.93 </p><p>94 A schema pattern may be database-qualified. For example, you may95 write <code class="literal">--schema=mydb*.myschema*</code> to select96 schemas matching <code class="literal">myschema*</code> in databases matching97 <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 specified100 <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.102 </p><p>103 As with <code class="option">--schema</code>, the pattern may be104 database-qualified.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 specified107 <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.110 </p><p>111 This is similar to the <code class="option">--relation</code> option, except that112 it applies only to tables, materialized views, and sequences, not to113 indexes.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 specified116 <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.118 </p><p>119 This is similar to the <code class="option">--exclude-relation</code> option,120 except that it applies only to tables, materialized views, and121 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 table124 will also be checked, even if they are not explicitly selected by125 an option such as <code class="literal">--index</code> or126 <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 also129 be checked, even if it is not explicitly selected by an option130 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 fatal136 error. This option downgrades that error to a warning.137 </p></dd></dl></div><p>138 </p><p>139 The following command-line options control checking of tables:140 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-valid144 entries in the toast table. These checks can be quite slow, and this145 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 where148 corruption is found, stop processing that table relation and move on149 to the next table or index.150 </p><p>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 checks155 will skip over pages in all tables that are marked as all frozen.156 </p><p>157 If <code class="literal">all-visible</code> is given, table corruption checks158 will skip over pages in all tables that are marked as all visible.159 </p><p>160 By default, no pages are skipped. This can be specified as161 <code class="literal">none</code>, but since this is the default, it need not be162 mentioned.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 if165 the table relation being checked has fewer than this number of blocks.166 This option does not apply to indexes, and is probably only useful167 when checking a single table relation. See <code class="literal">--endblock</code>168 for further caveats.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 the171 table relation being checked has fewer than this number of blocks.172 This option does not apply to indexes, and is probably only useful when173 checking a single table relation. If both a regular table and a toast174 table are checked, this option will apply to both, but higher-numbered175 toast blocks may still be accessed while validating toast pointers,176 unless that is suppressed using177 <code class="option">--exclude-toast-pointers</code>.178 </p></dd></dl></div><p>179 </p><p>180 The following command-line options control checking of B-tree indexes:181 182 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">--heapallindexed</code></span></dt><dd><p>183 For each index checked, verify the presence of all heap tuples as index184 tuples in the index using <a class="xref" href="amcheck.html" title="F.2. amcheck — tools to verify table and index consistency">amcheck</a>'s185 <code class="option">heapallindexed</code> option.186 </p></dd><dt><span class="term"><code class="option">--parent-check</code></span></dt><dd><p>187 For each btree index checked, use <a class="xref" href="amcheck.html" title="F.2. amcheck — tools to verify table and index consistency">amcheck</a>'s188 <code class="function">bt_index_parent_check</code> function, which performs189 additional checks of parent/child relationships during index checking.190 </p><p>191 The default is to use <span class="application">amcheck</span>'s192 <code class="function">bt_index_check</code> function, but note that use of the193 <code class="option">--rootdescend</code> option implicitly selects194 <code class="function">bt_index_parent_check</code>.195 </p></dd><dt><span class="term"><code class="option">--rootdescend</code></span></dt><dd><p>196 For each index checked, re-find tuples on the leaf level by performing a197 new search from the root page for each tuple using198 <a class="xref" href="amcheck.html" title="F.2. amcheck — tools to verify table and index consistency">amcheck</a>'s <code class="option">rootdescend</code> option.199 </p><p>200 Use of this option implicitly also selects the201 <code class="option">--parent-check</code> option.202 </p><p>203 This form of verification was originally written to help in the204 development of btree index features. It may be of limited use or even205 of no use in helping detect the kinds of corruption that occur in206 practice. It may also cause corruption checking to take considerably207 longer and consume considerably more resources on the server.208 </p></dd></dl></div><p>209 </p><div class="warning"><h3 class="title">Warning</h3><p>210 The extra checks performed against B-tree indexes when the211 <code class="option">--parent-check</code> option or the212 <code class="option">--rootdescend</code> option is specified require213 relatively strong relation-level locks. These checks are the only214 checks that will block concurrent data modification from215 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and216 <code class="command">DELETE</code> commands.217 </p></div><p>218 The following command-line options control the connection to the server:219 220 </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>221 Specifies the host name of the machine on which the server is running.222 If the value begins with a slash, it is used as the directory for the223 Unix domain socket.224 </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>225 Specifies the TCP port or local Unix domain socket file extension on226 which the server is listening for connections.227 </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>228 User name to connect as.229 </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>230 Never issue a password prompt. If the server requires password231 authentication and a password is not available by other means such as232 a <code class="filename">.pgpass</code> file, the connection attempt will fail.233 This option can be useful in batch jobs and scripts where no user is234 present to enter a password.235 </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>236 Force <span class="application">pg_amcheck</span> to prompt for a password237 before connecting to a database.238 </p><p>239 This option is never essential, since240 <span class="application">pg_amcheck</span> will automatically prompt for a241 password if the server demands password authentication. However,242 <span class="application">pg_amcheck</span> will waste a connection attempt243 finding out that the server wants a password. In some cases it is244 worth typing <code class="option">-W</code> to avoid the extra connection attempt.245 </p></dd><dt><span class="term"><code class="option">--maintenance-db=<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>246 Specifies a database or247 <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection string</a> to be248 used to discover the list of databases to be checked. If neither249 <code class="option">--all</code> nor any option including a database pattern is250 used, no such connection is required and this option does nothing.251 Otherwise, any connection string parameters other than252 the database name which are included in the value for this option253 will also be used when connecting to the databases254 being checked. If this option is omitted, the default is255 <code class="literal">postgres</code> or, if that fails,256 <code class="literal">template1</code>.257 </p></dd></dl></div><p>258 </p><p>259 Other options are also available:260 261 </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>262 Echo to stdout all SQL sent to the server.263 </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>264 Use <em class="replaceable"><code>num</code></em> concurrent connections to the server,265 or one per object to be checked, whichever is less.266 </p><p>267 The default is to use a single connection.268 </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>269 Show progress information. Progress information includes the number270 of relations for which checking has been completed, and the total271 size of those relations. It also includes the total number of relations272 that will eventually be checked, and the estimated size of those273 relations.274 </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>275 Print more messages. In particular, this will print a message for276 each relation being checked, and will increase the level of detail277 shown for server errors.278 </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>279 Print the <span class="application">pg_amcheck</span> version and exit.280 </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>281 Install any missing extensions that are required to check the282 database(s). If not yet installed, each extension's objects will be283 installed into the given284 <em class="replaceable"><code>schema</code></em>, or if not specified285 into schema <code class="literal">pg_catalog</code>.286 </p><p>287 At present, the only required extension is <a class="xref" href="amcheck.html" title="F.2. amcheck — tools to verify table and index consistency">amcheck</a>.288 </p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>289 Show help about <span class="application">pg_amcheck</span> command line290 arguments, and exit.291 </p></dd></dl></div><p>292 </p></div><div class="refsect1" id="id-1.9.4.9.7"><h2>Notes</h2><p>293 <span class="application">pg_amcheck</span> is designed to work with294 <span class="productname">PostgreSQL</span> 14.0 and later.295 </p></div><div class="refsect1" id="id-1.9.4.9.8"><h2>See Also</h2><span class="simplelist"><a class="xref" href="amcheck.html" title="F.2. 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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> <span class="application">pg_basebackup</span></td></tr></table></div></body></html>