PacktLib: PostgreSQL Replication

PostgreSQL Replication


About the Authors

About the Reviewers


Understanding Replication Concepts

The CAP theory and physical limitations

Different types of replication

Using sharding and data distribution


Understanding the PostgreSQL Transaction Log

How PostgreSQL writes data

The XLOG and replication

Understanding consistency and data loss

Tuning checkpoints and the XLOG

The internal structure of the XLOG


Understanding Point-In-Time-Recovery

Understanding the purpose of PITR

Archiving the transaction log

Taking base backups

Making use of traditional methods to create base backups

Tablespace issues

Keeping an eye on network bandwidth

Replaying the transaction log


Setting up Asynchronous Replication

Setting up streaming replication

Configuring a cascaded replication

Turning slaves to masters

Mixing streaming and file-based recovery

Making the streaming-only replication more robust

Efficient cleanup and the end of recovery

Conflict management

Dealing with the timelines


Setting up Synchronous Replication

Setting up synchronous replication

Understanding practical implications and performance

Redundancy and stopping replication


Monitoring Your Setup

Checking your archive

Checking pg_stat_replication

Checking for operating system processes

Dealing with monitoring tools


Understanding Linux High Availability

Understanding the purpose of high availability

Measuring availability

History of high-availability software

Terminology and concepts

High availability is all about redundancy

PostgreSQL and high availability


Working with pgbouncer

Understanding fundamental pgbouncer concepts

Installing pgbouncer

Configuring your first pgbouncer setup

Improving performance

Maintaining pgbouncer


Working with pgpool

Installing pgpool

Understanding pgpool features

Understanding the pgpool architecture

Setting up replication and load balancing

Checking replication

Running pgpool with streaming replication

Dealing with failovers and high availability


Configuring Slony

Installing Slony

Understanding how Slony works

Replicating your first database

Deploying DDLs

Adding tables to replication and managing problems

Performing failovers


Using Skytools

Installing skytools

Dissecting skytools

Managing pgq-queues

Using londiste to replicate data

One word about walmgr


Working with Postgres-XC

Understanding the Postgres-XC architecture

Installing Postgres-XC

Configuring a simple cluster

Optimizing for performance

Creating the tables and issuing the queries

Adding nodes

Handling failovers and dropping nodes


Scaling with PL/Proxy

Understanding the basic concepts

Setting up PL/Proxy

Extending and handling clusters in a clever way