PostgreSQL 9 High Availability Cookbook电子书

PostgreSQL 9 High Availability Cookbook

Table of Contents

PostgreSQL 9 High Availability Cookbook

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

Sections

Getting ready

How to do it…

How it works…

There's more…

See also

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Hardware Planning

Introduction

Planning for redundancy

Getting ready

How to do it...

How it works...

There's more...

See also

Having enough IOPS

Getting ready

How to do it...

How it works...

There's more...

A working example

Making concessions

Sizing storage

Getting ready

How to do it...

How it works...

There's more...

Real-world example

Adjusting the numbers

Incorporate the spreadsheet

Investing in a RAID

Getting ready

How to do it...

How it works...

There's more...

See also

Picking a processor

Getting ready

How to do it...

How it works...

There's more...

Hyperthreading

Turbo Boost

Power usage

See also

Making the most of memory

Getting ready

How to do it...

How it works...

There's more...

Exploring nimble networking

Getting ready

How to do it...

How it works...

There's more...

A networking example

Remember redundancy

Save the research

See also

Managing motherboards

Getting ready

How to do it...

How it works...

There's more...

See also

Selecting a chassis

Getting ready

How to do it...

How it works...

There's more...

Saddling up to a SAN

Getting ready

How to do it...

How it works...

There's more...

See also

Tallying up

Getting ready

How to do it...

How it works...

There's more...

Protecting your eggs

Getting ready

How to do it...

How it works...

There's more...

2. Handling and Avoiding Downtime

Introduction

Determining acceptable losses

Getting ready

How to do it...

How it works...

Configuration – getting it right the first time

Getting ready

How to do it...

How it works...

There's more...

See also

Configuration – managing scary settings

Getting ready

How to do it...

How it works...

There's more...

Distinct settings

More information

See also

Identifying important tables

Getting ready

How to do it...

How it works...

There's more...

Reset stats

Use pgstattuple

See also

Defusing cache poisoning

Getting ready

How to do it...

How it works...

See also

Exploring the magic of virtual IPs

Getting ready

How to do it...

How it works...

There's more...

Terminating rogue connections

Getting ready

How to do it...

How it works...

There's more...

Reducing contention with concurrent indexes

Getting ready

How to do it...

How it works...

There's more...

No transactions

One at a time

Danger with OLTP use

See also

Managing system migrations

Getting ready

How to do it...

How it works...

There's more...

See also

Managing software upgrades

Getting ready

How to do it...

How it works...

There's more...

See also

Mitigating the impact of hardware failure

Getting ready

How to do it...

How it works...

There's more...

Copy WAL files more easily

Add compression

Secondary delay

See also

Applying bonus kernel tweaks

Getting ready

How to do it...

How it works...

There's more...

3. Pooling Resources

Introduction

Determining connection costs and limits

Getting ready

How to do it...

How it works...

There's more...

Installing PgBouncer

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring PgBouncer safely

Getting ready

How to do it...

How it works...

There's more...

What about pool_mode?

Problems with prepared statements

See also

Connecting to PgBouncer

Getting ready

How to do it...

How it works...

See also

Listing PgBouncer server connections

Getting ready

How to do it...

How it works...

There's more...

See also

Listing PgBouncer client connections

Getting ready

How to do it...

How it works...

There's more...

See also

Evaluating PgBouncer pool health

Getting ready

How to do it...

How it works...

There's more...

See also

Installing pgpool

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring pgpool for master/slave mode

Getting ready

How to do it...

How it works...

There's more...

See also

Testing a write query on pgpool

Getting ready

How to do it...

How it works...

There's more...

Swapping active nodes with pgpool

Getting ready

How to do it...

How it works...

There's more...

See also

Combining the power of PgBouncer and pgpool

Getting ready

How to do it...

How it works...

There's more...

4. Troubleshooting

Introduction

Performing triage

Getting ready

How to do it...

How it works...

There's more...

Installing common statistics packages

Getting ready

How to do it...

How it works...

See also

Evaluating the current disk performance with iostat

Getting ready

How to do it...

How it works...

There's more...

See also

Tracking I/O-heavy processes with iotop

Getting ready

How to do it...

How it works...

There's more...

See also

Viewing past performance with sar

Getting ready

How to do it...

How it works...

There's more...

See also

Correlating performance with dstat

Getting ready

How to do it...

How it works...

See also

Interpreting /proc/meminfo

Getting ready

How to do it...

How it works...

There's more...

See also

Examining /proc/net/bonding/bond0

Getting ready

How to do it...

How it works...

See also

Checking the pg_stat_activity view

Getting ready

How to do it...

How it works...

There's more...

See also

Checking the pg_stat_statements view

Getting ready

How to do it...

How it works...

There's more...

Reset the stats

Catch more queries

See also

Debugging with strace

Getting ready

How to do it...

How it works...

There's more...

See also

Logging checkpoints properly

Getting ready

How to do it...

How it works...

There's more...

See also

5. Monitoring

Introduction

Figuring out what to monitor

Getting ready

How to do it...

How it works...

There's more...

Installing and configuring Nagios

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring Nagios to monitor a database host

Getting ready

How to do it...

How it works...

There's more...

See also

Enhancing Nagios with check_mk

Getting ready

How to do it...

How it works...

There's more...

See also

Getting to know check_postgres

Getting ready

How to do it...

How it works...

There's more...

See also

Installing and configuring collectd

Getting ready

How to do it...

How it works...

See also

Adding a custom PostgreSQL monitor to collectd

Getting ready

How to do it...

How it works...

There's more...

See also

Installing and configuring Graphite

Getting ready

How to do it...

How it works...

See also

Adding collectd data to Graphite

Getting ready

How to do it...

How it works...

See also

Building a graph in Graphite

Getting ready

How to do it...

How it works...

There's more...

Customizing a Graphite graph

Getting ready

How to do it...

How it works...

Creating a Graphite dashboard

Getting ready

How to do it...

How it works...

There's more...

6. Replication

Introduction

Deciding what to copy

Getting ready

How to do it...

How it works...

Securing the WAL stream

Getting ready

How to do it...

How it works...

There's more...

See also

Setting up a hot standby

Getting ready

How to do it...

How it works...

See also

Upgrading to asynchronous replication

Getting ready

How to do it...

How it works...

There's more...

See also

Bulletproofing with synchronous replication

Getting ready

How to do it...

How it works...

There's more...

See also

Faking replication with pg_receivexlog

Getting ready

How to do it...

How it works...

See also

Setting up Slony

Getting ready

How to do it...

How it works...

See also

Copying a few tables with Slony

Getting ready

How to do it...

How it works...

There's more...

See also

Setting up Bucardo

Getting ready

How to do it...

How it works...

See also

Copying a few tables with Bucardo

Getting ready

How to do it...

How it works...

See also

Setting up Londiste

Getting ready

How to do it...

How it works...

See also

Copying a few tables with Londiste

Getting ready

How to do it...

How it works...

See also

7. Replication Management Tools

Introduction

Deciding when to use third-party tools

Getting ready

How to do it...

How it works...

There's more...

Installing and configuring Barman

Getting ready

How to do it...

How it works...

See also

Backing up a database with Barman

Getting ready

How to do it...

How it works...

There's more...

See also

Restoring a database with Barman

Getting ready

How to do it...

How it works...

There's more...

See also

Installing and configuring OmniPITR

Getting ready

How to do it...

How it works...

See also

Managing WAL files with OmniPITR

Getting ready

How to do it...

How it works...

There's more...

See also

Installing and configuring repmgr

Getting ready

How to do it...

How it works...

See also

Cloning a database with repmgr

Getting ready

How to do it...

How it works...

There's more...

See also

Swapping active nodes with repmgr

Getting ready

How to do it...

How it works...

There's more...

See also

Installing and configuring walctl

Getting ready

How to do it...

How it works...

See also

Cloning a database with walctl

Getting ready

How to do it...

How it works...

Managing WAL files with walctl

Getting ready

How to do it...

How it works...

There's more...

8. Advanced Stack

Introduction

Why DRBD?

Why LVM?

Why XFS?

The stack

Preparing systems for the stack

Getting ready

How to do it...

How it works...

There's more...

See also

Getting started with the Linux Volume Manager

Getting ready

How to do it...

How it works...

There's more...

See also

Adding block-level replication

Getting ready

How to do it...

How it works...

See also

Incorporating the second LVM layer

Getting ready

How to do it...

How it works...

There's more...

See also

Verifying a DRBD filesystem

Getting ready

How to do it...

How it works...

See also

Correcting a DRBD split brain

Getting ready

How to do it...

How it works...

See also

Formatting an XFS filesystem

Getting ready

How to do it...

How it works...

See also

Tweaking XFS performance

Getting ready

How to do it...

How it works...

There's more...

See also

Maintaining an XFS filesystem

Getting ready

How to do it...

How it works...

There's more...

See also

Using LVM snapshots

Getting ready

How to do it...

How it works...

See also

Switching live stack systems

Getting ready

How to do it...

How it works...

There's more...

Detaching a problematic node

Getting ready

How to do it...

How it works...

There's more...

See also

9. Cluster Control

Introduction

Before we begin...

Installing the components

Getting ready

How to do it...

How it works...

There's more...

See also

Configuring Corosync

Getting ready

How to do it...

How it works...

See also

Preparing startup services

Getting ready

How to do it...

How it works...

There's more...

See also

Starting with base options

Getting ready

How to do it...

How it works...

There's more...

See also

Adding DRBD to cluster management

Getting ready

How to do it...

How it works...

There's more...

See also

Adding LVM to cluster management

Getting ready

How to do it...

How it works...

There's more...

Adding XFS to cluster management

Getting ready

How to do it...

How it works...

Adding PostgreSQL to cluster management

Getting ready

How to do it...

How it works...

There's more...

Adding a virtual IP to hide the cluster

Getting ready

How to do it...

How it works...

Adding an e-mail alert

Getting ready

How to do it...

How it works...

Grouping associated resources

Getting ready

How to do it...

How it works...

Combining and ordering related actions

Getting ready

How to do it...

How it works...

Performing a managed resource migration

Getting ready

How to do it...

How it works...

There's more...

Using an outage to test migration

Getting ready

How to do it...

How it works...

There's more...

10. Data Distribution

Introduction

Identifying horizontal candidates

Getting ready

How to do it...

How it works...

There's more...

See also

Setting up a foreign PostgreSQL server

Getting ready

How to do it...

How it works...

There's more...

Altering foreign servers

Dropping foreign servers

See also

Mapping a remote user

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a foreign table

Getting ready

How to do it...

How it works...

There's more...

See also

Using a foreign table in a query

Getting ready

How to do it...

How it works...

There's more...

Optimizing foreign table access

Getting ready

How to do it...

How it works...

There's more...

Transforming foreign tables into local tables

Getting ready

How to do it...

How it works...

There's more...

See also

Creating a scalable nextval replacement

Getting ready

How to do it...

How it works...

There's more...

Building a sharding API

Getting ready

How to do it...

How it works...

There's more...

See also

Talking to the right shard

Getting ready

How to do it...

How it works...

There's more...

Create a cache

Choose an application data to logical shard mapping

Moving a shard to another server

Getting ready

How to do it...

How it works...

There's more...

Index