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

Correlating performance with dstat

Eventually, we will want to view multiple types of system activity simultaneously. While sar has many operating modes, its output is linear. Without a tool to interpret its exhaustive data, we are left with a lot of manual analysis of several sar invocations. While iostat and iotop are wonderful tools, they are rather limited in scope by comparison.

So, let us introduce dstat. While dstat can't access historical data like sar, it can display output from several different operation modes side by side. It also includes color coding to easily distinguish units. It's a very pretty command-line tool and it summarizes several different metrics at a glance.

For servers that are of particular importance, we actually keep a terminal window that displays the dstat results open so that we get an early warning when numbers begin to look bad.

Getting ready

Unlike the sysstat package, dstat is ready to use immediately after being installed.

How to do it...

The output from...