PostgreSQL Multi-Active Replication - Part 2: Native Replication in PostgreSQL 15 and 16

(Credit: DALL·E)

Disclaimer: The postings on this site are my own and don’t necessarily represent my current or any previous employer’s positions, strategies or opinions.

This article, the second installment in our series on replication techniques with PostgreSQL, zeroes in on the built-in replication features provided by PostgreSQL, highlighting their limitations and assessing how these native capabilities align with the goal of achieving a global-scale multi-active deployment.

It’s important to note that, despite the advancements, the current PostgreSQL features do not yet fully support a multi-active replication setup.

This discussion aims to provide a realistic overview of what is currently achievable with PostgreSQL’s replication capabilities and to set the stage for exploring potential workarounds and enhancements in future articles.

Logical-Replication

PostgreSQL supports two main replication methods: Physical Replication and Logical Replication. Physical Replication, also known as Log Shipping, is designed to replicate the Write Ahead Log (WAL) either by copying the complete WAL files or streaming them. This method essentially replicates the entire database state from a primary server to a secondary server, mirroring every change as it happens. It’s comparable to continuous archiving from the primary server paired with continuous recovery on a secondary server.

While effective for scenarios with a single source of data changes, it struggles with environments that have multiple writers due to the risk of data overwrites or collisions, a challenge that PostgreSQL’s native capabilities do not address without the aid of external tools.

Logical Replication, in contrast, focuses on the replication of specific data objects and their changes, offering more granular control over what gets replicated and how.

This method is based on a publisher/subscriber model, where a source server (the publisher) defines publications for particular data objects, and a target server (the subscriber) can subscribe to these publications. One of PostgreSQL’s strengths in this area is its flexibility: publications and subscriptions are not mutually exclusive, allowing for complex configurations where a server can publish data objects while also subscribing to publications from other servers, facilitating sophisticated replication schemes beyond simple one-to-one replication scenarios.

Figure 1: Multi-Layer Publication / Subscriptions

Given these replication capabilities in PostgreSQL, setting up a global architecture with a single primary (writer) and multiple standby (reader) layers is relatively straightforward.

Figure 2: Multi-Layer, Multi-Geo Setup

This architectural model excels in spreading the read demand across various geographic regions while keeping write operations centralized, enhancing both performance and scalability for applications that experience a significantly higher volume of read transactions than writes. Positioning data geographically closer to the end-users significantly reduces latency for read operations, making the system more efficient and responsive.

Moreover, having standby servers in different regions not only aids in balancing the read load but also greatly simplifies the failover process. Should the primary server become unavailable, any of the regional standby servers — each potentially located in strategic locations like Germany (de) or France (fr) — is prepared to assume the role of the primary server. This flexibility (with the help of some on-top tooling like Patroni) could ensure high availability and resilience of the system, as there is always a prepared server ready to take over the primary’s duties, minimizing downtime and maintaining continuous service to users.

For this example, let’s consider a table that stores user information.

This table, which will call userinfo, might include various fields to capture essential user details. Here’s a simplified schema for the userinfo table:

CREATE TABLE userinfo
(
  id          bigint NOT NULL,
  name        varchar(40),
  email       varchar(100),
  dateOfBirth varchar(10),
  citizenship varchar(2)
  CONSTRAINT  userinfo_pkey PRIMARY KEY (id)
);

To facilitate replication of the userinfo table in PostgreSQL using logical replication, you would establish a publication on the publisher database and a subscription on the subscriber database.

On the publisher side, where the original data resides, you can create a publication for the userinfo. This is done with the CREATE PUBLICATION command. If the publication is intended to include all future tables automatically, you can specify FOR ALL TABLES; otherwise, specify the table explicitly as follows:

CREATE PUBLICATION userinfo_pub FOR TABLE userinfo

This command creates a publication named userinfo_pub that includes the userinfo table, making its changes available for replication.

You establish a subscription using the CREATE SUBSCRIPTION command on the subscriber side, which will receive the replicated data. This process requires specifying the connection string to the publisher database, including credentials, as the subscriber needs to authenticate with the publisher to initiate the replication process. Here’s an example command to create a subscription:

CREATE SUBSCRIPTION userinfo_sub CONNECTION 'host=<hostname> port=5432 user=postgres password=<pass> dbname=rep_test' PUBLICATION userinfo_pub

Replace <hostname>, <username>, <password>, and <dbname> with the actual values for your publisher database. This command creates a subscription named userinfo_sub that connects to the specified publisher and subscribes to the userinfo_pub publication.

By executing these commands on the respective publisher and subscriber databases, you set up logical replication for the userinfo table, allowing for real-time data synchronization between the databases.

Extensions to Logical Replication with PG15

With PostgreSQL 15, the introduction of row filters to logical replication marks a significant advancement in the customization of data replication. Row filters enhance the ability to control which data gets replicated by allowing publishers to apply a filter (similar to a WHERE clause in SQL) to data changes before publication. This means publishers can now decide more fine-grained what portion of the data should be included in the publication, enabling selective replication based on specific data criteria.

Such a filter allows selective replication based on the data involved, e.g., region-specific or similar. Columns referenced in a row filter must be part of a replica identity.

With figure 2 in mind, consider a scenario where the citizenship column of our userinfo table is crucial for ensuring GDPR compliance in data replication. The goal is to replicate only the data related to EU citizens to servers located within the EU. In contrast, data concerning non-EU citizens can be replicated across both EU and US servers.

Assuming the primary server is located in the EU and replicates data to servers in France, Germany, North Carolina, and Texas, you would want to ensure that only EU citizen data is replicated within the EU boundaries. This can be achieved by applying a row filter on the publication created on the primary server.

This setup requires that the citizenship column is part of a replica identity, which could be the primary key or a unique index that PostgreSQL can use for row identification during replication.

To include the citizenship column in the replica identity, you might need to adjust your table’s primary index. In practice, this might involve creating a unique index that includes the citizenship column if you do not wish to alter the existing primary key structure. For simplicity, let’s assume we adjust the primary index to include citizenship.

The adjusted structure of the userinfo table looks like this:

CREATE TABLE userinfo
(
  id          bigint NOT NULL,
  name        varchar(40),
  email       varchar(100),
  dateOfBirth varchar(10),
  citizenship varchar(2)
  CONSTRAINT  userinfo_pkey PRIMARY KEY (id, citizenship)
);

Here’s an illustrative command to create a publication with a row filter applied to the userinfo table, ensuring only rows where the citizenship column indicates non EU country are published:

CREATE PUBLICATION userinfo_non_eu_pub FOR TABLE userinfo WHERE citizenship <> 'EU';

This command sets up a publication named userinfo_non_eu_pub that only includes changes to rows in the userinfo table where the citizenship value does not equal EU. The WHERE clause applies the selective replication logic based on the citizenship column.

The strategy outlined above leads to establishing a thorough replication setup consisting of two distinct publications. The first publication, userinfo_pub, includes all data and is designed for replication to EU-based servers to ensure compliance with local data protection laws. The second publication, userinfo_non_eu_pub, filters out records with a citizenship value of EU, making them suitable for replication to non-EU servers.

To implement this strategy effectively, each subscriber’s configuration must be adjusted to subscribe to the appropriate publication based on the geographical and legal requirements of the data it will store.

This means modifying the subscriptions on each server so that EU servers subscribe to userinfo_pub, which replicates all user data, and non-EU servers subscribe to userinfo_non_eu_pub, which excludes EU citizen data.

This replication pattern offers a scalable solution for handling more complex data distribution needs. Organizations can significantly increase read transaction rates by distributing data across multiple servers based on content and geographical considerations and integrating with a load balancer. This setup can efficiently balance the load among servers, improving applications’ overall performance and responsiveness, especially in scenarios with high read demand.

Extensions to Logical Replication in PG16

Let’s make slight adjustments to the overall architecture example to enhance the resilience and expedite failover procedures within our setup. It’s important to note that this is merely a sample and may not directly translate to real-world configurations.

Figure 3: Multi-Layer, Redundant replication setup

In the depicted scenario, the PostgreSQL (PG) instance located in France serves as the primary node and is configured for replication to all standby servers. Additionally, to facilitate rapid failover, the standby servers are configured to replicate data among themselves, enabling them to receive updates from other standby servers.

However, this setup introduces challenges in tracking the origin of data and managing potential issues like loop-back or duplicate Primary Key conflicts.

Although PostgreSQL logs would highlight such problems, there are currently no built-in mechanisms to address them automatically.

PostgreSQL 16 introduces a subscription extension that simplifies handling these challenges: the origin subscription parameter.

The origin subscription parameter allows us to specify the origin of data we wish to receive. In PostgreSQL 16, the parameter offers two values: none and any, with any being the default. Specifying none indicates that changes must originate directly from the publisher rather than being received from another source. Alternatively, specifying any allows for data replication regardless of its origin.

For us to get the caveats mentioned earlier out of the way, let’s adjust the subscriptions of the standby servers as follows:

CREATE SUBSCRIPTION userinfo_sub 
  CONNECTION 'host=<hostname> port=5432 user=postgres password=<pass> dbname=rep_test' 
  PUBLICATION userinfo_pub 
  WITH (origin = none, copy_data = false)

In conjunction with specifying the origin parameter, we configure the copy_data parameter to false. This adjustment ensures that the data synchronization process exclusively involves data originating from the source server. In real-world deployments, this setup hinges on the configuration of the standby servers.

Establishing subscriptions this way lays the groundwork for an advanced initial setup. This approach allows for a streamlined transition in case of a failover, where the process to elevate a standby server to the primary role is simplified to only the essential steps. This strategy enhances efficiency and reliability during critical operational shifts.

Conclusion

Logical replication in PostgreSQL versions 15 and 16 has introduced significant improvements, making the concept of a multi-geographical, multi-active database setup more feasible with the default PostgreSQL configuration. Despite these advancements, certain limitations remain that hinder achieving a fully functioning setup.

A primary challenge in implementing such a system is managing conflict resolution across multiple primary servers that can all handle write operations simultaneously. For instance, the process for handling INSERT or UPDATE statements varies depending on where the data was initially recorded. A particular scenario that illustrates this challenge occurs when data is inserted on one server, then updated on another, and subsequently replicated back to the original server. This can lead to primary key conflicts because the system does not recognize the replicated data as a “replica.”

To mitigate replication loops and directional issues, PostgreSQL introduced the origin filter. However, this feature’s current implementation, which only allows for the specification of ‘none’ or any specific origin (excluding comprehensive or customized lists), restricts its applicability for diverse use cases.

In upcoming articles, we will explore these new features and other critical aspects relevant to advancing towards a fully functional multi-active setup. We aim to explore strategies and solutions that leverage these enhancements and alternatives to address the current limitations.

Upcoming Series Parts

The upcoming article by Dimitrij Pankratz will focus on the open-source PostgreSQL plugins, BDR (Bi-Directional Replication), and pglogical and their roles in our quest to establish a fully functioning global-scale multi-active deployment. These tools are instrumental in overcoming some of the inherent limitations of PostgreSQL’s native replication capabilities, offering enhanced features for more complex replication scenarios.

• Part 1: Introduction
• Part 2: Native replication in PostgreSQL 15 and 16 (this article)
• Part 3: BDR and PGLogical
• Part 4: Change Data Capture Approach
• Part 5: Local-First Approach
• Part 6: Overall Conclusion