44,39 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 696
Veröffentlichungsjahr: 2017
Ähnliche
Title Page
Second Edition
Copyright
PostgreSQL High Availability Cookbook
Second Edition
Copyright © 2017 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing, and its dealers and distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
First published: February 2017
Production reference: 1010217
ISBN 978-1-78712-553-7
www.packtpub.com
Credits
Author
Shaun M. Thomas
Copy Editor
Safis Editing
Reviewer
Sheldon E. Strauch
Project Coordinator
Shweta H Birwatkar
Commissioning Editor
Amey Varangaonkar
Proofreader
Safis Editing
Acquisition Editor
Ajith Menon
Indexer
Rekha Nair
ContentDevelopmentEditor
Sumeet Sawant
Graphics
Disha Haria
Technical Editors
Akash Patel
Production Coordinator
Melwyn Dsa
About the Author
Shaun M. Thomas has been working with PostgreSQL since late 2000. He is a frequent contributor to the PostgreSQL Performance and General mailing lists, assisting other DBAs with the knowledge he's gained over the years. In 2011 and 2012, he gave presentations at the Postgres Open conference on topics such as handling extreme throughput, high availability, server redundancy, and failover techniques. Most recently, he has contributed the Shard Manager extension and the walctl WAL management suite.Currently, he serves as the database architect at PEAK6 Investments, where he develops standard operating procedure (SOP) guidelines to facilitate reliable server architecture among many other tasks. Many of the techniques used in this book were developed specifically for this extreme environment.He believes that PostgreSQL has a stupendous future ahead, and he can't wait to see the advancements subsequent versions will bring.https://www.linkedin.com/in/bonesmoses
About the Reviewer
Sheldon Strauch is a 22-year veteran of software consulting at companies such as IBM, Sears, Ernst & Young, and Kraft Foods. He has a Bachelor's degree in Business Administration and leverages his technical skills to improve the business' self-awareness. His interests include data gathering, management, and mining; maps and mapping; business intelligence; and application of data analysis for continuous improvement. He is currently focused on development of end-to-end data management and mining at Enova International, a financial services company located in Chicago. In his spare time, he enjoys the performing arts, particularly music, and traveling with his wife, Marilyn.
www.Packtpub.com
For support files and downloads related to your book, please visit www.PacktPub.com.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
https://www.packtpub.com/mapt
Get the most in-demand software skills with Mapt. Mapt gives you full access to all Packt books and video courses, as well as industry-leading tools to help you plan your personal development and advance your career.
Why subscribe?
Fully searchable across every book published by Packt
Copy and paste, print, and bookmark content
On demand and accessible via a web browser
Customer Feedback
Thank you for purchasing this Packt book. We take our commitment to improving our content and products to meet your needs seriously - that's why your feedback is so valuable. Whatever your feelings about your purchase, please consider leaving a review on this book's Amazon page. Not only will this help us, more importantly it will also help others in the community to make an informed decision about the resources that they invest in to learn.
You can also review for us on a regular basis by joining our reviewers' club. If you're interested in joining, or would like to learn more about the benefits we offer, please contact us: [email protected].
Table of Contents
Title Page
Copyright
Credits
About the Author
About the Reviewer
www.Packtpub.com
Customer Feedback
Preface
What this book covers
What you need for this book
Who this book is for
Sections
Getting ready
How to do it...
How it works...
There's more...
See also
Conventions
Reader feedback
Customer support
Downloading the example code
Downloading the color images of this book
Errata
Piracy
Questions
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...
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...
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...
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
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...
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
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
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...
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
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...
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...
Preface
Welcome to the PostgreSQL 9 High Availability Cookbook! As a database engine, PostgreSQL is settling into its place as a reliable bastion of high-transaction rates and very large data installations. DB-Engines recently listed PostgreSQL as the third most popular database software in the world! With such notoriety comes increasing demand for PostgreSQL to act as a critical piece of infrastructure. System outages in these environments can be spectacularly costly and require a higher caliber of management and tooling.
It is the job of a DBA to ensure that the database is always available for application demands and client needs. Yet this is extremely difficult to accomplish without the necessary skills and experience with common operating-system and PostgreSQL tools. Installing, configuring, and optimizing a PostgreSQL cluster is but a tiny fraction of the process. We also need to know how to find and recognize problems, manage a swarm of logical and physical replicas, and scale to increasing demands, all while preventing or mitigating system outages.
This book is something the author wishes existed 10 years ago. Back then, there were no recipes to follow for building a fault-tolerant PostgreSQL cluster; we had to improvise. It is our aim to prevent other DBAs from experiencing the kind of frustration borne of reinventing the wheel. We've done all the hard work, taken notes, outlined everything we've ever learned about keeping PostgreSQL available, and written it all down in here.
New to the second edition is a simpler but more elastic approach to building a highly available PostgreSQL cluster. We’ve also incorporated updates to the recipes to make them compatible with PostgreSQL versions 9.5 and 9.6. A lot can change in two years, and PostgreSQL is a quickly moving target. We can only imagine what kind of features the future might bring.
We hope you find this book useful and relevant; it is the product of years of trial, error, testing, and no small amount of input from the PostgreSQL community.
What this book covers
Chapter 1 , Hardware Planning, sets the tone by covering the role that appropriate hardware selection plays in a successful PostgreSQL cluster of any size.
Chapter 2, Handling and Avoiding Downtime, provides safe settings and defaults for a stable cluster and explains basic techniques for responding to mishaps.
Chapter 3, Pooling Resources, presents PgBouncer and pgpool, two tools geared toward controlling PostgreSQL connections. Together, these can provide an abstraction layer to reduce the effect of outages and increase system performance.
Chapter 4, Troubleshooting, introduces a battery of common Unix and Linux tools and resources that can collect valuable diagnostic information. It also includes a couple of PostgreSQL views that can assist in finding database problems.
Chapter 5, Monitoring, further increases availability by adding Nagios, check_mk, collectd, and Graphite to watch active PostgreSQL clusters. Find potential problems before they happen and stay informed.
Chapter 6, Replication, discusses several PostgreSQL replication scenarios and techniques for more durable data. This includes logical replication tools such as Slony, Bucardo, Londiste, and the newly introduced pglogical.
Chapter 7, Replication Management Tools, brings WAL management to the forefront. Integrate Barman, OmniPITR, repmgr, or walctl into PostgreSQL to further prevent data loss and control complicated multi-server clusters. Or preserve your WAL data safely on the cloud with WAL-E.
Chapter 8, Simple Stack, proposes architecture comprised of HAProxy, Patroni, and etcd. This three-layer stack produces a self-healing and expandable cluster that’s easy to manage.
Chapter 9, Advanced Stack, explains how to combine LVM, DRBD, and XFS to build a solid and durable foundation. Keep data on two servers simultaneously to prevent costly outages. It's for OLTP systems where even PostgreSQL replication isn’t fast enough.
Chapter 10, Cluster Control, incorporates Pacemaker into the advanced stack. We fully automate PostgreSQL server migrations in case of impending maintenance or hardware failure. We add intricate rulesets to control outage and recovery protocols.
Chapter 11, Data Distribution, shows how PostgreSQL features like foreign data wrappers and materialized views can produce a scalable cluster. Included is a simple data sharding API technique to reduce dependency on a single PostgreSQL server.
What you need for this book
This book concentrates on Unix systems with a focus on Linux in particular. Such servers have become increasingly popular for hosting databases for large and small companies. As such, we highly recommend that you use a virtual machine or development system running a recent copy of Debian, Ubuntu, Red Hat Enterprise Linux, or a variant such as CentOS or Scientific Linux.
You will also need a copy of PostgreSQL. If your chosen Linux distribution isn't keeping the included PostgreSQL packages sufficiently up to date, the PostgreSQL website maintains binaries for most popular distributions. You can find these at the following URL:
https://www.postgresql.org/download/
Users of Red Hat Enterprise Linux and its variants should refer to the following URL to add the official PostgreSQL YUM repository to important database systems:
https://yum.postgresql.org/repopackages.php
Users of Debian, Ubuntu, Mint, and other related Linux systems should refer to the PostgreSQL APT wiki page at this URL instead:
https://wiki.postgresql.org/wiki/Apt
Be sure to include any “contrib” packages in your installation. They include helpful utilities and database extensions we will use in some recipes.
Users of BSD should still be able to follow along with these recipes. Some commands may require slight alterations to run properly on BSD, so be sure to understand the intent before executing them. Otherwise, all commands have been confirmed to work on BASH and recent GNU tools.
Who this book is for
This book is written for PostgreSQL DBAs who want an extremely fault-tolerant database cluster. While PostgreSQL is suitable for enterprise environments, there are a lot of tertiary details even a skilled DBA might not know. We're here to fill in those gaps.
There is a lot of material here for all levels of DBA. The primary assumption is that the reader is comfortable with a Unix command line and maintains at least some regular exposure to PostgreSQL as a DBA or system administrator.
If you've ever experienced a database outage, restored from a backup, or spent hours trying to repair a malfunctioning cluster, we have material that covers all of these scenarios. This book holds the key to managing a robust PostgreSQL cluster environment and should be of use to anyone in charge of a critical piece of database infrastructure.
Sections
In this book, you will find several headings that appear frequently (Getting ready, How to do it, How it works, There's more, and See also).
To give clear instructions on how to complete a recipe, we use these sections as follows.
Getting ready
This section tells you what to expect in the recipe, and describes how to set up any software or any preliminary settings required for the recipe.
How to do it...
This section contains the steps required to follow the recipe.
How it works...
This section usually consists of a detailed explanation of what happened in the previous section.
There's more...
This section consists of additional information about the recipe in order to make the reader more knowledgeable about the recipe.
See also
This section provides helpful links to other useful information for the recipe.
Conventions
In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: " By using the pg_stat_statements view, we learn quite a bit about our PostgreSQL cluster."
A block of code is set as follows:
CREATE VIEW v_current_activity AS SELECT * FROM pg_stat_activity WHERE state != 'idle';
When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:
CREATE VIEW v_running_queries AS
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle';
Any command-line input or output is written as follows:
rsync -av --progress --delete source-server:/db/pgdata/ \
/db/pgdata
New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "Clicking the Next button moves you to the next screen."
Warnings or important notes appear in a box like this.
Reader feedback
Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.
To send us general feedback, simply send an e-mail to [email protected], and mention the book title via the subject of your message.
If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on https://www.packtpub.com/books/info/packt/authors.
Customer support
Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.
Downloading the example code
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.
You can download the code files by following these steps:
Log in or register to our website using your e-mail address and password.
Hover the mouse pointer on the
SUPPORT
tab at the top.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box.
Select the book for which you're looking to download the code files.
Choose from the drop-down menu where you purchased this book from.
Click on
Code Download
.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR / 7-Zip for Windows
Zipeg / iZip / UnRarX for Mac
7-Zip / PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/PostgreSQL-High-Availability-Cookbook. We also have other code bundles from our rich catalog of books and videos available athttps://github.com/PacktPublishing/. Check them out!
Downloading the color images of this book
We also provide you a PDF file that has color images of the screenshots/diagrams used in this book. The color images will help you better understand the changes in the output. You can download this file from: https://www.packtpub.com/sites/default/files/downloads/PostgreSQLHighAvailabilityCookbook_ColorImages.pdf.
Errata
Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/submit-errata, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.
Piracy
Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.
Please contact us at [email protected] with a link to the suspected pirated material.
We appreciate your help in protecting our authors, and our ability to bring you valuable content.
Questions
You can contact us at [email protected] if you are having a problem with any aspect of the book, and we will do our best to address it.
Hardware Planning
In this chapter, we will learn about selection and provisioning of hardware necessary to build a highly-available PostgreSQL database. We will cover the following recipes in this chapter:
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
Introduction
What does high availability mean? In the context of what we're trying to build, it means we want our database to start and remain online for as long as possible. A critical component of this is the hardware that hosts the database itself. No matter how perfect a machine and its parts may be, failure or unexpected behavior of any element can result in an outage.
So how do we avoid these unwanted outages? Expect them. We must start by assuming hardware can and will fail, and at the worst possible moment. If we start with that in mind, it becomes much easier to make decisions regarding the composition of each server we are building.
Make no mistake! Much of this planning will rely on worksheets, caveats, and compromise. Some of our choices will have several expensive options, and we will have to weigh the benefits offered against our total cost outlay. We want to build something stable, which is not always easy. Depending on the size of our company, our purchasing power, and available hosting choices, we may be in for a rather complicated path to that goal.
This chapter will attempt to paint a complete picture of a highly-available environment in such a way that you can pick and choose the best solution without making too many detrimental compromises. Of course, we'll offer advice to what we believe is the best overall solution, but you don't always have to take our word for it.
Planning for redundancy
Redundancy means having a spare; but a spare for what? Everything. Every single part, from motherboard to chassis, power supply to network cable, disk space to throughput, should have at least one piece of excess equipment or capacity available for immediate use. Let's go through as many of these as we can imagine, before we do anything that might depend on something we bought.
Getting ready
Fire up your favorite spreadsheet program; we'll be using it to keep track of all the parts that go into the server, and any capacity concerns. If you don't have one, Open Office and Libre Office are good free alternatives for building these spreadsheets. Subsequent sections will help determine most of the row contents.
How to do it...
We simply need to produce a hardware spreadsheet to track our purchase needs. We can do that with the following steps:
Create a new spreadsheet for parts and details.
Create a heading row with the following columns:
Type
Capacity
Supplier
Price
Count
Total cost
Create a new row for each type of the following components:
Chassis
CPU
Hard Drive (3.5")
Hard Drive (2.5")
Hard Drive (SSD)
Motherboard
Network Card
Power Supply
RAID Controller
RAM
SAN
In the
Chassis
row, under the
Total cost
column, enter the following formula:
=D2*E2
Copy and paste the formula into the
Total Cost
column for all the rows we created. The end result should look something like the following screenshot:
How it works...
What we've done is prepare a spreadsheet that we can fill in with information collected from the rest of this chapter. We will have very long discussions regarding each part of the server we want to build, so we need a place to collect each decision we make along the way.
The heading column can include any other details you wish to retain about each part, but for the sake of simplicity, we are stuck to the bare minimum. This also goes for the parts we chose for each column. Depending on the vendor you select to supply your server, many of these decisions will already be made. It's still a good idea to include each component in case you need an emergency replacement.
The Total Cost column exists for one purpose: to itemize the cost of each part, multiplied by how many we will need to complete the server.
We'll have discussions later as to failure rates of different types of hardware, which will influence how many excess components to allocate. Don't worry about that for now.
There's more...
It's also a very good idea to include a summary for all of our Total Cost columns, so we get an aggregate cost estimate for the whole server. To do that with our spreadsheet example, keep in mind that the Total Cost column is listed as column F.
To add a Sum Total column to your spreadsheet on row 15, column F, enter the formula =SUM(F2:F12). If you've added more columns, substitute for column F whichever column now represents the Total Cost. Likewise, if you have more than 13 rows of different parts, use a different row to represent your summary price than row 15.
See also
There are a lot of spreadsheet options available. Many corporations supply a copy of Microsoft Excel. However, if this is not the case, there are many alternatives as follows:
Google Docs
:
http://sheets.google.com/
Open Office
:
http://www.openoffice.org/
Libre Office
:
http://www.libreoffice.org/
All of these options are free to use and popular enough that support and documentation are readily available.
Having enough IOPS
IOPS stands for Input/Output Operations Per Second. Essentially, this describes how many operations a device can perform per second before it should be considered saturated. If a device is saturated, further requests must wait until the device has spare bandwidth. A server overwhelmed with requests can amount to seconds, minutes, or even hours of delayed results.
Depending on application timeout settings and user patience, a device with low IOPS appears as a bottleneck that reduces both system responsiveness and the perception of quality. A database with insufficient IOPS to service queries in a timely manner is unavailable for all intents and purposes. It doesn't matter if PostgreSQL is still available and serving results in this scenario, as its availability has already suffered. We are trying to build a highly-available database. To do so, we need to build a server with enough performance to survive daily operation. In addition, we must overprovision for unexpected surges in popularity, and account for future storage and throughput needs based on monthly increases in storage utilization.
Getting ready
This process is more of a thought experiment. We will present some very rough estimates of IO performance for many different disk types. For each, we should increment entries in our hardware spreadsheet based on perceived need.
The main things we will need for this process are numbers. During development, applications commonly have a goal, expected client count, table count, estimated growth rates, and so on. Even if we have to guess for many of these, they will all contribute to our IOPS requirements. Have these numbers ready, even if they're simply guesses.
How to do it...
We need to figure out how many operations per second we can expect. We can estimate this by using the following steps:
Increment the
Count
column in our hardware spreadsheet for one or more of the following, and round up:
For 3.5" hard drives, divide by 200
For 2.5" hard drives, divide by 150
For SSD hard drives, divide by 50,000, then add two
Multiply these numbers together, and double the result. Then multiply the total by eight.
Count the amount of tables used in those queries. If this is unavailable, use three.
Obtain the average number of queries per page. If this is unavailable, use 10.
Collect the amount of simultaneous database connections. Start with the expected user count, and divide by 50.
Add 10 percent to any count greater than 0 and then round up.
How it works...
Wow, that's a lot of work! There's a reason for everything, of course.
In the initial three steps, we're trying to figure out how many operations might touch an object on disk. For every user that's actively loading a page, for every query in that page, and for every table in that query, that's a potential disk read or write.
We double that number to account for the fact we're estimating all of this. It's a common engineering trick to double or triple calculations to absorb unexpected capacity, variance in materials, and so on. We can use that same technique here.
But why do we then multiply by eight? In a worst (or best) case scenario, it's not uncommon for an application to double the amount of users or requests on a yearly basis. Doubled usage means doubled hardware needs. If requirements double in one year, we would need a server three times more powerful (1 + 2) than the original estimates to account for the second year. Another doubling would mean a server seven times better (1 + 2 + 4). CPUs, RAM, and storage are generally available as powers of two. Since it's fairly difficult to obtain storage seven times faster than what we already have, we multiply the total by eight.
That gives a total IOPS value roughly necessary for our database to immediately serve every request for the next three years, straight from the disk device. Several companies buy servers every three or four years as a balance between cost and capacity, so these estimates are based on that assumption.
In the next step, we get a rough estimate of the amount of disks necessary to serve the required IOPS. Our numbers in these steps are based on hard drive performance. A 15,000 RPM hard drive can serve under ideal conditions, roughly 200 operations per second. Likewise, a 10,000 RPM drive can provide about 150 operations per second. Current SSDs at the time of writing commonly reach 200,000-300,000 IOPS, and some even regularly eclipse a cool million. However, because they are so fast, we need far fewer of them, and thus the risk is not as evenly distributed. We artificially increase the amount of these drives because, again, we are erring toward availability.
Finally, we add a few extra devices for spares that will go in a closet somewhere, just in case one or more drives fail. This also insulates us from the rare event that hardware is discontinued or otherwise difficult to obtain.
There's more...
Figuring out the number of IOPS we need and the devices involved is only part of the story.
Making concessions
Our calculations always assume worst-case scenarios. This is both expensive and in many cases, overzealous. We ignore RAM caching of disk blocks, we don't account for application frontend caches, and the PostgreSQL shared buffers are also not included.
Why? Crashes are always a concern. If a database crashes, buffers are forfeit. If the application frontend cache gets emptied or has problems, reads will be served directly from the database. Until caches are rebuilt, query results can be multiple orders of magnitude slower than normal for minutes or hours. We will discuss methods of circumventing these effects, but these IOPS numbers give us a baseline.
The number of necessary IOPS, and hence disk requirements, are subject to risk evaluation and cost benefit analysis. Deciding between 100 percent coverage and an acceptable fraction is a careful balancing act. Feel free to reduce these numbers; just consider the cost of an outage as part of the total. If a delay is considered standard operating procedures, fractions up to 50 percent are relatively low risk. If possible, try to run tests for an ultimate decision before purchase.
Sizing storage
Capacity planning for a database server involves a lot of variables. We must account for table count, user activity, compliance storage requirements, indexes, object bloat, maintenance, archival, and more. We may even have to consider application features that do not exist. New functionality often brings new tables, new storage standards, and archival needs. Planning done now may have little relevance to future usage.
So how do we produce functional estimates for disk space, with so many uncertain or fluctuating elements? Primarily, we want to avoid a scenario where we do not have enough space. Running out of disk space results in ignored queries at best, and a completely frozen and difficult to repair database at worst. Neither are ingredients of a highly-available environment.
So we have a lower bound in this case, enough to avoid catastrophe, though it's in our best interest to allocate more than the bare minimum.
Getting ready
Since there are a lot of variables that contribute to the volume of storage we want, we need information about each of them. Gather as many data points as possible regarding things such as: largest expected tables and indexes, row counts per day, indexes per table, desired excess, and anything else imaginable. We'll use all of it.
How to do it...
We can collect some of the information we want from PostgreSQL if we have a running instance already. If not, we can use baseline numbers. Follow these steps if you already have a PostgreSQL database available:
Submit this query to get the amount of space used by all databases:
SELECT pg_size_pretty(sum(pg_database_size(oid))::BIGINT) FROM pg_database;
Wait one week.
Perform the preceding query again.
Subtract the first reading from the second.
If we don't have an existing install and are working with a project that has yet to start development, we can substitute a few guesses instead. Without a running PostgreSQL instance, use the following assumptions:
Our databases have a total size of 100 GB
After one week, our install grew by 1.5 GB
Next, we can calculate our growth needs for the next three years. Perform the following steps:
Multiply the change in install size by four.
Apply the following formula, where
x
is the most recent size of the databases, and
y
is the value from the previous step:
x * (1 + y/x)^36
.
Multiply the previous result by two.
How it works...
In the end, this is the magic of compounding interest. If we have an existing database installed, it can tell us not only how much space it currently consumes, but also how quickly it's currently growing. If not, we can start with a medium size and substitute a growth assumption that will cause the cumulative total to double in size every year. Remember, we begin by working with worst-case scenarios, and modify the numbers afterwards.
The PostgreSQL query we used takes advantage of the system catalog and known statistics regarding the database contents. The pg_database_size function always returns the number of bytes a database uses, so we must use the pg_size_pretty function to make it more human readable.
Once we know the size of the database instance and its growth rate, we can apply a simple compounding interest function to estimate the volume at any point in the future. This not only accounts for the current growth rate, but also incorporates additional accumulation caused by increases in clients, table counts, and other unspecified sources. It's extremely aggressive, since we take the weekly growth rate, translate that to a monthly rate, and apply the compounding monthly instead of yearly.
And then we use a standard engineering tactic and double the estimate, just in case. Using the provided values--that of a 100 GB database that grows at 1.5 GB per week-we would have an 815 GB database install in three years. With a system that large, we should allocate at least 1630 GB. If we simply added the 1.5 GB weekly growth rate for three years, the final tally would only be 334 GB, and we could get by with 668 GB.
There's more...
Don't let our formulas define your only path. Let's explore how they apply in a real-world situation, and how we can modify them to better fit our systems.
Adjusting the numbers
We already mentioned that the growth curve used here is extremely aggressive. We can't risk ever running out of space in a production database and still consider ourselves highly-available. However, there is probably a safe position between the current growth rate of the database, and the compounded estimate, especially since we are doubling the allocation anyway.
In the preceding real-world example, the database is likely to have a size between 2315 GB and 4374 GB. If we split the difference, that's 3345 GB. Furthermore, we don't necessarily have to double that number if we're comfortable having a disk device that's 70 percent full three years from now instead of 50 percent. With that in mind, we would probably be safe with 5 TB of space instead of 9 TB. That's a vast saving if we're willing to make those assumptions.
Incorporating the spreadsheet
At the beginning of this chapter, we created a hardware cost spreadsheet to estimate the total cost of a highly-available server. If we were following the chapter, our spreadsheet already accounts for the minimum number of devices necessary to provide the IOPS we want.
Suppose we needed 15,000 IOPS, and decided to use 2.5-inch drives. That would require over 40 drives. Even at only 300 GB each, that's 12 TB of total available space. Yet the case for SSDs is the opposite. For our previous example, we would need at least five 1 GB SSD drives, or one very large PCIe SSD to provide 5 TB of space for the adjusted sample.
Whichever solution we finally choose, we can take the advice from every section so far. At this point, the spreadsheet should have a device count that should satisfy most, if not all, of our space and IOPS requirements.
Investing in a RAID
RAID stands for Redundant Array of Independent (or Inexpensive) Disks
