Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
app-pgamcheck.html295 linesDownload Raw Back to html
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>