PostgreSQL 17 Synchronized Failover Replication Slot Invariant

--- description: PostgreSQL 17 Synchronized Failover Replication Slot Invariant - Enforces PostgreSQL 17 synchronized logical replication slot configurations (`failover = true` and `synchronized_standby_slots`) to ensure Change Data Capture (CDC) streams survive physical failover without message loss or slot rewinds. Standby promotion pipelines must verify slot synchronization convergence prior to triggering promotion. globs: src/**/*.{ts,js,py,go,sql}, scripts/**/*.{sh,py,ts}, infra/**/*.{tf,yaml,yml} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-replication-slot-sync-invariant" --- # PostgreSQL 17 Synchronized Failover Replication Slot Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Promoting Physical Standbys or Creating Logical Slots Without Failover Synchronization ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Failover Replication Slot Invariants. Mandate that all logical replication slots used for CDC (e.g., Debezium, custom streaming consumers) are initialized with `failover => true` and that primary server configurations enforce `synchronized_standby_slots`. Ensure all orchestration, failover automation, and promotion scripts strictly verify `synced = true` on `pg_replication_slots` across designated physical replicas before issuing `pg_promote()`. ## Strict Guidelines - Engineers MUST ALWAYS pass `failover => true` when calling `pg_create_logical_replication_slot()` on PostgreSQL 17+ for any streaming CDC or logical replica consumer. - Engineers MUST configure `synchronized_standby_slots` in `postgresql.conf` on the primary to list physical standby application names, preventing WAL recycling until standbys confirm receipt. - Standby promotion scripts MUST execute `SELECT pg_sync_replication_slots()` and assert that `synced` is true and `confirmed_flush_lsn` matches the target primary before executing `pg_promote()`. - Services MUST NOT bypass logical replication slot sync verification during automated failover sequences (e.g., in orchestrators, Patroni hooks, or custom health controllers). - Components NEVER drop or recreate a missing logical slot on a newly promoted primary without evaluating confirmed flush LSNs to prevent CDC downstream duplicate cascades or silent message loss. ## Bad vs Good Implementations ### PG17 Safe Logical Slot Creation and Standby Promotion Orchestrator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical slot without `failover => true` prevents PostgreSQL 17 from syncing the slot to physical standbys. Promoting the standby without calling `pg_sync_replication_slots()` or asserting `synced = true` causes CDC streams to fail permanently or re-stream massive duplicate datasets upon switchover. ```typescript import { Client } from 'pg'; export async function setupCdcAndPromote(primaryClient: Client, standbyClient: Client): Promise<void> { // Anti-pattern: Missing failover parameter in PG17 creates an ephemeral slot lost on failover await primaryClient.query(` SELECT pg_create_logical_replication_slot('cdc_events_slot', 'pgoutput'); `); // Unchecked promotion: Standby is promoted without ensuring CDC slots are synchronized await standbyClient.query('SELECT pg_promote();'); } ``` #### Production Standard (Correct) > **Standard**: Explicitly provisions the logical replication slot with `failover => true`, enforces slot synchronization on the physical replica via `pg_sync_replication_slots()`, and validates that `synced = true` and `conflicting = false` prior to triggering zero-data-loss promotion. ```typescript import { Client } from 'pg'; export async function setupCdcAndPromote(primaryClient: Client, standbyClient: Client): Promise<void> { // Invariant 1: Create logical replication slot with failover enabled in PostgreSQL 17 await primaryClient.query(` SELECT pg_create_logical_replication_slot( slot_name => 'cdc_events_slot', plugin => 'pgoutput', temporary => false, twophase => true, failover => true ); `); // Invariant 2: Explicitly sync logical slots on standby before promotion await standbyClient.query('SELECT pg_sync_replication_slots();'); // Invariant 3: Verify the slot is marked synchronized and caught up const { rows } = await standbyClient.query<{ slot_name: string; synced: boolean; conflicting: boolean; }>(` SELECT slot_name, synced, conflicting FROM pg_replication_slots WHERE slot_name = 'cdc_events_slot'; `); const slot = rows[0]; if (!slot || !slot.synced || slot.conflicting) { throw new Error(`Standby slot cdc_events_slot is not synchronized (synced=${slot?.synced}, conflicting=${slot?.conflicting}). Aborting promotion.`); } // Invariant 4: Promote standby only after verified synchronization await standbyClient.query('SELECT pg_promote(wait => true, wait_seconds => 60);'); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Synchronized Failover Replication Slot Invariant

PostgreSQL 17 Synchronized Failover Replication Slot Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 synchronized logical replication slot configurations (`failover = true` and `synchronized_standby_slots`) to ensure Change Data Capture (CDC) streams survive physical failover without message loss or slot rewinds. Standby promotion pipelines must verify slot synchronization convergence prior to triggering promotion. 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: Promoting Physical Standbys or Creating Logical Slots Without Failover 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	Promoting Physical Standbys or Creating Logical Slots Without Failover 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 pass `failover => true` when calling `pg_create_logical_replication_slot()` on PostgreSQL 17+ for any streaming CDC or logical replica consumer.

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.