Sign In Start Free Trial
Account

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL 12 High Availability Cookbook
  • Table Of Contents Toc
PostgreSQL 12 High Availability Cookbook

PostgreSQL 12 High Availability Cookbook - Third Edition

By : Shaun Thomas
4.5 (2)
close
close
PostgreSQL 12 High Availability Cookbook

PostgreSQL 12 High Availability Cookbook

4.5 (2)
By: Shaun Thomas

Overview of this book

Databases are nothing without the data they store. In the event of an outage or technical catastrophe, immediate recovery is essential. This updated edition ensures that you will learn the important concepts related to node architecture design, as well as techniques such as using repmgr for failover automation. From cluster layout and hardware selection to software stacks and horizontal scalability, this PostgreSQL cookbook will help you build a PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand. You’ll start by understanding how to plan a PostgreSQL database architecture that is resistant to outages and scalable, as it is the scaffolding on which everything rests. With the bedrock established, you'll cover the topics that PostgreSQL database administrators need to know to manage a highly available cluster. This includes configuration, troubleshooting, monitoring and alerting, backups through proxies, failover automation, and other considerations that are essential for a healthy PostgreSQL cluster. Later, you’ll learn to use multi-master replication to maximize server availability. Later chapters will guide you through managing major version upgrades without downtime. By the end of this book, you’ll have learned how to build an efficient and adaptive PostgreSQL 12 database cluster.
Table of Contents (17 chapters)
close
close

Selecting a chassis

To round out our hardware selection phase, it's time to decide just what kind of case to order from our server vendor. This is the final protective element that hosts the motherboard, drives, and power supplies necessary to keep everything running. And like always, we place a heavy emphasis on redundancy.

For the purposes of this section, we will concentrate primarily on 1U and 2U rack-mounted servers. Why not 4U or larger? Our goal is to obtain at least two of everything, with similar or matching specifications in every possible scenario. The idea is to scale horizontally in order to more easily replace a failed component or server. As the size of the chassis increases, its cost, complexity, and resource consumption also rise. In this delicate balancing act, it's safer to err toward two smaller systems with respectable capabilities, than one giant server that's twice as powerful.

This recipe will provide a quick set of calculations we can perform to estimate how many server chassis we may need for a PostgreSQL cluster.

Getting ready

Since the server chassis and motherboard are generally a package deal, it's a good idea to refer to the Managing motherboards recipe. We will be using a very similar process to choose a server case. This time, we will focus on adequate room for hard drives and redundant power supplies.

How to do it...

Now it's time to do some more research. Follow these steps:

  1. Refer to the final list of servers from our motherboard selection.
  2. For our ideal count of active (not replacement) hard drives, remove any choice that doesn't have enough drive slots. Use this list if it's not immediately obvious:
  • The maximum number of 2.5" drives in a 2U server is 24.
  • The maximum number of 3.5" drives in a 2U server is 12.
  • The maximum number of 2.5" drives in a 1U server is 8.
  • The maximum number of 3.5" drives in a 1U server is 4.
  1. Remove from consideration any chassis that does not support dual power supplies. This should happen rarely in server-class systems.
  2. As the list dwindles, give higher priority to cases with more fans or lower average operating temperatures.

How it works...

This time, our job was much easier than considering motherboard constraints, as the drives themselves determine most of our decisions.

Hot-swappable hard drives are slightly larger than their standard brethren due to the swap enclosure. Yet cases exist than can hold up to 24 hot-swappable drives across the front. If we need that many storage devices, we save space by taking advantage of cases that can accommodate them. We also need to remember to reserve two drives for the operating system in a RAID-1, separate from our PostgreSQL storage. We can't diagnose problems on a server that is unable to boot.

Some cases reserve mounts inside, or at the rear, for operating system drives. They are harder to replace, but free more room for storage dedicated to PostgreSQL. Here, operating system drives are treated as an operating overhead without sacrificing case functionality.

If we need more drives than are available in any configuration, we should consider Direct Attached Storage (DAS), Network Attached Storage (NAS), or Storage Area Network (SAN). Some vendors supply drive extension cages specifically to provide more hot-swap bays for specific server models. While we want to conserve space when possible, these are relatively inexpensive and much smaller than a NAS or SAN if we haven't progressed to requiring such a device.

The dual power-supply requirement is not negotiable. Many data centers provide two power rails per server rack. The intent is to deliver two separate sources of power to the server in case the server's power supply fails, or power is cut to one of the sources. Sometimes, these power sources even have separate generators. We're not the only ones interested in redundancy; data centers want to avoid outages too.

The final, more optional element involves investigating the case itself. Many server cases have several fans inside and along the rear, and are very loud as a consequence. This won't matter when the server is in the data center, but the number of fans and the shape of the airflow will directly affect the server temperature. Higher temperatures decrease system stability. It's not uncommon for vendors to list the maximum operating temperatures of each case, so try to gravitate toward the cooler ones if all else is equal.

There's more...

We use the word vendor frequently, and there's a reason for that. Short of outright accusing bare cases and motherboards of being faulty, they are simply not stable enough for our use. There are some great cases available that, in many ways, exceed the capabilities provided by established server providers.

We don't suggest the smaller vendors for a few reasons. Larger companies often have replacement policies for each server component, including the case and motherboard. Building a system ourselves may provide more satisfaction, but vendors presumably spend time testing for compatibility and failure conditions. They produce manuals hundreds of pages long detailing the viable parts, configurations, and failure conditions of the entire unit.

However, we could just as easily argue that redundant servers increase failure tolerance, as there's always an available backup. Bare cases and motherboards are usually cheaper, and user-serviceable besides. That is a completely valid path, and if your risk assessment suggests it's viable, try it out. Our particular recommendations and biases should not limit your choices.

CONTINUE READING
83
Tech Concepts
36
Programming languages
73
Tech Tools
Icon Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.
Icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Icon 50+ new titles added per month and exclusive early access to books as they are being written.
PostgreSQL 12 High Availability Cookbook
notes
bookmark Notes and Bookmarks search Search in title playlist Add to playlist download Download options font-size Font size

Change the font size

margin-width Margin width

Change margin width

day-mode Day/Sepia/Night Modes

Change background colour

Close icon Search
Country selected

Close icon Your notes and bookmarks

Confirmation

Modal Close icon
claim successful

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY

Submit Your Feedback

Modal Close icon
Modal Close icon
Modal Close icon