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.

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.

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.

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 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/ \     
       /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."

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.

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.