Streaming Replication in PostgreSQL: A Masterpiece of Data Synchronization

Introduction:

PostgreSQL's streaming replication is a native feature that enables seamless data synchronization between the primary and standby servers. In this four-part blog series, we'll explore the intricacies of streaming replication, including its startup process, communication flow, and management of multiple standby servers.

TL;DR:

Streaming replication in PostgreSQL allows for continuous data synchronization between the primary and standby servers. It involves processes like walsender, walreceiver, and startup, which work together to establish a connection, exchange data, and close the gap between the servers. This feature ensures reliable and efficient replication of data in a PostgreSQL cluster.

📣 Streaming Replication in PostgreSQL: A Masterpiece of Data Synchronization 🚀

Welcome, tech enthusiasts! Today, we embark on an exciting journey into the depths of PostgreSQL's streaming replication. 🌟 In this four-part blog series, we'll uncover the secrets behind this native replication feature and explore its inner workings. So, fasten your seatbelts and get ready for a data adventure! 🎢

Part 1: How Streaming Replication Starts Up 🔄

Let's dive right in and discover the magic of streaming replication startup. 🌊 Three key processes collaborate in perfect harmony to achieve this feat: the walsender, walreceiver, and startup processes. Their interactions resemble a well-choreographed dance routine. 💃

Picture this: 🖼️

🔸 Step 1: Starting the Show
First, both the primary server (the master) and the standby server(s) (the slaves) take their places on the stage. 🎭

🔸 Step 2: Lights, Camera, Action!
The standby server initiates the startup process, marking the beginning of our spectacle. 🎬

🔸 Step 3: Enter the Walreceiver
Next, the standby server summons the walreceiver process, which eagerly awaits its cue. 🎥

🔸 Step 4: Reaching Out
The walreceiver extends its virtual hand toward the primary server, sending a connection request via a TCP connection. If the primary server is unavailable, the walreceiver will keep trying until it succeeds. 🤝

🔸 Step 5: A Connection Is Born
As soon as the primary server receives the connection request, it swiftly dispatches a walsender process to establish a TCP connection with the walreceiver. A bond is formed between them. 💞

🔸 Step 6: Handshaking
In this phase, the walreceiver shares the latest Log Sequence Number (LSN) of the standby's database cluster with the primary server. It's like a secret handshake in the realm of information technology. 🤝✨

🔸 Step 7: Catching Up
If the LSN of the standby server is behind the primary's LSN, the walsender springs into action! It diligently sends Write-Ahead Log (WAL) data from the previous LSN to the current LSN. These precious data reside in the primary's pg_xlog (or pg_wal in version 10+) subdirectory. The standby eagerly replays the received WAL data, gradually closing the gap between itself and the primary server. 🚀

🔸 Step 8: Let the Replication Begin
At last, the stage is set, and the performance begins. Streaming Replication comes to life, with data flowing seamlessly from the primary server to the standby servers. 🌟

That's not all! Each walsender process has its own state, depending on the current phase it's involved in. Let's take a sneak peek at the possible states: 📊

1️⃣ Start-up: From the initiation of the walsender to the completion of handshaking. It's like the opening act of our replication drama. 🎭

2️⃣ Catch-up: During this phase, the standby server races to catch up with the primary by replaying the received WAL data. The plot thickens! 🏃

3️⃣ Streaming: Ah, the heart of our story! Here, Streaming Replication is in full swing, harmoniously synchronizing data between the primary and standby servers. It's the climax of our replication tale! 🎉

4️⃣ Backup: This state is relevant during the transmission of database cluster files for backup tools like the pg_basebackup utility. It ensures the integrity of the backup process.
 

📣 Streaming Replication: Unveiling the Symphony of Communication 🎵

Welcome back, dear readers! In this second part of our series on PostgreSQL's streaming replication, we'll explore the intricate dance between the primary server and synchronous standby server. 🌟 Get ready for a journey into the realm of log shipping and database synchronization! 🚀

2️⃣ How Communication Takes Place

Let's imagine a scenario where the standby server is in synchronous replication mode, while the hot-standby configuration parameter is disabled, and the wal_level is set to 'archive'. In this setup, we'll focus on transaction commits as our main act. 🎭

Now, picture this sequence diagram to understand the communication process: 🖼️

🔸 Step 1: Writing and Sending WAL Data
The backend process on the primary server takes the stage and diligently writes and flushes the Write-Ahead Log (WAL) data into a WAL segment file using functions like XLogInsert() and XLogFlush(). 📝

🔸 Step 2: The Journey Begins
The walsender process springs into action and sends the written WAL data to the eagerly waiting walreceiver process on the standby server. It's like a baton pass in a relay race! 🏃‍♀️

🔸 Step 3: The Wait
After dispatching the WAL data, the backend process patiently waits for an acknowledgment (ACK) response from the standby server. It enters a state of synchronization, eagerly anticipating the next move. ⌛

🔸 Step 4: Receiving and Responding
The walreceiver on the standby server receives the sent WAL data and writes it into the standby's own WAL segment using the write() system call. It then promptly sends an ACK response back to the walsender. It's a graceful exchange between the two! 🤝

🔸 Step 5: Flushing and Updating
The walreceiver takes an additional step by flushing the received WAL data to the WAL segment, ensuring its durability using a system call like fsync(). It sends another ACK response to the walsender and informs the startup process about the updated WAL data. It's like completing a vital task and signaling success! 💪

🔸 Step 6: Replaying the Drama
The startup process on the standby server takes its cue and replays the written WAL data from the WAL segment. It brings the replication storyline to life, mirroring the actions of the primary server. 📜

🔸 Step 7: Release and Completion
The walsender, upon receiving the ACK response from the walreceiver, releases the latch held by the backend process. This signifies the completion of the commit or abort action initiated by the backend process. The timing of the latch release depends on the synchronous_commit parameter. If set to 'on' (default), the latch is released upon receiving the ACK in Step 5. If set to 'remote_write', the latch is released upon receiving the ACK in Step 4. It's the grand finale of our synchronization symphony! 🎉

It's worth noting that if the wal_level configuration parameter is set to 'hot_standby' or 'logical', PostgreSQL writes a WAL record concerning the hot standby feature after the records of a commit or abort action. However, in our example, this record is not written as the wal_level is set to 'archive'.

Each ACK response carries valuable information about the standby server's internal status. It includes:

🔹 Location where the latest WAL data was written
🔹 Location where the latest WAL data was flushed
🔹 Location where the latest WAL data was replayed in the startup process
🔹 Timestamp when the response was sent

These ACK responses not only provide updates on the progress of writing and flushing WAL data but also serve as periodic heartbeats to ensure the primary server stays informed about the status of all connected standby servers. 💌

By executing the following query, you can get a glimpse of the LSN (Log Sequence Number) related information for the connected standby servers:

testdb=# SELECT application_name AS host,
write_location AS write_LSN, flush_location AS flush_LSN,
replay_location AS replay_LSN FROM pg_stat_replication;

host	write_lsn	flush_lsn	replay_lsn
standby1	0/5000280	0/5000280	0/5000280
standby2	0/5000280	0/5000280	0/5000280

(2 rows)

This query provides a snapshot of the LSN-related information for each connected standby server. It's like peering into the heartbeat of the replication process! 💓

Remember, the heartbeat's interval is set by the wal_receiver_status_interval parameter, which defaults to 10 seconds.

2️⃣ Behavior When a Failure Occurs

Now, let's dive into what happens when a failure occurs in the synchronous standby server and how the primary server copes with such a situation. 🛡️

If, for any reason, the synchronous standby server fails and cannot respond with an ACK, the primary server continues to wait indefinitely for a response. This can lead to a halt in operations, as running transactions cannot commit, and subsequent queries cannot proceed. It's like pressing pause on the primary server's activities. 🛑

However, there are two ways to avoid this predicament. One approach is to use multiple standby servers to increase system availability. The other option is to manually switch from synchronous to asynchronous mode by following these steps:

(1) Set the synchronous_standby_names parameter to an empty string:
synchronous_standby_names = ''

(2) Reload the configuration using the pg_ctl command:
postgres> pg_ctl -D $PGDATA reload

This procedure doesn't impact connected clients, as the primary server continues transaction processing, and client sessions with the respective backend processes remain intact. It's like smoothly transitioning between different modes of operation. ♻️

 

🔌 Managing Multiple-Standby Servers: Ensuring Replication Efficiency 🔄

In the world of database management, replication is a crucial aspect for ensuring data availability and reliability. PostgreSQL, a popular open-source database system, provides powerful features for streaming replication. In this blog post, we will explore how PostgreSQL manages multiple standby servers in a replication setup, and how it ensures consistency and synchronization. Let's dive in! 💻

sync_priority and sync_state 🎛️

To understand how multiple standby servers are managed, we need to familiarize ourselves with two important concepts: sync_priority and sync_state. The primary server assigns these values to each managed standby server, regardless of whether there is one or multiple standbys. Let's break them down:

🔸 sync_priority: This indicates the priority of a standby server in synchronous mode. It is a fixed value, where a smaller value represents a higher priority. The special value of 0 signifies the "asynchronous mode." The priorities of standby servers are defined in the primary server's configuration parameter called synchronous_standby_names. For instance, if the configuration is 'standby1, standby2', standby1 has a priority of 1, and standby2 has a priority of 2. Standby servers not listed in this parameter operate in asynchronous mode with a priority of 0.

🔸 sync_state: This represents the state of a standby server, which varies based on its running status and individual priority. Here are the possible states:

• Sync: This state denotes the synchronous-standby server with the highest priority among all working standbys (excluding asynchronous servers). It ensures synchronization with the primary server.

• Potential: When a synchronous-standby server fails, it enters the potential state. It refers to spare synchronous-standby servers with second or lower priority. If the current synchronous standby fails, it gets replaced with the highest priority standby among the potential ones.

• Async: This state is assigned to asynchronous-standby servers, which operate in asynchronous mode. Although similar to potential standbys, async standbys will never have a sync_state of 'sync' or 'potential'. The primary server treats them as potential standbys, except for this distinction.

To view the priority and state of standby servers, you can use the following query:

SELECT application_name AS host, sync_priority, sync_state FROM pg_stat_replication;

The query will provide a clear overview of the priority and state of each standby server.

💡 Several developers are currently working on implementing 'multiple synchronous-standby.' Check here for more details.

How the Primary Manages Multiple Standbys 🤝

In a replication setup with multiple standbys, the primary server relies solely on ACK (acknowledgment) responses from the synchronous standby server. This means that the primary server confirms the writing and flushing of Write-Ahead Log (WAL) data only from the synchronous standby. As a result, streaming replication guarantees a consistent and synchronous state between the primary and the synchronous standby.

Let's look at Figure 11.3, which illustrates a scenario where the ACK response from a potential standby arrives earlier than that of the primary standby. In this case:

(1) Despite receiving the ACK response from the potential standby server, the primary's backend process continues to wait for an ACK response from the synchronous standby server.
(2) Once the primary's response is received, the backend process releases the latch and completes the current transaction processing.

On the other hand, if the primary's ACK response arrives earlier than the potential standby's, the primary server immediately completes the commit action of the current transaction without waiting

for the potential standby's WAL data to be written and flushed.

The primary server's management of multiple standbys ensures that transactions are handled efficiently and guarantees data integrity.

Behavior When a Failure Occurs 🚨

Now, let's explore how the primary server handles failures of different types of standby servers.

🔸 Potential or Asynchronous Standby Failure: If either a potential or an asynchronous standby server fails, the primary server terminates the walsender process connected to the failed standby and continues with its normal processing. In other words, the failure of these standbys does not impact the primary server's transaction processing.

🔸 Synchronous Standby Failure: When a synchronous standby server fails, the primary server terminates the walsender process connected to the failed standby and replaces it with the highest priority potential standby. Refer to Figure 11.4 for a visual representation of this process. Unlike the previous scenario, the query processing on the primary server pauses from the point of failure until the replacement of the synchronous standby. Hence, detecting failures in standby servers is crucial to enhance the availability of the replication system. We will delve deeper into failure detection in the next section.

In any case, if one or more standby servers are running in synchronous mode, the primary server always maintains a single synchronous standby server. This ensures that the synchronous standby remains consistent and synchronized with the primary server at all times.

💪 PostgreSQL's management of multiple standby servers allows for robust replication setups with high availability and data integrity. By leveraging sync_priority, sync_state, and efficient ACK handling, PostgreSQL ensures consistent and reliable replication between the primary and standby servers.

That concludes our exploration of managing multiple standby servers in PostgreSQL's streaming replication.
 
🚨 Detecting Failures of Standby Servers: Ensuring Replication Reliability 🔄

In our previous blog post, we explored how PostgreSQL manages multiple standby servers in a replication setup. Now, let's dive into an essential aspect of replication: detecting failures of standby servers. By promptly identifying failures, PostgreSQL ensures the stability and integrity of the replication system. Let's explore the mechanisms in place! 💻

Failure Detection of Standby Server Process 🔍

PostgreSQL employs two common failure detection procedures, which do not require any special hardware. These procedures effectively identify issues with the standby server process. Here's how it works:

🔸 Connection Drop: When a disconnection between the walsender and walreceiver is detected, the primary server immediately determines that the standby server or walreceiver process is faulty. If a low-level network function encounters an error while writing or reading from the socket interface of walreceiver, the primary server swiftly recognizes the failure.

Failure Detection of Hardware and Networks ⚙️

In addition to process-level failure detection, PostgreSQL also employs mechanisms to detect failures related to hardware and networks. The primary server relies on the following procedure:

🔸 Time-Based Detection: If a walreceiver fails to return any response within the time defined by the parameter wal_sender_timeout (default: 60 seconds), the primary server considers the standby server as faulty. Unlike the immediate detection in the previous case, it takes a certain amount of time (up to wal_sender_timeout seconds) to confirm the standby's failure on the primary server. This delay allows for situations where the standby server experiences failures like hardware issues or network disruptions.

Depending on the type of failure, detection can occur immediately after the failure or may involve a time lag between the occurrence and detection. Notably, if such failures occur in a synchronous standby server, all transaction processing on the primary server will be paused until the failure is detected. This ensures the highest level of data consistency, even if multiple potential standby servers are operational.

💡 By employing these failure detection procedures, PostgreSQL enables prompt identification of issues in standby servers, ensuring the reliability and availability of the replication system.

Conclusion:

PostgreSQL's streaming replication is a robust and essential feature for ensuring data availability and redundancy in a distributed database environment. Through a well-orchestrated startup process, the primary and standby servers establish a connection and synchronize data using Write-Ahead Log (WAL) records. This replication mechanism enables high availability, fault tolerance, and scalability in PostgreSQL deployments. In the upcoming parts of this blog series, we'll delve deeper into the communication flow, failover scenarios, and advanced configurations of streaming replication, unlocking the full potential of this masterpiece of data synchronization. Stay tuned for more insights into PostgreSQL's streaming replication!