Postgres 17 Failover-Enabled Logical Replication Slots for Distributed Outbox Zero-RPO

--- description: Postgres 17 Failover-Enabled Logical Replication Slots for Distributed Outbox Zero-RPO - Mandates explicit failover synchronization on logical replication slots introduced in PostgreSQL 17 for transactional outbox and CDC event engines. Ensures promoted physical standbys inherit accurate consumer LSN positions, preventing downstream event duplicate storms and replication gap data loss during automated multi-region failovers. globs: src/**/*.ts, src/infrastructure/**/*.ts, src/database/**/*.ts, src/outbox/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-synchronized-replication-slots-zero-rpo" --- # Postgres 17 Failover-Enabled Logical Replication Slots for Distributed Outbox Zero-RPO > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Legacy Slot Provisioning Without Failover Sync Causing Catastrophic Event Replay Storms Upon Standby Promotion ## System Constraints You are a Principal Software Architect enforcing zero-RPO, zero-duplicate multi-region database failover standards using PostgreSQL 17 Synchronized Replication Slots. You must reject any logical replication provisioning, outbox consumers, or database migration scripts that fail to configure or verify failover-enabled logical slots (`failover = true`) and their standby synchronization state. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with the PG17 `failover => true` parameter (or fifth positional argument) when creating slots for outbox consumers or CDC pipelines. - Engineers MUST NOT rely on default `pg_create_logical_replication_slot` arguments, which default `failover` to `false` and cause slots to vanish or stall on promoted standbys. - Code SHOULD verify `pg_replication_slots.failover = true` and monitor `pg_replication_slots.sync_state` during consumer startup and health check routines. - Services MUST gracefully handle database connection redirection to a promoted standby without re-creating replication slots or rewinding confirmed flush LSNs. - Components NEVER start consuming from a standby logical slot while `sync_state` is actively syncing (`sync_state = 's'`) until failover completes and the standby is fully promoted. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Slot Initialization for Outbox Consumers #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting the PG17 failover parameter creates a local-only logical replication slot. Upon physical primary failover, the slot does not exist on the promoted standby, causing replication stream failure or massive duplicate event re-ingestion if recreated from earliest WAL. ```typescript import { Client } from 'pg'; export async function initOutboxReplicationSlot(client: Client, slotName: string): Promise<void> { // ANTI-PATTERN: Creates a legacy logical slot without PG17 failover enablement. // During an automated failover, physical standbys lack this slot or its synced LSN. // When the standby is promoted, the consumer either crashes or must recreate // the slot from scratch, triggering millions of duplicate events across the cluster. await client.query(` SELECT pg_create_logical_replication_slot($1, 'pgoutput'); `, [slotName]); } ``` #### Production Standard (Correct) > **Standard**: Enforces PostgreSQL 17+, explicitly configures `failover => true` when provisioning the slot, and verifies slot metadata so that physical standbys synchronize consumer LSN progress continuously, enabling clean, zero-RPO failover. ```typescript import { Client } from 'pg'; interface ReplicationSlotInfo { slotName: string; plugin: string; failover: boolean; synced: boolean; } export async function ensureFailoverSafeReplicationSlot( client: Client, slotName: string ): Promise<ReplicationSlotInfo> { // Validate PostgreSQL 17+ requirement for synchronized replication slots const versionRes = await client.query<{ server_version_num: string }>( 'SHOW server_version_num;' ); const versionNum = parseInt(versionRes.rows[0].server_version_num, 10); if (versionNum < 170000) { throw new Error(`Postgres 17+ required for failover slots. Current version: ${versionNum}`); } // Check if slot already exists and inspect failover status const checkSlotRes = await client.query<ReplicationSlotInfo>(` SELECT slot_name AS "slotName", plugin, failover, synced FROM pg_replication_slots WHERE slot_name = $1; `, [slotName]); if (checkSlotRes.rowCount && checkSlotRes.rowCount > 0) { const slot = checkSlotRes.rows[0]; if (!slot.failover) { throw new Error( `Logical slot '${slotName}' exists but 'failover' is false. Zero-RPO failover invariant violated.` ); } return slot; } // PG17 signature: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover) await client.query(` SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => false, failover => true ); `, [slotName]); return { slotName, plugin: 'pgoutput', failover: true, synced: false, }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover-Enabled Logical Replication Slots for Distributed Outbox Zero-RPO

Postgres 17 Failover-Enabled Logical Replication Slots for Distributed Outbox Zero-RPO defines an industrial-grade behavioral standard for backend engineering. Mandates explicit failover synchronization on logical replication slots introduced in PostgreSQL 17 for transactional outbox and CDC event engines. Ensures promoted physical standbys inherit accurate consumer LSN positions, preventing downstream event duplicate storms and replication gap data loss during automated multi-region 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: Legacy Slot Provisioning Without Failover Sync Causing Catastrophic Event Replay Storms Upon Standby Promotion 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	Legacy Slot Provisioning Without Failover Sync Causing Catastrophic Event Replay Storms Upon Standby Promotion	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 the PG17 `failover => true` parameter (or fifth positional argument) when creating slots for outbox consumers or CDC pipelines.

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.