Using the pgAdmin 4 GUI tool
Graphical administration tools are often requested by system administrators. PostgreSQL has a range of tool options. In this book, we’ll cover pgAdmin 4.
pgAdmin 4 is a client application that sends and receives SQL to and from PostgreSQL, displaying the results for you. The admin client can access many database servers, allowing you to manage a fleet of servers. The tool works in both standalone app mode and within web browsers.
How to do it…
pgAdmin 4 is usually named just pgAdmin. The 4 at the end has a long history but isn’t that important. It is more of an “epoch” than a release level; pgAdmin 4 replaces the earlier pgAdmin 3. Instructions to download and install it can be found at https://www.pgadmin.org/.
When you start pgAdmin, you will be prompted to register a new server.
Give your server a name on the General tab, and then click the Connection tab, as shown in the screenshot, and fill in the five basic connection parameters, as well as the other information. You should uncheck the Save password? option:
Figure 1.3: The server connection properties
If you have many database servers, you can group them together. I suggest keeping any replicated servers together in the same server group. Give each server a sensible name.
Once you’ve added a server, pgAdmin will connect to it and display information about it, using the information that you have added.
The default screen is Dashboard, which presents a few interesting graphs based on the data it polls from the server. That’s not very useful, so click on the Statistics tab.
You will then get access to the main browser screen, with the object tree view on the left and statistics on the right, as shown in the following screenshot:
Figure 1.4: The pgAdmin tree view with the Statistics tab
pgAdmin easily displays much of the data that is available from PostgreSQL. The information is context-sensitive, allowing you to navigate and see everything quickly and easily. Except for the dashboard, the information is not dynamically updated; this will occur only when you navigate the application, where every click will refresh the data, so bear this in mind when using the application.
pgAdmin also provides Grant Wizard. This is useful for DBAs for review and immediate maintenance. In the example shown in the screenshot, the user first selected the Sequences on the navigation tree and then selected Tools 
Grant Wizard. This will open a pop-up window to select the objects on which privileges will be granted to a selected role:
Figure 1.5: Grant Wizard: selecting sequences to grant privileges to a role
The pgAdmin query tool allows you to have multiple active sessions. The query tool has a good-looking visual Explain feature, which displays the EXPLAIN plan for your query. To do this, go to Tools Query Tool. Write your query in the Query text box, and then click on the E (Explain) button, as shown in the following screenshot. The graphical execution tree is shown below the query:
Figure 1.6: The visual Explain feature
How it works…
pgAdmin provides a wide range of features, many of which are provided by other tools as well. This gives us the opportunity to choose which of those tools we want. For many reasons, it is best to use the right tool for the right job, and that is always a matter of expertise, experience, and personal taste.
pgAdmin submits SQL to the PostgreSQL server and displays the results quickly and easily. As a database browser, it is fantastic. For performing small DBA tasks, it is ideal. As you might’ve guessed from these comments, I don’t recommend GUI tools for every task.
Scripting is an important technique for DBAs. You keep an exact copy of the task executed, so you document all the actions in a way that is automatically repeatable, and you can edit and resubmit if problems occur. It’s also easy to put all the tasks in a script into a single transaction, which isn’t possible using the current GUI tools. For scripting, I strongly recommend the psql utility, which has many additional features that you’ll increasingly appreciate over time.
Although I recommend psql as a scripting tool, many people find it convenient as a query tool. Some people may find this strange and assume that it is a choice for experts only. Two great features of psql as an interactive query tool are the online help for SQL and the tab completion feature, which allows you to build up SQL quickly without having to remember the syntax. This is why the Using the psql query and scripting tool recipe (which we recommend particularly for more information) is named that way.
pgAdmin provides the PSQL Tool in the Tools menu, which allows you to run psql alongside pgAdmin. This is a great innovation and allows you to get the power of a GUI alongside the power of psql.
pgAdmin also provides pgAgent, a job scheduler, which we will discuss in Chapter 7, Database Administration.
A quick warning! When you create an object in pgAdmin, the object will be created with a mixed-case name if you use capitals or spaces anywhere in the object name. If I ask for a table named MyTable, the only way to access that table is by referring to it in double quotes as "MyTable". See the Handling objects with quoted names recipe in Chapter 5, Tables and Data:
Figure 1.7: Table options
See also
You may also be interested in commercial tools of various kinds for PostgreSQL. A full listing is given in the PostgreSQL software catalog at http://www.postgresql.org/download/products/1.
