Similar to VACUUM , vacuumdb cleans and analyzes a PostgreSQL database. What makes vacuumdb different is that it allows the user to specify a database name or all databases to be vacuumed. It also provides connection parameters that allow the user to perform the vacuumdb command from a remote machine.

The syntax for vacuumdb is:

   vacuumdb [ --analyze | -z ] 
            [ --alldb   | -a ] 
            [ --verbose | -v ]
            [ --table   '

table_name

 [ ( 

column

 [,...] ) ]' ] 
            [ --dbname  | -d ] 

database_name

 ]

Here are the parameters used with the vacuumdb command:

-z, --analyze

Updates the column statistics used by the query optimizer to determine the most efficient way to execute a query.

-a, --alldb

Applies the vacuumdb command to all databases. You cannot use this option in conjunction with the --dbname database_name option. It cannot vacuum all databases and a specific one at the same time.

-v, --verbose

Displays a detailed vacuum report.

--table ' table_name [ ( column [,...])

Vacuums a specific table or columns. If columns are specified, then the analyze option is required. For instance, this vacuum a column named isbn in the book table is in the booktown database:

      vacuumdb --analyze --table 'book (isbn)' --dbname booktown
      

-d, --dbname database_name

Specifies the name of the database to vacuum. If this option is used, then the --alldb option cannot also be used. It cannot vacuum a specific database and all databases at the same time.

This command vacuums all databases on the server that the command is ran from. It also prints the detailed vacuum report.

   vacuumdb --alldb --verbose
   

Another option is to perform a vacuum on a specific table in a database and analyze the table:

   vacuumdb --analyze --table author --dbname booktown
   

There are additional parameters which allow a user to perform a vacuum from a remote machine. These parameters are:

-h, --host= host_name

The hostname of the database server that postmaster is running on.

-p, --port= port_num

Specifies the database server port it will use to connect. This number could either be the local port number or the Internet TCP/IP port number.

-U, --username= user_name

Specifies the username to connect as. Make sure this user has access to all databases you are vacuuming.

-W, --password

Prompts for a password.

There are two other additional parameters which can specify to display a message or not, after execution has finished:

-e, --echo

Display messages generated by the vacuumdb command and was consequently sent to the backend.

-q, --quiet

Do not display any output information.

The following remotely accesses a machine named dev.mybooktown.com. Port number 5432 is used to connect with dev. It also specifies that all databases on that machine are vacuumed. The ANANLYZE option will apply and the postgres user is used to dial into this machine. It also displays any messages sent to the backend as a result of executing this command.

Example 7-4. Vacuuming a Remote Database

  vacuumdb -a -z -h dev.mybooktown.com -p 5432 -U postgres -e
   

The previous command uses the shorthand alternative to this:

   vacuumdb --alldb --analyze -host dev.mybooktown.com
            --port 5432 --username postgres --echo 
   

	Connection Method
	You will need to make sure that the pg_hba.conf file on the server machine accepts connections from the remote machine you are using to dial in. This will require adding a line which specifies the connection method and TCP/IP port number. For more information on the pg_hba.conf file, refer to the Client Authentication chapter.

Help us make a better book, leave feedback. (http://www.opendocspublishing.com/entry.lxp?lxpe=92)