PostgreSQL 17 Logical Replication and Conflict Resolution in Multi-Master Setups

Designing resilient, distributed database systems requires eliminating single-point write bottlenecks without compromising ACID guarantees across geographically dispersed nodes. In high-density cloud environments like CpanelFree, enterprise workloads frequently hit wall-clock write saturation on traditional single-primary topologies when scaling across edge clusters. With the release of PostgreSQL 17, native logical replication introduces critical architectural advancements—including failover slot synchronization, the revolutionary pg_createsubscriber utility, and refined origin filtering—that make bi-directional multi-master architectures reliably viable for production environments.

The Architecture of PostgreSQL 17 Logical Replication in Multi-Master Topologies

Prior to PostgreSQL 17, deploying multi-master or active-active topologies using native logical replication suffered from two critical shortcomings: replication slots on primary nodes were not synchronized to physical standby replicas, and configuring new subscribers required complex manual initial syncs that risked replication lag spikes. PostgreSQL 17 resolves these foundational issues with native failover slot management (sync_replication_slots) and the new pg_createsubscriber binary, allowing zero-downtime subscriber provisioning directly from streaming standbys.

Key Advancements in PostgreSQL 17 Logical Decoding

PostgreSQL 17 refactors logical decoding memory management and concurrency controls. By increasing the efficiency of logical_decoding_work_mem and minimizing disk spillover during large transaction reassembly, logical workers can maintain throughput exceeding 85,000 write ops/sec on NVMe storage fabrics. Furthermore, PostgreSQL 17 enhances generated columns replication and maintains stricter dependency tracking during subscriber catalog updates.

Configuring Bi-Directional Multi-Master Replication

To establish a fault-tolerant bi-directional topology between node_alpha (10.0.10.11) and node_beta (10.0.10.12), your kernel and PostgreSQL instances must be tuned for high-throughput write-ahead log processing and low-latency socket polling.

Linux Kernel Tuning (/etc/sysctl.d/99-postgresql-replication.conf)

Deploy this sysctl configuration across all participating nodes to prevent TCP buffer starvation and minimize write stalls under heavy transactional volume:

# /etc/sysctl.d/99-postgresql-replication.conf
# High-Performance PostgreSQL 17 Multi-Master Network & Memory Settings

# Maximize socket receive and transmit buffers
net.core.rmem_max = 67108864
net.core.wmem_max = 67108864
net.core.rmem_default = 33554432
net.core.wmem_default = 33554432
net.core.optmem_max = 2048576
net.ipv4.tcp_rmem = 4096 87380 67108864
net.ipv4.tcp_wmem = 4096 65536 67108864

# TCP connection persistence and fast recovery
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_keepalive_intvl = 10
net.ipv4.tcp_keepalive_probes = 6
net.core.netdev_max_backlog = 100000
net.core.somaxconn = 65535

# Virtual memory and dirty page flushing to NVMe
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.overcommit_memory = 2
vm.overcommit_ratio = 80

Apply the kernel parameters immediately with:

sudo sysctl --system

PostgreSQL 17 Engine Configuration (postgresql.conf)

Both nodes must enable logical WAL decoding, dedicate adequate worker threads, and activate PostgreSQL 17 failover slot synchronization:

# /etc/postgresql/17/main/conf.d/replication.conf
# Core Logical Replication Engine Tuning

wal_level = logical
max_wal_senders = 20
max_replication_slots = 20
max_worker_processes = 24
max_logical_replication_workers = 12
max_sync_workers_per_subscription = 4

# Logical Decoding Memory Optimization (PG 17)
logical_decoding_work_mem = 128MB

# Failover Slot Synchronization (Ensures standby continuity)
sync_replication_slots = true
standby_slot_names = 'node_alpha_physical_standby,node_beta_physical_standby'

# Transaction Commit Latency Tuning
wal_writer_delay = 10ms
commit_delay = 50
commit_siblings = 5

# Checkpoint and Disk IO Tuning
checkpoint_timeout = 15min
max_wal_size = 16GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9

Establishing Publishers and Subscriptions with Loop Prevention

Execute the following commands on node_alpha to publish tables and subscribe to node_beta while preventing infinite transaction reflection:

-- Execute on Node Alpha (10.0.10.11)
CREATE PUBLICATION pub_alpha FOR ALL TABLES;

-- Create subscription to Node Beta with origin = none
CREATE SUBSCRIPTION sub_alpha_from_beta
  CONNECTION 'host=10.0.10.12 port=5432 dbname=production user=replicator password=SecretAuthToken'
  PUBLICATION pub_beta
  WITH (
    copy_data = false,
    origin = none,
    failover = true
  );

Repeat the reciprocal operation on node_beta:

-- Execute on Node Beta (10.0.10.12)
CREATE PUBLICATION pub_beta FOR ALL TABLES;

-- Create subscription to Node Alpha with origin = none
CREATE SUBSCRIPTION sub_beta_from_alpha
  CONNECTION 'host=10.0.10.11 port=5432 dbname=production user=replicator password=SecretAuthToken'
  PUBLICATION pub_alpha
  WITH (
    copy_data = false,
    origin = none,
    failover = true
  );

Conflict Resolution Mechanisms in Active-Active Topologies

In any multi-master database system, data divergence and replication conflicts are inevitable when asynchronous write streams target identical tuples across nodes. In PostgreSQL logical replication, conflicts typically manifest as:

• insert_exists: An incoming replicated row attempts to insert a primary key or unique constraint that already exists locally.
• update_missing: An update statement arrives for a row that does not exist in the local table.
• delete_missing: A delete statement targets a row that has already been removed or was never received.
• update_exists / concurrent update: Concurrent transactions modify the same tuple on different nodes before replication arrives.

Implementing Deterministic Last-Write-Wins (LWW) via Triggers

A resilient production strategy for concurrent update conflicts is Last-Write-Wins (LWW) enforced via microsecond-precision monotonic timestamps and row-level before triggers. Ensure every synchronized table includes an updated_at and origin_node column:

-- Create audit and conflict tracking columns
ALTER TABLE customer_accounts 
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
  ADD COLUMN IF NOT EXISTS origin_node VARCHAR(32) NOT NULL DEFAULT 'node_alpha';

-- Create the LWW conflict resolution trigger function
CREATE OR REPLACE FUNCTION resolve_customer_account_conflict()
RETURNS TRIGGER AS $$
BEGIN
  -- Detect if the update originated from replication
  IF current_setting('pg_logical.replication_origin', true) IS NOT NULL THEN
    -- Compare incoming row timestamp with existing local row timestamp
    IF OLD.updated_at > NEW.updated_at THEN
      -- Local row is newer than the incoming replica; suppress update
      RETURN NULL;
    ELSIF OLD.updated_at = NEW.updated_at THEN
      -- Tie-breaker: Deterministic lexical comparison on origin node name
      IF OLD.origin_node > NEW.origin_node THEN
        RETURN NULL;
      END IF;
    END IF;
  END IF;

  -- Otherwise accept the incoming or local update
  NEW.updated_at = clock_timestamp();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach trigger on BEFORE UPDATE
CREATE TRIGGER trg_resolve_customer_account_conflict
  BEFORE UPDATE ON customer_accounts
  FOR EACH ROW
  EXECUTE FUNCTION resolve_customer_account_conflict();

Handling Conflict Skips via pg_replication_origin

When an unexpected conflict causes a subscription to halt, PostgreSQL 17 allows operators to safely advance the replication progress past the conflicting LSN using pg_replication_origin_advance:

-- 1. Identify the failing subscription and remote transaction commit LSN
SELECT subname, latest_end_lsn, last_msg_send_time 
FROM pg_stat_subscription 
WHERE subname = 'sub_alpha_from_beta';

-- 2. Temporarily disable the subscription
ALTER SUBSCRIPTION sub_alpha_from_beta DISABLE;

-- 3. Advance the origin LSN past the conflicting transaction (e.g., 0/3B87A90)
SELECT pg_replication_origin_advance(
  'pg_' || (SELECT subid FROM pg_subscription WHERE subname = 'sub_alpha_from_beta'),
  '0/3B87A90'
);

-- 4. Re-enable the subscription
ALTER SUBSCRIPTION sub_alpha_from_beta ENABLE;

Automated Monitoring and Health Verification

Monitoring replication lag and slot bloat is essential. Use this production query to calculate exact byte lag, transaction decode rates, and conflict counts across all active nodes:

-- Query: Real-Time Replication Lag and Worker Health
SELECT
  s.subname AS subscription_name,
  s.pid AS worker_pid,
  s.received_lsn,
  s.last_msg_receipt_time,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), s.received_lsn)) AS byte_lag,
  stat.confl_tablespace,
  stat.confl_lock,
  stat.confl_snapshot,
  stat.confl_bufferpin,
  stat.confl_deadlock
FROM pg_stat_subscription s
JOIN pg_database db ON db.datname = current_database()
JOIN pg_stat_database_conflicts stat ON stat.datid = db.datid;

Frequently Asked Questions