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, 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
4 2009-07-28 The fourth release contains some bugfixes to tools and all tools have been tested on FreeBSD.
3 2008-10-14 Third release with new tools: pg_file2str.pl, xloginfo, dbrelationsize and updates to pgcomment. Thanks to Pierre-Emmanuel André for contributing the new tools.
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 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

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

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

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

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

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'

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