path: root/doc/src/sgml/man1/vacuumdb.1

'\" t
.\"     Title: vacuumdb
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2024
.\"    Manual: PostgreSQL 16.2 Documentation
.\"    Source: PostgreSQL 16.2
.\"  Language: English
.\"
.TH "VACUUMDB" "1" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
vacuumdb \- garbage\-collect and analyze a PostgreSQL database
.SH "SYNOPSIS"
.HP \w'\fBvacuumdb\fR\ 'u
\fBvacuumdb\fR [\fIconnection\-option\fR...] [\fIoption\fR...] [\ \fB\-t\fR\ |\ \fB\-\-table\fR\ \fItable\fR\ [(\ \fIcolumn\fR\ [,\&.\&.\&.]\ )]\ ]...  [\fIdbname\fR]
.HP \w'\fBvacuumdb\fR\ 'u
\fBvacuumdb\fR [\fIconnection\-option\fR...] [\fIoption\fR...] [\ [\ \fB\-n\fR\ |\ \fB\-\-schema\fR\ \fIschema\fR\ ]\ |\ [\ \fB\-N\fR\ |\ \fB\-\-exclude\-schema\fR\ \fIschema\fR\ ]\ ]...  [\fIdbname\fR]
.HP \w'\fBvacuumdb\fR\ 'u
\fBvacuumdb\fR [\fIconnection\-option\fR...] [\fIoption\fR...] \fB\-a\fR | \fB\-\-all\fR 
.SH "DESCRIPTION"
.PP
vacuumdb
is a utility for cleaning a
PostgreSQL
database\&.
vacuumdb
will also generate internal statistics used by the
PostgreSQL
query optimizer\&.
.PP
vacuumdb
is a wrapper around the SQL command
\fBVACUUM\fR\&. There is no effective difference between vacuuming and analyzing databases via this utility and via other methods for accessing the server\&.
.SH "OPTIONS"
.PP
vacuumdb
accepts the following command\-line arguments:
.PP
\fB\-a\fR
.br
\fB\-\-all\fR
.RS 4
Vacuum all databases\&.
.RE
.PP
\fB\-\-buffer\-usage\-limit \fR\fB\fIsize\fR\fR
.RS 4
Specifies the
Buffer Access Strategy
ring buffer size for a given invocation of
vacuumdb\&. This size is used to calculate the number of shared buffers which will be reused as part of this strategy\&. See
\fBVACUUM\fR(7)\&.
.RE
.PP
\fB[\-d]\fR\fB \fR\fB\fIdbname\fR\fR
.br
\fB[\-\-dbname=]\fR\fB\fIdbname\fR\fR
.RS 4
Specifies the name of the database to be cleaned or analyzed, when
\fB\-a\fR/\fB\-\-all\fR
is not used\&. If this is not specified, the database name is read from the environment variable
\fBPGDATABASE\fR\&. If that is not set, the user name specified for the connection is used\&. The
\fIdbname\fR
can be a
connection string\&. If so, connection string parameters will override any conflicting command line options\&.
.RE
.PP
\fB\-\-disable\-page\-skipping\fR
.RS 4
Disable skipping pages based on the contents of the visibility map\&.
.RE
.PP
\fB\-e\fR
.br
\fB\-\-echo\fR
.RS 4
Echo the commands that
vacuumdb
generates and sends to the server\&.
.RE
.PP
\fB\-f\fR
.br
\fB\-\-full\fR
.RS 4
Perform
\(lqfull\(rq
vacuuming\&.
.RE
.PP
\fB\-F\fR
.br
\fB\-\-freeze\fR
.RS 4
Aggressively
\(lqfreeze\(rq
tuples\&.
.RE
.PP
\fB\-\-force\-index\-cleanup\fR
.RS 4
Always remove index entries pointing to dead tuples\&.
.RE
.PP
\fB\-j \fR\fB\fInjobs\fR\fR
.br
\fB\-\-jobs=\fR\fB\fInjobs\fR\fR
.RS 4
Execute the vacuum or analyze commands in parallel by running
\fInjobs\fR
commands simultaneously\&. This option may reduce the processing time but it also increases the load on the database server\&.
.sp
vacuumdb
will open
\fInjobs\fR
connections to the database, so make sure your
max_connections
setting is high enough to accommodate all connections\&.
.sp
Note that using this mode together with the
\fB\-f\fR
(FULL) option might cause deadlock failures if certain system catalogs are processed in parallel\&.
.RE
.PP
\fB\-\-min\-mxid\-age \fR\fB\fImxid_age\fR\fR
.RS 4
Only execute the vacuum or analyze commands on tables with a multixact ID age of at least
\fImxid_age\fR\&. This setting is useful for prioritizing tables to process to prevent multixact ID wraparound (see
Section\ \&25.1.5.1)\&.
.sp
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
TOAST
table, if one exists\&. Since the commands issued by
vacuumdb
will also process the
TOAST
table for the relation if necessary, it does not need to be considered separately\&.
.RE
.PP
\fB\-\-min\-xid\-age \fR\fB\fIxid_age\fR\fR
.RS 4
Only execute the vacuum or analyze commands on tables with a transaction ID age of at least
\fIxid_age\fR\&. This setting is useful for prioritizing tables to process to prevent transaction ID wraparound (see
Section\ \&25.1.5)\&.
.sp
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
TOAST
table, if one exists\&. Since the commands issued by
vacuumdb
will also process the
TOAST
table for the relation if necessary, it does not need to be considered separately\&.
.RE
.PP
\fB\-n \fR\fB\fIschema\fR\fR
.br
\fB\-\-schema=\fR\fB\fIschema\fR\fR
.RS 4
Clean or analyze all tables in
\fIschema\fR
only\&. Multiple schemas can be vacuumed by writing multiple
\fB\-n\fR
switches\&.
.RE
.PP
\fB\-N \fR\fB\fIschema\fR\fR
.br
\fB\-\-exclude\-schema=\fR\fB\fIschema\fR\fR
.RS 4
Do not clean or analyze any tables in
\fIschema\fR\&. Multiple schemas can be excluded by writing multiple
\fB\-N\fR
switches\&.
.RE
.PP
\fB\-\-no\-index\-cleanup\fR
.RS 4
Do not remove index entries pointing to dead tuples\&.
.RE
.PP
\fB\-\-no\-process\-main\fR
.RS 4
Skip the main relation\&.
.RE
.PP
\fB\-\-no\-process\-toast\fR
.RS 4
Skip the TOAST table associated with the table to vacuum, if any\&.
.RE
.PP
\fB\-\-no\-truncate\fR
.RS 4
Do not truncate empty pages at the end of the table\&.
.RE
.PP
\fB\-P \fR\fB\fIparallel_workers\fR\fR
.br
\fB\-\-parallel=\fR\fB\fIparallel_workers\fR\fR
.RS 4
Specify the number of parallel workers for
parallel vacuum\&. This allows the vacuum to leverage multiple CPUs to process indexes\&. See
\fBVACUUM\fR(7)\&.
.RE
.PP
\fB\-q\fR
.br
\fB\-\-quiet\fR
.RS 4
Do not display progress messages\&.
.RE
.PP
\fB\-\-skip\-locked\fR
.RS 4
Skip relations that cannot be immediately locked for processing\&.
.RE
.PP
\fB\-t \fR\fB\fItable\fR\fR\fB [ (\fR\fB\fIcolumn\fR\fR\fB [,\&.\&.\&.]) ]\fR
.br
\fB\-\-table=\fR\fB\fItable\fR\fR\fB [ (\fR\fB\fIcolumn\fR\fR\fB [,\&.\&.\&.]) ]\fR
.RS 4
Clean or analyze
\fItable\fR
only\&. Column names can be specified only in conjunction with the
\fB\-\-analyze\fR
or
\fB\-\-analyze\-only\fR
options\&. Multiple tables can be vacuumed by writing multiple
\fB\-t\fR
switches\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBTip\fR
.ps -1
.br
If you specify columns, you probably have to escape the parentheses from the shell\&. (See examples below\&.)
.sp .5v
.RE
.RE
.PP
\fB\-v\fR
.br
\fB\-\-verbose\fR
.RS 4
Print detailed information during processing\&.
.RE
.PP
\fB\-V\fR
.br
\fB\-\-version\fR
.RS 4
Print the
vacuumdb
version and exit\&.
.RE
.PP
\fB\-z\fR
.br
\fB\-\-analyze\fR
.RS 4
Also calculate statistics for use by the optimizer\&.
.RE
.PP
\fB\-Z\fR
.br
\fB\-\-analyze\-only\fR
.RS 4
Only calculate statistics for use by the optimizer (no vacuum)\&.
.RE
.PP
\fB\-\-analyze\-in\-stages\fR
.RS 4
Only calculate statistics for use by the optimizer (no vacuum), like
\fB\-\-analyze\-only\fR\&. Run three stages of analyze; the first stage uses the lowest possible statistics target (see
default_statistics_target) to produce usable statistics faster, and subsequent stages build the full statistics\&.
.sp
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
\fBpg_upgrade\fR\&. 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\&.
.RE
.PP
\fB\-?\fR
.br
\fB\-\-help\fR
.RS 4
Show help about
vacuumdb
command line arguments, and exit\&.
.RE
.PP
vacuumdb
also accepts the following command\-line arguments for connection parameters:
.PP
\fB\-h \fR\fB\fIhost\fR\fR
.br
\fB\-\-host=\fR\fB\fIhost\fR\fR
.RS 4
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\&.
.RE
.PP
\fB\-p \fR\fB\fIport\fR\fR
.br
\fB\-\-port=\fR\fB\fIport\fR\fR
.RS 4
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections\&.
.RE
.PP
\fB\-U \fR\fB\fIusername\fR\fR
.br
\fB\-\-username=\fR\fB\fIusername\fR\fR
.RS 4
User name to connect as\&.
.RE
.PP
\fB\-w\fR
.br
\fB\-\-no\-password\fR
.RS 4
Never issue a password prompt\&. If the server requires password authentication and a password is not available by other means such as a
\&.pgpass
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\&.
.RE
.PP
\fB\-W\fR
.br
\fB\-\-password\fR
.RS 4
Force
vacuumdb
to prompt for a password before connecting to a database\&.
.sp
This option is never essential, since
vacuumdb
will automatically prompt for a password if the server demands password authentication\&. However,
vacuumdb
will waste a connection attempt finding out that the server wants a password\&. In some cases it is worth typing
\fB\-W\fR
to avoid the extra connection attempt\&.
.RE
.PP
\fB\-\-maintenance\-db=\fR\fB\fIdbname\fR\fR
.RS 4
Specifies the name of the database to connect to to discover which databases should be vacuumed, when
\fB\-a\fR/\fB\-\-all\fR
is used\&. If not specified, the
postgres
database will be used, or if that does not exist,
template1
will be used\&. This can be a
connection string\&. 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\&.
.RE
.SH "ENVIRONMENT"
.PP
\fBPGDATABASE\fR
.br
\fBPGHOST\fR
.br
\fBPGPORT\fR
.br
\fBPGUSER\fR
.RS 4
Default connection parameters
.RE
.PP
\fBPG_COLOR\fR
.RS 4
Specifies whether to use color in diagnostic messages\&. Possible values are
always,
auto
and
never\&.
.RE
.PP
This utility, like most other
PostgreSQL
utilities, also uses the environment variables supported by
libpq
(see
Section\ \&34.15)\&.
.SH "DIAGNOSTICS"
.PP
In case of difficulty, see
\fBVACUUM\fR(7)
and
\fBpsql\fR(1)
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
libpq
front\-end library will apply\&.
.SH "NOTES"
.PP
vacuumdb
might need to connect several times to the
PostgreSQL
server, asking for a password each time\&. It is convenient to have a
~/\&.pgpass
file in such cases\&. See
Section\ \&34.16
for more information\&.
.SH "EXAMPLES"
.PP
To clean the database
test:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBvacuumdb test\fR
.fi
.if n \{\
.RE
.\}
.PP
To clean and analyze for the optimizer a database named
bigdb:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBvacuumdb \-\-analyze bigdb\fR
.fi
.if n \{\
.RE
.\}
.PP
To clean a single table
foo
in a database named
xyzzy, and analyze a single column
bar
of the table for the optimizer:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBvacuumdb \-\-analyze \-\-verbose \-\-table=\*(Aqfoo(bar)\*(Aq xyzzy\fR
.fi
.if n \{\
.RE
.\}
.PP
To clean all tables in the
foo
and
bar
schemas in a database named
xyzzy:
.sp
.if n \{\
.RS 4
.\}
.nf
$ \fBvacuumdb \-\-schema=\*(Aqfoo\*(Aq \-\-schema=\*(Aqbar\*(Aq xyzzy\fR
.fi
.if n \{\
.RE
.\}
.SH "SEE ALSO"
\fBVACUUM\fR(7)