Troubleshooting PostgreSQL

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

Troubleshooting PostgreSQL

Learning
Hans-Jürgen Schönig

3 customer reviews
Intercept problems and challenges typically faced by PostgreSQL database administrators with the best troubleshooting techniques
$23.99
$29.99
RRP $23.99
RRP $29.99
eBook
Print + eBook

Instantly access this course right now and get the skills you need in 2017

With unlimited access to a constantly growing library of over 4,000 eBooks and Videos, a subscription to Mapt gives you everything you need to learn new skills. Cancel anytime.

Code Files
Preview in Mapt

Book Details

ISBN 139781783555314
Paperback164 pages

Book Description

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.

Table of Contents

Chapter 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
Chapter 2: Creating Data Structures
Grouping columns the right way
Deciding on data types and structure
Deciding on normalization
Summary
Chapter 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
Chapter 4: Reading Data Efficiently and Correctly
Understanding the power of NULL
Fixing disastrous joins
Reading large amounts of data
Understanding prepared queries
Summary
Chapter 5: Getting Transactions and Locking Right
The PostgreSQL transaction model
Transaction isolation
Inspecting locks
Summary
Chapter 6: Writing Proper Procedures
Choosing the right language
Managing procedures and transactions
Procedures and indexing
Understanding memory
Summary
Chapter 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
Chapter 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
Chapter 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
Chapter 10: A Standard Approach to Troubleshooting
Getting an overview of the problem
Attacking low performance
Fixing common replication issues
Summary

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

Authors

Table of Contents

Chapter 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
Chapter 2: Creating Data Structures
Grouping columns the right way
Deciding on data types and structure
Deciding on normalization
Summary
Chapter 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
Chapter 4: Reading Data Efficiently and Correctly
Understanding the power of NULL
Fixing disastrous joins
Reading large amounts of data
Understanding prepared queries
Summary
Chapter 5: Getting Transactions and Locking Right
The PostgreSQL transaction model
Transaction isolation
Inspecting locks
Summary
Chapter 6: Writing Proper Procedures
Choosing the right language
Managing procedures and transactions
Procedures and indexing
Understanding memory
Summary
Chapter 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
Chapter 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
Chapter 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
Chapter 10: A Standard Approach to Troubleshooting
Getting an overview of the problem
Attacking low performance
Fixing common replication issues
Summary

Book Details

ISBN 139781783555314
Paperback164 pages
Read More
From 3 reviews

Read More Reviews