PostgreSQL high availability cookbook : master over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL / Shaun M. Thomas.

Format:

Book

Author/Creator:

Thomas, Shaun M., author.

Language:

English

Subjects (All):

PostgreSQL.

Database management.

SQL (Computer program language).

Physical Description:

1 online resource (530 pages)

Edition:

Second edition.

Other Title:

Postgre Structured Query Language high availability cookbook

Place of Publication:

Birmingham, [England] ; Mumbai, [India] : Packt Publishing, 2017.

System Details:

text file

Biography/History:

Thomas Shaun: Shaun Thomas has been experimenting with PostgreSQL since late 2000 and serves as a database consultant, teacher, blogger, and support engineer with 2ndQuadrant. He has presented at conferences such as Postgres Open, 2Q PGCONF, and PgConf on topics such as handling extreme throughput, high availability, failover techniques, monitoring tools, database architecture, multi-master conflict avoidance, and high availability upgrade concepts. He believes in a multi-disciplinary approach when it comes to high availability. He believes that PostgreSQL has a stupendous future ahead, and he can't wait to see the advancements subsequent versions will bring.

Summary:

Master over 100 recipes to design and implement a highly available server with the advanced features of PostgreSQL About This Book Create a PostgreSQL cluster that stays online even when disaster strikes Avoid costly downtime and data loss that can ruin your business Updated to include the newest features introduced in PostgreSQL 9.6 with hands-on industry-driven recipes Who This Book Is For 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, pglogical, and WAL-E Hardware planning to help your database run efficiently Prepare for catastrophes and prevent them before they happen Reduce database resource contention with connection pooling using pgpool and PgBouncer Automate monitoring and alerts to visualize cluster activity using Nagios and collected Construct a robust software stack that can detect and fix outages Learn simple PostgreSQL High Availability with Patroni, or dive into the full power of Pacemaker. In Detail 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 ...

Contents:

Cover

Copyright

Credits

About the Author

About the Reviewer

www.Packtpub.com

Customer Feedback

Table of Contents

Preface

Chapter 1: Hardware Planning

Introduction

Planning for redundancy

Getting ready

How to do it…

How it works…

There's more…

See also

Having enough IOPS

A working example

Making concessions

Sizing storage

Real-world example

Adjusting the numbers

Incorporating the spreadsheet

Investing in a RAID

Picking a processor

Hyperthreading

Turbo Boost

Power usage

Making the most of memory

Exploring nimble networking

A networking example

Remembering redundancy

Saving the research

Managing motherboards

Selecting a chassis

Saddling up to a SAN

Tallying up

Protecting your eggs

Chapter 2: Handling and Avoiding Downtime

Determining acceptable losses

Configuration - getting it right the first time

There's more….

See also

Configuration - managing scary settings

Distinct settings

More information

Identifying important tables

Reset stats

Using pgstattuple

Defusing cache poisoning

Exploring the magic of virtual IPs

Terminating rogue connections

Reducing contention with concurrent indexes

No transactions

One at a time

Danger with OLTP use

Managing system migrations

Managing software upgrades

Mitigating the impact of hardware failure

Copying WAL files more easily

Adding compression

Secondary delay

Applying bonus kernel tweaks

Chapter 3: Pooling Resources

Determining connection costs and limits

Installing PgBouncer

Configuring PgBouncer safely

What about pool_mode?

Problems with prepared statements

Connecting to PgBouncer

See also.

Listing PgBouncer server connections

Listing PgBouncer client connections

Evaluating PgBouncer pool health

Installing pgpool

Configuring pgpool for master/slave mode

Testing a write query on pgpool

Swapping active nodes with pgpool

Combining the power of PgBouncer and pgpool

Chapter 4: 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

How to do it….

How it works…

Checking the pg_stat_statements view

Resetting the stats

Catching more queries

Deciphering database locks

Debugging with strace

Logging checkpoints properly

Chapter 5: 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 collectd

Adding a custom PostgreSQL monitor to collectd

Installing and configuring Graphite

Adding collectd data to Graphite

Building a graph in Graphite

Customizing a Graphite graph

Creating a Graphite dashboard

Chapter 6: Replication

Deciding what to copy

Securing the WAL stream

Setting up a hot standby

Upgrading to asynchronous replication

Cascading replication

Using replication slots

Viewing replication status on a replica

Bulletproofing with synchronous replication

Being less strict

Being more strict

Enabling extreme durability

Faking replication with pg_receivexlog

Setting up Slony

Copying a few tables with Slony

Setting up Bucardo

Copying a few tables with Bucardo

Setting up Londiste

Copying a few tables with Londiste

Setting up pglogical

Copying a few tables with pglogical

Chapter 7: Replication Management Tools

Deciding when to use third-party tools

Installing and configuring Barman

How it works….

Notes:

Includes index.

Description based on online resource; title from PDF title page (ebrary, viewed March 3, 2017).

ISBN:

9781787125674

178712567X

OCLC:

974654502