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 for operating system processes

Once we have checked the archives and our system views, we are ready to check for system processes. Checking for system processes might look a little crude, but it has been proven to be highly effective.

On the master, we can simply check for a process called wal_sender. On the slave, we have to check for a process called wal_receiver.

Let's check out what we are supposed to see on the master first. The following command does the job:

ps ax | grep "wal sender"

Here is the output:

9314   ??  Ss     0:00.00 postgres: wal sender process
hs ::1(61498) idle

On Linux, we can see that the process carries not only its purpose (in this case, wal_sender) but also the name of the end user and network-related information. In our case, we can see that somebody has connected from localhost through port 61498.

The situation on the slave is pretty simple as well:

9313   ??  Ss     0:00.00 postgres: wal receiver process

All we see is a process informing us that we are consuming...

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