Troubleshooting PostgreSQL

Table of Contents

Troubleshooting PostgreSQL

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Installing PostgreSQL

Deciding on a version number

Methods of installing PostgreSQL

Installing RPM packages

Installing Debian packages

Memory and kernel issues

Fixing memory issues

Adjusting kernel parameters for Linux

Adjusting kernel parameters for Mac OS X

Fixing other kernel-related limitations

Adding checksums to a database instance

Preventing encoding-related issues

Avoiding template pollution

Killing the postmaster

Summary

2. Creating Data Structures

Grouping columns the right way

Deciding on data types and structure

Finding the right type

varchar versus text

numeric versus floating point

boolean fields versus bit fields

text versus cidr/inet/circle/point

Deciding on normalization

The 7th normal form

Arrays versus normalizations

Summary

3. Handling Indexes

Understanding indexes in PostgreSQL

Using a simple index

How an index works

Avoiding trouble with indexes

Detecting missing indexes

Removing useless indexes

Solving common problems

Managing foreign keys

Indexing geometric data using GiST

Handling LIKE queries

Simple LIKE queries

More advanced LIKE queries

Finding good matches

Fixing full-text search

Not using full-text search at all

Full-text search and sorting

Summary

4. Reading Data Efficiently and Correctly

Understanding the power of NULL

Seeing NULL in action

NULL and storage

Fixing disastrous joins

Create demo data for joins

Understanding outer joins

Reading large amounts of data

Making use of cursors

Synchronized scanning

Understanding prepared queries

Summary

5. Getting Transactions and Locking Right

The PostgreSQL transaction model

Understanding savepoints

Understanding basic locking and deadlocks

Locking in FOR UPDATE mode

Avoiding performance bottlenecks

Avoiding table locks

Transaction isolation

Demonstrating read committed mode

Using repeatable read

Beyond repeatable read

Inspecting locks

Summary

6. Writing Proper Procedures

Choosing the right language

Trusted versus untrusted

Managing procedures and transactions

Using transactions to avoid trouble

Understanding transactions and procedures

Procedures and indexing

LEAKPROOF versus NOT LEAKPROOF

Understanding memory

Procedures and cursors

Handling set-returning functions

Assigning memory parameters to functions

Summary

7. PostgreSQL Monitoring

Checking the overall database behavior

Checking pg_stat_activity

Checking database-level information

Detecting I/O bottlenecks

Checking for conflicts

Chasing down slow queries

Notes about overhead

Resetting data

Inspecting internal information

Looking inside a table

Inspecting the I/O cache

Integrating with external tools

Using Nagios plugins

Alternative tools

Zabbix plugins

pganalyze-collector

pg_view – a simple alternative

Summary

8. Fixing Backups and Replication

Using pg_dump

Creating textual dumps

Taking care of blobs

Handling passwords

Creating custom format dumps

Making use of many CPUs

Managing point-in-time recovery

How PITR works

Preparing PostgreSQL for PITR

Taking base backups

Replaying xlog

Making use of asynchronous replication

Working with pg_basebackup

Firing up replication

Promoting slaves to masters

Making replication safer

Switching to synchronous replication

Handling timelines

Summary

9. Handling Hardware and Software Disasters

Checksums – preventing silent corruption

Zeroing out damaged pages

Dealing with index corruption

Dumping individual pages

Extracting the page header

Resetting the transaction log

Power-out-related issues

Summary

10. A Standard Approach to Troubleshooting

Getting an overview of the problem

Attacking low performance

Reviewing indexes

Fixing UPDATE commands

Detecting slow queries

Fixing common replication issues

Fixing stopped replication

Fixing failed queries

Summary

Index

Troubleshooting PostgreSQL

Troubleshooting PostgreSQL

Copyright © 2015 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: March 2015

Production reference: 1250315

Published by Packt Publishing Ltd.

Livery Place

35 Livery Street

Birmingham B3 2PB, UK.

ISBN 978-1-78355-531-4

www.packtpub.com

Credits

Author

Hans-Jürgen Schönig

Reviewers

Ludovic Gasc

Baji Shaik

Sheldon E. Strauch

Commissioning Editor

Julian Ursell

Acquisition Editor

Harsha Bharwani

Content Development Editor

Adrian Raposo

Technical Editor

Siddhesh Ghadi

Copy Editor

Vikrant Phadke

Project Coordinator

Sanchita Mandal

Proofreaders

Simran Bhogal

Bernadette Watkins

Indexer

Monica Ajmera Mehta

Production Coordinator

Arvindkumar Gupta

Cover Work

Arvindkumar Gupta

About the Author

Hans-Jürgen Schönig has been in the PostgreSQL business since 2000. His company, Cybertec Schönig & Schönig GmbH (http://www.postgresql-support.de), serves clients around the globe, providing 24/7 support, replication, development, consulting, and training. He has written numerous books on PostgreSQL.

About the Reviewers

Ludovic Gasc is a senior software developer and engineer at Eyepea/ALLOcloud, a highly renowned open source VoIP and unified communications company in Europe.

Over 5 years, Ludovic has developed redundant distributed systems for Telecom, based on Python, AsyncIO, and PostgreSQL.

He is also the creator of API-Hour. He writes efficient network daemons (HTTP, SSH, and so on) with ease. For more information, you can visit http://www.gmludo.eu/.

Baji Shaik has completed his engineering in telecommunications and started his career as a developer for C# and Java. Later, he worked as a consultant in production and development environments. He then explored different database technologies such as Oracle, PostgreSQL, and Greenplum. Baji is certified in PostgreSQL, and at present, he is leading a team of 15 members in a multinational organization and is directly involved in big data analytics.

Baji is from a small village named Vutukuru, Andhra Pradesh, India. Currently, he lives in Bangalore. His hobbies are watching movies and playing with Rubik's Cube. He likes to read and writes technical blogs.

Sheldon E. Strauch has been a veteran for 20 years of software consulting at companies such as IBM, Sears, Ernst & Young, and Kraft Foods. He has a bachelor's degree in business administration and leverages his technical skills to improve self-awareness of business. His interests include data gathering, management, and mining; maps and mapping; business intelligence; and application of data analysis for continuous improvement. He is currently focused on the development of end-to-end data management and mining at Enova International, a financial services company located in Chicago. In his spare time, Sheldon enjoys performing arts, particularly music, and traveling with his wife, Marilyn.

www.PacktPub.com

Support files, eBooks, discount offers, and more

For support files and downloads related to your book, please visit www.PacktPub.com.

Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at for more details.

At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.

https://www2.packtpub.com/books/subscription/packtlib

Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book library. Here, you can search, access, and read Packt's entire library of books.

Why subscribe?

Fully searchable across every book published by Packt

Copy and paste, print, and bookmark content

On demand and accessible via a web browser

Free access for Packt account holders

If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib today and view 9 entirely free books. Simply use your login credentials for immediate access.

This book is dedicated to all who have supported me over the years. Special thanks goes to my entire team here at Cybertec Schönig & Schönig GmbH (www.postgresql-support.de), which has done a great job over the years. Thank you for your loyalty, your professionalism, and your wonderful characters. I cannot envision any better people to work with. Thank you all ...

Preface

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.

What this book covers

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.

What you need for this book

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.

Who this book is for

This book has been written for and is dedicated to people who actively work with