PostgreSQL High Availability Cookbook - Second Edition

Description

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