1VACUUMDB(1) PostgreSQL 12.2 Documentation VACUUMDB(1)
2
6 vacuumdb - garbage-collect and analyze a PostgreSQL database
7
9 vacuumdb [connection-option...] [option...]
10 [ --table | -t table [( column [,...] )] ]... [dbname]
11 vacuumdb [connection-option...] [option...] --all | -a
13
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 vacuumdb is a wrapper around the SQL command VACUUM(7). There is no
20 effective difference between vacuuming and analyzing databases via this
21 utility and via other methods for accessing the server.
22
24 vacuumdb accepts the following command-line arguments:
25 -a
27 --all
28 Vacuum all databases.
29 [-d] dbname
31 [--dbname=]dbname
32 Specifies the name of the database to be cleaned or analyzed. If
33 this is not specified and -a (or --all) is not used, the database
34 name is read from the environment variable PGDATABASE. If that is
35 not set, the user name specified for the connection is used.
36 --disable-page-skipping
38 Disable skipping pages based on the contents of the visibility map.
39 Note
41 This option is only available for servers running PostgreSQL
42 9.6 and later.
43 -e
45 --echo
46 Echo the commands that vacuumdb generates and sends to the server.
47 -f
49 --full
50 Perform “full” vacuuming.
51 -F
53 --freeze
54 Aggressively “freeze” tuples.
55 -j njobs
57 --jobs=njobs
58 Execute the vacuum or analyze commands in parallel by running njobs
59 commands simultaneously. This option reduces the time of the
60 processing but it also increases the load on the database server.
61 vacuumdb will open njobs connections to the database, so make sure
63 your max_connections setting is high enough to accommodate all
64 connections.
65 Note that using this mode together with the -f (FULL) option might
67 cause deadlock failures if certain system catalogs are processed in
68 parallel.
69 --min-mxid-age mxid_age
71 Only execute the vacuum or analyze commands on tables with a
72 multixact ID age of at least mxid_age. This setting is useful for
73 prioritizing tables to process to prevent multixact ID wraparound
74 (see Section 24.1.5.1).
75 For the purposes of this option, the multixact ID age of a relation
77 is the greatest of the ages of the main relation and its associated
78 TOAST table, if one exists. Since the commands issued by vacuumdb
79 will also process the TOAST table for the relation if necessary, it
80 does not need to be considered separately.
81 Note
83 This option is only available for servers running PostgreSQL
84 9.6 and later.
85 --min-xid-age xid_age
87 Only execute the vacuum or analyze commands on tables with a
88 transaction ID age of at least xid_age. This setting is useful for
89 prioritizing tables to process to prevent transaction ID wraparound
90 (see Section 24.1.5).
91 For the purposes of this option, the transaction ID age of a
93 relation is the greatest of the ages of the main relation and its
94 associated TOAST table, if one exists. Since the commands issued by
95 vacuumdb will also process the TOAST table for the relation if
96 necessary, it does not need to be considered separately.
97 Note
99 This option is only available for servers running PostgreSQL
100 9.6 and later.
101 -q
103 --quiet
104 Do not display progress messages.
105 --skip-locked
107 Skip relations that cannot be immediately locked for processing.
108 Note
110 This option is only available for servers running PostgreSQL 12
111 and later.
112 -t table [ (column [,...]) ]
114 --table=table [ (column [,...]) ]
115 Clean or analyze table only. Column names can be specified only in
116 conjunction with the --analyze or --analyze-only options. Multiple
117 tables can be vacuumed by writing multiple -t switches.
118 Tip
120 If you specify columns, you probably have to escape the
121 parentheses from the shell. (See examples below.)
122 -v
124 --verbose
125 Print detailed information during processing.
126 -V
128 --version
129 Print the vacuumdb version and exit.
130 -z
132 --analyze
133 Also calculate statistics for use by the optimizer.
134 -Z
136 --analyze-only
137 Only calculate statistics for use by the optimizer (no vacuum).
138 --analyze-in-stages
140 Only calculate statistics for use by the optimizer (no vacuum),
141 like --analyze-only. Run several (currently three) stages of
142 analyze with different configuration settings, to produce usable
143 statistics faster.
144 This option is useful to analyze a database that was newly
146 populated from a restored dump or by pg_upgrade. This option will
147 try to create some statistics as fast as possible, to make the
148 database usable, and then produce full statistics in the subsequent
149 stages.
150 -?
152 --help
153 Show help about vacuumdb command line arguments, and exit.
154 vacuumdb also accepts the following command-line arguments for
156 connection parameters:
157 -h host
159 --host=host
160 Specifies the host name of the machine on which the server is
161 running. If the value begins with a slash, it is used as the
162 directory for the Unix domain socket.
163 -p port
165 --port=port
166 Specifies the TCP port or local Unix domain socket file extension
167 on which the server is listening for connections.
168 -U username
170 --username=username
171 User name to connect as.
172 -w
174 --no-password
175 Never issue a password prompt. If the server requires password
176 authentication and a password is not available by other means such
177 as a .pgpass file, the connection attempt will fail. This option
178 can be useful in batch jobs and scripts where no user is present to
179 enter a password.
180 -W
182 --password
183 Force vacuumdb to prompt for a password before connecting to a
184 database.
185 This option is never essential, since vacuumdb will automatically
187 prompt for a password if the server demands password
188 authentication. However, vacuumdb will waste a connection attempt
189 finding out that the server wants a password. In some cases it is
190 worth typing -W to avoid the extra connection attempt.
191 --maintenance-db=dbname
193 Specifies the name of the database to connect to discover what
194 other databases should be vacuumed. If not specified, the postgres
195 database will be used, and if that does not exist, template1 will
196 be used.
197
199 PGDATABASE
200 PGHOST
201 PGPORT
202 PGUSER
203 Default connection parameters
204 PG_COLOR
206 Specifies whether to use color in diagnostics messages. Possible
207 values are always, auto, never.
208 This utility, like most other PostgreSQL utilities, also uses the
210 environment variables supported by libpq (see Section 33.14).
211
213 In case of difficulty, see VACUUM(7) and psql(1) for discussions of
214 potential problems and error messages. The database server must be
215 running at the targeted host. Also, any default connection settings and
216 environment variables used by the libpq front-end library will apply.
217
219 vacuumdb might need to connect several times to the PostgreSQL server,
220 asking for a password each time. It is convenient to have a ~/.pgpass
221 file in such cases. See Section 33.15 for more information.
222
224 To clean the database test:
225 $ vacuumdb test
227 To clean and analyze for the optimizer a database named bigdb:
229 $ vacuumdb --analyze bigdb
231 To clean a single table foo in a database named xyzzy, and analyze a
233 single column bar of the table for the optimizer:
234 $ vacuumdb --analyze --verbose --table='foo(bar)' xyzzy
236
238 VACUUM(7)
239PostgreSQL 12.2 2020 VACUUMDB(1)