Book Image

PostgreSQL 9 High Availability Cookbook

By : Shaun Thomas
Book Image

PostgreSQL 9 High Availability Cookbook

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.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL 9 High Availability Cookbook
Shaun Thomas
Jul, 2014 | 6 hours 38 minutes
No titles found
Table of Contents (12 chapters)
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
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
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
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
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
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
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
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
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
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
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
11
Index
Index

Chapter 1. Hardware Planning

In this chapter, we will learn about selection and provisioning of hardware necessary to build a highly available PostgreSQL database. We will cover the following recipes in this chapter:

  • 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
Previous Section
End of Section 1
Next Section