This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
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:
Updates the column statistics used by the query optimizer to determine the most efficient way to execute a query.
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.
Displays a detailed vacuum report.
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
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:
The hostname of the database server that postmaster is running on.
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.
Specifies the username to connect as. Make sure this user has access to all databases you are vacuuming.
Prompts for a password.
There are two other additional parameters which can specify to display a message or not, after execution has finished:
Display messages generated by the vacuumdb command and was consequently sent to the backend.
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)
 
Continue to: