First of all, I have to say "thank you" to you, for deciding to give this little book a chance as you are about to read this preface. I hope this book will give you valuable insights, and I sincerely hope that it will contribute to successful work on PostgreSQL. I did my best to include all of the information I think is needed to make you greatly successful and efficient when it comes to your daily business.
In my 15 years as a professional PostgreSQL consultant and commercial supporter at my company, Cybertec Schönig & Schönig GmbH (www.postgresql-support.de), I have seen countless setups—big, small, medium, complex, and simple. Over the past couple of years, I have assembled the 'best of problems,' which I would like to present in this book.
The book you are looking at is unlike all other books. It has not been written in some tutorial-style way. It really focuses on common problems and their solutions. Each problem described will include some background information, why it happens, how it can be solved, and what you can do to avoid it. In this way, I hope to solve and fix your problems as quickly and efficiently as possible.
I truly hope that you find this little book useful, and that it contains all you need to make your setup work.
Chapter 1, Installing PostgreSQL, explains how to install PostgreSQL on various types of systems. Source as well as binary installation are discussed and common pitfalls are presented.
Chapter 2, Creating Data Structures, focuses entirely on optimizing data structures. A lot can be achieved by following some basic rules, such as ordering columns the right way and a lot more.
Chapter 3, Handling Indexes, is probably the most important chapter when it comes to troubleshooting. If indexes are not used wisely, performance problems are certain to happen, and customer satisfaction will be low. In this chapter, indexes and their impact are discussed in great detail.
Chapter 4, Reading Data Efficiently and Correctly, provides insights into how to read data efficiently. Especially when reading large amounts of data, the way data is read does make a real difference, and a lot of potential is lost if data is retrieved in a dumb way.
Chapter 5, Getting Transactions and Locking Right, focuses on common problems related to transactions. How can problematic locks be found, and how can bad locking be avoided in general? How can consistency be ensured, and how can speed be optimized? All of these questions are discussed in this chapter.
Chapter 6, Writing Proper Procedures, attacks the most common pitfalls related to stored procedures. In particular, performance-related topics are covered in detail.
Chapter 7, PostgreSQL Monitoring, is dedicated to all administrators out there who want to ensure that their systems stay up and running 24 x 7. Monitoring can be the key to success here, and therefore, an entire chapter is dedicated to this highly important topic.
Chapter 8, Fixing Backups and Replication, focuses on backups as well as advanced replication topics. This chapter discusses point-in-time recovery, asynchronous replication, as well as synchronous replication in a practical way. On top of that, normal, textual backups are explained.
Chapter 9, Handling Hardware and Software Disasters, shows you all the tools required to handle typical hardware and filesystem failures. It shows how to rescue a PostgreSQL database and restore as much data as possible.
Chapter 10, A Standard Approach to Troubleshooting, outlines a basic and standard approach that works for most cases of ad hoc diagnosis in the field of PostgreSQL. A step-by-step guide is presented, and it can easily be replicated by users to attack most common performance issues.