Скачать или смотреть Can Logical Subscriptions be Replicated through Streaming in PostgreSQL?

Discover the mechanics of logical subscriptions in `PostgreSQL` and how they interact with streaming replication. Understand when to establish direct subscriptions and the implications for your database architecture.
---
This video is based on the question https://stackoverflow.com/q/63766621/ asked by the user 'user2592360' ( https://stackoverflow.com/u/2592360/ ) and on the answer https://stackoverflow.com/a/63772228/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Is logical subscription replicated (as all other data) by streaming replication in PostgreSQL?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Logical Subscriptions in PostgreSQL

When managing databases with multiple nodes, understanding how data replication works can become complex. In this post, we will address a specific question regarding logical subscriptions and their behavior under streaming replication in PostgreSQL.

The Problem at Hand

Consider the scenario involving three PostgreSQL 12 nodes: A, B, and C. Here's how they are set up:

Node A has the dataset and function as a logical publisher.

Node B is a logical subscriber to A but does not contain any data—it mirrors the schema and tables of Node A.

Node C is configured as a streaming replica of Node B.

The question arises: Will the logical subscription from Node B to Node A be replicated onto Node C via streaming replication? Or does Node C need to establish a separate logical subscription to Node A?

The Answer: Logical Replication from Node B to Node C

The good news is that the subscription from B to A will be replicated to C, but not in the usual way expected with logical replication. Let’s break down what that means:

Streaming Replication Behavior

Read-Only Nature of Node C: Node C is designed to act as a streaming replica, which means it is in a read-only state. This is an essential aspect because it impacts the data handling capabilities of Node C.

Lack of Logical Changes: While the subscription itself may be available and viewed on Node C, no logical replication changes can be applied there. This limitation arises because streaming replicas do not execute logical changes; they only replicate data in its physical form.

Replication Automation: Despite these limitations, the changes made to data in Node B as a result of the logical subscription to Node A will still be automatically replicated to Node C. Hence, any updates, inserts, or deletions made on Node B that result from the logical subscription will flow through to Node C.

Conclusion: One Subscription is Sufficient

In summary, there’s no need for Node C to establish a separate logical subscription with Node A since all logical replication changes applied on Node B will be streamed directly to C. This makes managing multiple nodes easier and reduces configuration complexity.

Instead of setting up two subscriptions (one from A to C directly), one can effectively rely on the streaming capabilities to maintain consistency and up-to-date data across the nodes.

By grasping these dynamics of replication in PostgreSQL, database administrators can streamline their systems and ensure robust, efficient data synchronization without unnecessary complexity.

Feel free to reach out with any further questions or scenarios regarding PostgreSQL replication strategies!