Book Image

PostgreSQL 9 High Availability Cookbook

By : Shaun Thomas
Book Image

PostgreSQL 9 High Availability Cookbook

By: Shaun Thomas

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL 9 High Availability Cookbook
Shaun Thomas
Jul, 2014 | 398 pages
No titles found
Table of Contents (17 chapters)
PostgreSQL 9 High Availability Cookbook
PostgreSQL 9 High Availability Cookbook
Credits
Credits
About the Author
About the Author
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Hardware Planning
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
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
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
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
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
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
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
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
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
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

Chapter 3. Pooling Resources

In this chapter, we will learn to combine and abstract connectivity to isolate and protect the database. We will cover the following recipes in this chapter:

  • 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

Previous Section
End of Section 1
Next Section