Book Image

PostgreSQL 11 Administration Cookbook

By : Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala
Book Image

PostgreSQL 11 Administration Cookbook

By: Simon Riggs, Gianni Ciolli, Sudheer Kumar Meesala

Overview of this book

PostgreSQL is a powerful, open source database management system with an enviable reputation for high performance and stability. With many new features in its arsenal, PostgreSQL 11 allows you to scale up your PostgreSQL infrastructure. This book takes a step-by-step, recipe-based approach to effective PostgreSQL administration. The book will introduce you to new features such as logical replication, native table partitioning, additional query parallelism, and much more to help you to understand and control, crash recovery and plan backups. You will learn how to tackle a variety of problems and pain points for any database administrator such as creating tables, managing views, improving performance, and securing your database. As you make steady progress, the book will draw attention to important topics such as monitoring roles, backup, and recovery of your PostgreSQL 11 database to help you understand roles and produce a summary of log files, ensuring high availability, concurrency, and replication. By the end of this book, you will have the necessary knowledge to manage your PostgreSQL 11 database efficiently.
Table of Contents (19 chapters)
Title Page
Copyright and Credits
About Packt

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 pgAdmin4 and OmniDB, which offers access to PostgreSQL and other databases.

Both of these tools are client applications that send and receive SQL to PostgreSQL, displaying the results for you. The admin client can access many databases servers, allowing you to manage a fleet of servers. Both tools work in 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 not the release level; pgAdmin 4 replaces the earlier pgAdmin 3.

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 Connection and fill in the five basic connection parameters, as well as the other information. You should uncheck the Save 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, 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:

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 bear this in mind when using the application.

pgAdmin also provides Grant Wizard. This is 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 EXPLAIN plan 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 recommend psql as a scripting tool, many people 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, which is 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, 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: