PostgreSQL Replication - Second Edition电子书

PostgreSQL Replication Second Edition

Table of Contents

PostgreSQL Replication Second Edition

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Errata

Piracy

Questions

1. Understanding the Concepts of Replication

The CAP theorem and physical limitations of replication

Understanding the CAP theorem

Understanding the limits of physics

Different types of replication

Synchronous versus asynchronous replication

Considering performance issues

Understanding replication and data loss

Single-master versus multimaster replication

Logical versus physical replication

When to use physical replication

When to use logical replication

Using sharding and data distribution

Understanding the purpose of sharding

Designing a sharded system

Querying different fields

Pros and cons of sharding

Choosing between sharding and redundancy

Increasing and decreasing the size of a cluster

Combining sharding and replication

Various sharding solutions

PostgreSQL-based sharding

Summary

2. Understanding the PostgreSQL Transaction Log

How PostgreSQL writes data

The PostgreSQL disk layout

Looking into the data directory

PG_VERSION – the PostgreSQL version number

base – the actual data directory

Growing data files

Performing I/O in chunks

Relation forks

global – the global data

Dealing with standalone data files

pg_clog – the commit log

pg_dynshmem – shared memory

pg_hba.conf – host-based network configuration

pg_ident.conf – ident authentication

pg_logical – logical decoding

pg_multixact – multitransaction status data

pg_notify – LISTEN/NOTIFY data

pg_replslot – replication slots

pg_serial – information about committed serializable transactions

pg_snapshot – exported snapshots

pg_stat – permanent statistics

pg_stat_tmp – temporary statistics data

pg_subtrans – subtransaction data

pg_tblspc – symbolic links to tablespaces

pg_twophase – information about prepared statements

pg_xlog – the PostgreSQL transaction log (WAL)

postgresql.conf – the central PostgreSQL configuration file

Writing one row of data

A simple INSERT statement

Crashing during WAL writing

Crashing after WAL writing

Read consistency

The purpose of the shared buffer

Mixed reads and writes

The format of the XLOG

The XLOG and replication

Understanding consistency and data loss

All the way to the disk

From memory to memory

From the memory to the disk

A word about batteries

Beyond the fsync function

PostgreSQL consistency levels

Tuning checkpoints and the XLOG

Understanding the checkpoints

Configuring the checkpoints

Segments and timeouts

To write or not to write?

Scenario 1 – storing stock market data

Scenario 2 – bulk loading

Scenario 3 – I/O spikes and throughput considerations

Conclusion

Tweaking WAL buffers

Experiencing the XLOG in action

Understanding the XLOG records

Making the XLOG deterministic

Making the XLOG reliable

LSNs and shared buffer interaction

Debugging the XLOG and putting it all together

Making use of replication slots

Physical replication slots

Logical replication slots

Configuring replication identities

Summary

3. Understanding Point-in-time Recovery

Understanding the purpose of PITR

Moving to the bigger picture

Archiving the transaction log

Taking base backups

Using pg_basebackup

Modifying pg_hba.conf

Signaling the master server

pg_basebackup – basic features

Backup throttling

pg_basebackup – self-sufficient backups

Making use of traditional methods to create base backups

Tablespace issues

Keeping an eye on the network bandwidth

Replaying the transaction log

Performing a basic recovery

More sophisticated positioning in the XLOG

Cleaning up the XLOG on the way

Switching the XLOG files

Summary

4. Setting Up Asynchronous Replication

Setting up streaming replication

Tweaking the config files on the master

Handling pg_basebackup and recovery.conf

Making the slave readable

The underlying protocol

Configuring a cascaded replication

Turning slaves into masters

Mixing streaming-based and file-based recovery

The master configuration

The slave configuration

Error scenarios

Network connection between the master and slave is dead

Rebooting the slave

Rebooting the master

Corrupted XLOG in the archive

Making streaming-only replication more robust

Using wal_keep_segments

Utilizing replication slots

Efficient cleanup and the end of recovery

Gaining control over the restart points

Tweaking the end of your recovery

Conflict management

Dealing with timelines

Delayed replicas

Handling crashes

Summary

5. Setting Up Synchronous Replication

Synchronous replication setup

Understanding the downside to synchronous replication

Understanding the application_name parameter

Making synchronous replication work

Checking the replication

Understanding performance issues

Setting synchronous_commit to on

Setting synchronous_commit to remote_write

Setting synchronous_commit to off

Setting synchronous_commit to local

Changing durability settings on the fly

Understanding the practical implications and performance

Redundancy and stopping replication

Summary

6. Monitoring Your Setup

Checking your archive

Checking archive_command

Monitoring the transaction log archive

Checking pg_stat_replication

Relevant fields in pg_stat_replication

Checking for operating system processes

Checking for replication slots

Dealing with monitoring tools

Installing check_postgres

Deciding on a monitoring strategy

Summary

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

Corosync

Pacemaker

Resource agents / fence agents

PCS

The PostgreSQL resource agent

Setting up a simple HA cluster

Preparing the servers

Installing the necessary software

Configuring the clustering software

Preparing for the PostgreSQL installation

Syncing the standby

Configuring the cluster

Configuring cluster resources

Configuring the constraints

Setting up fencing

Verifying the setup

Common maintenance tasks

Performing maintenance on a single node

Forcing a failover

Recovering from failed PostgreSQL starts

Performing cluster-wide maintenance

Resynchronizing after master failure

Summary

8. Working with PgBouncer

Understanding the fundamental PgBouncer concepts

Installing PgBouncer

Configuring your first PgBouncer setup

Writing a simple config file and starting PgBouncer up

Dispatching requests

More basic settings

Handling pool sizes

max_client_conn

default_pool_size

min_pool_size

reserve_pool_size

pool_size

Authentication

Connecting to PgBouncer

Java issues

Pool modes

Cleanup issues

Improving performance

A simple benchmark

Maintaining PgBouncer

Configuring the admin interface

Using the management database

Extracting runtime information

Suspending and resuming operations

Summary

9. Working with pgpool

Installing pgpool

Installing additional modules

Understanding the features of pgpool

Understanding the pgpool architecture

Setting up replication and load balancing

Password authentication

Firing up pgpool and testing the setup

Attaching hosts

Checking the replication

Running pgpool with streaming replication

Optimizing the pgpool configuration for master/slave mode

Dealing with failovers and High Availability

Using PostgreSQL streaming and Linux HA

pgpool mechanisms for High Availability and failover

Summary

10. Configuring Slony

Installing Slony

Understanding how Slony works

Dealing with logical replication

The slon daemon

Replicating your first database

Deploying DDLs

Adding tables to replication and managing problems

Performing failovers

Planned failovers

Unplanned failovers

Summary

11. Using SkyTools

Installing SkyTools

Dissecting SkyTools

Managing pgq queues

Running pgq

Creating queues and adding data

Adding consumers

Configuring the ticker

Consuming messages

Dropping queues

Using pgq for large projects

Using Londiste to replicate data

Replicating our first table

A word about walmgr

Summary

12. Working with Postgres-XC

Understanding the Postgres-XC architecture

Data nodes

GTM

Coordinators

GTM proxy

Installing Postgres-XC

Configuring a simple cluster

Creating the GTM

Optimizing for performance

Dispatching the tables

Optimizing joins

Optimizing for warehousing

Creating a GTM proxy

Creating tables and issuing queries

Adding nodes

Rebalancing data

Handling failovers and dropping nodes

Handling node failovers

Replacing the nodes

Running a GTM standby

Summary

13. Scaling with PL/Proxy

Understanding the basic concepts

Dealing with the bigger picture

Partitioning the data

Setting up PL/Proxy

A basic example

Partitioned reads and writes

Extending and handling clusters in a clever way

Adding and moving partitions

Increasing the availability

Managing foreign keys

Upgrading the PL/Proxy nodes

Summary

14. Scaling with BDR

Understanding BDR replication concepts

Understanding eventual consistency

Handling conflicts

Distributing sequences

Handling DDLs

Use cases for BDR

Good use cases for BDR

Bad use cases for BDR

Logical decoding does the trick

Installing BDR

Installing binary packages

Setting up a simple cluster

Arranging storage

Creating database instances

Loading modules and firing up the cluster

Checking your setup

Handling conflicts

Understanding sets

Unidirectional replication

Handling data tables

Controlling replication

Summary

15. Working with Walbouncer

The concepts of walbouncer

Filtering XLOG

Installing walbouncer

Configuring walbouncer

Creating a base backup

Firing up walbouncer

Using additional configuration options

Adjusting filtering rules

Removing and filtering objects

Adding objects to slaves

Summary

Index