12. Logical Replication

Beta Version: Work in progress.

PostgreSQL has supported native logical replication since version 10.

In streaming replication, the participating nodes are referred to as the Primary and the Standby. In contrast, logical replication uses the terms Publisher and Subscriber. See Figure 12.1.

Figure 12.1. Comparison of data flow and node roles in streaming and logical replication.

Logical replication differs from streaming replication in several key aspects. For instance, while streaming replication replicates an entire database cluster, logical replication allows for the replication of an individual database or specific tables.

Furthermore, as illustrated in Figure 12.1, logical replication provides greater flexibility regarding node operations. A standby node in streaming replication is typically restricted to read-only queries. In contrast, a subscriber can handle local write operations independently while simultaneously receiving replicated data from the publisher.

Table 12.1 summarizes the characteristics of logical replication through a detailed comparison with streaming replication:

Table 12.1: Logical Replication vs. Streaming Replication
Logical Replication Streaming Replication
WAL Sending Timing After transaction commit *1 Whenever WAL is generated
Sending Processes walsender walsender
Receiving Processes
  • Logical replication launcher
  • apply worker
  • walreceiver
  • startup
Replication Unit Table, Database Database cluster
Unsent Operations DDL (See Official Documentation for details.) N/A
Unsent Objects Non-Table objects, e.g., views, sequences, indexes, large objects, etc.
(See Official Documentation for details.) 		UNLOGGED tables
Supports Different Versions YES NO
Supports Different Operating Systems YES NO
DML on Subscriber/Standby INSERT, UPDATE, DELETE, SELECT, TRUNCATE*2 SELECT
Triggers on Subscriber/Standby Enabled Disabled

  • *1: Since the introduction of streaming (Version 14), logical changes for large transactions may be sent in segments prior to the commit; see Sections 12.1.2 and 12.7 for details.

  • *2: TRUNCATE has been supported since version 11.

This chapter provides a technical analysis of logical replication through the following sections:

  • Section 12.1: Presents a high-level overview, covering fundamental operations and core concepts of the logical replication framework.
  • Section 12.2: Details the internal startup process of logical replication.
  • Sections 12.3 - 12.4: Examine the architecture and functionality of the ReorderBuffer, a central component in managing logical replication.
  • Section 12.5: Explains pgoutput, the default pluggable plugin used to decode WAL data and populate the ReorderBuffer.
  • Section 12.6: Describes the mechanisms for constructing replication messages from the ReorderBuffer to send to the subscriber.
  • Section 12.7: Analyzes the internal operations and message-handling logic of the subscriber’s apply worker.
  • Section 12.8: Concludes the chapter with an explanation of subscriber restart sequences and crash recovery mechanisms.
Chapter Contents