Book Image

PostgreSQL High Availability Cookbook - Second Edition

By : Shaun Thomas
Book Image

PostgreSQL High Availability Cookbook - Second Edition

By: Shaun Thomas

Overview of this book

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.
Table of Contents (18 chapters)
Title Page
About the Author
About the Reviewer
Customer Feedback


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:

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:

Users of Debian, Ubuntu, Mint, and other related Linux systems should refer to the PostgreSQL APT wiki page at this URL instead:

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.


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.


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
  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 ASSELECT 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/ \     

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.


Tips and tricks appear 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

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 If you purchased this book elsewhere, you can visit and register to have the files e-mailed directly to you.

You can download the code files by following these steps:

  1. Log in or register to our website using your e-mail address and password.
  2. Hover the mouse pointer on the SUPPORT tab at the top.
  3. Click on Code Downloads & Errata.
  4. Enter the name of the book in the Search box.
  5. Select the book for which you're looking to download the code files.
  6. Choose from the drop-down menu where you purchased this book from.
  7. 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 We also have other code bundles from our rich catalog of books and videos available at 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:


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, 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


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.


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.