1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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>vacuumdb</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-reindexdb.html" title="reindexdb" /><link rel="next" href="reference-server.html" title="PostgreSQL Server Applications" /></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">vacuumdb</span></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="app-reindexdb.html" title="reindexdb">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="reference-server.html" title="PostgreSQL Server Applications">Next</a></td></tr></table><hr /></div><div class="refentry" id="APP-VACUUMDB"><div class="titlepage"></div><a id="id-1.9.4.22.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle"><span class="application">vacuumdb</span></span></h2><p>vacuumdb — garbage-collect and analyze a <span class="productname">PostgreSQL</span> database</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><div class="cmdsynopsis"><p id="id-1.9.4.22.4.1"><code class="command">vacuumdb</code> [<em class="replaceable"><code>connection-option</code></em>...] [<em class="replaceable"><code>option</code></em>...]
[
<code class="option">-t</code> | <code class="option">--table</code>
<em class="replaceable"><code>table</code></em>
[( <em class="replaceable"><code>column</code></em> [,...] )]
]
... [<em class="replaceable"><code>dbname</code></em>]</p></div><div class="cmdsynopsis"><p id="id-1.9.4.22.4.2"><code class="command">vacuumdb</code> [<em class="replaceable"><code>connection-option</code></em>...] [<em class="replaceable"><code>option</code></em>...]
[
[
<code class="option">-n</code> | <code class="option">--schema</code>
<em class="replaceable"><code>schema</code></em>
]
|
[
<code class="option">-N</code> | <code class="option">--exclude-schema</code>
<em class="replaceable"><code>schema</code></em>
]
]
... [<em class="replaceable"><code>dbname</code></em>]</p></div><div class="cmdsynopsis"><p id="id-1.9.4.22.4.3"><code class="command">vacuumdb</code> [<em class="replaceable"><code>connection-option</code></em>...] [<em class="replaceable"><code>option</code></em>...] <code class="option">-a</code> | <code class="option">--all</code> </p></div></div><div class="refsect1" id="id-1.9.4.22.5"><h2>Description</h2><p>
<span class="application">vacuumdb</span> is a utility for cleaning a
<span class="productname">PostgreSQL</span> database.
<span class="application">vacuumdb</span> will also generate internal statistics
used by the <span class="productname">PostgreSQL</span> query optimizer.
</p><p>
<span class="application">vacuumdb</span> is a wrapper around the SQL
command <a class="link" href="sql-vacuum.html" title="VACUUM"><code class="command">VACUUM</code></a>.
There is no effective difference between vacuuming and analyzing
databases via this utility and via other methods for accessing the
server.
</p></div><div class="refsect1" id="id-1.9.4.22.6"><h2>Options</h2><p>
<span class="application">vacuumdb</span> accepts the following command-line arguments:
</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>
Vacuum all databases.
</p></dd><dt><span class="term"><code class="option">--buffer-usage-limit <em class="replaceable"><code>size</code></em></code></span></dt><dd><p>
Specifies the
<a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-BUFFER-ACCESS-STRATEGY" title="Buffer Access Strategy">Buffer Access Strategy</a></em></a>
ring buffer size for a given invocation of <span class="application">vacuumdb</span>.
This size is used to calculate the number of shared buffers which will
be reused as part of this strategy. See <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>.
</p></dd><dt><span class="term"><code class="option">[<span class="optional">-d</span>] <em class="replaceable"><code>dbname</code></em></code><br /></span><span class="term"><code class="option">[<span class="optional">--dbname=</span>]<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>
Specifies the name of the database to be cleaned or analyzed,
when <code class="option">-a</code>/<code class="option">--all</code> is not used.
If this is not specified, the database name is read
from the environment variable <code class="envar">PGDATABASE</code>. If
that is not set, the user name specified for the connection is
used. The <em class="replaceable"><code>dbname</code></em> can be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection string</a>. If so,
connection string parameters will override any conflicting command
line options.
</p></dd><dt><span class="term"><code class="option">--disable-page-skipping</code></span></dt><dd><p>
Disable skipping pages based on the contents of the visibility map.
</p></dd><dt><span class="term"><code class="option">-e</code><br /></span><span class="term"><code class="option">--echo</code></span></dt><dd><p>
Echo the commands that <span class="application">vacuumdb</span> generates
and sends to the server.
</p></dd><dt><span class="term"><code class="option">-f</code><br /></span><span class="term"><code class="option">--full</code></span></dt><dd><p>
Perform <span class="quote">“<span class="quote">full</span>”</span> vacuuming.
</p></dd><dt><span class="term"><code class="option">-F</code><br /></span><span class="term"><code class="option">--freeze</code></span></dt><dd><p>
Aggressively <span class="quote">“<span class="quote">freeze</span>”</span> tuples.
</p></dd><dt><span class="term"><code class="option">--force-index-cleanup</code></span></dt><dd><p>
Always remove index entries pointing to dead tuples.
</p></dd><dt><span class="term"><code class="option">-j <em class="replaceable"><code>njobs</code></em></code><br /></span><span class="term"><code class="option">--jobs=<em class="replaceable"><code>njobs</code></em></code></span></dt><dd><p>
Execute the vacuum or analyze commands in parallel by running
<em class="replaceable"><code>njobs</code></em>
commands simultaneously. This option may reduce the processing time
but it also increases the load on the database server.
</p><p>
<span class="application">vacuumdb</span> will open
<em class="replaceable"><code>njobs</code></em> connections to the
database, so make sure your <a class="xref" href="runtime-config-connection.html#GUC-MAX-CONNECTIONS">max_connections</a>
setting is high enough to accommodate all connections.
</p><p>
Note that using this mode together with the <code class="option">-f</code>
(<code class="literal">FULL</code>) option might cause deadlock failures if
certain system catalogs are processed in parallel.
</p></dd><dt><span class="term"><code class="option">--min-mxid-age <em class="replaceable"><code>mxid_age</code></em></code></span></dt><dd><p>
Only execute the vacuum or analyze commands on tables with a multixact
ID age of at least <em class="replaceable"><code>mxid_age</code></em>.
This setting is useful for prioritizing tables to process to prevent
multixact ID wraparound (see
<a class="xref" href="routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND" title="25.1.5.1. Multixacts and Wraparound">Section 25.1.5.1</a>).
</p><p>
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 class="acronym">TOAST</acronym> table, if one exists. Since the commands
issued by <span class="application">vacuumdb</span> will also process the
<acronym class="acronym">TOAST</acronym> table for the relation if necessary, it does
not need to be considered separately.
</p></dd><dt><span class="term"><code class="option">--min-xid-age <em class="replaceable"><code>xid_age</code></em></code></span></dt><dd><p>
Only execute the vacuum or analyze commands on tables with a
transaction ID age of at least
<em class="replaceable"><code>xid_age</code></em>. This setting
is useful for prioritizing tables to process to prevent transaction
ID wraparound (see <a class="xref" href="routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" title="25.1.5. Preventing Transaction ID Wraparound Failures">Section 25.1.5</a>).
</p><p>
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 class="acronym">TOAST</acronym> table, if one exists. Since the commands
issued by <span class="application">vacuumdb</span> will also process the
<acronym class="acronym">TOAST</acronym> table for the relation if necessary, it does
not need to be considered separately.
</p></dd><dt><span class="term"><code class="option">-n <em class="replaceable"><code>schema</code></em></code><br /></span><span class="term"><code class="option">--schema=<em class="replaceable"><code>schema</code></em></code></span></dt><dd><p>
Clean or analyze all tables in
<em class="replaceable"><code>schema</code></em> only. Multiple
schemas can be vacuumed by writing multiple <code class="option">-n</code> switches.
</p></dd><dt><span class="term"><code class="option">-N <em class="replaceable"><code>schema</code></em></code><br /></span><span class="term"><code class="option">--exclude-schema=<em class="replaceable"><code>schema</code></em></code></span></dt><dd><p>
Do not clean or analyze any tables in
<em class="replaceable"><code>schema</code></em>. Multiple schemas
can be excluded by writing multiple <code class="option">-N</code> switches.
</p></dd><dt><span class="term"><code class="option">--no-index-cleanup</code></span></dt><dd><p>
Do not remove index entries pointing to dead tuples.
</p></dd><dt><span class="term"><code class="option">--no-process-main</code></span></dt><dd><p>
Skip the main relation.
</p></dd><dt><span class="term"><code class="option">--no-process-toast</code></span></dt><dd><p>
Skip the TOAST table associated with the table to vacuum, if any.
</p></dd><dt><span class="term"><code class="option">--no-truncate</code></span></dt><dd><p>
Do not truncate empty pages at the end of the table.
</p></dd><dt><span class="term"><code class="option">-P <em class="replaceable"><code>parallel_workers</code></em></code><br /></span><span class="term"><code class="option">--parallel=<em class="replaceable"><code>parallel_workers</code></em></code></span></dt><dd><p>
Specify the number of parallel workers for <em class="firstterm">parallel vacuum</em>.
This allows the vacuum to leverage multiple CPUs to process indexes.
See <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>.
</p></dd><dt><span class="term"><code class="option">-q</code><br /></span><span class="term"><code class="option">--quiet</code></span></dt><dd><p>
Do not display progress messages.
</p></dd><dt><span class="term"><code class="option">--skip-locked</code></span></dt><dd><p>
Skip relations that cannot be immediately locked for processing.
</p></dd><dt><span class="term"><code class="option">-t <em class="replaceable"><code>table</code></em> [ (<em class="replaceable"><code>column</code></em> [,...]) ]</code><br /></span><span class="term"><code class="option">--table=<em class="replaceable"><code>table</code></em> [ (<em class="replaceable"><code>column</code></em> [,...]) ]</code></span></dt><dd><p>
Clean or analyze <em class="replaceable"><code>table</code></em> only.
Column names can be specified only in conjunction with
the <code class="option">--analyze</code> or <code class="option">--analyze-only</code> options.
Multiple tables can be vacuumed by writing multiple
<code class="option">-t</code> switches.
</p><div class="tip"><h3 class="title">Tip</h3><p>
If you specify columns, you probably have to escape the parentheses
from the shell. (See examples below.)
</p></div></dd><dt><span class="term"><code class="option">-v</code><br /></span><span class="term"><code class="option">--verbose</code></span></dt><dd><p>
Print detailed information during processing.
</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>
Print the <span class="application">vacuumdb</span> version and exit.
</p></dd><dt><span class="term"><code class="option">-z</code><br /></span><span class="term"><code class="option">--analyze</code></span></dt><dd><p>
Also calculate statistics for use by the optimizer.
</p></dd><dt><span class="term"><code class="option">-Z</code><br /></span><span class="term"><code class="option">--analyze-only</code></span></dt><dd><p>
Only calculate statistics for use by the optimizer (no vacuum).
</p></dd><dt><span class="term"><code class="option">--analyze-in-stages</code></span></dt><dd><p>
Only calculate statistics for use by the optimizer (no vacuum),
like <code class="option">--analyze-only</code>. Run three
stages of analyze; the first stage uses the lowest possible statistics
target (see <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a>)
to produce usable statistics faster, and subsequent stages build the
full statistics.
</p><p>
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 <code class="command">pg_upgrade</code>.
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.
</p></dd><dt><span class="term"><code class="option">-?</code><br /></span><span class="term"><code class="option">--help</code></span></dt><dd><p>
Show help about <span class="application">vacuumdb</span> command line
arguments, and exit.
</p></dd></dl></div><p>
</p><p>
<span class="application">vacuumdb</span> also accepts
the following command-line arguments for connection parameters:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="option">-h <em class="replaceable"><code>host</code></em></code><br /></span><span class="term"><code class="option">--host=<em class="replaceable"><code>host</code></em></code></span></dt><dd><p>
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.
</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>
Specifies the TCP port or local Unix domain socket file
extension on which the server
is listening for connections.
</p></dd><dt><span class="term"><code class="option">-U <em class="replaceable"><code>username</code></em></code><br /></span><span class="term"><code class="option">--username=<em class="replaceable"><code>username</code></em></code></span></dt><dd><p>
User name to connect as.
</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>
Never issue a password prompt. If the server requires
password authentication and a password is not available by
other means such as a <code class="filename">.pgpass</code> 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.
</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>
Force <span class="application">vacuumdb</span> to prompt for a
password before connecting to a database.
</p><p>
This option is never essential, since
<span class="application">vacuumdb</span> will automatically prompt
for a password if the server demands password authentication.
However, <span class="application">vacuumdb</span> will waste a
connection attempt finding out that the server wants a password.
In some cases it is worth typing <code class="option">-W</code> to avoid the extra
connection attempt.
</p></dd><dt><span class="term"><code class="option">--maintenance-db=<em class="replaceable"><code>dbname</code></em></code></span></dt><dd><p>
Specifies the name of the database to connect to to discover which
databases should be vacuumed,
when <code class="option">-a</code>/<code class="option">--all</code> is used.
If not specified, the <code class="literal">postgres</code> database will be used,
or if that does not exist, <code class="literal">template1</code> will be used.
This can be a <a class="link" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">connection
string</a>. 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.
</p></dd></dl></div><p>
</p></div><div class="refsect1" id="id-1.9.4.22.7"><h2>Environment</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="envar">PGDATABASE</code><br /></span><span class="term"><code class="envar">PGHOST</code><br /></span><span class="term"><code class="envar">PGPORT</code><br /></span><span class="term"><code class="envar">PGUSER</code></span></dt><dd><p>
Default connection parameters
</p></dd><dt><span class="term"><code class="envar">PG_COLOR</code></span></dt><dd><p>
Specifies whether to use color in diagnostic messages. Possible values
are <code class="literal">always</code>, <code class="literal">auto</code> and
<code class="literal">never</code>.
</p></dd></dl></div><p>
This utility, like most other <span class="productname">PostgreSQL</span> utilities,
also uses the environment variables supported by <span class="application">libpq</span>
(see <a class="xref" href="libpq-envars.html" title="34.15. Environment Variables">Section 34.15</a>).
</p></div><div class="refsect1" id="id-1.9.4.22.8"><h2>Diagnostics</h2><p>
In case of difficulty, see <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>
and <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> 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 <span class="application">libpq</span> front-end
library will apply.
</p></div><div class="refsect1" id="id-1.9.4.22.9"><h2>Notes</h2><p>
<span class="application">vacuumdb</span> might need to connect several
times to the <span class="productname">PostgreSQL</span> server, asking
for a password each time. It is convenient to have a
<code class="filename">~/.pgpass</code> file in such cases. See <a class="xref" href="libpq-pgpass.html" title="34.16. The Password File">Section 34.16</a> for more information.
</p></div><div class="refsect1" id="id-1.9.4.22.10"><h2>Examples</h2><p>
To clean the database <code class="literal">test</code>:
</p><pre class="screen">
<code class="prompt">$ </code><strong class="userinput"><code>vacuumdb test</code></strong>
</pre><p>
</p><p>
To clean and analyze for the optimizer a database named
<code class="literal">bigdb</code>:
</p><pre class="screen">
<code class="prompt">$ </code><strong class="userinput"><code>vacuumdb --analyze bigdb</code></strong>
</pre><p>
</p><p>
To clean a single table
<code class="literal">foo</code> in a database named
<code class="literal">xyzzy</code>, and analyze a single column
<code class="literal">bar</code> of the table for the optimizer:
</p><pre class="screen">
<code class="prompt">$ </code><strong class="userinput"><code>vacuumdb --analyze --verbose --table='foo(bar)' xyzzy</code></strong>
</pre><p>
To clean all tables in the <code class="literal">foo</code> and <code class="literal">bar</code> schemas
in a database named <code class="literal">xyzzy</code>:
</p><pre class="screen">
<code class="prompt">$ </code><strong class="userinput"><code>vacuumdb --schema='foo' --schema='bar' xyzzy</code></strong>
</pre></div><div class="refsect1" id="id-1.9.4.22.11"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></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-reindexdb.html" title="reindexdb">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="reference-server.html" title="PostgreSQL Server Applications">Next</a></td></tr><tr><td width="40%" align="left" valign="top"><span class="application">reindexdb</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"> PostgreSQL Server Applications</td></tr></table></div></body></html>
|
