vacuumdb

Garbage-collects and analyzes a database.

vacuumdb is typically run on system catalog tables. It has no effect when run on HAWQ user tables.

Synopsis

vacuumdb [<connection_options>] [<vacuum_options>] [<database_name>]

vacuumdb [-? | --help]

vacuumdb --version

where:

<connection_options> =
    [-h <host> | --host <host>] 
    [-p <port> | --port <port>] 
    [-U <username> | --username <username>] 
    [-w | --no-password]
    [-W | --password] 

<vacuum_options> =
    [(-a | --all) | (-d <dbname> | --dbame <dbname>)]
    [-e | --echo]
    [-f | --full] 
    [-F | --freeze] 
    [-t <tablename> [( column [,...] )] | --table <tablename> [( column [,...] )] ]
    [(-v | --verbose) | (-q | --quiet)]
    [-z | --analyze]

Description

vacuumdb is a utility for cleaning a PostgreSQL database. vacuumdb will also generate internal statistics used by the PostgreSQL query optimizer.

vacuumdb is a wrapper around the SQL command VACUUM. There is no effective difference between vacuuming databases via this utility and via other methods for accessing the server.

Options

<database_name>
Identifies the name of the database to vacuum. If both this option and the -d option are not provided, the environment variable PGDATABASE is used. If that is not set, the user name specified for the connection is used.

<vacuum_options>

-a, --all
Vacuums all databases.

-d, --dbname <dbname>
The name of the database to vacuum. If this option is not specified, <database_name> is not provided, and --all is not used, the database name is read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used.

-e, --echo
Show the commands being sent to the server.

-f, --full
Selects a full vacuum, which may reclaim more space, but takes much longer and exclusively locks the table.

Warning: A VACUUM FULL is not recommended in HAWQ.

-F, --freeze
Freeze row transaction information.

-q, --quiet
Do not display a response.

-t, --table <tablename>[(<column>)]
Clean or analyze this table only. Column names may be specified only in conjunction with the --analyze option. If you specify columns, you probably have to escape the parentheses from the shell.

-v, --verbose
Print detailed information during processing.

-z, --analyze
Collect statistics for use by the query planner.

<connection_options>

-h, --host <host>
Specifies the host name of the machine on which the HAWQ master database server is running. If not specified, reads from the environment variable PGHOST or defaults to localhost.

-p, --port <port>
Specifies the TCP port on which the HAWQ master database server is listening for connections. If not specified, reads from the environment variable PGPORT or defaults to 5432.

-U, --username <username>
The database role name to connect as. If not specified, reads from the environment variable PGUSER or defaults to the current system user name.

-w, --no-password
Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password.

-W, --password
Force a password prompt.

Notes

vacuumdb might need to connect several times to the master server, asking for a password each time. It is convenient to have a ~/.pgpass file for such cases.

Examples

To clean the database test:

$ vacuumdb testdb

To clean and analyze a database named bigdb:

$ vacuumdb --analyze bigdb

To clean a single table foo in a database named mydb, and analyze a single column bar of the table:

$ vacuumdb --analyze --verbose --table 'foo(bar)' mydb

Note the quotes around the table and column names to escape the parentheses from the shell.