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 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:
\dtto 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
\hlists syntax of SQL commands
\h SQL-commandlists syntax of the named SQL-command
\?help to all 'backslash' commands
\llists all databases in the current cluster
\echo :DBNAMElists the current database (consider upper case letters). In most cases the name is part of the psql prompt.
\dnlists all schemas in the current database
\dlists all tables, views, sequences, materialized views, and foreign tables in the current schema
\dtlists all tables in the current schema
\d+ TABLENAMElists all columns and indexes in table TABLENAME
\dulists all users in the current cluster
\dplists access rights (privileges) to tables, ...
\dxlists installed extensions
\o FILENAMEredirects following output to FILENAME
\tchanges output to 'pure' data (no header, ...)
\! COMMANDexecutes COMMAND in a shell (outside psql)
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 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 is distributed via sourgeforge. The project was largely dormant over the past few years, but as of 2019 has been updated with support for PHP7 and PostgreSQL 12.
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.