Using graphical administration tools

Graphical administration tools are often requested by system administrators.

PostgreSQL has a range of tool options. In this book, we'll cover pgAdmin 4.

How to do it...

pgAdmin 4 is a client application that sends and receives SQL to PostgreSQL, displaying the results for you to browse. One pgAdmin client can access many PostgreSQL servers, and a PostgreSQL server can be accessed by many pgAdmin clients.

pgAdmin 4 is usually named just pgAdmin. The "4" at the end has a long history, but isn't that important. It is not the release level; the release level at the time of writing this book is 1.1. pgAdmin 4 replaces the earlier pgAdmin 3.

When you start pgAdmin, you will be prompted to register a new server, as shown in the following screenshot:

Give your server a name on the General tab, then click Connection and fill in the five basic connection parameters, as well as the other information.
You should uncheck the Store password box.

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, you can connect to it and display information about it.

The default screen is the Dashboard that presents a few interesting graphs based upon 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:

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. The information is not dynamically updated; this will occur only when you click to refresh, so keep F5 in mind when using the application.

pgAdmin also provides Grant Wizard. These are useful for DBAs for review and immediate maintenance.

The pgAdmin Query tool allows you to have multiple active sessions. The Query tool has a good-looking Visual Explain feature, which displays the best execution plan found for your query.

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 browser, it is fantastic. For performing small DBA tasks, it is ideal. As you might've guessed from these comments, I don't recommend pgAdmin for every task.

Scripting is an important technique for DBAs. You keep a copy of the task executed, 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. pgAdmin provides pgScript, which only works with pgAdmin, so it is more difficult to port. For scripting, I strongly recommend the psql utility, which has many additional features that you'll increasingly appreciate over time.

Although I use psql as a scripting tool, I also find it convenient as a query tool. Some people may find this strange, and assume it is a choice for experts only. Two great features of psql 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. See the Using the psql query and scripting tool recipe for more information.

pgAdmin also provides pgAgent, a task scheduler. Again, more portable schedulers are available, and you may wish to use those instead. Schedulers aren't covered in this book.

A quick warning! When you create an object in pgAdmin, the object will be created with a mixed case name if you use capitals anywhere in the object name. If I ask for a table named MyTable, then 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.