Troubleshooting PostgreSQL电子书

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