Скачать или смотреть How to Implement Custom Column Expressions in PostgreSQL Logical Replication

Discover how to work with custom column expressions during PostgreSQL logical replication to adapt to your database structure and needs.
---
This video is based on the question https://stackoverflow.com/q/72518395/ asked by the user 'Stavros Koureas' ( https://stackoverflow.com/u/2123099/ ) and on the answer https://stackoverflow.com/a/73458339/ provided by the user 'Stavros Koureas' ( https://stackoverflow.com/u/2123099/ ) 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: PostgreSQL Logical Replication Custom Column Expression

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.
---
How to Implement Custom Column Expressions in PostgreSQL Logical Replication

When working with PostgreSQL, many developers find themselves in a situation where they need to replicate data between different databases while accommodating varying column requirements. One common query arises: Can we replicate additional columns with customized values in PostgreSQL Logical Replication? This guide will address that question and explore potential solutions.

The Situation

Let's illustrate the problem with an example.

Database "test1" has a table called "test" that consists of:

id (integer, primary key)

name (varchar)

Database "test0" also contains a table named "test," but its structure is slightly different:

tenant (integer, primary key with a default value of 1)

id (integer, primary key)

name (varchar)

As part of logical replication, you create a publisher in "test1" and a subscriber in "test0" to synchronize data. The goal is that every time a new record is added in "test1," it should also be inserted into "test0" along with a value for the tenant column. However, the challenge arises when you want the tenant value to dynamically change based on where the incoming data is coming from.

The Existing Setup

Here’s how you set up your logical replication:

Publisher Creation in Test1

[[See Video to Reveal this Text or Code Snippet]]

Subscriber Creation in Test0

[[See Video to Reveal this Text or Code Snippet]]

The Challenge

The existing setup allows for data replication from "test1" to "test0," but it only assigns the default value of 1 for the tenant column in "test0." The question is:

Can we have different tenant values, such as 1 for data coming from "test1" and 2 for data from "test2"?

The Current Limitation

As of now, based on the capabilities provided by PostgreSQL14 and its subsequent release PostgreSQL15, logical replication does not support replicating additional columns with custom expressions. This limitation prevents the use of dynamic values for columns that are not directly included in the publisher declaration.

Here is a summary of the current capabilities:

PostgreSQL15 allows publishing and subscribing to a subset of columns.

It still lacks functionality to customize column expressions for replicated values.

Conclusion

While these limitations can initially seem daunting, it's essential to understand the capabilities of the current PostgreSQL versions and plan data architecture accordingly. For requirements involving multiple tenant values across different databases, consider alternative solutions:

Triggers: Implement triggers in "test0" that modify the tenant column after inserting data from "test1."

Views: Use views or additional processing layers before data insertion in "test0" to assign the correct tenant values.

By keeping these workarounds in mind, you can navigate PostgreSQL's replication features more effectively, ensuring your data environment remains robust and flexible.

If you have any questions or need more guidance on PostgreSQL logical replication, feel free to reach out or join the discussion below!