8. Working with PgBouncer | PostgreSQL Replication, Second Edition

Sign In Start Free Trial

Book Overview & Buying
Table Of Contents
Feedback & Rating

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

Customer Reviews

4.5 (4)

5 star

50%

4 star

50%

3 star

0

2 star

0

1 star

0

Installing PgBouncer

Before we dig into the details, we will see how PgBouncer can be installed. Just as with PostgreSQL, you can take two routes. You can either install binary packages or simply compile from source. In our case, we will show you how a source installation can be performed:

The first thing you have to do is download PgBouncer from the official website at http://pgfoundry.org/projects/pgbouncer.
Once you have downloaded the .tar archive, you can safely unpack it using the following command:
```
tar xvfz pgbouncer-1.5.4.tar.gz
```
Once you are done with extracting the package, you can enter the newly created directory and run configure. Expect this to fail due to missing packages. In many cases, you have to install libevent (the development package) first before you can successfully run configure.
Tip
On Debian (or Debian-based distributions), the easiest way to install the libevent development package is to run apt-get install libevent-dev.
Once you have successfully executed configure...

Search

Your notes and bookmarks