You are previewing PostgreSQL 9 High Availability Cookbook.
O'Reilly logo
PostgreSQL 9 High Availability Cookbook

Book Description

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

In Detail

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.

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
  • 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 files e-mailed directly to you.

    Table of Contents

    1. PostgreSQL 9 High Availability Cookbook
      1. Table of Contents
      2. PostgreSQL 9 High Availability Cookbook
      3. Credits
      4. About the Author
      5. About the Reviewers
      6. www.PacktPub.com
        1. Support files, eBooks, discount offers, and more
          1. Why Subscribe?
          2. Free Access for Packt account holders
      7. 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. Errata
          3. Piracy
          4. Questions
      8. 1. 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. Incorporate 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. Remember redundancy
            3. Save 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...
      9. 2. 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. Use 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. Copy WAL files more easily
            2. Add 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...
      10. 3. 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...
      11. 4. 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. Getting ready
          2. How to do it...
          3. How it works...
          4. See also
        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. Reset the stats
            2. Catch more queries
          5. See also
        12. Debugging with strace
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        13. Logging checkpoints properly
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
      12. 5. 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...
      13. 6. 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...
          5. See also
        6. Bulletproofing with synchronous replication
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. There's more...
          5. See also
        7. Faking replication with pg_receivexlog
          1. Getting ready
          2. How to do it...
          3. How it works...
          4. 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. 7. 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. 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...
      15. 8. 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. 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
      16. 9. Cluster Control
        1. Introduction
          1. Before we begin...
        2. Installing the 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...
      17. 10. 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...
          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...
        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. Create a cache
            2. Choose 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...
      18. Index