A Handy Guide to Solving PostgreSQL Problems

Written in the frequently-asked-questions style, this book provides the best troubleshooting techniques for PostgreSQL database administrators. It covers all major aspects of managing a PostgreSQL database; from database installation through optimizing performance, to dealing with transaction locks or fixing replication, to handling hardware and software disasters. So if you’re getting an error message when working with PostgreSQL, let this book help you solve your problem.

“Troubleshooting PostgreSQL” is the latest book by Hans-Jürgen Schönig, the recognized authority in the PostgreSQL community. Schönig has 15 years of professional experience as a PostgreSQL consultant and commercial supporter at his company Cybertec Schönig & Schönig GmbH, which provides PostgreSQL support, development, replication and training. He is the author or co-author of several books on PostgreSQL, including “PostgreSQL Administration Essentials” (Oct 2014), “PostgreSQL Replication” (Aug 2013; co-author with Zoltan Böszörmenyi), “PHP and PostgreSQL Advanced Web Programming” (June 2002; co-author with Ewald Geschwinde) and “PostgreSQL Developer’s Handbook” (Dec 2001; co-author with Ewald Geschwinde).

The latest Schönig’s book is written in a different style than any other book about databases or coding that you may already be familiar with. Schönig describes his book as having a question-and-answer style rather than a tutorial style, similar to the popular FAQ (Frequently Asked Questions) which you can find on many websites. So if you have just started using PostgreSQL and you are trying to gain basic knowledge, this is not a book for you. But if you have some experience in database management and you want to quickly find a solution or just read about common problems, you should consider purchasing this publication.

The book is divided into ten chapters, each giving ready-to-use solutions to problems relative to a specific aspect of database administration. It starts with troubleshooting the installation and setup of PostgreSQL, and effectively creating data structures. In the next chapters Schönig describes more complicated issues like problems with indexes, transactions and writing procedures. The final chapters have information about advanced topics like monitoring and replication. Each problem is described in the same way – first you will read general information about the issue such as when and why it appears. Then you will find a solution and advice on how to avoid this problem in the future. All information presented is clear and complete so that the reader can not only fix the issue but will also gain all required knowledge to prevent repeating it. The majority of problems are presented with queries, so the reader can easily solve it and see the result.

In my opinion, the most interesting chapter deals with database monitoring. Schönig describes a few methods for checking the general behaviour of the database or to verify slow queries. I didn't know about several system tables which contain information about database performance, such as the query execution time or the total number of query executions. A few smart queries tell you the most important information about your database and allow you to improve performance.

Moreover, I would like to highlight the chapter about indexes – especially its introduction. Schönig explains index operation in a clear way – how it is built and how it works in a PostgreSQL database. With a few example queries, PostgreSQL beginners in particular could understand one of the most useful features for optimizing your database.

If you are an administrator of a PostgreSQL database and you want to manage it more effectively this book is for you. Also, if you have just started with database management and want to improve your knowledge and skills, maybe you should check out a few pages and assess the level of the difficulty. The different writing style employed is the greatest advantage of this book. It allows you find information about an issue and its solution quickly, which is just what the modern database developer needs.

Troubleshooting PostgreSQL
by Hans-Jürgen Schönig

Publisher: Packt Publishing
Release date: March 2015
Paperback: 164 pages (Kindle edition available)

Chapters:

1. Installing PostgreSQL
2. Creating data structures
3. Handling indexes
4. Reading data efficiently and correctly
5. Getting transactions and locking right
6. Writing proper procedures
7. PostgreSQL monitoring
8. Fixing backups and replication
9. Handling hardware and software disasters
10. A standard approach to troubleshooting