8 pgAdmin
pgAdmin is a widely used graphical tool for managing PostgreSQL databases. It simplifies common database administration tasks, provides an interface for exploring database objects, and allows users to execute SQL queries.
For many years, pgAdmin 3 was the de facto standard for PostgreSQL administration. However, in 2016, EnterpriseDB discontinued support for pgAdmin 3 and introduced pgAdmin 4, which was completely rewritten in Python and modern web technologies, replacing the original C++ implementation. Initially, pgAdmin 4 received mixed reviews due to its redesigned interface. However, ongoing development has led to significant improvements, making it a more robust and feature-rich tool.
Installation
To launch pgAdmin 4 on Windows, use the installer available at pgadmin.org/download. The installation procedure is straightforward, and the default options are typically sufficient.
For Debian and Ubuntu, add the PostgreSQL repository (see p. 30), then install pgAdmin 4 using the following command:
“pgAdmin4” appears in the list of available programs.
The user interface of this program is fully localized into a dozen languages. To switch to another language, click Configure pgAdmin, select Miscellaneous > User language in the settings window, and then reload the page in your web browser.
Connecting to a Server
To begin, set up a connection to the server. Click the Add New Server button and enter an arbitrary connection name in the General tab of the opened window.
In the Connection tab, enter hostname or address, port number, username, and password.
If you don’t want to enter the password every time, select the Save password checkbox. pgAdmin encrypts stored passwords using a master password, which you will be prompted to set upon first use.
Note that this user must already have a password. For example, for the postgres user, you can do it with the following command:
Click Save to verify the server connection and add it to pgAdmin.
Browser
The left pane displays the Browser tree. Expanding the tree reveals the server, labeled LOCAL in this example. At the next level, you can see all its databases:
appdbhas been created to check connection to PostgreSQL using different programming languages.demois our demo database.postgresis always created when PostgreSQL gets installed.testwas used in the “Trying SQL” chapter (p. 35).
Expanding the Schemas section under appdb reveals the greetings table, along with its columns, constraints, indexes, and triggers.
The context (right-click) menu provides various actions for each object type, including exporting, importing, assigning privileges, and deleting.
The right pane includes several tabs that display reference information:
Dashboard provides system activity charts.
Properties displays the properties of the object selected in the Browser (data types for columns, etc.)
SQL shows the SQL command used to create the selected object.
Statistics lists information used by the query optimizer to build query plans; it can be used by a database administrator for case analysis.
Dependencies, Dependents illustrates dependencies between the selected object and other objects in the database.
Running Queries
To execute a query, open a new SQL editor tab by selecting Tools > Query Tool from the menu.
Type your query in the editor and press F5 to execute it. The Data Output tab in the bottom panel will display the result of the query.
To execute a new query without deleting the previous one, highlight the desired portion of SQL before pressing F5. Thus, the whole history of your actions will be always in front of you. It is usually more convenient than searching for the required query in the log on the Query History tab.
Other Features
pgAdmin provides a graphical user interface for standard PostgreSQL utilities, system catalog information, administration functions, and SQL commands. pgAdmin includes a built-in PL/pgSQL debugger for troubleshooting stored procedures. You can learn about pgAdmin features either on the product website pgadmin.org or in the built-in pgAdmin help system.