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.