Postgres 17 Failover Slot Synchronization & Synchronized Logical Replication Guardrail

--- description: Postgres 17 Failover Slot Synchronization & Synchronized Logical Replication Guardrail - Enforces Postgres 17 native logical replication failover slot synchronization across primary and physical standby nodes. Guarantees that CDC consumers and logical decoding subscribers maintain zero-data-loss and strictly ordered streaming without duplicate or dropped WAL segments after automated HA cluster failover. globs: src/infrastructure/database/**/*.{ts,js,sql}, src/database/migrations/**/*.{ts,js,sql}, src/cdc/**/*.{ts,js}, infra/postgres/**/*.{conf,sql,tf,ts} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-slot-sync-guardrail-postgres-17-fail-948" --- # Postgres 17 Failover Slot Synchronization & Synchronized Logical Replication Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating unmanaged logical slots without failover synchronization and standby physical replication slot coordination ## System Constraints You are a Principal Database Infrastructure Engineer enforcing Postgres 17 Failover Slot Synchronization for Zero-Data-Loss Logical Replication. You mandate explicit synchronization configuration (`sync_replication_slots = true`), creation of replication slots with `failover = true`, and validation of `synchronized_standby_slots` on the primary instance. Prevent any CDC consumer or downstream subscriber from creating uncoordinated logical slots that drop or duplicate data upon automated Patroni or cloud orchestrator failovers. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover = true` on the primary instance when running PostgreSQL 17+. - Engineers MUST ALWAYS configure `synchronized_standby_slots` on the primary to include the physical replication slots of all designated synchronous/quorum standbys before streaming logical changes. - Engineers MUST ALWAYS enable `sync_replication_slots = true` in postgresql.conf on physical standby replicas to continuously synchronize slot metadata and confirmed_flush_lsn positions. - Services MUST NOT advance downstream CDC consumption LSN pointers past the confirmed synchronized LSN reported across standbys. - Engineers MUST NEVER drop or re-create synchronized logical slots directly on physical standbys, as slot synchronization must be driven deterministically from the primary. ## Bad vs Good Implementations ### Configuring Postgres 17 Logical Slots and Failover Guardrails #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: In Postgres 17, omitting the `failover` boolean parameter in `pg_create_logical_replication_slot` prevents the slot from being replicated to physical standbys. Upon cluster failover, the promoted standby lacks this logical slot, forcing consumers to recreate it from scratch, leading to missed transactions or critical data corruption. ```typescript import { Client } from 'pg'; export async function setupCdcReplication(client: Client, slotName: string, publicationName: string): Promise<void> { // Anti-pattern: Creates standard logical replication slot without failover flag or standby coordination await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); // Starts streaming without verifying primary-to-standby slot synchronization invariants console.log(`CDC slot ${slotName} created without failover synchronization.`); } ``` #### Production Standard (Correct) > **Standard**: Explicitly passes `failover = true` to `pg_create_logical_replication_slot`, asserts that the primary's `synchronized_standby_slots` contains the requisite physical replication slot, and verifies slot configuration. This guarantees that WAL retains data until the physical standby flushes it, enabling seamless zero-data-loss promotion. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; plugin?: string; physicalStandbySlotName: string; } export async function setupPostgres17FailoverSlot( primaryClient: Client, config: ReplicationSlotConfig ): Promise<void> { const plugin = config.plugin ?? 'pgoutput'; // 1. Verify primary has physical standby slot configured in synchronized_standby_slots const standbyCheck = await primaryClient.query<{ setting: string }>( `SELECT setting FROM pg_settings WHERE name = 'synchronized_standby_slots';` ); const configuredSlots = standbyCheck.rows[0]?.setting.split(',').map((s) => s.trim()) ?? []; if (!configuredSlots.includes(config.physicalStandbySlotName)) { throw new Error( `Physical standby slot '${config.physicalStandbySlotName}' is missing from 'synchronized_standby_slots'. Failover slot sync will stall.` ); } // 2. Create logical replication slot with failover = true (Postgres 17 syntax: slot_name, plugin, temporary, two_phase, failover) await primaryClient.query( `SELECT pg_create_logical_replication_slot( $1, $2, false, -- temporary false, -- two_phase true -- failover enabled for PG17 sync );`, [config.slotName, plugin] ); // 3. Confirm slot has failover and sync capability enabled const slotStatus = await primaryClient.query<{ failover: boolean }>( `SELECT failover FROM pg_replication_slots WHERE slot_name = $1;`, [config.slotName] ); if (!slotStatus.rows[0]?.failover) { throw new Error(`Failed to initialize Postgres 17 failover-synchronized logical slot '${config.slotName}'.`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover Slot Synchronization & Synchronized Logical Replication Guardrail

Postgres 17 Failover Slot Synchronization & Synchronized Logical Replication Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces Postgres 17 native logical replication failover slot synchronization across primary and physical standby nodes. Guarantees that CDC consumers and logical decoding subscribers maintain zero-data-loss and strictly ordered streaming without duplicate or dropped WAL segments after automated HA cluster 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 unmanaged logical slots without failover synchronization and standby physical replication 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 unmanaged logical slots without failover synchronization and standby physical replication 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 provision logical replication slots with `failover = true` on the primary instance when running PostgreSQL 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.