12. Working with Postgres-XC | 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

Creating tables and issuing queries

After this introduction to Postgres-XC and its underlying ideas, it is time to create our first table and see how the cluster will behave. The next example shows a simple table. It will be distributed using the hash key of the id column:

test=# CREATE TABLE t_test (id int4)
DISTRIBUTE BY HASH (id);
CREATE TABLE
test=# INSERT INTO t_test
SELECT * FROM generate_series(1, 1000);
INSERT 0 1000

Once the table has been created, we can add data to it. After completion, we can check whether the data has been written correctly to the cluster:

test=# SELECT count(*) FROM t_test;
count
-------
  1000
(1 row)

Not surprisingly, we have 1,000 rows in our table.

The interesting thing here is to see how the data is returned by the database engine. Let's take a look at the execution plan of our query:

test=# explain (VERBOSE TRUE, ANALYZE TRUE,
NODES true, NUM_NODES true)
SELECT count(*) FROM t_test;
QUERY PLAN
-------------------------------------------------------
 Aggregate...

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