Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
PostgreSQL Replication, Second Edition
PostgreSQL Replication, Second Edition

PostgreSQL Replication, Second Edition: Leverage the power of PostgreSQL replication to make your databases more robust, secure, scalable, and fast

Arrow left icon
Profile Icon Hans-Jürgen Schönig
Arrow right icon
$19.99 per month
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (4 Ratings)
Paperback Jul 2015 322 pages 1st Edition
eBook
$27.98 $39.99
Paperback
$48.99
Subscription
Free Trial
Renews at $19.99p/m
Arrow left icon
Profile Icon Hans-Jürgen Schönig
Arrow right icon
$19.99 per month
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (4 Ratings)
Paperback Jul 2015 322 pages 1st Edition
eBook
$27.98 $39.99
Paperback
$48.99
Subscription
Free Trial
Renews at $19.99p/m
eBook
$27.98 $39.99
Paperback
$48.99
Subscription
Free Trial
Renews at $19.99p/m

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

PostgreSQL Replication, Second Edition

Chapter 2. Understanding the PostgreSQL Transaction Log

In the previous chapter, we dealt with various replication concepts. That was meant to be more of a theoretical overview to sharpen your senses for what is to come, and was supposed to introduce the topic in general.

In this chapter, we will move closer to practical solutions, and you will learn how PostgreSQL works internally and what it means for replication. We will see what the so-called transaction log (XLOG) does and how it operates. The XLOG is the very backbone of the PostgreSQL onboard replication machinery. It is essential to understand how this part works in order to fully understand replication. You will learn the following topics in this chapter:

  • How PostgreSQL writes data
  • Which memory and storage parameters are involved
  • How writes can be optimized
  • How writes are replicated
  • How data consistency can be ensured

In this chapter, you will also learn about replication slots and logical decoding. Once you have completed reading...

How PostgreSQL writes data

PostgreSQL replication is all about writing data. Therefore, the way PostgreSQL writes a chunk of data internally is highly relevant and directly connected to replication and replication concepts. In this section, we will dig into writes.

The PostgreSQL disk layout

One of the first things we want to take a look at in this chapter is the PostgreSQL disk layout. Knowing about the disk layout can be very helpful when inspecting an existing setup, and it can be helpful when designing an efficient, high-performance installation.

In contrast to other database systems, such as Oracle, PostgreSQL will always rely on a filesystem to store data. PostgreSQL does not use raw devices. The idea behind this is that if a filesystem developer has done their job well, there is no need to reimplement the filesystem functionality over and over again.

Looking into the data directory

To understand the filesystem layout used by PostgreSQL, we can take a look at what we can find inside the...

The XLOG and replication

In this chapter, you have already learned that the transaction log of PostgreSQL has all the changes made to the database. The transaction log itself is packed into nice and easy-to-use 16 MB segments.

The idea of using this set of changes to replicate data is not farfetched. In fact, it is a logical step in the development of every relational (or maybe even a nonrelational) database system. For the rest of this book, you will see in many ways how the PostgreSQL transaction log can be used, fetched, stored, replicated, and analyzed.

In most replicated systems, the PostgreSQL transaction log is the backbone of the entire architecture (for synchronous as well as for asynchronous replication).

Understanding consistency and data loss

Digging into the PostgreSQL transaction log without thinking about consistency is impossible. In the first part of this chapter, we tried hard to explain the basic idea of the transaction log in general. You learned that it is hard, or even impossible, to keep data files in good shape without the ability to log changes beforehand.

So far, we have mostly talked about corruption. It is definitely not good to lose data files because of corrupted entries in them, but corruption is not the only issue you have to be concerned about. Two other important topics are:

  • Performance
  • Data loss

While these might be an obvious choice for important topics, we have the feeling that they are not well understood and honored. Therefore, they have been taken into consideration.

In our daily business as PostgreSQL consultants and trainers, we usually tend to see people who are only focused on performance.

Performance is everything. We want to be fast; tell us how to be fast&...

Tuning checkpoints and the XLOG

So far, this chapter has hopefully provided some insights into how PostgreSQL writes data and what the XLOG is used for in general. Given this knowledge, we can now move on and see what we can do to make our databases work even more efficiently, both in the case of replication and in the case of running just a single server.

Understanding the checkpoints

In this chapter, we have seen that data has to be written to the XLOG before it can go anywhere. The thing is that if the XLOG was never deleted, clearly, we would not write to it forever without filling up the disk at some point in time.

To solve this problem, the XLOG has to be deleted at some point. This process is called checkpointing.

The main question arising from this issue is, "When can the XLOG be truncated up to a certain point?" The answer is, "When PostgreSQL has put everything that is already in the XLOG into the storage files." If all the changes made to the XLOG are also made...

Experiencing the XLOG in action

We will use the transaction log throughout this book, and to give you a deeper insight into how things work on a technical level, we have added this section dealing exclusively with the internal workings of the XLOG machinery. We will avoid going down to the C level as this would be way beyond the scope of this book, but we will provide you with insights that are hopefully deep enough.

Understanding the XLOG records

Changes made to the XLOG are record-based. What does that mean? Let's assume you are adding a row to a table:

test=# INSERT INTO t_test VALUES (1, 'hans');
INSERT 0 1

In this example, we are inserting values into a table containing two columns. For the sake of this example, we want to assume that both columns are indexed.

Remember what you learned before: the purpose of the XLOG is to keep those data files safe. So this operation will trigger a series of XLOG entries. First, the data file (or files) related to the table will be written...

How PostgreSQL writes data


PostgreSQL replication is all about writing data. Therefore, the way PostgreSQL writes a chunk of data internally is highly relevant and directly connected to replication and replication concepts. In this section, we will dig into writes.

The PostgreSQL disk layout

One of the first things we want to take a look at in this chapter is the PostgreSQL disk layout. Knowing about the disk layout can be very helpful when inspecting an existing setup, and it can be helpful when designing an efficient, high-performance installation.

In contrast to other database systems, such as Oracle, PostgreSQL will always rely on a filesystem to store data. PostgreSQL does not use raw devices. The idea behind this is that if a filesystem developer has done their job well, there is no need to reimplement the filesystem functionality over and over again.

Looking into the data directory

To understand the filesystem layout used by PostgreSQL, we can take a look at what we can find inside the...

The XLOG and replication


In this chapter, you have already learned that the transaction log of PostgreSQL has all the changes made to the database. The transaction log itself is packed into nice and easy-to-use 16 MB segments.

The idea of using this set of changes to replicate data is not farfetched. In fact, it is a logical step in the development of every relational (or maybe even a nonrelational) database system. For the rest of this book, you will see in many ways how the PostgreSQL transaction log can be used, fetched, stored, replicated, and analyzed.

In most replicated systems, the PostgreSQL transaction log is the backbone of the entire architecture (for synchronous as well as for asynchronous replication).

Left arrow icon Right arrow icon

Description

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.

Who is this book for?

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.

What you will learn

  • Use Pointintime Recovery to perform data recovery as well as replication
  • Set up synchronous as well as asynchronous streaming replication
  • Get familiarized with the transaction log, the core component of most replication setups and its purpose
  • Improve speed and reliability with an understanding of pgpool and PgBouncer
  • Increase your data security and geographically distribute data
  • Make your systems more available and secure with Linux High Availability
  • Scale out with PL/Proxy and PostgresXC
  • Detect, investigate, and solve replicationrelated problems

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jul 28, 2015
Length: 322 pages
Edition : 1st
Language : English
ISBN-13 : 9781783550609
Category :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Jul 28, 2015
Length: 322 pages
Edition : 1st
Language : English
ISBN-13 : 9781783550609
Category :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just $5 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total $ 142.97
Troubleshooting PostgreSQL
$32.99
PostgreSQL Replication, Second Edition
$48.99
Learning PostgreSQL
$60.99
Total $ 142.97 Stars icon

Table of Contents

16 Chapters
1. Understanding the Concepts of Replication Chevron down icon Chevron up icon
2. Understanding the PostgreSQL Transaction Log Chevron down icon Chevron up icon
3. Understanding Point-in-time Recovery Chevron down icon Chevron up icon
4. Setting Up Asynchronous Replication Chevron down icon Chevron up icon
5. Setting Up Synchronous Replication Chevron down icon Chevron up icon
6. Monitoring Your Setup Chevron down icon Chevron up icon
7. Understanding Linux High Availability Chevron down icon Chevron up icon
8. Working with PgBouncer Chevron down icon Chevron up icon
9. Working with pgpool Chevron down icon Chevron up icon
10. Configuring Slony Chevron down icon Chevron up icon
11. Using SkyTools Chevron down icon Chevron up icon
12. Working with Postgres-XC Chevron down icon Chevron up icon
13. Scaling with PL/Proxy Chevron down icon Chevron up icon
14. Scaling with BDR Chevron down icon Chevron up icon
15. Working with Walbouncer Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5
(4 Ratings)
5 star 50%
4 star 50%
3 star 0%
2 star 0%
1 star 0%
Jeff L Sep 21, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
This book is a great introductory text to anyone needing to get started with data replication on PostgreSQL and is unsure about how to get started. It covers all of the major replication technologies currently available and describes some of the benefits and limitations of each. General optimization strategies to improve replication performance are also covered. Since the scope of coverage is pretty wide across all of the different replication technologies, this book doesn't quiet have enough space to devote more than single chapter to each, so you may still need to consult other manuals and reference materials once you've narrowed down your replication strategy.
Amazon Verified review Amazon
Lance Alligood Nov 12, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Normally when I am googling for assistance with PostgreSQL, it's all but guaranteed that at least one of the first 3 hits is for the postgresql.org. However, when it came to configuring master-slave replication, that wasn't the case. Most of the hits were instead for various blog posts or forums. Any details were frequently poorly formatted, glossed over--or omitted(!)--important steps, and never supplied adequate background information for the values assigned to parameters in postgresql.conf. To make matters worse, there was little mention of what version of PostgreSQL the instructions were for in those posts. My frustration level was off the charts as I tried to make sense of this poor & inconsistent "documentation"!I was in (desperate!) need of some accurate, explanatory guidance to get replication & point-in-time-recovery (PITR) up & going on my PostgreSQL 9.4 servers. Normally the PostgreSQL project is fantastic in supplying clear & precise documentation for commands & components of the database, but that just isn't the case at all when it came to making sense of replication and/or PITR. However, I must fully acknowledge that the PostgreSQL project has been exerting considerable effort into improving the ease of configuration & use when it comes to replication & PITR. (And it's only a matter of time the website documentation is there to go along with it.) Based on my searches, there have been massive changes in replication & PITR for (at least) 9.1, 9.2, & 9.3.PostgreSQL Replication Second Edition just came out in July 2015 so it is thankfully relevant to 9.4, although the changes from 9.3 -> 9.4 (command-wise at least) appear to be less dramatic than the previous 9.x releases. This was very much the documentation I was looking for! I have no issues with an author nor a publisher putting out a well-written book, but why PostgreSQL doesn't have webpages on its site comparable to this excellent tome is a real head-scratcher. That said, this book definitely lives up to the hype of showing you what it takes to get either/both replication or PITR running on PostgreSQL. I was particularly thankful for the explanation of the main files/directories that make up the database & all of the necessary parameters in postgresql.conf & pg_hba.conf!The project admits that there will certainly be more changes in the future--and I'm quite certain they'll all be for the better!--when it comes to replication & PITR, but for the hear & now in late 2015, this book will get you up to speed on configuring those with the current tools right now, quickly, & with little effort.
Amazon Verified review Amazon
Ángel Mar 28, 2018
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
Tiene base teórica, ejemplos prácticos, bastante completo en sus explicaciones, además de que referencia a documentos en internet (las URLs han cambiado desde la edición del libro y es el único fallo que le encuentro).
Amazon Verified review Amazon
Ajay Thakur Aug 12, 2015
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
This is an amazing book on Replication.Concepts are well explained and things are well described too.RegardsChitij Chauhan
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.