diff options
Diffstat (limited to 'doc/src/sgml/ref/vacuumdb.sgml')
| -rw-r--r-- | doc/src/sgml/ref/vacuumdb.sgml | 632 |
1 files changed, 632 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml new file mode 100644 index 0000000..956c0f0 --- /dev/null +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -0,0 +1,632 @@ +<!-- +doc/src/sgml/ref/vacuumdb.sgml +PostgreSQL documentation +--> + +<refentry id="app-vacuumdb"> + <indexterm zone="app-vacuumdb"> + <primary>vacuumdb</primary> + </indexterm> + + <refmeta> + <refentrytitle><application>vacuumdb</application></refentrytitle> + <manvolnum>1</manvolnum> + <refmiscinfo>Application</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>vacuumdb</refname> + <refpurpose>garbage-collect and analyze a <productname>PostgreSQL</productname> database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <cmdsynopsis> + <command>vacuumdb</command> + <arg rep="repeat"><replaceable>connection-option</replaceable></arg> + <arg rep="repeat"><replaceable>option</replaceable></arg> + + <arg choice="plain" rep="repeat"> + <arg choice="opt"> + <group choice="plain"> + <arg choice="plain"><option>-t</option></arg> + <arg choice="plain"><option>--table</option></arg> + </group> + <replaceable>table</replaceable> + <arg choice="opt">( <replaceable class="parameter">column</replaceable> [,...] )</arg> + </arg> + </arg> + + <arg choice="opt"><replaceable>dbname</replaceable></arg> + </cmdsynopsis> + + <cmdsynopsis> + <command>vacuumdb</command> + <arg rep="repeat"><replaceable>connection-option</replaceable></arg> + <arg rep="repeat"><replaceable>option</replaceable></arg> + <group choice="plain"> + <arg choice="plain"><option>-a</option></arg> + <arg choice="plain"><option>--all</option></arg> + </group> + </cmdsynopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <application>vacuumdb</application> is a utility for cleaning a + <productname>PostgreSQL</productname> database. + <application>vacuumdb</application> will also generate internal statistics + used by the <productname>PostgreSQL</productname> query optimizer. + </para> + + <para> + <application>vacuumdb</application> is a wrapper around the SQL + command <link linkend="sql-vacuum"><command>VACUUM</command></link>. + There is no effective difference between vacuuming and analyzing + databases via this utility and via other methods for accessing the + server. + </para> + + </refsect1> + + + <refsect1> + <title>Options</title> + + <para> + <application>vacuumdb</application> accepts the following command-line arguments: + <variablelist> + <varlistentry> + <term><option>-a</option></term> + <term><option>--all</option></term> + <listitem> + <para> + Vacuum all databases. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option><optional>-d</optional> <replaceable class="parameter">dbname</replaceable></option></term> + <term><option><optional>--dbname=</optional><replaceable class="parameter">dbname</replaceable></option></term> + <listitem> + <para> + Specifies the name of the database to be cleaned or analyzed, + when <option>-a</option>/<option>--all</option> is not used. + If this is not specified, the database name is read + from the environment variable <envar>PGDATABASE</envar>. If + that is not set, the user name specified for the connection is + used. The <replaceable>dbname</replaceable> can be a <link + linkend="libpq-connstring">connection string</link>. If so, + connection string parameters will override any conflicting command + line options. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--disable-page-skipping</option></term> + <listitem> + <para> + Disable skipping pages based on the contents of the visibility map. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 9.6 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-e</option></term> + <term><option>--echo</option></term> + <listitem> + <para> + Echo the commands that <application>vacuumdb</application> generates + and sends to the server. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-f</option></term> + <term><option>--full</option></term> + <listitem> + <para> + Perform <quote>full</quote> vacuuming. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-F</option></term> + <term><option>--freeze</option></term> + <listitem> + <para> + Aggressively <quote>freeze</quote> tuples. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--force-index-cleanup</option></term> + <listitem> + <para> + Always remove index entries pointing to dead tuples. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 12 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-j <replaceable class="parameter">njobs</replaceable></option></term> + <term><option>--jobs=<replaceable class="parameter">njobs</replaceable></option></term> + <listitem> + <para> + Execute the vacuum or analyze commands in parallel by running + <replaceable class="parameter">njobs</replaceable> + commands simultaneously. This option may reduce the processing time + but it also increases the load on the database server. + </para> + <para> + <application>vacuumdb</application> will open + <replaceable class="parameter">njobs</replaceable> connections to the + database, so make sure your <xref linkend="guc-max-connections"/> + setting is high enough to accommodate all connections. + </para> + <para> + Note that using this mode together with the <option>-f</option> + (<literal>FULL</literal>) option might cause deadlock failures if + certain system catalogs are processed in parallel. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--min-mxid-age <replaceable class="parameter">mxid_age</replaceable></option></term> + <listitem> + <para> + Only execute the vacuum or analyze commands on tables with a multixact + ID age of at least <replaceable class="parameter">mxid_age</replaceable>. + This setting is useful for prioritizing tables to process to prevent + multixact ID wraparound (see + <xref linkend="vacuum-for-multixact-wraparound"/>). + </para> + <para> + For the purposes of this option, the multixact ID age of a relation is + the greatest of the ages of the main relation and its associated + <acronym>TOAST</acronym> table, if one exists. Since the commands + issued by <application>vacuumdb</application> will also process the + <acronym>TOAST</acronym> table for the relation if necessary, it does + not need to be considered separately. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 9.6 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--min-xid-age <replaceable class="parameter">xid_age</replaceable></option></term> + <listitem> + <para> + Only execute the vacuum or analyze commands on tables with a + transaction ID age of at least + <replaceable class="parameter">xid_age</replaceable>. This setting + is useful for prioritizing tables to process to prevent transaction + ID wraparound (see <xref linkend="vacuum-for-wraparound"/>). + </para> + <para> + For the purposes of this option, the transaction ID age of a relation + is the greatest of the ages of the main relation and its associated + <acronym>TOAST</acronym> table, if one exists. Since the commands + issued by <application>vacuumdb</application> will also process the + <acronym>TOAST</acronym> table for the relation if necessary, it does + not need to be considered separately. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 9.6 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--no-index-cleanup</option></term> + <listitem> + <para> + Do not remove index entries pointing to dead tuples. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 12 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--no-process-toast</option></term> + <listitem> + <para> + Skip the TOAST table associated with the table to vacuum, if any. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 14 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--no-truncate</option></term> + <listitem> + <para> + Do not truncate empty pages at the end of the table. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 12 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-P <replaceable class="parameter">parallel_workers</replaceable></option></term> + <term><option>--parallel=<replaceable class="parameter">parallel_workers</replaceable></option></term> + <listitem> + <para> + Specify the number of parallel workers for <firstterm>parallel vacuum</firstterm>. + This allows the vacuum to leverage multiple CPUs to process indexes. + See <xref linkend="sql-vacuum"/>. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 13 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-q</option></term> + <term><option>--quiet</option></term> + <listitem> + <para> + Do not display progress messages. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--skip-locked</option></term> + <listitem> + <para> + Skip relations that cannot be immediately locked for processing. + </para> + <note> + <para> + This option is only available for servers running + <productname>PostgreSQL</productname> 12 and later. + </para> + </note> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-t <replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term> + <term><option>--table=<replaceable class="parameter">table</replaceable> [ (<replaceable class="parameter">column</replaceable> [,...]) ]</option></term> + <listitem> + <para> + Clean or analyze <replaceable class="parameter">table</replaceable> only. + Column names can be specified only in conjunction with + the <option>--analyze</option> or <option>--analyze-only</option> options. + Multiple tables can be vacuumed by writing multiple + <option>-t</option> switches. + </para> + <tip> + <para> + If you specify columns, you probably have to escape the parentheses + from the shell. (See examples below.) + </para> + </tip> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-v</option></term> + <term><option>--verbose</option></term> + <listitem> + <para> + Print detailed information during processing. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-V</option></term> + <term><option>--version</option></term> + <listitem> + <para> + Print the <application>vacuumdb</application> version and exit. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-z</option></term> + <term><option>--analyze</option></term> + <listitem> + <para> + Also calculate statistics for use by the optimizer. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-Z</option></term> + <term><option>--analyze-only</option></term> + <listitem> + <para> + Only calculate statistics for use by the optimizer (no vacuum). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--analyze-in-stages</option></term> + <listitem> + <para> + Only calculate statistics for use by the optimizer (no vacuum), + like <option>--analyze-only</option>. Run three + stages of analyze; the first stage uses the lowest possible statistics + target (see <xref linkend="guc-default-statistics-target"/>) + to produce usable statistics faster, and subsequent stages build the + full statistics. + </para> + + <para> + This option is only useful to analyze a database that currently has + no statistics or has wholly incorrect ones, such as if it is newly + populated from a restored dump or by <command>pg_upgrade</command>. + Be aware that running with this option in a database with existing + statistics may cause the query optimizer choices to become + transiently worse due to the low statistics targets of the early + stages. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-?</option></term> + <term><option>--help</option></term> + <listitem> + <para> + Show help about <application>vacuumdb</application> command line + arguments, and exit. + </para> + </listitem> + </varlistentry> + + </variablelist> + </para> + + <para> + <application>vacuumdb</application> also accepts + the following command-line arguments for connection parameters: + <variablelist> + <varlistentry> + <term><option>-h <replaceable class="parameter">host</replaceable></option></term> + <term><option>--host=<replaceable class="parameter">host</replaceable></option></term> + <listitem> + <para> + Specifies the host name of the machine on which the server + is running. If the value begins with a slash, it is used + as the directory for the Unix domain socket. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-p <replaceable class="parameter">port</replaceable></option></term> + <term><option>--port=<replaceable class="parameter">port</replaceable></option></term> + <listitem> + <para> + Specifies the TCP port or local Unix domain socket file + extension on which the server + is listening for connections. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-U <replaceable class="parameter">username</replaceable></option></term> + <term><option>--username=<replaceable class="parameter">username</replaceable></option></term> + <listitem> + <para> + User name to connect as. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-w</option></term> + <term><option>--no-password</option></term> + <listitem> + <para> + Never issue a password prompt. If the server requires + password authentication and a password is not available by + other means such as a <filename>.pgpass</filename> file, the + connection attempt will fail. This option can be useful in + batch jobs and scripts where no user is present to enter a + password. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>-W</option></term> + <term><option>--password</option></term> + <listitem> + <para> + Force <application>vacuumdb</application> to prompt for a + password before connecting to a database. + </para> + + <para> + This option is never essential, since + <application>vacuumdb</application> will automatically prompt + for a password if the server demands password authentication. + However, <application>vacuumdb</application> will waste a + connection attempt finding out that the server wants a password. + In some cases it is worth typing <option>-W</option> to avoid the extra + connection attempt. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><option>--maintenance-db=<replaceable class="parameter">dbname</replaceable></option></term> + <listitem> + <para> + Specifies the name of the database to connect to to discover which + databases should be vacuumed, + when <option>-a</option>/<option>--all</option> is used. + If not specified, the <literal>postgres</literal> database will be used, + or if that does not exist, <literal>template1</literal> will be used. + This can be a <link linkend="libpq-connstring">connection + string</link>. If so, connection string parameters will override any + conflicting command line options. Also, connection string parameters + other than the database name itself will be re-used when connecting + to other databases. + </para> + </listitem> + </varlistentry> + </variablelist> + </para> + </refsect1> + + + <refsect1> + <title>Environment</title> + + <variablelist> + <varlistentry> + <term><envar>PGDATABASE</envar></term> + <term><envar>PGHOST</envar></term> + <term><envar>PGPORT</envar></term> + <term><envar>PGUSER</envar></term> + + <listitem> + <para> + Default connection parameters + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><envar>PG_COLOR</envar></term> + <listitem> + <para> + Specifies whether to use color in diagnostic messages. Possible values + are <literal>always</literal>, <literal>auto</literal> and + <literal>never</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + This utility, like most other <productname>PostgreSQL</productname> utilities, + also uses the environment variables supported by <application>libpq</application> + (see <xref linkend="libpq-envars"/>). + </para> + + </refsect1> + + + <refsect1> + <title>Diagnostics</title> + + <para> + In case of difficulty, see <xref linkend="sql-vacuum"/> + and <xref linkend="app-psql"/> for + discussions of potential problems and error messages. + The database server must be running at the + targeted host. Also, any default connection settings and environment + variables used by the <application>libpq</application> front-end + library will apply. + </para> + + </refsect1> + + + <refsect1> + <title>Notes</title> + + <para> + <application>vacuumdb</application> might need to connect several + times to the <productname>PostgreSQL</productname> server, asking + for a password each time. It is convenient to have a + <filename>~/.pgpass</filename> file in such cases. See <xref + linkend="libpq-pgpass"/> for more information. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + To clean the database <literal>test</literal>: +<screen> +<prompt>$ </prompt><userinput>vacuumdb test</userinput> +</screen> + </para> + + <para> + To clean and analyze for the optimizer a database named + <literal>bigdb</literal>: +<screen> +<prompt>$ </prompt><userinput>vacuumdb --analyze bigdb</userinput> +</screen> + </para> + + <para> + To clean a single table + <literal>foo</literal> in a database named + <literal>xyzzy</literal>, and analyze a single column + <literal>bar</literal> of the table for the optimizer: +<screen> +<prompt>$ </prompt><userinput>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</userinput> +</screen></para> + + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-vacuum"/></member> + </simplelist> + </refsect1> + +</refentry> |