Book Image

PostgreSQL 12 High Availability Cookbook - Third Edition

By : Shaun Thomas

Book Image

PostgreSQL 12 High Availability Cookbook - Third Edition

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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Architectural Considerations

Architectural Considerations

Setting expectations with RPO

Defining timetables through RTO

Picking redundant copies

Selecting locations

Having enough backups

Considering quorum

Introducing indirection

Preventing split brain

Incorporating multi-master

Leveraging multi-master

Free Chapter

Hardware Planning

Hardware Planning

Planning for redundancy

Having enough IOPS

Investing in a RAID

Picking a processor

Allocating enough memory

Exploring nimble networking

Managing motherboards

Selecting a chassis

Saddling up to a SAN

Protecting your eggs

Minimizing Downtime

Minimizing Downtime

Determining acceptable losses

Configuration – getting it right the first time

Configuration – managing scary settings

Identifying important tables

Defusing cache poisoning

Terminating rogue connections

Reducing contention with concurrent indexes

Managing system migrations

Managing software upgrades

Mitigating the impact of hardware failure

Applying bonus kernel tweaks

Proxy and Pooling Resources

Proxy and Pooling Resources

Exploring the magic of virtual IPs

Obtaining and installing HAProxy

Configuring HAProxy to load balance PostgreSQL

Determining connection costs and limits

Installing PgBouncer

Configuring PgBouncer safely

Connecting to PgBouncer

Listing PgBouncer server connections

Listing PgBouncer client connections

Evaluating PgBouncer pool health

Changing PgBouncer connections while online

Enhancing PgBouncer authentication

Troubleshooting

Troubleshooting

Performing triage

Installing common statistics packages

Evaluating the current disk performance with iostat

Tracking I/O-heavy processes with iotop

Viewing past performance with sar

Correlating performance with dstat

Interpreting /proc/meminfo

Examining /proc/net/bonding/bond0

Checking the pg_stat_activity view

Checking the pg_stat_statements view

Deciphering database locks

Debugging with strace

Logging checkpoints properly

Monitoring

Figuring out what to monitor

Installing and configuring Nagios

Configuring Nagios to monitor a database host

Enhancing Nagios with Check_MK

Getting to know check_postgres

Installing and configuring Telegraf

Adding a custom PostgreSQL monitor to Telegraf

Installing and configuring InfluxDB

Installing and configuring Grafana

Building a graph in Grafana

Customizing a Grafana graph

Using InfluxDB tags in Grafana

PostgreSQL Replication

PostgreSQL Replication

Deciding what to copy

Securing the WAL stream

Setting up a hot standby

Upgrading to asynchronous replication

Bulletproofing with synchronous replication

Faking replication with pg_receivewal

Setting up Slony

Copying a few tables with Slony

Setting up Bucardo

Copying a few tables with Bucardo

Setting up pglogical

Copying a few tables with pglogical

Copying a few tables with native logical replication

Backup Management

Backup Management

Deciding when to use third-party tools

Installing and configuring Barman

Backing up a database with Barman

Restoring a database with Barman

Obtaining Barman diagnostics and information

Sending Barman backups to a remote location

Installing and configuring pgBackRest

Backing up a database with pgBackRest

Restoring a database with pgBackRest

Installing and configuring WAL-E

Managing WAL files with WAL-E

High Availability with repmgr

High Availability with repmgr

Preparing systems for repmgr

Installing and configuring repmgr

Cloning a database with repmgr

Incorporating a repmgr witness

Performing a managed failover

Customizing the failover process

Using an outage to test availability

Returning a node to the cluster

Integrating primary fencing

Performing online maintenance and upgrades

High Availability with Patroni

High Availability with Patroni

Understanding more about Patroni and its components

Preparing systems for the stack

Installing and configuring etcd

Installing and configuring Patroni

Installing and configuring HAProxy

Performing a managed switchover

Using an outage to test availability

Returning a node to the cluster

Adding additional nodes to the mix

Replacing etcd with ZooKeeper

Replacing etcd with Consul

Upgrading while staying online

Low-Level Server Mirroring

Low-Level Server Mirroring

Understanding our chosen filesystem components

Preparing systems for volume mirroring

Getting started with the LVM

Adding block-level replication

Incorporating the second LVM layer

Verifying a DRBD filesystem

Correcting a DRBD split brain

Formatting an XFS filesystem

Tweaking XFS performance

Maintaining an XFS filesystem

Using LVM snapshots

Switching live stack systems

Detaching a problematic node

High Availability via Pacemaker

High Availability via Pacemaker

Before we begin...

Installing the components

Configuring Corosync

Preparing start up services

Starting with base options

Adding DRBD to cluster management

Adding LVM to cluster management

Adding XFS to cluster management

Adding PostgreSQL to cluster management

Adding a virtual IP to proxy the cluster

Adding an email alert

Grouping associated resources

Combining and ordering related actions

Performing a managed resource migration

Using an outage to test migration

High Availability with Multi-Master Replication

High Availability with Multi-Master Replication

Overview of multi-master

Deciding whether multi-master is right for you

Obtaining and installing BDR

Starting with a single BDR node

Creating an additional BDR node

Testing DDL replication on each node

Using sequences safely

Configuring HAProxy for the multi-master approach

Combining PgBouncer with HAProxy

Performing a managed node switchover

Improving failover speed

Performing a major version upgrade online

Data Distribution

Data Distribution

Identifying horizontal candidates

Setting up a foreign PostgreSQL server

Mapping a remote user

Creating a foreign table

Using a foreign table in a query

Optimizing foreign table access

Transforming foreign tables into local tables

Creating a scalable nextval replacement

Building a sharding API

Talking to the correct shard

Moving a shard to another server

Zero-downtime Upgrades

Zero-downtime Upgrades

Preparing upgrade requirements

Remembering PgBouncer and pglogical

Creating a publication set

Handling sequences

Bootstrapping the target cluster

Starting the subscription

Monitoring progress

Switching targets

Cleaning everything up

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Other Books You May Enjoy

If you enjoyed this book, you may be interested in these other books by Packt:

PostgreSQL 11 Administration Cookbook
Simon Riggs, Gianni Ciolli, Et al

ISBN: 978-1-78953-758-1

Troubleshoot open source PostgreSQL version 11 on various platforms
Deploy best practices for planning and designing live databases
Select and implement robust backup and recovery techniques in PostgreSQL 11
Use pgAdmin or OmniDB to perform database administrator (DBA) tasks
Adopt efficient replication and high availability techniques in PostgreSQL
Improve the performance of your PostgreSQL solution

Mastering PostgreSQL 12 - Third Edition
Hans-Jürgen Schönig

ISBN: 978-1-83898-882-1

Understand the advanced SQL functions in PostgreSQL 12
Use indexing features in PostgreSQL to fine-tune the performance of queries
Work with stored procedures and manage backup and recovery...