pgAdmin3 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.

pgAdmin3 is usually named just pgAdmin. The "3" 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.20.

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

Note the five basic connection parameters encircled in the preceding screenshot, as well as other information.

The port number prompted is 2345, but this is deliberately not the default PostgreSQL port of 5432. Presumably, this is done to force you to think about the setting that should be used.

You should uncheck the Store password box.

If you have many database servers, you can group them together. Personally, I would avoid giving each server a color, as green, yellow, and red are usually taken to mean status, which can easily be misinterpreted. Just give each server a sensible name.

You will then get access to the main browser screen, with the object tree view on the left and properties 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.

You'll also notice that pgAdmin provides a Tip of the Day, though I would turn those off. Keep the Guru Hints option on. Luckily, there are no chirpy paperclips offering suggestions.

pgAdmin also provides an Object Report generator and a 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 a given query, as well as Graphical Query Builder, as shown in the following screenshot:

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—"MyTable". See the Handling objects with quoted names recipe in Chapter 5, Tables and Data.

phpPgAdmin is available at http://phppgadmin.sourceforge.net/. There is an online demonstration of the software, so you can try it out yourself and see whether it does the job you want done. The following screenshot shows phpPgAdmin 4 displayed on the Windows Internet Explorer browser. Version 5.1 works with PostgreSQL 9:

One of the big contrasts with pgAdmin is that phpPgAdmin is browser-based, so it may be easier to provide secure access to administrators this way.

phpPgAdmin provides the familiar left-hand-side tree view of the database, and also provides a simple SQL query tool. These are the basics that you should be looking for. Many additional features in pgAdmin3 aren't available, but if you follow my advice you will be doing much of your work using scripts, so this may not be a problem.

For more details on the meaning of the output of the Auto Explain option, refer to the Finding out what makes SQL slow recipe from Chapter 10, Performance and Concurrency.

You may also be interested in commercial tools of various kinds for PostgreSQL. A full listing is given in the PostgreSQL software catalogue at http://www.postgresql.org/download/products/1.