6. Monitoring Your Setup | PostgreSQL Replication, Second Edition

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents

PostgreSQL Replication, Second Edition

By : Hans-Jürgen Schönig

4.5 (4)

PostgreSQL Replication, Second Edition

4.5 (4)

By: Hans-Jürgen Schönig

Overview of this book

This book is ideal for PostgreSQL administrators who want to set up and understand replication. By the end of the book, you will be able to make your databases more robust and secure by getting to grips with PostgreSQL replication.

Preface

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Free Chapter

1. Understanding the Concepts of Replication

1. Understanding the Concepts of Replication

The CAP theorem and physical limitations of replication

Different types of replication

Using sharding and data distribution

Summary

2. Understanding the PostgreSQL Transaction Log

2. Understanding the PostgreSQL Transaction Log

How PostgreSQL writes data

The XLOG and replication

Understanding consistency and data loss

Tuning checkpoints and the XLOG

Experiencing the XLOG in action

Making use of replication slots

Summary

3. Understanding Point-in-time Recovery

3. Understanding Point-in-time Recovery

Understanding the purpose of PITR

Archiving the transaction log

Taking base backups

Replaying the transaction log

Summary

4. Setting Up Asynchronous Replication

4. Setting Up Asynchronous Replication

Setting up streaming replication

Configuring a cascaded replication

Turning slaves into masters

Mixing streaming-based and file-based recovery

Making streaming-only replication more robust

Efficient cleanup and the end of recovery

Conflict management

Dealing with timelines

Delayed replicas

Summary

5. Setting Up Synchronous Replication

5. Setting Up Synchronous Replication

Synchronous replication setup

Understanding the practical implications and performance

Redundancy and stopping replication

Summary

6. Monitoring Your Setup

6. Monitoring Your Setup

Checking your archive

Checking pg_stat_replication

Checking for operating system processes

Checking for replication slots

Dealing with monitoring tools

Summary

7. Understanding Linux High Availability

7. Understanding Linux High Availability

Understanding the purpose of High Availability

Measuring availability

Durability and availability

Detecting failures

The split-brain syndrome

Understanding Linux-HA

Setting up a simple HA cluster

Common maintenance tasks

Summary

8. Working with PgBouncer

8. Working with PgBouncer

Understanding the fundamental PgBouncer concepts

Installing PgBouncer

Configuring your first PgBouncer setup

Improving performance

Maintaining PgBouncer

Summary

9. Working with pgpool

9. Working with pgpool

Installing pgpool

Understanding the features of pgpool

Understanding the pgpool architecture

Setting up replication and load balancing

Checking the replication

Running pgpool with streaming replication

Dealing with failovers and High Availability

Summary

10. Configuring Slony

10. Configuring Slony

Installing Slony

Understanding how Slony works

Replicating your first database

Deploying DDLs

Adding tables to replication and managing problems

Performing failovers

Summary

11. Using SkyTools

11. Using SkyTools

Installing SkyTools

Dissecting SkyTools

Managing pgq queues

Using Londiste to replicate data

A word about walmgr

Summary

12. Working with Postgres-XC

12. Working with Postgres-XC

Understanding the Postgres-XC architecture

Installing Postgres-XC

Configuring a simple cluster

Optimizing for performance

Creating tables and issuing queries

Adding nodes

Handling failovers and dropping nodes

Summary

13. Scaling with PL/Proxy

13. Scaling with PL/Proxy

Understanding the basic concepts

Setting up PL/Proxy

Extending and handling clusters in a clever way

Summary

14. Scaling with BDR

14. Scaling with BDR

Understanding BDR replication concepts

Installing BDR

Setting up a simple cluster

Understanding sets

Controlling replication

Summary

15. Working with Walbouncer

15. Working with Walbouncer

The concepts of walbouncer

Installing walbouncer

Using additional configuration options

Adjusting filtering rules

Summary

Index

Index

Checking your archive

If you are planning to use PITR or if you want to use an XLOG archive to assist in your streaming setup, various things can go wrong, for example:

Pushing of the XLOG might fail
Cleanup of the archive might fail

Of course, there are countless other things that can go wrong. However, in this chapter, our goal is to focus on the most common issues people face.

Checking archive_command

A failing archive_command variable might be one of the greatest showstoppers in your setup. The purpose of archive_command is to push XLOG to some archive and store the data there. But what happens if those XLOG files cannot be pushed for some reason?

The answer is quite simple: the master has to keep these XLOG files to ensure that no XLOG files can be lost. There must always be an uninterrupted sequence of XLOG files. Even if a single file in the sequence of files is missing, your slave won't be able to recover anymore. For example, if your network has failed, the master will accumulate those...

CONTINUE READING

83

Tech Concepts

36

Programming languages

73

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

PostgreSQL Replication, Second Edition

Search

Your notes and bookmarks