Troubleshooting PostgreSQL

Hans-Jurgen Schonig

• Éditeur: Packt Publishing
• Année: 2015
• ISBN:
  9781783555314
• Sujet: Ciencia de la computación
• Sous-sujet: Bases de datos

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

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 commun...