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.

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 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 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 tells the overall size of a single database. Details are described in the manpage dbsize(1).

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

dbrelationsize tells the size of tables, view, indices, sequences of a specific database. Details are described in the manpage dbrelationsize(1).

[doj@cubicle ~/code/pg-toolbox]$ dbrelationsize -d xmms
 Schema | Name | Type  |  size
--------+------+-------+--------
 public | song | table | 488 kB
 public | stat | table | 144 kB
(2 rows)

dbstat prints the collected statistics about a database. Details are described in the manpage dbstat(1).

[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 prints locks held by a database. Details are described in the manpage listlock(1).

[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

xloginfo shows the current write and insert location in the xlogs. Details are described in the manpage xloginfo(1).

[doj@cubicle ~/code/pg-toolbox]$ xloginfo
         filename         | write_location | insert_location
--------------------------+----------------+-----------------
 00000001000000010000003E | 1/3E38ACC0     | 1/3E38ACC0
(1 row)

pg_file2str.pl reads a file from STDIN or the filename given as its first command line argument and prints the file properly escaped as a PostgreSQL string literal to STDOUT. Details are described in the manpage pg_file2str(1).

[doj@cubicle ~/code/pg-toolbox]$ cat /tmp/demo.txt
This is a demo file,
showing how pg_file2str.pl works.

	We've got a tab here.
	Änd söme Ümläüt Làtïñ-1 ¢hárâ¢tërs¿
[doj@cubicle ~/code/pg-toolbox]$ pg_file2str.pl /tmp/demo.txt
E'This is a demo file,\nshowing how pg_file2str.pl works.\n\n\tWe''\ve got 
a tab here.\n\t\xC4nd s\xF6me \xDCml\xE4\xFCt L\xE0t\xEF\xF1-1 
\xA2h\xE1r\xE2\xA2t\xEBrs\xBF\n'