PostgreSQL 17 Logical Replication Slot Failover Synchronization Guardrail

--- description: PostgreSQL 17 Logical Replication Slot Failover Synchronization Guardrail - Enforces strict configuration and application-level handling of PostgreSQL 17 failover-enabled logical replication slots across high-availability physical standby promotions. Prevents change-data-capture message loss, silent stream truncation, and uncoordinated LSN regression during unexpected primary node failovers. globs: src/**/*.ts, src/**/*.js, infra/**/*.sql, migrations/**/*.sql, deploy/**/*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-slot-sync-ha-replication-guardrail" --- # PostgreSQL 17 Logical Replication Slot Failover Synchronization Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots Dropping or Replaying Corrupted Data Streams Post-Failover ## System Constraints You are a Principal Software Architect enforcing zero-data-loss CDC pipelines under PostgreSQL 17 HA architectures. You mandate the explicit activation of slot failover flags (`failover => true`), primary physical synchronization boundaries (`standby_slot_names`), standby slot synchronizer configurations (`sync_replication_slots = on`), and client-side LSN validation guards across failover promotions. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover => true` in PostgreSQL 17+ environments. - Engineers MUST NOT allow logical replication slots to advance ahead of physical standbys; primary clusters MUST list all physical replication slots in `standby_slot_names`. - Physical standby instances MUST configure `sync_replication_slots = on` to allow the background slot sync worker to replicate slot metadata continuously. - CDC consumer clients MUST verify that a connected slot is not in `synced = true` status before opening an active streaming protocol connection, ensuring the standby has fully promoted. - CDC consumer clients MUST NEVER automatically drop and recreate logical replication slots upon encountering failover disconnection errors (`08006`, `57P01`, `57P03`). - Code SHOULD validate that the promoted server's `confirmed_flush_lsn` is greater than or equal to the locally acknowledged consumer offset before resuming CDC consumption. ## Bad vs Good Implementations ### CDC Logical Replication Slot Provisioning and Failover Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating logical slots without setting `failover => true` prevents PostgreSQL 17 standby sync workers from mirroring the slot. Furthermore, dropping and recreating a slot during transient failover disconnects purges unconsumed transaction logs, permanently corrupting consumer state. ```typescript import { Client } from 'pg'; export async function initCdcPipeline(client: Client, slotName: string): Promise<void> { // Flaw: Creates slot without failover flag and blindly recreates on failure try { await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } catch (err: any) { if (err.code === '42710') { // Slot exists, ignore return; } // DANGEROUS: Drops and recreates the slot on reconnect errors during failover, // destroying unconsumed WAL offsets and causing silent data loss. await client.query(`SELECT pg_drop_replication_slot($1);`, [slotName]); await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } } ``` #### Production Standard (Correct) > **Standard**: Correctly invokes PostgreSQL 17 `pg_create_logical_replication_slot` with `failover => true`. Inspects node recovery status, verifies the slot is not in an unpromoted `synced` state, and validates that `confirmed_flush_lsn` does not regress before starting consumer ingestion. ```typescript import { Client } from 'pg'; interface SlotMetadata { slot_name: string; plugin: string; failover: boolean; synced: boolean; confirmed_flush_lsn: string | null; } export async function initCdcPipeline( client: Client, slotName: string, lastAckedLsn: string ): Promise<void> { // 1. Verify cluster role and standby promotion state const recoveryRes = await client.query<{ pg_is_in_recovery: boolean }>( 'SELECT pg_is_in_recovery();' ); if (recoveryRes.rows[0].pg_is_in_recovery) { throw new Error('Target node is still in standby recovery mode. Promotion incomplete.'); } // 2. Query slot metadata const slotRes = await client.query<SlotMetadata>( `SELECT slot_name, plugin, failover, synced, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (slotRes.rowCount === 0) { // Create logical slot with Postgres 17 failover flag enabled await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, twophase => false, failover => true );`, [slotName] ); return; } const slot = slotRes.rows[0]; if (!slot.failover) { throw new Error( `Replication slot '${slotName}' exists but failover flag is false. Manual migration required.` ); } // In PG17, promoted slots retain synced=false on the new primary once promoted if (slot.synced) { throw new Error( `Replication slot '${slotName}' is marked as synced. Target node must be promoted before consumption.` ); } // 3. Prevent LSN regression if (slot.confirmed_flush_lsn && lastAckedLsn) { const compareRes = await client.query<{ diff: string }>( `SELECT ($1::pg_lsn - $2::pg_lsn) AS diff;`, [slot.confirmed_flush_lsn, lastAckedLsn] ); if (BigInt(compareRes.rows[0].diff) < 0n) { throw new Error( `Slot confirmed LSN (${slot.confirmed_flush_lsn}) is behind last acknowledged LSN (${lastAckedLsn}). Failover sync violation detected.` ); } } } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Slot Failover Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict configuration and application-level handling of PostgreSQL 17 failover-enabled logical replication slots across high-availability physical standby promotions. Prevents change-data-capture message loss, silent stream truncation, and uncoordinated LSN regression during unexpected primary node failovers. 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: Unsynchronized Logical Slots Dropping or Replaying Corrupted Data Streams Post-Failover 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	Unsynchronized Logical Slots Dropping or Replaying Corrupted Data Streams Post-Failover	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 provision logical replication slots with `failover => true` in PostgreSQL 17+ environments.

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.