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>.
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. |
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 "example" 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's name<br/> <b>Column <i>job</i>:</b> a person's job description<br/> <h2>FUNCTION normalize_name(name text)</h2> normalizes "e;name"e;. 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 size of a single database.
[doj@cubicle ~/code/pg-toolbox]$ dbsize template1_size ---------------- 4271 kB (1 row)
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 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
A talk on pgcomment (odp) Dirk Jagdmann gave during PostgreSQL Community Day at Linux World Expo 2008 in San Francisco.