1VACUUMDB(1) PostgreSQL 16.1 Documentation VACUUMDB(1)
2
6 vacuumdb - garbage-collect and analyze a PostgreSQL database
7
9 vacuumdb [connection-option...] [option...]
10 [ -t | --table table [( column [,...] )] ]... [dbname]
11 vacuumdb [connection-option...] [option...]
13 [ [ -n | --schema schema ] | [ -N | --exclude-schema schema ] ]...
14 [dbname]
15 vacuumdb [connection-option...] [option...] -a | --all
17
19 vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb
20 will also generate internal statistics used by the PostgreSQL query
21 optimizer.
22 vacuumdb is a wrapper around the SQL command VACUUM. There is no
24 effective difference between vacuuming and analyzing databases via this
25 utility and via other methods for accessing the server.
26
28 vacuumdb accepts the following command-line arguments:
29 -a
31 --all
32 Vacuum all databases.
33 --buffer-usage-limit size
35 Specifies the Buffer Access Strategy ring buffer size for a given
36 invocation of vacuumdb. This size is used to calculate the number
37 of shared buffers which will be reused as part of this strategy.
38 See VACUUM(7).
39 [-d] dbname
41 [--dbname=]dbname
42 Specifies the name of the database to be cleaned or analyzed, when
43 -a/--all is not used. If this is not specified, the database name
44 is read from the environment variable PGDATABASE. If that is not
45 set, the user name specified for the connection is used. The dbname
46 can be a connection string. If so, connection string parameters
47 will override any conflicting command line options.
48 --disable-page-skipping
50 Disable skipping pages based on the contents of the visibility map.
51 -e
53 --echo
54 Echo the commands that vacuumdb generates and sends to the server.
55 -f
57 --full
58 Perform “full” vacuuming.
59 -F
61 --freeze
62 Aggressively “freeze” tuples.
63 --force-index-cleanup
65 Always remove index entries pointing to dead tuples.
66 -j njobs
68 --jobs=njobs
69 Execute the vacuum or analyze commands in parallel by running njobs
70 commands simultaneously. This option may reduce the processing time
71 but it also increases the load on the database server.
72 vacuumdb will open njobs connections to the database, so make sure
74 your max_connections setting is high enough to accommodate all
75 connections.
76 Note that using this mode together with the -f (FULL) option might
78 cause deadlock failures if certain system catalogs are processed in
79 parallel.
80 --min-mxid-age mxid_age
82 Only execute the vacuum or analyze commands on tables with a
83 multixact ID age of at least mxid_age. This setting is useful for
84 prioritizing tables to process to prevent multixact ID wraparound
85 (see Section 25.1.5.1).
86 For the purposes of this option, the multixact ID age of a relation
88 is the greatest of the ages of the main relation and its associated
89 TOAST table, if one exists. Since the commands issued by vacuumdb
90 will also process the TOAST table for the relation if necessary, it
91 does not need to be considered separately.
92 --min-xid-age xid_age
94 Only execute the vacuum or analyze commands on tables with a
95 transaction ID age of at least xid_age. This setting is useful for
96 prioritizing tables to process to prevent transaction ID wraparound
97 (see Section 25.1.5).
98 For the purposes of this option, the transaction ID age of a
100 relation is the greatest of the ages of the main relation and its
101 associated TOAST table, if one exists. Since the commands issued by
102 vacuumdb will also process the TOAST table for the relation if
103 necessary, it does not need to be considered separately.
104 -n schema
106 --schema=schema
107 Clean or analyze all tables in schema only. Multiple schemas can be
108 vacuumed by writing multiple -n switches.
109 -N schema
111 --exclude-schema=schema
112 Do not clean or analyze any tables in schema. Multiple schemas can
113 be excluded by writing multiple -N switches.
114 --no-index-cleanup
116 Do not remove index entries pointing to dead tuples.
117 --no-process-main
119 Skip the main relation.
120 --no-process-toast
122 Skip the TOAST table associated with the table to vacuum, if any.
123 --no-truncate
125 Do not truncate empty pages at the end of the table.
126 -P parallel_workers
128 --parallel=parallel_workers
129 Specify the number of parallel workers for parallel vacuum. This
130 allows the vacuum to leverage multiple CPUs to process indexes. See
131 VACUUM(7).
132 -q
134 --quiet
135 Do not display progress messages.
136 --skip-locked
138 Skip relations that cannot be immediately locked for processing.
139 -t table [ (column [,...]) ]
141 --table=table [ (column [,...]) ]
142 Clean or analyze table only. Column names can be specified only in
143 conjunction with the --analyze or --analyze-only options. Multiple
144 tables can be vacuumed by writing multiple -t switches.
145 Tip
147 If you specify columns, you probably have to escape the
148 parentheses from the shell. (See examples below.)
149 -v
151 --verbose
152 Print detailed information during processing.
153 -V
155 --version
156 Print the vacuumdb version and exit.
157 -z
159 --analyze
160 Also calculate statistics for use by the optimizer.
161 -Z
163 --analyze-only
164 Only calculate statistics for use by the optimizer (no vacuum).
165 --analyze-in-stages
167 Only calculate statistics for use by the optimizer (no vacuum),
168 like --analyze-only. Run three stages of analyze; the first stage
169 uses the lowest possible statistics target (see
170 default_statistics_target) to produce usable statistics faster, and
171 subsequent stages build the full statistics.
172 This option is only useful to analyze a database that currently has
174 no statistics or has wholly incorrect ones, such as if it is newly
175 populated from a restored dump or by pg_upgrade. Be aware that
176 running with this option in a database with existing statistics may
177 cause the query optimizer choices to become transiently worse due
178 to the low statistics targets of the early stages.
179 -?
181 --help
182 Show help about vacuumdb command line arguments, and exit.
183 vacuumdb also accepts the following command-line arguments for
185 connection parameters:
186 -h host
188 --host=host
189 Specifies the host name of the machine on which the server is
190 running. If the value begins with a slash, it is used as the
191 directory for the Unix domain socket.
192 -p port
194 --port=port
195 Specifies the TCP port or local Unix domain socket file extension
196 on which the server is listening for connections.
197 -U username
199 --username=username
200 User name to connect as.
201 -w
203 --no-password
204 Never issue a password prompt. If the server requires password
205 authentication and a password is not available by other means such
206 as a .pgpass file, the connection attempt will fail. This option
207 can be useful in batch jobs and scripts where no user is present to
208 enter a password.
209 -W
211 --password
212 Force vacuumdb to prompt for a password before connecting to a
213 database.
214 This option is never essential, since vacuumdb will automatically
216 prompt for a password if the server demands password
217 authentication. However, vacuumdb will waste a connection attempt
218 finding out that the server wants a password. In some cases it is
219 worth typing -W to avoid the extra connection attempt.
220 --maintenance-db=dbname
222 Specifies the name of the database to connect to to discover which
223 databases should be vacuumed, when -a/--all is used. If not
224 specified, the postgres database will be used, or if that does not
225 exist, template1 will be used. This can be a connection string. If
226 so, connection string parameters will override any conflicting
227 command line options. Also, connection string parameters other than
228 the database name itself will be re-used when connecting to other
229 databases.
230
232 PGDATABASE
233 PGHOST
234 PGPORT
235 PGUSER
236 Default connection parameters
237 PG_COLOR
239 Specifies whether to use color in diagnostic messages. Possible
240 values are always, auto and never.
241 This utility, like most other PostgreSQL utilities, also uses the
243 environment variables supported by libpq (see Section 34.15).
244
246 In case of difficulty, see VACUUM(7) and psql(1) for discussions of
247 potential problems and error messages. The database server must be
248 running at the targeted host. Also, any default connection settings and
249 environment variables used by the libpq front-end library will apply.
250
252 vacuumdb might need to connect several times to the PostgreSQL server,
253 asking for a password each time. It is convenient to have a ~/.pgpass
254 file in such cases. See Section 34.16 for more information.
255
257 To clean the database test:
258 $ vacuumdb test
260 To clean and analyze for the optimizer a database named bigdb:
262 $ vacuumdb --analyze bigdb
264 To clean a single table foo in a database named xyzzy, and analyze a
266 single column bar of the table for the optimizer:
267 $ vacuumdb --analyze --verbose --table='foo(bar)' xyzzy
269 To clean all tables in the foo and bar schemas in a database named
271 xyzzy:
272 $ vacuumdb --schema='foo' --schema='bar' xyzzy
274
276 VACUUM(7)
277PostgreSQL 16.1 2023 VACUUMDB(1)