1VACUUMDB(1)              PostgreSQL 14.3 Documentation             VACUUMDB(1)
2
3
4

NAME

6       vacuumdb - garbage-collect and analyze a PostgreSQL database
7

SYNOPSIS

9       vacuumdb [connection-option...] [option...]
10                [ -t | --table table [( column [,...] )] ]...  [dbname]
11
12       vacuumdb [connection-option...] [option...] -a | --all
13

DESCRIPTION

15       vacuumdb is a utility for cleaning a PostgreSQL database.  vacuumdb
16       will also generate internal statistics used by the PostgreSQL query
17       optimizer.
18
19       vacuumdb is a wrapper around the SQL command VACUUM. There is no
20       effective difference between vacuuming and analyzing databases via this
21       utility and via other methods for accessing the server.
22

OPTIONS

24       vacuumdb accepts the following command-line arguments:
25
26       -a
27       --all
28           Vacuum all databases.
29
30       [-d] dbname
31       [--dbname=]dbname
32           Specifies the name of the database to be cleaned or analyzed, when
33           -a/--all is not used. If this is not specified, the database name
34           is read from the environment variable PGDATABASE. If that is not
35           set, the user name specified for the connection is used. The dbname
36           can be a connection string. If so, connection string parameters
37           will override any conflicting command line options.
38
39       --disable-page-skipping
40           Disable skipping pages based on the contents of the visibility map.
41
42               Note
43               This option is only available for servers running PostgreSQL
44               9.6 and later.
45
46       -e
47       --echo
48           Echo the commands that vacuumdb generates and sends to the server.
49
50       -f
51       --full
52           Perform “full” vacuuming.
53
54       -F
55       --freeze
56           Aggressively “freeze” tuples.
57
58       --force-index-cleanup
59           Always remove index entries pointing to dead tuples.
60
61               Note
62               This option is only available for servers running PostgreSQL 12
63               and later.
64
65       -j njobs
66       --jobs=njobs
67           Execute the vacuum or analyze commands in parallel by running njobs
68           commands simultaneously. This option may reduce the processing time
69           but it also increases the load on the database server.
70
71           vacuumdb will open njobs connections to the database, so make sure
72           your max_connections setting is high enough to accommodate all
73           connections.
74
75           Note that using this mode together with the -f (FULL) option might
76           cause deadlock failures if certain system catalogs are processed in
77           parallel.
78
79       --min-mxid-age mxid_age
80           Only execute the vacuum or analyze commands on tables with a
81           multixact ID age of at least mxid_age. This setting is useful for
82           prioritizing tables to process to prevent multixact ID wraparound
83           (see Section 25.1.5.1).
84
85           For the purposes of this option, the multixact ID age of a relation
86           is the greatest of the ages of the main relation and its associated
87           TOAST table, if one exists. Since the commands issued by vacuumdb
88           will also process the TOAST table for the relation if necessary, it
89           does not need to be considered separately.
90
91               Note
92               This option is only available for servers running PostgreSQL
93               9.6 and later.
94
95       --min-xid-age xid_age
96           Only execute the vacuum or analyze commands on tables with a
97           transaction ID age of at least xid_age. This setting is useful for
98           prioritizing tables to process to prevent transaction ID wraparound
99           (see Section 25.1.5).
100
101           For the purposes of this option, the transaction ID age of a
102           relation is the greatest of the ages of the main relation and its
103           associated TOAST table, if one exists. Since the commands issued by
104           vacuumdb will also process the TOAST table for the relation if
105           necessary, it does not need to be considered separately.
106
107               Note
108               This option is only available for servers running PostgreSQL
109               9.6 and later.
110
111       --no-index-cleanup
112           Do not remove index entries pointing to dead tuples.
113
114               Note
115               This option is only available for servers running PostgreSQL 12
116               and later.
117
118       --no-process-toast
119           Skip the TOAST table associated with the table to vacuum, if any.
120
121               Note
122               This option is only available for servers running PostgreSQL 14
123               and later.
124
125       --no-truncate
126           Do not truncate empty pages at the end of the table.
127
128               Note
129               This option is only available for servers running PostgreSQL 12
130               and later.
131
132       -P parallel_workers
133       --parallel=parallel_workers
134           Specify the number of parallel workers for parallel vacuum. This
135           allows the vacuum to leverage multiple CPUs to process indexes. See
136           VACUUM(7).
137
138               Note
139               This option is only available for servers running PostgreSQL 13
140               and later.
141
142       -q
143       --quiet
144           Do not display progress messages.
145
146       --skip-locked
147           Skip relations that cannot be immediately locked for processing.
148
149               Note
150               This option is only available for servers running PostgreSQL 12
151               and later.
152
153       -t table [ (column [,...]) ]
154       --table=table [ (column [,...]) ]
155           Clean or analyze table only. Column names can be specified only in
156           conjunction with the --analyze or --analyze-only options. Multiple
157           tables can be vacuumed by writing multiple -t switches.
158
159               Tip
160               If you specify columns, you probably have to escape the
161               parentheses from the shell. (See examples below.)
162
163       -v
164       --verbose
165           Print detailed information during processing.
166
167       -V
168       --version
169           Print the vacuumdb version and exit.
170
171       -z
172       --analyze
173           Also calculate statistics for use by the optimizer.
174
175       -Z
176       --analyze-only
177           Only calculate statistics for use by the optimizer (no vacuum).
178
179       --analyze-in-stages
180           Only calculate statistics for use by the optimizer (no vacuum),
181           like --analyze-only. Run several (currently three) stages of
182           analyze with different configuration settings, to produce usable
183           statistics faster.
184
185           This option is useful to analyze a database that was newly
186           populated from a restored dump or by pg_upgrade. This option will
187           try to create some statistics as fast as possible, to make the
188           database usable, and then produce full statistics in the subsequent
189           stages.
190
191       -?
192       --help
193           Show help about vacuumdb command line arguments, and exit.
194
195       vacuumdb also accepts the following command-line arguments for
196       connection parameters:
197
198       -h host
199       --host=host
200           Specifies the host name of the machine on which the server is
201           running. If the value begins with a slash, it is used as the
202           directory for the Unix domain socket.
203
204       -p port
205       --port=port
206           Specifies the TCP port or local Unix domain socket file extension
207           on which the server is listening for connections.
208
209       -U username
210       --username=username
211           User name to connect as.
212
213       -w
214       --no-password
215           Never issue a password prompt. If the server requires password
216           authentication and a password is not available by other means such
217           as a .pgpass file, the connection attempt will fail. This option
218           can be useful in batch jobs and scripts where no user is present to
219           enter a password.
220
221       -W
222       --password
223           Force vacuumdb to prompt for a password before connecting to a
224           database.
225
226           This option is never essential, since vacuumdb will automatically
227           prompt for a password if the server demands password
228           authentication. However, vacuumdb will waste a connection attempt
229           finding out that the server wants a password. In some cases it is
230           worth typing -W to avoid the extra connection attempt.
231
232       --maintenance-db=dbname
233           Specifies the name of the database to connect to to discover which
234           databases should be vacuumed, when -a/--all is used. If not
235           specified, the postgres database will be used, or if that does not
236           exist, template1 will be used. This can be a connection string. If
237           so, connection string parameters will override any conflicting
238           command line options. Also, connection string parameters other than
239           the database name itself will be re-used when connecting to other
240           databases.
241

ENVIRONMENT

243       PGDATABASE
244       PGHOST
245       PGPORT
246       PGUSER
247           Default connection parameters
248
249       PG_COLOR
250           Specifies whether to use color in diagnostic messages. Possible
251           values are always, auto and never.
252
253       This utility, like most other PostgreSQL utilities, also uses the
254       environment variables supported by libpq (see Section 34.15).
255

DIAGNOSTICS

257       In case of difficulty, see VACUUM(7) and psql(1) for discussions of
258       potential problems and error messages. The database server must be
259       running at the targeted host. Also, any default connection settings and
260       environment variables used by the libpq front-end library will apply.
261

NOTES

263       vacuumdb might need to connect several times to the PostgreSQL server,
264       asking for a password each time. It is convenient to have a ~/.pgpass
265       file in such cases. See Section 34.16 for more information.
266

EXAMPLES

268       To clean the database test:
269
270           $ vacuumdb test
271
272       To clean and analyze for the optimizer a database named bigdb:
273
274           $ vacuumdb --analyze bigdb
275
276       To clean a single table foo in a database named xyzzy, and analyze a
277       single column bar of the table for the optimizer:
278
279           $ vacuumdb --analyze --verbose --table='foo(bar)' xyzzy
280

SEE ALSO

282       VACUUM(7)
283
284
285
286PostgreSQL 14.3                      2022                          VACUUMDB(1)
Impressum