Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

Troubleshooting PostgreSQL

By : Hans-Jürgen Schönig

4.2 (5)

Troubleshooting PostgreSQL

4.2 (5)

By: Hans-Jürgen Schönig

Overview of this book

If you are a database administrator looking for solutions to common PostgreSQL problems, this is the book for you. The book is suitable for people with intermediate and professional expertise.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Installing PostgreSQL

1. Installing PostgreSQL

Deciding on a version number

Methods of installing PostgreSQL

Memory and kernel issues

Adding checksums to a database instance

Preventing encoding-related issues

Avoiding template pollution

Summary

2. Creating Data Structures

2. Creating Data Structures

Grouping columns the right way

Deciding on data types and structure

Deciding on normalization

Summary

3. Handling Indexes

3. Handling Indexes

Understanding indexes in PostgreSQL

Avoiding trouble with indexes

Solving common problems

Handling LIKE queries

Finding good matches

Fixing full-text search

Summary

4. Reading Data Efficiently and Correctly

4. Reading Data Efficiently and Correctly

Understanding the power of NULL

Fixing disastrous joins

Reading large amounts of data

Understanding prepared queries

Summary

5. Getting Transactions and Locking Right

5. Getting Transactions and Locking Right

The PostgreSQL transaction model

Transaction isolation

Inspecting locks

Summary

6. Writing Proper Procedures

6. Writing Proper Procedures

Choosing the right language

Managing procedures and transactions

Procedures and indexing

Understanding memory

Summary

7. PostgreSQL Monitoring

7. PostgreSQL Monitoring

Checking the overall database behavior

Detecting I/O bottlenecks

Checking for conflicts

Chasing down slow queries

Inspecting internal information

Integrating with external tools

Alternative tools

Summary

8. Fixing Backups and Replication

8. Fixing Backups and Replication

Using pg_dump

Managing point-in-time recovery

Making use of asynchronous replication

Switching to synchronous replication

Handling timelines

Summary

9. Handling Hardware and Software Disasters

9. Handling Hardware and Software Disasters

Checksums – preventing silent corruption

Zeroing out damaged pages

Dealing with index corruption

Dumping individual pages

Resetting the transaction log

Power-out-related issues

Summary

10. A Standard Approach to Troubleshooting

10. A Standard Approach to Troubleshooting

Getting an overview of the problem

Attacking low performance

Fixing common replication issues

Summary

Index

Index

Chapter 3. Handling Indexes

In this chapter, one of the most important topics in the area of database work will be covered—indexing. In many cases, missing or wrong indexes are the main source of trouble, leading to bad performance, unexpected behavior, and a great deal of frustration. To avoid these issues, this chapter will present all the information that you need to make your life as easy as possible.

The following issues will be covered:

Understanding PostgreSQL indexing
Types of indexes
Managing operator classes
Detecting missing indexes
Reading execution plans
Fixing LIKE
Fixing regular expressions
Improving indexes and functions
Indexes and insertions
Indexes and foreign keys

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Troubleshooting PostgreSQL

Search

Your notes and bookmarks