PostgreSQL 17 Logical Replication Failover Slot Synchronization Invariant

--- description: PostgreSQL 17 Logical Replication Failover Slot Synchronization Invariant - PostgreSQL 17 introduces native logical replication slot failover support via the failover parameter and pg_sync_replication_slots(). Backend orchestration and database clients must ensure logical slots are explicitly flagged with failover = true and synchronized across physical standbys before acknowledging primary promotion, preventing silent data duplication or permanent WAL subscriber stalls. globs: src/database/**/*.{ts,js,py,go}, migrations/**/*.{sql,ts,js}, infra/**/*.{tf,hcl,yaml,yml} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-replication-failover-slot-sync-postgresql-17-lo-801" --- # PostgreSQL 17 Logical Replication Failover Slot Synchronization Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating Logical Slots Without Failover Capabilities or Promoting Physical Standbys Without Verifying Slot Synchronization ## System Constraints You are a Principal Database Reliability Engineer enforcing PostgreSQL 17 Logical Replication Failover Invariants. Enforce that all logical replication slots configured for downstream consumers (CDC pipelines, Debezium, Kafka connectors, read replicas) explicitly set failover = true on the primary and configure synchronized slots on all physical standbys via pg_sync_replication_slots() and sync_replication_slots in postgresql.conf. Forbid uncontrolled HA promotions that fail to verify synchronized LSNs across standby logical slots. ## Strict Guidelines - Engineers MUST ALWAYS set failover = true when executing pg_create_logical_replication_slot on PostgreSQL 17+ primaries. - Engineers MUST configure standby_slot_names on the primary to ensure the physical standby confirms WAL ingestion prior to primary logical consumers advancing. - Engineers MUST configure sync_replication_slots = true or periodically execute pg_sync_replication_slots() on physical standbys prior to initiating failover. - Code MUST verify confirmed_flush_lsn synchronization between primary and standby slots prior to switching subscription connection strings to a promoted standby. - Engineers MUST NOT promote a physical standby until pg_replication_slots.synced returns true for all critical logical slots. - Automation scripts MUST handle slot conflicts and avoid recreating logical slots post-failover, ensuring WAL continuity without catastrophic re-consumption or data loss. ## Bad vs Good Implementations ### Provisioning and Promoting Logical Slots with PG17 Failover Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating logical replication slots without the 'failover' boolean parameter leaves them untracked by PG17 standby sync mechanisms. Blindly promoting the standby without verifying pg_replication_slots.synced and confirmed_flush_lsn leads to lost consumer offsets or broken replication streams. ```typescript import { Client } from 'pg'; export async function setupConsumerSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Missing failover parameter in PG17 // When primary dies, this slot does not sync to physical standbys, // causing CDC subscribers to fail or re-read duplicated data post-failover. await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } export async function failoverPromote(standbyClient: Client): Promise<void> { // Anti-pattern: Blind promotion without checking slot synchronization await standbyClient.query('SELECT pg_promote();'); } ``` #### Production Standard (Correct) > **Standard**: Uses PostgreSQL 17's native failover parameter during slot creation, invokes pg_sync_replication_slots() on the target standby, and polls pg_replication_slots to assert that synced is true before triggering pg_promote(). ```typescript import { Client } from 'pg'; export async function setupConsumerSlot(client: Client, slotName: string): Promise<void> { // PG17 Invariant: Explicitly declare failover = true and two-phase support await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, twophase => true, failover => true );`, [slotName] ); } export async function safePromoteWithSlotVerification( primaryClient: Client, standbyClient: Client, slotName: string, timeoutMs: number = 30000 ): Promise<void> { // Ensure standby synchronizes the slot before promotion await standbyClient.query('SELECT pg_sync_replication_slots();'); const startTime = Date.now(); while (Date.now() - startTime < timeoutMs) { const res = await standbyClient.query( `SELECT slot_name, synced, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = $1 AND failover = true;`, [slotName] ); const slot = res.rows[0]; if (slot && slot.synced === true) { // Standby has fully caught up with synchronized slot metadata await standbyClient.query('SELECT pg_promote();'); return; } await new Promise((resolve) => setTimeout(resolve, 500)); } throw new Error(`Failover aborted: Slot ${slotName} failed to synchronize on standby.`); } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Failover Slot Synchronization Invariant defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces native logical replication slot failover support via the failover parameter and pg_sync_replication_slots(). Backend orchestration and database clients must ensure logical slots are explicitly flagged with failover = true and synchronized across physical standbys before acknowledging primary promotion, preventing silent data duplication or permanent WAL subscriber stalls. 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 Capabilities or Promoting Physical Standbys Without Verifying Slot Synchronization 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 Capabilities or Promoting Physical Standbys Without Verifying Slot Synchronization	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 set failover = true when executing pg_create_logical_replication_slot on PostgreSQL 17+ primaries.

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.