Chapter 3 - part 2, the wrappers and debian's utilities

Wrappers and contributed modules

In this section we'll take a brief look to the contributed binaries and the sql wrappers.

create/drop binaries

These binaries, createdb createlang createuser and dropdb droplang dropuser, are wrappers for the corresponding SQL functions. Each binary can create/drop a database an user or a procedural language. The command line parameters are quite the same as psql for the connection part.

clusterdb

Performs a database wide cluster on previously clustered tables. The word cluster can be confusing as the PostgreSQL implementation is very peculiar. Check the chapter 6 for further readings.

reindexdb

Performs a database wide reindex. In chapter 6 we'll look deeply to the index maintenance, how this can affect the performances.

vacuumdb

Performs a database wide vacuum and/or statistics gathering for the query optimiser. VACUUM is a very important maintenance task. We'll take a deep look on this in the chapter 8.

vacuumlo

Despite the name this binary doesn't perform any vacuum, its purpose is to remove orphaned large object from the pg_largeobject. The pg_largeobject is a system table where the database stores the binary objects. Usually is used when the size of the large object is bigger than 1GB. The theoretical limit for the large object is now 4 TB. Before the version 9.3 the limit were 2 GB.

Debian's specific utilities

The debian packaged install ships with some other, not official, utilities, mostly written in PERL.

pg_createcluster

Creates a new PostgreSQL cluster naming the configuration's directory in /etc/postgresql after the major version and the cluster's name. It's possible to specify the data directory and the initd options.

pg_dropcluster

Removes a PostgreSQL cluster created previously with pg_createcluster. The cluster must be stopped before the drop.

pg_lscluster

Lists the clusters created with pg_createcluster.

pg_ctlcluster

Controls the cluster in an almost similar way pg_ctl does. Using this wrapper for the shutdown is not a good idea as there's no way to tell the script which shutdown mode to use. For further readings on the shutdown sequence check the section 4.2.2 By default pg_ctlcluster performs a smart shutdown mode. Using the -force option the script first try a fast shutdown mode. If the database doesn't shutdown in a reasonable time the script then try an immediate shutdown. If the the instance is still up the script then sends a kill -9 on the postgres process.