PostgreSQL/Tools

There are various tools which supports the DBA in its daily work. Some parts of this work can be done in standard SQL syntax, eg: CREATE USER ..., whereas a lot of important tasks like backups or cleanups are out of scope of SQL and are supported only by ventor-specific SQL extentions or utilities, eg: VACUUM. Thus in most cases the DBA tools support standard-SQL syntax as well as PostgreSQL-specific SQL syntax and the spawning of PostgreSQL's utilities.

psql

psql is a client program which is delivered as an integral part of the PostgreSQL downloads. Similar to a bash shell it is a line-mode program and may run on the server hardware or at a client. psql knows two kinds of commands:

• Commands starting with a backslash, eg: \dt to list tables. Those commands are interpreted by psql itself.
• All other commands are send to the instance and intepreted there, eg: SELECT * FROM mytable;.

Thus it is an ideal tool for interactive and batch SQL processing. The whole range of PostgreSQL SQL syntax can be used to perform everythink, what can be expressed in SQL.

$ # start psql from a bash shell for database 'postgres' and user 'postgres'
$ psql postgres postgres
postgres=#
postgres=# -- a standard SQL command
postgres=# CREATE TABLE t1 (id integer, col_1 text);
CREATE TABLE
postgres=# -- display information about the new table
postgres=# \dt t1
        List of relations
 Schema | Name | Type  |  Owner 
--------+------+-------+---------
 public | t1   | table | postgres
(1 row)
postgres=#
postgres=# -- perform a PostgreSQL specific task - as an example of a typically DBA action
postgres=# SELECT pg_start_backup('pitr');
 pg_start_backup
-----------------
 0/2000028
(1 row)
postgres=#
postgres=# -- terminate psql
postgres=#\q
$

Here are some more examples of psql 'backslash'-commands

• \h lists syntax of SQL commands
• \h SQL-command lists syntax of the named SQL-command
• \? help to all 'backslash' commands
• \l lists all databases in the actual cluster
• \echo :DBNAME lists the actual database (consider upper case letters). In most cases the name is part of the psql prompt.
• \dn lists all schemas in the actual database
• \d lists all tables, views, sequences, materialized views, and foreign tables in the actual schema
• \dt lists all tables in the actual schema
• \d+ TABLENAME lists all columns and indexes in table TABLENAME
• \du lists all users in the actual cluster
• \dp lists access rights (priviledges) to tables, ...
• \o FILENAME redirects following output to FILENAME
• \t changes output to 'pure' data (no header, ...)
• \! COMMAND executes COMMAND in a shell (outside psql)
• \q terminates psql

pgAdmin

pgAdmin is a tool with a graphical user interface for Unix, Mac OSX and Windows operating systems. In most cases it runs on a different hardware than the instance. For the major operating systems it is an integral part of the download, but it is possible to download the tool separately.

pgAdmin extends the functionalities of psql by a lot of intuitive, graphical representations of database objects, eg. schemas, tables, columns, users, result lists, query execution plans, dependencies between database objects, and much more. To give you a first impression of the surface, some screenshots are online.

phpPgAdmin

phpPgAdmin is a graphical tool which offers features that are similar to those of pgAdmin. It is written in PHP, therefore you additionally need Apache and PHP packages.

phpPgAdmin is not part of the standard PostgreSQL downloads, it's distributed via sourgeforge.

Other Tools

There is a lot of other tools with a GUI interface. Their functionality variies greatly from pure SQL support up to entity-relationship and UML support. Some of the tools are open/free source, others proprietary.