Cover image for Troubleshooting PostgreSQL

Troubleshooting PostgreSQL

Author Hans-Jürgen Schönig
Release Date: 2015/03/01
ISBN: 9781783555314
Topic:
0%
72
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Intercept problems and challenges typically faced by PostgreSQL database administrators with the best troubleshooting techniques

In Detail

PostgreSQL is a powerful, open source, object-relational database system. PostgreSQL offers many advantages for your company or business over other database systems.

This book is your practical guide to understanding the advanced techniques used to handle complex data and methods to solve issues that you might face while working with PostgreSQL. We will start with discussing the issues that arise at the time of setting up PostgreSQL. The next step will be to decide which data types will be most suitable to the task at hand and optimize performance using the right ones.

We will understand how to deal with transaction locks, fuzzy matches, cursors, joins, and so on. Know the warning signs to look for, and how to avoid the most common issues before they even happen. In addition to that, specific high-level topics such as replication and Point-In-Time-Recovery will be discussed. The entire book is based on PostgreSQL 9.4 and contains up-to-date information.

What You Will Learn

  • Detect bottlenecks caused by missing indexes
  • Optimize your data structures for optimal memory footprint
  • Write better, performance-optimized stored procedures
  • Monitor PostgreSQL in an efficient way and deal with system corruption and filesystem issues
  • Detect replication-related problems and make replication more failsafe
  • Fix missing indexes and problems arising out of transaction locking
  • Find slow queries and optimize your system for speed

Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

Table of Contents

  1. Troubleshooting PostgreSQL
    1. Table of Contents
    2. Troubleshooting PostgreSQL
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
      6. Customer support
        1. Errata
        2. Piracy
        3. Questions
    8. 1. Installing PostgreSQL
      1. Deciding on a version number
      2. Methods of installing PostgreSQL
        1. Installing RPM packages
        2. Installing Debian packages
      3. Memory and kernel issues
        1. Fixing memory issues
        2. Adjusting kernel parameters for Linux
        3. Adjusting kernel parameters for Mac OS X
        4. Fixing other kernel-related limitations
      4. Adding checksums to a database instance
      5. Preventing encoding-related issues
      6. Avoiding template pollution
        1. Killing the postmaster
      7. Summary
    9. 2. Creating Data Structures
      1. Grouping columns the right way
      2. Deciding on data types and structure
        1. Finding the right type
          1. varchar versus text
          2. numeric versus floating point
          3. boolean fields versus bit fields
          4. text versus cidr/inet/circle/point
      3. Deciding on normalization
        1. The 7th normal form
        2. Arrays versus normalizations
      4. Summary
    10. 3. Handling Indexes
      1. Understanding indexes in PostgreSQL
        1. Using a simple index
        2. How an index works
      2. Avoiding trouble with indexes
        1. Detecting missing indexes
        2. Removing useless indexes
      3. Solving common problems
        1. Managing foreign keys
        2. Indexing geometric data using GiST
      4. Handling LIKE queries
        1. Simple LIKE queries
        2. More advanced LIKE queries
      5. Finding good matches
      6. Fixing full-text search
        1. Not using full-text search at all
        2. Full-text search and sorting
      7. Summary
    11. 4. Reading Data Efficiently and Correctly
      1. Understanding the power of NULL
        1. Seeing NULL in action
        2. NULL and storage
      2. Fixing disastrous joins
        1. Create demo data for joins
        2. Understanding outer joins
      3. Reading large amounts of data
        1. Making use of cursors
        2. Synchronized scanning
      4. Understanding prepared queries
      5. Summary
    12. 5. Getting Transactions and Locking Right
      1. The PostgreSQL transaction model
        1. Understanding savepoints
        2. Understanding basic locking and deadlocks
        3. Locking in FOR UPDATE mode
          1. Avoiding performance bottlenecks
        4. Avoiding table locks
      2. Transaction isolation
        1. Demonstrating read committed mode
        2. Using repeatable read
        3. Beyond repeatable read
      3. Inspecting locks
      4. Summary
    13. 6. Writing Proper Procedures
      1. Choosing the right language
        1. Trusted versus untrusted
      2. Managing procedures and transactions
        1. Using transactions to avoid trouble
        2. Understanding transactions and procedures
      3. Procedures and indexing
        1. LEAKPROOF versus NOT LEAKPROOF
      4. Understanding memory
        1. Procedures and cursors
        2. Handling set-returning functions
        3. Assigning memory parameters to functions
      5. Summary
    14. 7. PostgreSQL Monitoring
      1. Checking the overall database behavior
        1. Checking pg_stat_activity
        2. Checking database-level information
      2. Detecting I/O bottlenecks
      3. Checking for conflicts
      4. Chasing down slow queries
        1. Notes about overhead
        2. Resetting data
      5. Inspecting internal information
        1. Looking inside a table
        2. Inspecting the I/O cache
      6. Integrating with external tools
        1. Using Nagios plugins
      7. Alternative tools
        1. Zabbix plugins
        2. pganalyze-collector
        3. pg_view – a simple alternative
      8. Summary
    15. 8. Fixing Backups and Replication
      1. Using pg_dump
        1. Creating textual dumps
          1. Taking care of blobs
          2. Handling passwords
        2. Creating custom format dumps
          1. Making use of many CPUs
      2. Managing point-in-time recovery
        1. How PITR works
          1. Preparing PostgreSQL for PITR
          2. Taking base backups
          3. Replaying xlog
      3. Making use of asynchronous replication
        1. Working with pg_basebackup
        2. Firing up replication
        3. Promoting slaves to masters
        4. Making replication safer
      4. Switching to synchronous replication
      5. Handling timelines
      6. Summary
    16. 9. Handling Hardware and Software Disasters
      1. Checksums – preventing silent corruption
      2. Zeroing out damaged pages
      3. Dealing with index corruption
      4. Dumping individual pages
        1. Extracting the page header
      5. Resetting the transaction log
      6. Power-out-related issues
      7. Summary
    17. 10. A Standard Approach to Troubleshooting
      1. Getting an overview of the problem
      2. Attacking low performance
        1. Reviewing indexes
        2. Fixing UPDATE commands
        3. Detecting slow queries
      3. Fixing common replication issues
        1. Fixing stopped replication
        2. Fixing failed queries
      4. Summary
    18. Index
3.144.100.237