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: 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.
 * Commands starting with a backslash, eg:  to list tables. Those commands are interpreted by psql itself.
 * All other commands are sent to the instance and interpreted there, e.g.:.

$ # 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
 * lists syntax of SQL commands
 * lists syntax of the named SQL-command
 * help to all 'backslash' commands
 * lists all databases in the current cluster
 * lists the current database (consider upper case letters). In most cases the name is part of the psql prompt.
 * lists all schemas in the current database
 * lists all tables, views, sequences, materialized views, and foreign tables in the current schema
 * lists all tables in the current schema
 * lists all columns and indexes in table TABLENAME
 * lists all users in the current cluster
 * lists access rights (privileges) to tables, ...
 * lists installed extensions
 * redirects following output to FILENAME
 * changes output to 'pure' data (no header, ...)
 * executes COMMAND in a shell (outside psql)
 * 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 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 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.