PostgreSQL Toolbox

A collection of small programs to aid the PostgreSQL administrator and developer. The PostgreSQL Toolbox is a PostgreSQL Community project that is a part of the pgFoundry. The pgFoundry page for the project is at http://pgfoundry.org/projects/pg-toolbox, where you can find downloads, documentation, bug reports, mailing lists, and a whole lot more.

If you want to contribute with a tool to the toolbox, please write to Dirk Jagdmann <doj@cubic.org>.

Current Release

Source code releases are found on the pgFoundry project page.

Ver Date Comment
2 2008-08-26 Second release with new tools: listlock, dbsize, dbstat and updated manpages. Thanks to Pierre-Emmanuel André for contributing the new tools.
1 2008-07-28 Initial release with pgcomment, pgstat, listdb.

The Tools

pgcomment

pgcomment parses SQL files which should contain DDL and (javadoc) comments and can generate the appropriate COMMENT ON statements or HTML and LaTeX fragments.

/** table example
  The table "example" shows how to set the column comments in a table.

  @column nr the primary key as artifical autoassigned integer

  @column name a person's name

  @column job a person's job description
*/
create table example (
  nr   serial,
  name text not null unique,
  job  text not null
);

/** FUNCTION normalize_name(name text)
normalizes "name".  Only lowercase characters, digits, dash and underscore survive the normalization.

@param name an arbitrary string.

@return name parameter normalized.
*/
CREATE OR REPLACE FUNCTION normalize_name(name text)
RETURNS TEXT
AS $$
BEGIN
  RETURN regexp_replace(lower(name), E'[^\\w\\d\\-_]', '', 'g');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

Generates the following DDL:

COMMENT ON COLUMN example.nr IS 'the primary key as artifical autoassigned integer';
COMMENT ON COLUMN example.name IS 'a person''s name';
COMMENT ON COLUMN example.job IS 'a person''s job description';
COMMENT ON TABLE example IS 'The table "example" shows how to set the column comments in a table.';
COMMENT ON FUNCTION normalize_name(name text) IS 'normalizes "name".  Only lowercase characters, digits, dash and underscore survive the normalization.

@param name an arbitrary string.

@return name parameter normalized.';

And the following HTML:

<h2>TABLE example</h2>
  The table &quot;example&quot; shows how to set the column comments in a table.

<br/><b>Column <i>nr</i>:</b> the primary key as artifical autoassigned integer<br/>
<b>Column <i>name</i>:</b> a person&apos;s name<br/>
<b>Column <i>job</i>:</b> a person&apos;s job description<br/>

<h2>FUNCTION normalize_name(name text)</h2>
normalizes &quote;name&quote;.  Only lowercase characters, digits, dash and underscore survive the normalization.

<br/><b>param <i>name</i>:</b> an arbitrary string.

<br/><b>return:</b> name parameter normalized.

TABLE example

The table "example" shows how to set the column comments in a table.
Column nr: the primary key as artifical autoassigned integer
Column name: a person's name
Column job: a person's job description

FUNCTION normalize_name(name text)

normalizes "e;name"e;. Only lowercase characters, digits, dash and underscore survive the normalization.
param name: an arbitrary string.
return: name parameter normalized.

And the following LaTeX:


\section{TABLE: example}

  The table ''example'' shows how to set the column comments in a table.

\textbf{Column nr:}\quad\ the primary key as artifical autoassigned integer

\textbf{Column name:}\quad\ a person's name

\textbf{Column job:}\quad\ a person's job description

\section{FUNCTION: normalize\_name(name text)}

normalizes ''name''.  Only lowercase characters, digits, dash and underscore survive the normalization.

\textbf{param \textit{name}:}\quad\ an arbitrary string.

\textbf{return:} name parameter normalized.

More details are found in the manpage pgcomment(1).

pgstat

pgstat shows the queries the PostgreSQL server is currently executing and optionally idle connections. Details on command line options and environment variables are found in the manpage pgstat(1). Example:

 database | user |             start             |        elapsed         |                     q
----------+------+-------------------------------+------------------------+--------------------------------------------
 archiv   | doj  | 2008-07-28 16:12:18.2844-07   | 00:00:01.171851        | <IDLE>
 desktop2 | doj  | 2008-07-26 11:54:33.122805-07 | 2 days 04:17:46.333446 | <IDLE>
 desktop2 | doj  | 2008-07-28 14:22:21.537592-07 | 01:49:57.918659        | <IDLE>
 rfwall   | doj  | 2008-07-28 16:12:06.265323-07 | 00:00:13.190928        | VACUUM FULL ANALYZE;
 b        | doj  | 2008-07-28 16:12:16.371107-07 | 00:00:03.085144        | autovacuum: VACUUM pg_catalog.pg_statistic
 archiv   | doj  | 2008-07-28 16:12:49.335817-07 | 00:00:02.468138        | select a.nr from postleitzahl a, postleitzahl b;
(6 rows)
      

listdb

listdb lists all databases like the \l+ command in psql. Details are described in the manpage listdb(1).

[doj@cubicle ~]$ listdb
   name    | owner | encoding | tablespace |  size   |                  description
-----------+-------+----------+------------+---------+-----------------------------------------------
 a         | doj   | LATIN1   | pg_default | 4655 kB |
 archiv    | doj   | LATIN1   | pg_default | 399 MB  |
 b         | doj   | LATIN1   | pg_default | 4663 kB |
 ezdms     | doj   | LATIN1   | pg_default | 420 MB  | Datenbank für EZDMS Web Application
 hsp       | doj   | LATIN1   | pg_default | 6543 kB |
 postgres  | doj   | LATIN1   | pg_default | 4271 kB |
 template0 | doj   | LATIN1   | pg_default | 4144 kB |
 template1 | doj   | LATIN1   | pg_default | 4271 kB | default template database
 xmms      | doj   | LATIN1   | pg_default | 5047 kB |
 z         | doj   | LATIN1   | pg_default | 6055 kB |
      

dbsize

dbsize tells the size of a single database.

[doj@cubicle ~/code/pg-toolbox]$ dbsize
 template1_size
----------------
 4271 kB
(1 row)

dbstat

dbstat prints the collected statistics about a database.

[doj@cubicle ~/code/pg-toolbox]$ dbstat
-[ RECORD 1 ]-+----------
datid         | 1
datname       | template1
numbackends   | 1
xact_commit   | 13235
xact_rollback | 1
blks_read     | 684
blks_hit      | 816142
tup_returned  | 4646794
tup_fetched   | 186628
tup_inserted  | 0
tup_updated   | 344
tup_deleted   | 0

listlock

listlock prints locks held by a database.

[doj@cubicle ~/code/pg-toolbox]$ listlock -d a
Version > 8.1 detected
-[ RECORD 1 ]------+----------------------------
datname            | a
locktype           | relation
relation           | replication.nodes
page               |
tuple              |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/30
pid                | 934
mode               | RowShareLock
granted            | t
procpid            | 934
usename            | doj
current_query      | <IDLE> in transaction
-[ RECORD 2 ]------+----------------------------
datname            | a
locktype           | relation
relation           | replication.nodes
page               |
tuple              |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 3/30
pid                | 934
mode               | AccessShareLock
granted            | t
procpid            | 934
usename            | doj
current_query      | <IDLE> in transaction

more documentation

A talk on pgcomment (odp) Dirk Jagdmann gave during PostgreSQL Community Day at Linux World Expo 2008 in San Francisco.


Powered By GForge Collaborative Development Environment