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.
This book has been written with Linux and Mac OS X in mind. However, 90 percent of all examples will also work with all changes on Windows platforms.
No special tools are needed to work through this book.
This book has been written for and is dedicated to people who actively work with PostgreSQL and who want to avoid common pitfalls. It has been written with more than 15 years of professional PostgreSQL consulting, support, and training in mind. The goal is to really capture those hot topics and provide easy-to-understand solutions.
In this book, you will find a number of text styles that distinguish between different kinds of information. Here are some examples of these styles and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "The count(bid) function returns 1, while the count(*) function will return 3."
A block of code is set as follows:
test=# INSERT INTO t_test SELECT 10, 20, 30, 'abcd', 'abcd', 'abcd' FROM generate_series(1, 10000000); INSERT 0 10000000
Any command-line input or output is written as follows:
SELECT * FROM website WHERE domain = 'www.cybertec.at';
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or disliked. Reader feedback is important for us as it helps us develop titles that you will really get the most out of.
To send us general feedback, simply e-mail <[email protected]>, and mention the book's title in the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide at www.packtpub.com/authors.
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you could report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded to our website or added to any list of existing errata under the Errata section of that title.
To view the previously submitted errata, go to https://www.packtpub.com/books/content/support and enter the name of the book in the search field. The required information will appear under the Errata section.
Piracy of copyrighted material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at <[email protected]> with a link to the suspected pirated material.
We appreciate your help in protecting our authors and our ability to bring you valuable content.
If you have a problem with any aspect of this book, you can contact us at <[email protected]>, and we will do our best to address the problem.