As with many of the database management SQL commands, the VACUUM command has a
command-line executable wrapper called
vacuumdb
. The
vacuumdb
script provides one
significant added function to the normal use of the VACUUM SQL statement, in that you can
instruct it to perform a VACUUM on each PostgreSQL database on your system.
Additionally, since it accepts connection parameters on how to connect to PostgreSQL, you may use
vacuumdb
remotely (i.e., without having to first connect to the machine via a terminal client, and
then executing
vacuumdb
or
psql
from the remote machine). This is provided that
your authentication scheme in PostgreSQL's
pg_hba.conf
file is configured for outside access (see
Chapter 8 for more information on this).
Here is the syntax for
vacuumdb
:
vacuumdb [
options
] [
dbname
]
Like the
createdb
and
dropdb
scripts,
vacuumdb
accepts
both single-dashed and GNU-style double-dashed arguments from the command line. The only required option is the
dbname
(unless you specify
- -all
), which describes the database to be cleaned and
analyzed. The
options
parameters describe which mode the VACUUM
command should be invoked in. The following are the available options for the
vacuumdb
script:
- -h
HOSTNAME
, - -host=
HOSTNAME
-
Specifies that you are connected to
HOSTNAME
, rather than the localhost. Use this option when vacuuming a remote database.
- -p
PORT
, - -port=
PORT
-
Specifies that the database connection is made on port
PORT
, rather than the
default port (usually 5432, though it may have been configured differently when PostgreSQL was
compiled, by the
- -with-pgport
flag).
- -U
USERNAME
, - -username=
USERNAME
-
Specifies that the username
USERNAME
is the user who connects to PostgreSQL
(rather than the name of the system user executing
vacuumdb
).
- -W, - -password
-
Accepts no parameters, and causes a password prompt, which occurs automatically if the
pg_hba.conf
file on the target server is configured not to
trust
the requesting host.
- -d
DBNAME
, - -dbname=
DBNAME
-
Explicitly specifies the name of the database to perform the VACUUM
statement on. This option is mutually exclusive to the
- -all
option.
- -a, - -all
-
Applies the VACUUM command, with specified options, to all databases
in the system catalog.
- -z, - -analyze
-
Equivalent to the ANALYZE keyword for the
VACUUM SQL command. Updates stored statistics about the data allocation between
columns, which are used by the query optimizer to help guide internal query planning.
- -t '
TABLE
[ (
column
[, ...] ) ]', - -table='
TABLE
[ (
column
[, ...] ) ]'
-
Targets a specific table
TABLE
(or specific
columns
within that table) to be affected. The
- -analyze
option is required to describe specific columns.
- -v, - -verbose
-
Equivalent to the VERBOSE keyword for the VACUUM
SQL command. Causes a detailed internal report of the processing performed to be displayed.
- -e, - -echo
-
Accepts no parameters, and causes the query sent to PostgreSQL to be displayed to the screen as it is
executed by
vacuumdb
.
- -q, - -quiet
-
Accepts no parameters, and causes no output to be sent to
stdout
(though any errors will
still be sent to
stderr
).
Example 9-16 demonstrates the use of the
vacuumdb
script from
the Book Town server. The
-U
flag specifies that the connection should use the
manager user to connect, while the
- -all
flag causes all databases in the system
catalog to be affected in sequence.
Example 9-16. Using vacuumdb on all databases
[jworsley@booktown ~]$
vacuumdb -U manager - -all
Vacuuming postgres
VACUUM
Vacuuming booktown
VACUUM
Vacuuming template1
VACUUM
As mentioned, because of the connectivity options available to the
vacuumdb
script, it can be
easily executed from a remote server. Example 9-17 shows a similar process to the
command used in Example 9-16, but with the addition of the
-h
flag to
specify a remote server named
booktown.commandprompt.com
. Example 9-17 also targets the booktown database specifically, rather than
all databases.
Example 9-17. Using vacuumdb on a remote database
[jworsley@cmd ~]$
vacuumdb -h booktown.commandprompt.com -U manager booktown
VACUUM
