PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization

--- description: PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization - PostgreSQL 17 introduces synchronized logical replication slots across physical standby failovers. Infrastructure and migration automation must mandate failover-enabled logical slots, primary-side standby coordination, and multi-host client failover strings to prevent data duplication, missing WAL segments, and cluster desynchronization after primary failover. globs: src/**/*.{ts,js}, database/**/*.{ts,sql}, infra/**/*.{ts,tf,json} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-logical-slot-sync" --- # PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating Logical Slots Without Failover Support and Standby Slot Coordination ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 logical replication invariants. Enforce failover slot synchronization (`failover => true`), physical standby synchronization guarantees (`standby_slot_names`), standby synchronization daemon configuration (`sync_replication_slots`), and subscriber libpq multi-host connection failover parameters (`target_session_attrs=read-write`). Reject any manual slot recreation on standbys or unprotected slot creation. ## Strict Guidelines - Engineers MUST ALWAYS create logical replication slots with `failover => true` (or `CREATE_REPLICATION_SLOT ... (FAILOVER)`) when subscribing to high-availability clusters in Postgres 17. - Engineers MUST ALWAYS configure `standby_slot_names` on the primary instance to include physical replication standbys, guaranteeing the primary does not discard WAL before physical standbys and logical subscribers consume it. - Engineers MUST ALWAYS configure `sync_replication_slots = true` (or run `pg_sync_replication_slots()`) on physical standbys to maintain real-time slot state mirrored from the primary. - Code interacting with subscriber connections MUST provide multi-host connection URIs with `target_session_attrs=read-write` so logical subscribers automatically reconnect to the newly promoted standby. - Services MUST NOT attempt to drop, advance, or mutate synchronized logical replication slots (`synchronized = true`) directly on physical standbys, as these are managed by the slot sync worker. ## Bad vs Good Implementations ### Configuring Postgres 17 Logical Replication Slot with Failover Synchronization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical replication slot without `failover => true` means Postgres 17 physical standbys will not sync the slot. During primary failover, the promoted standby lacks the slot or its confirmed LSN, resulting in subscriber crashes, skipped changes, or fatal desynchronization requiring full re-cloning. ```typescript import { Client } from 'pg'; export async function setupSubscriberSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Postgres < 17 legacy style or omitting failover // Will not be synchronized to standby; standby promotion drops or invalidates this slot await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput', false, false);`, [slotName] ); // Anti-pattern: Single host connection string without read-write failover detection const subscriberConn = 'postgresql://replicator:[email protected]:5432/app_db'; await client.query( `CREATE SUBSCRIPTION app_sub CONNECTION '${subscriberConn}' PUBLICATION app_pub WITH (create_slot = false, slot_name = $1);`, [slotName] ); } ``` #### Production Standard (Correct) > **Standard**: The good example sets `failover => true` on the slot and `failover = true` on the subscription, validates that `standby_slot_names` preserves WAL on the primary for the standby, and provides a multi-host `target_session_attrs=read-write` connection string enabling seamless automatic reconnection upon failover. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; primaryHost: string; standbyHost: string; publicationName: string; physicalStandbySlotName: string; } export async function setupFailoverSafeReplication( primaryClient: Client, config: ReplicationSlotConfig ): Promise<void> { // 1. Verify primary protects WAL until the physical standby consumes it const standbyCheck = await primaryClient.query<{ setting: string; }>('SHOW standby_slot_names;'); const standbySlots = standbyCheck.rows[0]?.setting.split(',').map((s) => s.trim()) ?? []; if (!standbySlots.includes(config.physicalStandbySlotName)) { throw new Error( `standby_slot_names must include '${config.physicalStandbySlotName}' on primary to ensure failover safety.` ); } // 2. Create logical replication slot with Postgres 17 failover flag enabled await primaryClient.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => false, failover => true );`, [config.slotName] ); // 3. Multi-host connection string targeting read-write master after failover const connString = `postgresql://replicator:secret@${config.primaryHost}:5432,${config.standbyHost}:5432/app_db?target_session_attrs=read-write`; // 4. Create subscription referencing the pre-synchronized failover slot await primaryClient.query( `CREATE SUBSCRIPTION app_sub CONNECTION '${connString}' PUBLICATION ${config.publicationName} WITH (create_slot = false, slot_name = $1, failover = true);`, [config.slotName] ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization

PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces synchronized logical replication slots across physical standby failovers. Infrastructure and migration automation must mandate failover-enabled logical slots, primary-side standby coordination, and multi-host client failover strings to prevent data duplication, missing WAL segments, and cluster desynchronization after primary failover. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Creating Logical Slots Without Failover Support and Standby Slot Coordination Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Creating Logical Slots Without Failover Support and Standby Slot Coordination	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS create logical replication slots with `failover => true` (or `CREATE_REPLICATION_SLOT ... (FAILOVER)`) when subscribing to high-availability clusters in Postgres 17.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.