Browse Library
Sign In Start Free Trial
Close icon Search icon
Account
Browse Library Advanced Search Sign In Start Free Trial

Add to playlist

Create a Playlist

Modal Close icon
You need to login to use this feature.
  • Book Overview & Buying PostgreSQL Replication, Second Edition
  • Table Of Contents Toc
  • Feedback & Rating feedback
PostgreSQL Replication, Second Edition

PostgreSQL Replication, Second Edition

By : Hans-Jürgen Schönig
4.5 (4)
Buy this Book
close
close
PostgreSQL Replication, Second Edition

PostgreSQL Replication, Second Edition

4.5 (4)
By: Hans-Jürgen Schönig
Buy this Book

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.
Table of Contents (17 chapters)
close
close
close
Preface
Icon
Preface
Icon
What this book covers
Icon
What you need for this book
Icon
Who this book is for
Icon
Conventions
Icon
Reader feedback
Icon
Customer support
Free Chapter
1
1. Understanding the Concepts of Replication
Icon
1. Understanding the Concepts of Replication
Icon
The CAP theorem and physical limitations of replication
Icon
Different types of replication
Icon
Using sharding and data distribution
Icon
Summary
2
2. Understanding the PostgreSQL Transaction Log
Icon
2. Understanding the PostgreSQL Transaction Log
Icon
How PostgreSQL writes data
Icon
The XLOG and replication
Icon
Understanding consistency and data loss
Icon
Tuning checkpoints and the XLOG
Icon
Experiencing the XLOG in action
Icon
Making use of replication slots
Icon
Summary
3
3. Understanding Point-in-time Recovery
Icon
3. Understanding Point-in-time Recovery
Icon
Understanding the purpose of PITR
Icon
Archiving the transaction log
Icon
Taking base backups
Icon
Replaying the transaction log
Icon
Summary
4
4. Setting Up Asynchronous Replication
Icon
4. Setting Up Asynchronous Replication
Icon
Setting up streaming replication
Icon
Configuring a cascaded replication
Icon
Turning slaves into masters
Icon
Mixing streaming-based and file-based recovery
Icon
Making streaming-only replication more robust
Icon
Efficient cleanup and the end of recovery
Icon
Conflict management
Icon
Dealing with timelines
Icon
Delayed replicas
Icon
Summary
5
5. Setting Up Synchronous Replication
Icon
5. Setting Up Synchronous Replication
Icon
Synchronous replication setup
Icon
Understanding the practical implications and performance
Icon
Redundancy and stopping replication
Icon
Summary
6
6. Monitoring Your Setup
Icon
6. Monitoring Your Setup
Icon
Checking your archive
Icon
Checking pg_stat_replication
Icon
Checking for operating system processes
Icon
Checking for replication slots
Icon
Dealing with monitoring tools
Icon
Summary
7
7. Understanding Linux High Availability
Icon
7. Understanding Linux High Availability
Icon
Understanding the purpose of High Availability
Icon
Measuring availability
Icon
Durability and availability
Icon
Detecting failures
Icon
The split-brain syndrome
Icon
Understanding Linux-HA
Icon
Setting up a simple HA cluster
Icon
Common maintenance tasks
Icon
Summary
8
8. Working with PgBouncer
Icon
8. Working with PgBouncer
Icon
Understanding the fundamental PgBouncer concepts
Icon
Installing PgBouncer
Icon
Configuring your first PgBouncer setup
Icon
Improving performance
Icon
Maintaining PgBouncer
Icon
Summary
9
9. Working with pgpool
Icon
9. Working with pgpool
Icon
Installing pgpool
Icon
Understanding the features of pgpool
Icon
Understanding the pgpool architecture
Icon
Setting up replication and load balancing
Icon
Checking the replication
Icon
Running pgpool with streaming replication
Icon
Dealing with failovers and High Availability
Icon
Summary
10
10. Configuring Slony
Icon
10. Configuring Slony
Icon
Installing Slony
Icon
Understanding how Slony works
Icon
Replicating your first database
Icon
Deploying DDLs
Icon
Adding tables to replication and managing problems
Icon
Performing failovers
Icon
Summary
11
11. Using SkyTools
Icon
11. Using SkyTools
Icon
Installing SkyTools
Icon
Dissecting SkyTools
Icon
Managing pgq queues
Icon
Using Londiste to replicate data
Icon
A word about walmgr
Icon
Summary
12
12. Working with Postgres-XC
chevron up
Icon
12. Working with Postgres-XC
Icon
Understanding the Postgres-XC architecture
Icon
Installing Postgres-XC
Icon
Configuring a simple cluster
Icon
Optimizing for performance
Icon
Creating tables and issuing queries
Icon
Adding nodes
Icon
Handling failovers and dropping nodes
Icon
Summary
13
13. Scaling with PL/Proxy
Icon
13. Scaling with PL/Proxy
Icon
Understanding the basic concepts
Icon
Setting up PL/Proxy
Icon
Extending and handling clusters in a clever way
Icon
Summary
14
14. Scaling with BDR
Icon
14. Scaling with BDR
Icon
Understanding BDR replication concepts
Icon
Installing BDR
Icon
Setting up a simple cluster
Icon
Understanding sets
Icon
Controlling replication
Icon
Summary
15
15. Working with Walbouncer
Icon
15. Working with Walbouncer
Icon
The concepts of walbouncer
Icon
Installing walbouncer
Icon
Using additional configuration options
Icon
Adjusting filtering rules
Icon
Summary
16
Index
Icon
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...

Unlock full access

Continue reading for free

A Packt free trial gives you instant online access to our library of over 7000 practical eBooks and videos, constantly updated with the latest in tech
Start a 7-day FREE trial
End of Section 1
Next Section

Create a Note

Modal Close icon
You need to login to use this feature.
notes
bookmark search playlist font-size

Change the font size

margin-width

Change margin width

day-mode

Change background colour

Close icon Search
Country selected
Close icon Your notes and bookmarks

Delete Bookmark

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Delete Note

Modal Close icon
Are you sure you want to delete it?
Cancel
Yes, Delete

Edit Note

Modal Close icon
Write a note (max 255 characters)
Cancel
Update Note

Confirmation

Modal Close icon
claim successful
Order Page Read Now

Buy this book with your credits?

Modal Close icon
Are you sure you want to buy this book with one of your credits?
Close
YES, BUY