Editing PostgreSQL (section)

== Database administration ==
{{See also|Comparison of database administration tools}}

Open source front-ends and tools for administering PostgreSQL include:
{| class="wikitable mw-collapsible mw-collapsed floatright"
! psql Session Example<ref>{{cite web | url=https://pygments.org/docs/lexers/#pygments.lexers.sql.PostgresConsoleLexer | title=Available lexers — Pygments }}</ref>
|-
|<syntaxhighlight lang="psql">

regression=# select foo;
ERROR:  column "foo" does not exist
CONTEXT:  PL/pgSQL function "test1" while casting return value to function's return type
LINE 1: select foo;
               ^
regression=# \q
</syntaxhighlight>
<syntaxhighlight lang="psql">
peter@localhost testdb=> \a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
</syntaxhighlight>
<syntaxhighlight lang="psql">
regression=# select '\x';
WARNING:  nonstandard use of escape in a string literal
LINE 1: select '\x';
               ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
 ?column? 
----------
 x
(1 row)

regression=# select E'\x';
</syntaxhighlight>
<syntaxhighlight lang="psql">
piro=> \set foo 30;
piro=> select * from test where foo <= :foo;
 foo | bar 
-----+-----
  10 | 
  20 | 
(2 rows)
</syntaxhighlight>
<syntaxhighlight lang="psql">
testdb=> \set foo 'my_table'
testdb=> SELECT * FROM :"foo";

testdb=> \set content `cat my_file.txt`
testdb=> INSERT INTO my_table VALUES (:'content');
</syntaxhighlight>
<syntaxhighlight lang="psql">
regression=# select (
regression(# 1);
 ?column? 
----------
        1
(1 row)
</syntaxhighlight>
<syntaxhighlight lang="psql">
piro=> select (
piro(> '
piro'> ' || $$
piro$> $$)
piro-> from "
piro"> foo";
ERROR:  relation "
foo" does not exist
LINE 5: from "
             ^
</syntaxhighlight>
<syntaxhighlight lang="psql">
testdb=> CREATE TABLE my_table (
first integer not null default 0,
second text) ; -- end of command
CREATE TABLE

=# SELECT '0x10'::mpz AS "hex", '10'::mpz AS "dec",
-#        '010'::mpz AS oct, '0b10'::mpz AS bin; -- Table output
 hex | dec | oct | bin
-----+-----+-----+-----
 16  | 10  | 8   | 2
(1 row)
</syntaxhighlight>
<syntaxhighlight lang="psql">
regression=# select schemaname from  pg_tables limit 3; -- One field output
 schemaname 
------------
 pg_catalog
 pg_catalog
 pg_catalog
(3 rows)

=# select 10.0, 1e-6, 1E+6;
 ?column? | ?column? | ?column? 
----------+----------+----------
     10.0 | 0.000001 |  1000000
(1 row)

regression=# begin;
BEGIN
regression=# create table asdf (foo serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "asdf_foo_seq" for serial column "asdf.foo"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "asdf_pkey" for table "asdf"
CREATE TABLE
regression=# insert into asdf values (10) returning foo;
 foo 
-----
  10
(1 row)

INSERT 0 1
regression=# ROLLBACK ;
ROLLBACK
</syntaxhighlight>
|}
; {{anchor|psql}}psql: The primary [[Front and back ends|front-end]] for PostgreSQL is the {{code|psql}} [[command-line program]], which can be used to enter SQL queries directly, or execute them from a file. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks; for example tab completion of object names and SQL syntax.
; {{anchor|pgAdmin}}pgAdmin: The pgAdmin package is a free and open-source [[graphical user interface]] (GUI) administration tool for PostgreSQL, which is supported on many computer platforms.<ref name="pgAdmin" /> The program is available in more than a dozen languages. The first prototype, named pgManager, was written for PostgreSQL 6.3.2 from 1998, and rewritten and released as pgAdmin under the [[GNU General Public License]] (GPL) in later months. The second incarnation (named pgAdmin II) was a complete rewrite, first released on January 16, 2002. The third version, pgAdmin III, was originally released under the [[Artistic License]] and then released under the same license as PostgreSQL. Unlike prior versions that were written in [[Visual Basic]], pgAdmin III is written in C++, using the [[wxWidgets]]<ref>{{cite web | url=https://packages.debian.org/jessie/pgadmin3 | title=Debian -- Details of package pgadmin3 in jessie | access-date=March 10, 2017}}</ref> framework allowing it to run on most common operating systems. The query tool includes a scripting language called pgScript for supporting admin and development tasks. In December 2014, Dave Page, the pgAdmin project founder and primary developer,<ref>{{cite web |title=pgAdmin Development Team |url=http://www.pgadmin.org/development/team.php |website=pgadmin.org |access-date=June 22, 2015}}</ref> announced that with the shift towards web-based models, work has begun on pgAdmin 4 with the aim to facilitate cloud deployments.<ref>{{cite web |last1=Dave |first1=Page |title=The story of pgAdmin |url=http://pgsnake.blogspot.co.uk/2014/12/the-story-of-pgadmin.html |website=Dave's Postgres Blog |publisher=pgsnake.blogspot.co.uk |access-date=December 7, 2014|date=December 7, 2014 }}</ref> In 2016, pgAdmin 4 was released. The pgAdmin 4 backend was written in [[Python (programming language)|Python]], using [[Flask (web framework)|Flask]] and the [[Qt (software)|Qt framework]].<ref>{{cite web |title=pgAdmin 4 README |website=[[GitHub]] |url=https://github.com/postgres/pgadmin4/blob/master/README |access-date=August 15, 2018}}</ref>
; phpPgAdmin: phpPgAdmin is a web-based administration tool for PostgreSQL written in [[PHP]] and based on the popular [[phpMyAdmin]] interface originally written for [[MySQL]] administration.<ref name="PHPADMIN" />
; PostgreSQL Studio: PostgreSQL Studio allows users to perform essential PostgreSQL database development tasks from a web-based console. PostgreSQL Studio allows users to work with cloud databases without the need to open firewalls.<ref name="POSTGRESQLSTUDIO" />
; TeamPostgreSQL: AJAX/JavaScript-driven web interface for PostgreSQL. Allows browsing, maintaining and creating data and database objects via a web browser. The interface offers tabbed SQL editor with autocompletion, row editing widgets, click-through foreign key navigation between rows and tables, ''favorites'' management for commonly used scripts, among other features. Supports SSH for both the web interface and the [[database connection]]s. Installers are available for Windows, Macintosh, and Linux, and a simple cross-platform archive that runs from a script.<ref name="TEAMPOSTGRESQL" />
; LibreOffice, OpenOffice.org: [[LibreOffice]] and [[OpenOffice.org]] Base can be used as a front-end for PostgreSQL.<ref name="ooAsFrntEnd" /><ref name="loAsFrntEnd" />
; pgBadger: The pgBadger PostgreSQL log analyzer generates detailed reports from a PostgreSQL log file.<ref name="tuningPGinstance" />
; pgDevOps: pgDevOps is a suite of web tools to install & manage multiple PostgreSQL versions, extensions, and community components, develop SQL queries, monitor running databases and find performance problems.<ref>{{cite web |title=pgDevOps |url=https://www.bigsql.org/pgdevops/ |website=BigSQL.org |access-date=May 4, 2017 |archive-url=https://web.archive.org/web/20170401220832/http://www1.bigsql.org/pgdevops/ |archive-date=April 1, 2017 |url-status=dead}}</ref>
; Adminer: [[Adminer]] is a simple web-based administration tool for PostgreSQL and others, written in PHP.
; pgBackRest: pgBackRest is a backup and restore tool for PostgreSQL that provides support for full, differential, and incremental backups.<ref>{{Cite web|url=https://github.com/pgbackrest/pgbackrest|title=pgbackrest/pgbackrest|website=GitHub|date=November 21, 2021}}</ref>
; pgaudit: pgaudit is a PostgreSQL extension that provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.<ref>{{Cite web|url=https://github.com/pgaudit/pgaudit|title=pgaudit/pgaudit|website=GitHub|date=November 21, 2021}}</ref>
; WAL-E: WAL-E is a backup and restore tool for PostgreSQL that provides support for physical ([[Write-ahead logging|WAL]]-based) backups, written in Python.<ref>{{Cite web|url=https://github.com/wal-e/wal-e|title=wal-e/wal-e|date=June 24, 2021|via=GitHub}}</ref>
; DBeaver: [[DBeaver]] is a free and open source GUI administration tool for PostgreSQL, it has Visual Entity Diagrams and [[Intellisense]] features. It also has a commercial PRO license. 

A number of companies offer proprietary tools for PostgreSQL. They often consist of a universal core that is adapted for various specific database products. These tools mostly share the administration features with the open source tools but offer improvements in [[data modeling]], importing, exporting or reporting.