Book Image

PostgreSQL Replication

By : Zoltan Böszörmenyi, Hans-Jürgen Schönig
Book Image

PostgreSQL Replication

By: Zoltan Böszörmenyi, Hans-Jürgen Schönig

Overview of this book

<p>PostgreSQL offers a comprehensive set of replication related features, which can be used to make your database servers more robust and way more scalable. Unleashing the power of PostgreSQL provides the user with countless opportunities and a competitive advantage over other database systems. To make things more powerful, PostgreSQL can be used in conjunction with a handful of sophisticated tools serving various different needs such as queuing, logical replication, or simplified transaction log handling.</p> <p>"PostgreSQL Replication" is a practical, hands-on guide to PostgreSQL replication. It will provide you with the theoretical background as well as simple examples showing you how to make replication work on your system. A broad toolchain will be presented along with mature PostgreSQL-core technology.</p> <p>"PostgreSQL Replication" starts with an introduction to replication concepts as well as the physical limitations of different replication solutions. You will be guided through various techniques such as Point-In-Time-Recovery, transaction-log-based replication and you will be introduced to a set of replication-related tools. In the final chapter you will learn to scale PostgreSQL to many different servers using PL/Proxy.</p> <p>You will learn how to reset PostgreSQL to a certain point in time and figure out how to replicate data in many ways. You will deal with both synchronous as well as asynchronous replication. In addition to that, the book covers important topics, such as Slony, and upgrades with virtually no downtime. We will also cover important performance-related topics to make sure your database setups will provide you with high speed AND high availability.</p> <p>"PostgreSQL Replication" contains all the information you need to design and operate replicated setups. You will learn everything you need to know for your daily work and a lot more.</p>

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL Replication
Zoltan Böszörmenyi | Hans-Jürgen Schönig
Aug, 2013 | 250 pages
No titles found
Table of Contents (20 chapters)
PostgreSQL Replication
PostgreSQL Replication
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
Understanding Replication Concepts
Understanding Replication Concepts
The CAP theory and physical limitations
Different types of replication
Using sharding and data distribution
Summary
2
Understanding the PostgreSQL Transaction Log
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
Summary
3
Understanding Point-In-Time-Recovery
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
Summary
4
Setting up Asynchronous Replication
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
Summary
5
Setting up Synchronous Replication
Setting up Synchronous Replication
Setting up synchronous replication
Understanding practical implications and performance
Redundancy and stopping replication
Summary
6
Monitoring Your Setup
Monitoring Your Setup
Checking your archive
Checking pg_stat_replication
Checking for operating system processes
Dealing with monitoring tools
Summary
7
Understanding Linux High Availability
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
Summary
8
Working with pgbouncer
Working with pgbouncer
Understanding fundamental pgbouncer concepts
Installing pgbouncer
Configuring your first pgbouncer setup
Improving performance
Maintaining pgbouncer
Summary
9
Working with pgpool
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
Summary
10
Configuring Slony
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
Using Skytools
Installing skytools
Dissecting skytools
Managing pgq-queues
Using londiste to replicate data
One word about walmgr
Summary
12
Working with Postgres-XC
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
Summary
13
Scaling with PL/Proxy
Scaling with PL/Proxy
Understanding the basic concepts
Setting up PL/Proxy
Extending and handling clusters in a clever way
Summary
Index
Index

Performing failovers

Once you have learned how to replicate tables and add them to sets, it is time to learn about failover. Basically we can distinguish between two types of failovers:

  • Planned failovers

  • Unplanned failovers and crashes

In this section we will learn about both scenarios.

Planned failovers

Having planned failovers is more of a luxury scenario. In many cases you will not be so lucky and you have to rely on automatic failover or face unplanned outages.

Basically a planned failover can be seen as moving a set of tables to some other node. Once that other node is in charge of those tables, you can handle things accordingly.

In our example we want to move all tables from node 1 to node 2. In addition to that we want to drop the first node. Here is the code:

slonik<<_EOF_
cluster name = first_cluster;

node 1 admin conninfo = 'dbname=$MASTERDB host=$HOST1 user=$DBUSER';
node 2 admin conninfo = 'dbname=$SLAVEDB host=$HOST2 user=$DBUSER';

lock set (id = 1, origin = 1);
move set (id...
Previous Section
End of Section 7
Next Section