PostgreSQL 9 High Availability Cookbook

Over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL.
Preview in Mapt

PostgreSQL 9 High Availability Cookbook

Shaun M. Thomas

1 customer reviews
Over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL.
Mapt Subscription
FREE
$29.99/m after trial
eBook
$18.90
RRP $26.99
Save 29%
Print + eBook
$44.99
RRP $44.99
What do I get with a Mapt Pro subscription?
  • Unlimited access to all Packt’s 5,000+ eBooks and Videos
  • Early Access content, Progress Tracking, and Assessments
  • 1 Free eBook or Video to download and keep every month after trial
What do I get with an eBook?
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with Print & eBook?
  • Get a paperback copy of the book delivered to you
  • Download this book in EPUB, PDF, MOBI formats
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
What do I get with a Video?
  • Download this Video course in MP4 format
  • DRM FREE - read and interact with your content when you want, where you want, and how you want
  • Access this title in the Mapt reader
$0.00
$18.90
$44.99
$29.99p/m after trial
RRP $26.99
RRP $44.99
Subscription
eBook
Print + eBook
Start 30 Day Trial

Frequently bought together


PostgreSQL 9 High Availability Cookbook Book Cover
PostgreSQL 9 High Availability Cookbook
$ 26.99
$ 18.90
PostgreSQL High Availability Cookbook - Second Edition Book Cover
PostgreSQL High Availability Cookbook - Second Edition
$ 43.99
$ 30.80
Buy 2 for $35.00
Save $35.98
Add to Cart
Subscribe and access every Packt eBook & Video.
 
  • 5,000+ eBooks & Videos
  • 50+ New titles a month
  • 1 Free eBook/Video to keep every month
Start Free Trial
 

Book Details

ISBN 139781849516969
Paperback398 pages

Book Description

PostgreSQL, often known as simply "Postgres", is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance.

From hardware selection to software stacks and horizontal scalability, this book will help you build a versatile PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand. We start with selecting the necessary hardware to handle multiple failure scenarios with redundancy. Then, we discuss how to automate and visualize these checks with Nagios, check_mk, and Graphite. We'll finally round off by tackling the complex problem of data scalability.

Table of Contents

Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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

What You Will Learn

  • Protect your data with PostgreSQL replication and management tools such as Slony, Bucardo, and Londiste
  • Choose the correct hardware for redundancy and scale
  • Prepare for catastrophes and prevent them before they happen
  • Reduce database resource contention with connection pooling
  • Automate monitoring and alerts to visualize cluster activity using Nagios and collectd
  • Construct a robust software stack that can detect and fix outages
  • Design a scalable schema architecture to handle billions of queries

Authors

Table of Contents

Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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
Chapter 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

Book Details

ISBN 139781849516969
Paperback398 pages
Read More
From 1 reviews

Read More Reviews

Recommended for You

PostgreSQL 9.0 High Performance Book Cover
PostgreSQL 9.0 High Performance
$ 29.99
$ 21.00
PostgreSQL 9 Admin Cookbook Book Cover
PostgreSQL 9 Admin Cookbook
$ 29.99
$ 21.00
PostgreSQL Replication Book Cover
PostgreSQL Replication
$ 29.99
$ 21.00
PostgreSQL Server Programming Book Cover
PostgreSQL Server Programming
$ 29.99
$ 6.00
Mastering Web Application Development with AngularJS Book Cover
Mastering Web Application Development with AngularJS
$ 26.99
$ 5.40
PostgreSQL for Data Architects Book Cover
PostgreSQL for Data Architects
$ 39.99
$ 28.00