What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

PostgreSQL 9 High Availability Cookbook

Chapter 2. Handling and Avoiding Downtime

In this chapter, we will learn how we should react when outages inevitably occur and how to prepare ourselves for them. We will cover the following recipes in this chapter:

Determining acceptable losses
Configuration – getting it right the first time
Configuration – managing scary settings
Identifying important tables
Defusing cache poisoning
Exploring the magic of virtual IPs
Terminating rogue connections
Reducing contention with concurrent indexes
Managing system migrations
Managing software upgrades
Mitigating the impact of hardware failure
Applying bonus kernel tweaks

Description

A comprehensive series of dependable recipes to design, build, and implement a PostgreSQL server architecture free of common pitfalls that can operate for years to come. Each chapter is packed with instructions and examples to simplify even highly complex database operations. If 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, and Londiste

Choose the correct hardware for redundancy and scale

Prepare for catastrophes and prevent them before they happen

Reduce database resource contention with connection pooling

Automate monitoring and alerts to visualize cluster activity using Nagios and collectd

Construct a robust software stack that can detect and fix outages

Design a scalable schema architecture to handle billions of queries

What do you get with Print?

Instant access to your digital eBook copy whilst your Print order is Shipped

Paperback book shipped to your preferred address

Download this book in EPUB and PDF formats

Access this title in our online reader with advanced features

DRM FREE - Read whenever, wherever and however you want

Frequently bought together

$54.99

$29.99

$48.99

Total $ 133.97

Filter reviews by

All

Amazon verified reviews

Denver Water - Dawson Mar 04, 2016

Wonderful published book. Great vendor!!

Amazon Verified review

skemppin Aug 21, 2014

Several good guidelines for designing PostgreSQL databases and HA Clusters.For example recipes for counting storage size, IOPS, cpu, memory etc...Also very specific guides how to use different replications and their monitoring + management and HA solutions.

loumarin Feb 09, 2015

This book exceeded my expectations, I am constantly reading and re-doing the recipes, it will be a good adquisition to anyone interested in Postgresql Big Leagues.

Emre Sevinç Aug 21, 2014

PostgreSQL 9 High Availability Cookbook is a very well written book whose primary audience are experienced DBAs and system engineers who want to take their PostgreSQL skills to the next level by diving into the details of building highly available PostgreSQL based systems. Reading this book is like drinking from a fire hose, the signal-to-noise ratio is very high; in other words, every single page is packed with important, critical, and very practical information. As a consequence, this also means that the book is not for newbies: not only you have to know the fundamental aspects of PostgreSQL from a database administrator’s point of view, but you also need to have solid GNU/Linux system administration background.One of the strongest aspects of the book is the author’s principled and well-structured engineering approach to building a highly available PostgreSQL system. Instead of jumping to some recipes to be memorized, the book teaches you basic but very important principles of capacity planning. More importantly, this planning of servers and networking is not only given as a good template, but the author also explains the logic behind it, as well as drawing attention to the reason behind the heuristics he use and why some magic numbers are taken as a good estimate in case of lack of more case-specific information. This style is applied very consistently throughout the book, each recipe is explained so that you know why you do something in addition to how you do it.After the first chapter on basic planning, the author jumps to a set of miscellaneous topics in the Chapter 2, and details some important tricks such as defusing cache poisoning, concurrent indexes, and Linux kernel tweaks. This chapter starts to reveal another valuable aspect of the book: the information regarding an open source RDBMS such as PostgreSQL is freely available on the Internet, but depending on your needs, a particular set of information can very well be scattered over a lot of e-mail list messages, forum posts, Wiki pages, etc., and it takes a disciplined mind with a lot of field experience to put all of that scattered information into a single, consistent, logical and easy to follow form.Starting from Chapter 3, each chapter explores a single topic in a lot of practical detail, starting with connection pooling. This chapter, as well as almost all of the remaining ones has a nice feature: the author always try to explain alternative solutions, describes their advantages and disadvantages, and where possible shows how to combine some alternatives to get best of each.Chapters 4 and 5, namely Troubleshooting and Monitoring can be thought as a single chapter, because it is difficult to think these fundamental concepts separately. These chapters are also not only valuable for PostgreSQL DBAs but for any DBA or any GNU/Linux system administrator in general. Troubleshooting and monitoring a highly available database requires a book by itself, but since this book’s scope is clearly defined, the author provides enough background and practical starting points in about 70 pages.I can easily say that Chapter 6: Replication, together with Chapter 7: Replication Management Tools starts to form the ‘meat’ of the book; without successfully implementing and practically managing the replication of your critical database servers, it is impossible to think about building a highly available system, in other words, you need at least one replica of your database system, so that if your primary system goes down, you can very easily switch to your replica (or offload some of your less criticial applications to your replica and relive the stress on your primary system). These two chapters presents you the solid and practical information to achieve that goal. Similar to the previous chapters, the author shows and explains many useful and practical tools, he also does not refrain from presenting an open source tool, walctl, that he developed to as a “PostgreSQL WAL management system that pushes or pulls WAL files from a remote central storage server”. I consider another positive point for the book because it clearly indicates the serious time investment of the author for PostgreSQL and its high availability configuration.Chapter 8: Advanced Stack, is aptly named, because this chapter, together with Chapter 9: Cluster Control, forms the most advanced and complex part of the book. The author’s warnings regarding the information density, and related real-life complexity of the topics explained in these two chapters should not be taken lightly. Indeed, there are many combinations of events that can lead to subtle and hard to debug errors in case of clusters set up to take over from failing nodes. Creating such a highly available system with Linux based tools such LVM, XFS, DRBD, Pacemaker, and Corosync requires careful planning, probably experimenting in a safe virtual environment, and then a disciplined execution, as well as monitoring. Again, these chapters alone include topics that can take a volume, and a detailed training by themselves, and I think the author kept a good balance between depth and breadth.Final chapter, Data Distribution, can be considered as a bonus chapter that briefly shows setting up a PostgreSQL server, dealing with foreign tables, managing shards, creating a scalable nextval replacement, and relevant tips and tricks.There are not many negative sides to this very dense PostgreSQL book. A few minor points that deserves mention are its focus on the most popular Linux distributions such as Red Hat, Debian and their derivatives (FreeBSD and other BSD admins will require slightly more effort), some obsolete networking command usage such as ifconfig instead of ip (but then again, this might be helpful for FreeBSD admins), and inconsistent use of command outputs (sometimes no output is shown, whereas for some commands screen-shots or textual outputs are used inconsistently). One might also argue for a slight reordering of chapters for pedagogical concerns, but then again this is highly open to debate and one’s particular preferences when it comes to system and database administration.I can recommend PostgreSQL 9 High Availability Cookbook without hesitation to PostgreSQL DBAs who want to push their skill to the next level, and learn the fundamentals of building highly available PostgreSQL based database clusters. It certainly will not be as easy as reading a book, but it is good to know such a book exists as a very good guide.

Igor A. Polishchuk Aug 22, 2014

As it happens with fiction literature, I did not quiet like this book from the very beginning, but it became interesting after the first chapter. So, if you read it cover to cover, get some patience.In the beginning, the author admits that he does not cover cloud specific Postgres high availability methods. Well, it leaves an opportunity for somebody else to write a book dedicated to Postgres in a cloud. Also, the subject of high availability is huge and cannot be fully covered in a limited format of a cookbook. Anyway, the majority of the book's material is relevant in a cloud environment, too.The whole first chapter "Hardware Planning", perhaps, may have some value for a new to the subject users, but only to get basic ideas. Some recipes in this chapter are obvious, very basic, or oversimplified. Just one example, “Having enough IOPS” (p.11) is oversimplified in its relying on arbitrary assumptions. It is not clear why the author assumes that 3.5" hard drives produce 500 IOPS and 2.5” drives 350 IOPS (page 12). And don’t believe that you can get 500 IPOS from a 15K RPM drive. Even storage vendors usually claim no more than 180 IOPS. We are talking about random IOPS here, right? It does not make sense to plan a system for perfectly sequential IOs. I could continue complaining about the first chapter. Towards the end of it I was seriously thinking about putting the book away.My patience was fully rewarded in the consecutive chapters.As a cookbook should, it provides a bunch of handy queries. An example of a recipe with handy queries is “Identifying important tables” on page 53. There are also many very useful techniques like “Defusing cache poisoning” (how to avoid database slowness caused by empty caches after a crash), “Exploring the magic of virtual IPs” (how to switch to a standby server without using additional software), “Terminating rogue connections” (how to kill connection which does not want to die). These are just a few examples out of many.The author recommends and explains multiple handy Postgresql extensions and Linux tools throughout the book. dstat, iotop, and iostat are just a few out of really many. Hopefully, the readers already know how to use iostat or sar, but some other recommended tools are less known. Honestly, some tricks were new for me. In the end, I felt satisfied with the book. This book may teach new users many useful technics, tools, and queries. It also not just provides recipes, but in most cases provides good insights on how the things work. Experienced users may just use it as a source of readily available queries and commands and save time on producing their own. I would totally recommend this book to anybody who needs to maintain Postgresql databases.

PostgreSQL 9 High Availability Cookbook: Over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL.

What do you get with Print?

PostgreSQL 9 High Availability Cookbook

Chapter 2. Handling and Avoiding Downtime

Introduction

Determining acceptable losses

Configuration – getting it right the first time

Getting ready

Configuration – managing scary settings

Getting ready

Identifying important tables

Defusing cache poisoning

Introduction

Determining acceptable losses

Configuration – getting it right the first time

Getting ready

Configuration – managing scary settings

Getting ready

Identifying important tables

Defusing cache poisoning

Exploring the magic of virtual IPs

Page 1 of 14

Description

What you will learn

Product Details

What do you get with Print?

Product Details

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

Filter reviews by

People who bought this also bought

About the author

FAQs

PostgreSQL 9 High Availability Cookbook: Over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL.

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Description

What you will learn

Product Details

What do you get with Print?

Contact Details

Shipping Address

Billing Address

Product Details

Packt Subscriptions

Frequently bought together

Table of Contents

Recommendations for you

Customer reviews

Filter reviews by

People who bought this also bought

About the author

FAQs