O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

PostgreSQL High Availability Cookbook - Second Edition

Book Description

Master over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL

About This Book

  • Create a PostgreSQL cluster that stays online even when disaster strikes
  • Avoid costly downtime and data loss that can ruin your business
  • Updated to include the newest features introduced in PostgreSQL 9.6 with hands-on industry-driven recipes

Who This Book Is For

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.

What You Will Learn

  • Protect your data with PostgreSQL replication and management tools such as Slony, Bucardo, pglogical, and WAL-E
  • Hardware planning to help your database run efficiently
  • Prepare for catastrophes and prevent them before they happen
  • Reduce database resource contention with connection pooling using pgpool and PgBouncer
  • Automate monitoring and alerts to visualize cluster activity using Nagios and collected
  • Construct a robust software stack that can detect and fix outages
  • Learn simple PostgreSQL High Availability with Patroni, or dive into the full power of Pacemaker.

In Detail

Databases are nothing without the data they store. In the event of a failure - catastrophic or otherwise - immediate recovery is essential. By carefully combining multiple servers, it's even possible to hide the fact a failure occurred at all.

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. It all begins with hardware selection for the skeleton of an efficient PostgreSQL database cluster. Then it's on to preventing downtime as well as troubleshooting some real life problems that administrators commonly face. Next, we add database monitoring to the stack, using collectd, Nagios, and Graphite. And no stack is complete without replication using multiple internal and external tools, including the newly released pglogical extension. Pacemaker or Raft consensus tools are the final piece to grant the cluster the ability to heal itself. We even round off by tackling the complex problem of data scalability.

This book exploits many new features introduced in PostgreSQL 9.6 to make the database more efficient and adaptive, and most importantly, keep it running.

Style and approach

This book contains practical recipes that will help the reader solve real world problems related to high availability in PostgreSQL. Every recipe is explained in detail, with relevant explanations, tips and tricks provided for quicker and easier understanding.

Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the code file.

Table of Contents

  1. Title Page
  2. Copyright
  3. Credits
  4. About the Author
  5. About the Reviewer
  6. www.Packtpub.com
  7. Customer Feedback
  8. Preface
    1. What this book covers
    2. What you need for this book 
    3. Who this book is for 
    4. Sections
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Conventions
    6. Reader feedback
    7. Customer support
      1. Downloading the example code
      2. Downloading the color images of this book
      3. Errata
      4. Piracy
      5. Questions
  9. Hardware Planning
    1. Introduction
    2. Planning for redundancy
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    3. Having enough IOPS
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. A working example
        2. Making concessions
    4. Sizing storage
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Real-world example
        2. Adjusting the numbers
        3. Incorporating the spreadsheet
    5. Investing in a RAID
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Picking a processor
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Hyperthreading
        2. Turbo Boost
        3. Power usage
      5. See also
    7. Making the most of memory
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Exploring nimble networking
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. A networking example
        2. Remembering redundancy
        3. Saving the research
      5. See also
    9. Managing motherboards
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Selecting a chassis
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    11. Saddling up to a SAN
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    12. Tallying up
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    13. Protecting your eggs
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  10. Handling and Avoiding Downtime
    1. Introduction
    2. Determining acceptable losses
      1. Getting ready
      2. How to do it...
      3. How it works...
    3. Configuration - getting it right the first time
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Configuration - managing scary settings
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Distinct settings
        2. More information
      5. See also
    5. Identifying important tables
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Reset stats
        2. Using pgstattuple
      5. See also
    6. Defusing cache poisoning
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    7. Exploring the magic of virtual IPs
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Terminating rogue connections
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Reducing contention with concurrent indexes
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. No transactions
        2. One at a time
        3. Danger with OLTP use
      5. See also
    10. Managing system migrations
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Managing software upgrades
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    12. Mitigating the impact of hardware failure
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Copying WAL files more easily
        2. Adding compression
        3. Secondary delay
      5. See also
    13. Applying bonus kernel tweaks
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  11. Pooling Resources
    1. Introduction
    2. Determining connection costs and limits
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Installing PgBouncer
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Configuring PgBouncer safely
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. What about pool_mode?
        2. Problems with prepared statements
      5. See also
    5. Connecting to PgBouncer
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    6. Listing PgBouncer server connections
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Listing PgBouncer client connections
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    8. Evaluating PgBouncer pool health
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    9. Installing pgpool
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Configuring pgpool for master/slave mode
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Testing a write query on pgpool
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    12. Swapping active nodes with pgpool
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    13. Combining the power of PgBouncer and pgpool
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  12. Troubleshooting
    1. Introduction
    2. Performing triage
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Installing common statistics packages
      1. How to do it...
      2. How it works...
    4. Evaluating the current disk performance with iostat
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Tracking I/O-heavy processes with iotop
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Viewing past performance with sar
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Correlating performance with dstat
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    8. Interpreting /proc/meminfo
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    9. Examining /proc/net/bonding/bond0
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    10. Checking the pg_stat_activity view
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Checking the pg_stat_statements view
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Resetting the stats
        2. Catching more queries
      5. See also
    12. Deciphering database locks
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    13. Debugging with strace
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    14. Logging checkpoints properly
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
  13. Monitoring
    1. Introduction
    2. Figuring out what to monitor
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Installing and configuring Nagios
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Configuring Nagios to monitor a database host
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Enhancing Nagios with check_mk
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Getting to know check_postgres
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Installing and configuring collectd
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    8. Adding a custom PostgreSQL monitor to collectd
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    9. Installing and configuring Graphite
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    10. Adding collectd data to Graphite
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    11. Building a graph in Graphite
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    12. Customizing a Graphite graph
      1. Getting ready
      2. How to do it...
      3. How it works...
    13. Creating a Graphite dashboard
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  14. Replication
    1. Introduction
    2. Deciding what to copy
      1. Getting ready
      2. How to do it...
      3. How it works...
    3. Securing the WAL stream
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Setting up a hot standby
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    5. Upgrading to asynchronous replication
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Cascading replication
        2. Using replication slots
        3. Viewing replication status on a replica
      5. See also
    6. Bulletproofing with synchronous replication
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Being less strict
        2. Being more strict
        3. Enabling extreme durability
      5. See also
    7. Faking replication with pg_receivexlog
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    8. Setting up Slony
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    9. Copying a few tables with Slony
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Setting up Bucardo
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    11. Copying a few tables with Bucardo
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    12. Setting up Londiste
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    13. Copying a few tables with Londiste
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    14. Setting up pglogical
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    15. Copying a few tables with pglogical
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
  15. Replication Management Tools
    1. Introduction
    2. Deciding when to use third-party tools
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Installing and configuring Barman
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Backing up a database with Barman
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Restoring a database with Barman
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Installing and configuring OmniPITR
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    7. Managing WAL files with OmniPITR
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    8. Installing and configuring repmgr
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    9. Cloning a database with repmgr
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Swapping active nodes with repmgr
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Installing and configuring walctl
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    12. Cloning a database with walctl
      1. Getting ready
      2. How to do it...
      3. How it  works...
    13. Managing WAL files with walctl
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    14. Installing and configuring WAL-E
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    15. Managing WAL files with WAL-E
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Recovering WAL files
        2. Backing up the database
        3. Removing old files
      5. See also
  16. Simple Stack
    1. Introduction
      1. Why HAProxy?
      2. Why etcd?
      3. Why Patroni?
      4. The stack
    2. Preparing systems for the stack
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    3. Installing and configuring etcd
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Installing and configuring Patroni
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Installing and configuring HAProxy
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    6. Performing a managed failover
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Using an outage to test availability
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Adding a node back into the cluster
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Adding additional nodes to the mix
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Replacing etcd with ZooKeeper
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Replacing etcd with Consul
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    12. Upgrading while staying online
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  17. Advanced Stack
    1. Introduction
      1. Why DRBD?
      2. Why LVM?
      3. Why XFS?
      4. The stack
    2. Preparing systems for the stack
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    3. Getting started with the Linux Volume Manager
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    4. Adding block-level replication
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    5. Incorporating the second LVM layer
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Verifying a DRBD filesystem
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Correcting a DRBD split brain
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    8. Formatting an XFS filesystem
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    9. Tweaking XFS performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    10. Maintaining an XFS filesystem
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Using LVM snapshots
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    12. Switching live stack systems
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    13. Detaching a problematic node
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
  18. Cluster Control
    1. Introduction
      1. Before we begin...
    2. Installing the necessary components
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    3. Configuring Corosync
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
    4. Preparing startup services
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Starting with base options
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    6. Adding DRBD to cluster management
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Adding LVM to cluster management
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Adding XFS to cluster management
      1. Getting ready
      2. How to do it...
      3. How it works...
    9. Adding PostgreSQL to cluster management
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    10. Adding a virtual IP to hide the cluster
      1. Getting ready
      2. How to do it...
      3. How it works...
    11. Adding an e-mail alert
      1. Getting ready
      2. How to do it...
      3. How it works...
    12. Grouping associated resources
      1. Getting ready
      2. How to do it...
      3. How it works...
    13. Combining and ordering related actions
      1. Getting ready
      2. How to do it...
      3. How it works...
    14. Performing a managed resource migration
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    15. Using an outage to test migration
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  19. Data Distribution
    1. Introduction
    2. Identifying horizontal candidates
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    3. Setting up a foreign PostgreSQL server
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Altering foreign servers
        2. Dropping foreign servers
      5. See also
    4. Mapping a remote user
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Creating a foreign table
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Creating all tables for a foreign schema
        2. Dropping foreign tables
      5. See also
    6. Using a foreign table in a query
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Explaining strange planner decisions
        2. Improvements in PostgreSQL 9.6
    7. Optimizing foreign table access
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Transforming foreign tables into local tables
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    9. Creating a scalable nextval replacement
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    10. Building a sharding API
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Talking to the right shard
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Creating a cache
        2. Choosing an application data to logical shard mapping
    12. Moving a shard to another server
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...