Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Using vacuumdb

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

[[email protected] ~]$ 
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

[[email protected] ~]$ 
vacuumdb -h booktown.commandprompt.com -U manager booktown

VACUUM
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire