Postgres 17 Failover Slot Synchronization & Lag Invariant

--- description: Postgres 17 Failover Slot Synchronization & Lag Invariant - Enforces strict failover slot synchronization (`failover = true` and `pg_sync_replication_slots()`) between primary and physical standby instances in Postgres 17. Guarantees zero message loss for active change data capture (CDC) consumers during unplanned primary failovers while actively gating primary transaction commit horizons via `synchronized_standby_slots` to avert catastrophic slot lag and disk-exhausting WAL bloat. globs: src/infrastructure/database/**/*.ts, src/services/cdc/**/*.ts, src/db/migrations/**/*.sql, scripts/db/**/*.sh alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-failover-slot-sync-postgres-17-logi-632" --- # Postgres 17 Failover Slot Synchronization & Lag Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating Logical Replication Slots Without Failover Flags or Synchronized Standby Slot Gating ## System Constraints You are a Principal Database Architect enforcing the Postgres 17 Logical Replication Failover Slot Synchronization Invariant. You must reject any replication slot creation, CDC consumer loop, or failover script that omits Postgres 17 native slot synchronization configurations (`failover = true`, `synchronized_standby_slots`, or standby slot synchronization validation). You must mandate safeguards against unbounded slot WAL retention. ## Strict Guidelines - Engineers MUST ALWAYS create logical replication slots with `failover = true` in Postgres 17 when connected downstream CDC consumers must survive standby promotions. - Engineers MUST configure `synchronized_standby_slots` on the primary instance to include all physical standbys serving as failover targets before consumers advance slot confirmed_flush_lsn. - Engineers MUST NOT rely on external user-space tools to replicate slot state across nodes when native Postgres 17 `sync_replication_slots` standby workers are available. - Code SHOULD verify that standby slots have caught up via `pg_replication_slots.synced = true` before redirecting CDC consumer connection strings post-promotion. - Services MUST monitor WAL lag (`pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)`) alongside `max_slot_wal_keep_size` to fail fast and disconnect unhealthy CDC slots before primary disk exhaustion. - Components NEVER execute slot advance (`pg_replication_slot_advance`) without verifying that downstream checkpoints have been durably flushed to disk on downstream message brokers or event queues. ## Bad vs Good Implementations ### Provisioning CDC Replication Slot with Failover Capability and Standby Synchronization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a replication slot without `failover = true` prevents Postgres 17 from synchronizing the slot's restart_lsn and confirmed_flush_lsn to physical replicas. When failover occurs, the new primary has no record of the slot, breaking downstream event streaming and risking message loss. ```typescript import { Client } from 'pg'; export async function registerCdcSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Omits Postgres 17 failover flag and does not check standby sync constraints. // Upon primary failover, the promoted standby will NOT contain this slot, // causing CDC stream failure and unrecoverable data loss or duplicate reads. await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } ``` #### Production Standard (Correct) > **Standard**: Passing `failover => true` activates Postgres 17's slot synchronization infrastructure. Verifying `synchronized_standby_slots` guarantees that physical standbys confirm reception of WAL data before logical consumers consume past it, ensuring failover promotions retain exact stream offsets. ```typescript import { Client } from 'pg'; export async function registerCdcSlot(client: Client, slotName: string): Promise<void> { // Invariant Fix: Explicitly specify failover = true for PG17 native standby slot replication // and assert that synchronized_standby_slots includes the target failover replicas. await client.query('BEGIN;'); try { const checkConfig = await client.query( `SELECT current_setting('synchronized_standby_slots', true) AS sync_slots;` ); if (!checkConfig.rows[0]?.sync_slots) { throw new Error('Invariant violation: synchronized_standby_slots must be configured prior to slot provisioning.'); } // Postgres 17 native syntax enabling physical standby synchronization await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, twophase => false, failover => true );`, [slotName] ); await client.query('COMMIT;'); } catch (err) { await client.query('ROLLBACK;'); throw err; } } ``` ### Verifying Standby Sync Readiness Before CDC Consumer Reconnection Post-Failover #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Standby slots can be stale or unfinalized if promotion triggers while synchronization is lagging. Connecting before validation causes consumers to read duplicated or inconsistent stream intervals. ```typescript import { Client } from 'pg'; export async function reconnectConsumerToStandby(standbyClient: Client, slotName: string): Promise<void> { // Anti-pattern: Immediately attaches to slot after promotion without validating // whether the slot was fully synced from the old primary before promoting. const res = await standbyClient.query( `SELECT active, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (res.rows.length === 0) { throw new Error('Slot missing on standby'); } // Starts streaming blindly... } ``` #### Production Standard (Correct) > **Standard**: Validates the slot metadata on the target instance by checking active status, recovery context, and the Postgres 17 `synced` flag, ensuring no overlapping active consumers and verifying slot continuity prior to resuming message ingress. ```typescript import { Client } from 'pg'; export async function reconnectConsumerToStandby(standbyClient: Client, slotName: string): Promise<void> { // Invariant Fix: Validate slot existence, synced attribute, and lag delta before opening replication protocol. const res = await standbyClient.query<{ active: boolean; synced: boolean; confirmed_flush_lsn: string; wal_lag_bytes: number; }>( `SELECT active, synced, confirmed_flush_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)::bigint AS wal_lag_bytes FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); const slot = res.rows[0]; if (!slot) { throw new Error(`Invariant violation: Failover slot '${slotName}' does not exist on promoted node.`); } // After promotion, synced transitions to false (promoted to local slot), // but if still on standby, synced must be true. const isStandby = (await standbyClient.query('SELECT pg_is_in_recovery()')).rows[0].pg_is_in_recovery; if (isStandby && !slot.synced) { throw new Error(`Slot '${slotName}' is not synchronized with primary. Cannot consume safely.`); } if (slot.active) { throw new Error(`Slot '${slotName}' is already locked by another active CDC consumer instance.`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover Slot Synchronization & Lag Invariant

Postgres 17 Failover Slot Synchronization & Lag Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict failover slot synchronization (`failover = true` and `pg_sync_replication_slots()`) between primary and physical standby instances in Postgres 17. Guarantees zero message loss for active change data capture (CDC) consumers during unplanned primary failovers while actively gating primary transaction commit horizons via `synchronized_standby_slots` to avert catastrophic slot lag and disk-exhausting WAL bloat. 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 Replication Slots Without Failover Flags or Synchronized Standby Slot Gating 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 Replication Slots Without Failover Flags or Synchronized Standby Slot Gating	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` in Postgres 17 when connected downstream CDC consumers must survive standby promotions.

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.