4. Troubleshooting | PostgreSQL 9 High Availability Cookbook

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PostgreSQL 9 High Availability Cookbook

By : Shaun Thomas

4.7 (6)

PostgreSQL 9 High Availability Cookbook

4.7 (6)

By: Shaun Thomas

Overview of this book

A comprehensive series of dependable recipes to design, build, and implement a PostgreSQL server architecture free of common pitfalls that can operate for years to come. Each chapter is packed with instructions and examples to simplify even highly complex database operations. If you are a PostgreSQL DBA working on Linux systems who want a database that never gives up, this book is for you. If you've ever experienced a database outage, restored from a backup, spent hours trying to repair a malfunctioning cluster, or simply want to guarantee system stability, this book is definitely for you.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Sections

Conventions

Reader feedback

Customer support

Free Chapter

1. Hardware Planning

1. Hardware Planning

Introduction

Planning for redundancy

Having enough IOPS

Sizing storage

Investing in a RAID

Picking a processor

Making the most of memory

Exploring nimble networking

Managing motherboards

Selecting a chassis

Saddling up to a SAN

Tallying up

Protecting your eggs

2. Handling and Avoiding Downtime

2. Handling and Avoiding Downtime

Introduction

Determining acceptable losses

Configuration – getting it right the first time

Configuration – managing scary settings

Identifying important tables

Defusing cache poisoning

Exploring the magic of virtual IPs

Terminating rogue connections

Reducing contention with concurrent indexes

Managing system migrations

Managing software upgrades

Mitigating the impact of hardware failure

Applying bonus kernel tweaks

3. Pooling Resources

3. Pooling Resources

Introduction

Determining connection costs and limits

Installing PgBouncer

Configuring PgBouncer safely

Connecting to PgBouncer

Listing PgBouncer server connections

Listing PgBouncer client connections

Evaluating PgBouncer pool health

Installing pgpool

Configuring pgpool for master/slave mode

Testing a write query on pgpool

Swapping active nodes with pgpool

Combining the power of PgBouncer and pgpool

4. Troubleshooting

4. Troubleshooting

Introduction

Performing triage

Installing common statistics packages

Evaluating the current disk performance with iostat

Tracking I/O-heavy processes with iotop

Viewing past performance with sar

Correlating performance with dstat

Interpreting /proc/meminfo

Examining /proc/net/bonding/bond0

Checking the pg_stat_activity view

Checking the pg_stat_statements view

Debugging with strace

Logging checkpoints properly

5. Monitoring

5. Monitoring

Introduction

Figuring out what to monitor

Installing and configuring Nagios

Configuring Nagios to monitor a database host

Enhancing Nagios with check_mk

Getting to know check_postgres

Installing and configuring collectd

Adding a custom PostgreSQL monitor to collectd

Installing and configuring Graphite

Adding collectd data to Graphite

Building a graph in Graphite

Customizing a Graphite graph

Creating a Graphite dashboard

6. Replication

6. Replication

Introduction

Deciding what to copy

Securing the WAL stream

Setting up a hot standby

Upgrading to asynchronous replication

Bulletproofing with synchronous replication

Faking replication with pg_receivexlog

Setting up Slony

Copying a few tables with Slony

Setting up Bucardo

Copying a few tables with Bucardo

Setting up Londiste

Copying a few tables with Londiste

7. Replication Management Tools

7. Replication Management Tools

Introduction

Deciding when to use third-party tools

Installing and configuring Barman

Backing up a database with Barman

Restoring a database with Barman

Installing and configuring OmniPITR

Managing WAL files with OmniPITR

Installing and configuring repmgr

Cloning a database with repmgr

Swapping active nodes with repmgr

Installing and configuring walctl

Cloning a database with walctl

Managing WAL files with walctl

8. Advanced Stack

8. Advanced Stack

Introduction

Preparing systems for the stack

Getting started with the Linux Volume Manager

Adding block-level replication

Incorporating the second LVM layer

Verifying a DRBD filesystem

Correcting a DRBD split brain

Formatting an XFS filesystem

Tweaking XFS performance

Maintaining an XFS filesystem

Using LVM snapshots

Switching live stack systems

Detaching a problematic node

9. Cluster Control

9. Cluster Control

Introduction

Installing the components

Configuring Corosync

Preparing startup services

Starting with base options

Adding DRBD to cluster management

Adding LVM to cluster management

Adding XFS to cluster management

Adding PostgreSQL to cluster management

Adding a virtual IP to hide the cluster

Adding an e-mail alert

Grouping associated resources

Combining and ordering related actions

Performing a managed resource migration

Using an outage to test migration

10. Data Distribution

10. Data Distribution

Introduction

Identifying horizontal candidates

Setting up a foreign PostgreSQL server

Mapping a remote user

Creating a foreign table

Using a foreign table in a query

Optimizing foreign table access

Transforming foreign tables into local tables

Creating a scalable nextval replacement

Building a sharding API

Talking to the right shard

Moving a shard to another server

Index

Index

Viewing past performance with sar

While there are many tools to view or analyze the current server performance and behavior, how do we examine historical activity? Most Linux systems rotate logfiles in /var/log for varying periods of time. Unfortunately, these are programs and system logs, not performance measurements.

When we installed the sysstat package in a previous recipe, we gained the use of the sar utility. Some argue that sar is the Swiss Army knife of metric collection. A simple invocation can display past data regarding memory, CPUs, IRQs, disk devices, networks, or even TTYs.

When administering a highly available server, there are a few things as helpful as performance trends. Let's examine them.

Getting ready

As sar and iostat are both part of the sysstat package, we recommend that you review the Evaluating the current disk performance with iostat recipe before continuing.

How to do it...

Collect some sample sar data by following these steps:

Display the default sar output with the...

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.

PostgreSQL 9 High Availability Cookbook

Search

Your notes and bookmarks