eBook - ePub
Troubleshooting PostgreSQL
Hans-Jurgen Schonig
This is a test
Share book
- 164 pages
- English
- ePUB (mobile friendly)
- Available on iOS & Android
eBook - ePub
Troubleshooting PostgreSQL
Hans-Jurgen Schonig
Book details
Book preview
Table of contents
Citations
Frequently asked questions
How do I cancel my subscription?
Can/how do I download books?
At the moment all of our mobile-responsive ePub books are available to download via the app. Most of our PDFs are also available to download and we're working on making the final remaining ones downloadable now. Learn more here.
What is the difference between the pricing plans?
Both plans give you full access to the library and all of Perlego’s features. The only differences are the price and subscription period: With the annual plan you’ll save around 30% compared to 12 months on the monthly plan.
What is Perlego?
We are an online textbook subscription service, where you can get access to an entire online library for less than the price of a single book per month. With over 1 million books across 1000+ topics, we’ve got you covered! Learn more here.
Do you support text-to-speech?
Look out for the read-aloud symbol on your next book to see if you can listen to it. The read-aloud tool reads text aloud for you, highlighting the text as it is being read. You can pause it, speed it up and slow it down. Learn more here.
Is Troubleshooting PostgreSQL an online PDF/ePUB?
Yes, you can access Troubleshooting PostgreSQL by Hans-Jurgen Schonig in PDF and/or ePUB format, as well as other popular books in Ciencia de la computación & Bases de datos. We have over one million books available in our catalogue for you to explore.
Information
Subtopic
Bases de datosTroubleshooting 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...