PostgreSQL/Tools

Tools for database administration (DBA) tasks such as backups, restores, and cleanups are mostly not part of the SQL standard. Vendor specific database products usually include a combination of database specific tools and SQL extensions for administration purposes. PostgreSQL provides a set of both PostgreSQL specific tools and SQL extensions. The main ones are described here as well as some reliable external tools.

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 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 sent to the instance and interpreted there, e.g.: 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 everything that 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 current cluster
• \echo :DBNAME lists the current database (consider upper case letters). In most cases the name is part of the psql prompt.
• \dn lists all schemas in the current database
• \d lists all tables, views, sequences, materialized views, and foreign tables in the current schema
• \dt lists all tables in the current schema
• \d+ TABLENAME lists all columns and indexes in table TABLENAME
• \du lists all users in the current cluster
• \dp lists access rights (privileges) to tables, ...
• \dx lists installed extensions
• \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

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 significantly extends the functionalities of psql with 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.

Since 2016 pgAdmin 3 is superseded by pgAdmin 4. pgAdmin 4 is a complete re-implementation - written as a web application in Python. You can run it either on a web server using a browser, or standalone on a workstation.

phpPgAdmin

PhpPgAdmin

phpPgAdmin is a graphical tool that 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 is distributed via GitHub. The project was largely dormant over many years, but as of 2019 has been updated with support for PHP7 and PostgreSQL 13.

Other Tools

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