Postgres 17 Failover Slot Sync Replication Invariant for Transactional Outbox

--- description: Postgres 17 Failover Slot Sync Replication Invariant for Transactional Outbox - Enforces failover logical replication slot synchronization natively introduced in Postgres 17 across streaming physical standby instances. Guarantees zero message loss and prevents silent outbox replication stream drops or catastrophic offset regression during planned or unplanned failovers. globs: src/**/*.service.ts, src/**/*.consumer.ts, src/infrastructure/database/**/*.ts, src/infrastructure/cdc/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-slot-sync-invariant" --- # Postgres 17 Failover Slot Sync Replication Invariant for Transactional Outbox > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating unsynced physical/logical replication slots causing silent event drops or duplicate re-emission upon standby promotion. ## System Constraints You are a Principal Database Reliability Engineer enforcing the PostgreSQL 17 Failover Slot Synchronization invariant. In transactional outbox or CDC architectures, logical replication slots must be created with `failover = true` on the primary, and physical standbys must enable `sync_replication_slots = true` with proper `synchronized_standby_slots` upstream alignment. Prevent split-brain consumption, missing slot data, and out-of-order event consumption across high-availability failovers. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover = true` using `pg_create_logical_replication_slot()` when consuming transactional outbox streams in PostgreSQL 17+ environments. - Engineers MUST NOT allow logical replication slots to consume ahead of physical replication standbys; `synchronized_standby_slots` MUST explicitly include the standby application names on the primary node. - Standby database configurations MUST enable `sync_replication_slots = true` in `postgresql.conf` or equivalent patroni/repmgr orchestration manifests. - Outbox workers MUST verify slot synchronization state (`sync_state = 'synced'`) and failover eligibility before resuming consumption from a newly promoted primary. - Workers MUST NEVER drop or recreate a missing replication slot blindly on a newly promoted standby without reconciling the confirmed LSN (`confirmed_flush_lsn`) against the last committed message boundary. ## Bad vs Good Implementations ### Configuring Postgres 17 Logical Replication Slot with Failover Synchronization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a replication slot without `failover = true` ensures it will not be replicated to physical standbys. Upon failover, the slot disappears, forcing consumers to recreate it from the current WAL head and permanently dropping intermediate outbox events. ```typescript import { Client } from 'pg'; export async function setupOutboxReplicationSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Missing failover parameter defaults to false. // When the physical standby promotes during a failover, this slot will NOT exist, // causing the outbox consumer to drop events or fail to connect. await client.query(` SELECT pg_create_logical_replication_slot($1, 'pgoutput'); `, [slotName]); } ``` #### Production Standard (Correct) > **Standard**: Sets the 5th parameter (`failover = true`) in PostgreSQL 17's `pg_create_logical_replication_slot()`. This flags the slot for the standby's slot sync worker (`sync_replication_slots = true`), guaranteeing the slot exists with its exact flush position after physical failover. ```typescript import { Client } from 'pg'; export async function setupOutboxReplicationSlot(client: Client, slotName: string): Promise<void> { // In Postgres 17, pass failover: true to synchronize across physical standbys // Check server major version >= 17 before setting failover parameter const versionRes = await client.query<{ server_version_num: string }>('SHOW server_version_num'); const serverVersion = parseInt(versionRes.rows[0].server_version_num, 10); if (serverVersion < 170000) { throw new Error('PostgreSQL 17+ is strictly required for zero-loss failover slot synchronization'); } // pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover) await client.query(` DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_replication_slots WHERE slot_name = $1 ) THEN PERFORM pg_create_logical_replication_slot( $1, 'pgoutput', false, false, true /* failover enabled */ ); END IF; END $$; `, [slotName]); } ``` ### Verifying Failover Slot Health Pre-Flight Check in Consumer Startup #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: If a failover occurs and the promoted node did not sync the slot (or if the worker connects to a node with a stale slot), blindly recreating it causes a massive silent data gap without warning the outbox engine. ```typescript import { Client } from 'pg'; export async function ensureSlotReady(client: Client, slotName: string): Promise<void> { // Anti-pattern: Blindly recreating slot if missing, ignoring lost LSN progress const res = await client.query('SELECT 1 FROM pg_replication_slots WHERE slot_name = $1', [slotName]); if (res.rowCount === 0) { await client.query("SELECT pg_create_logical_replication_slot($1, 'pgoutput')", [slotName]); } } ``` #### Production Standard (Correct) > **Standard**: Inspects Postgres 17 metadata (`failover` and `synced` columns in `pg_replication_slots`), preventing outbox consumption from starting on unsynchronized standbys or unmonitored primary instances. ```typescript import { Client } from 'pg'; interface ReplicationSlotInfo { slot_name: string; failover: boolean; synced: boolean; confirmed_flush_lsn: string | null; } export async function ensureSlotReady(client: Client, slotName: string): Promise<void> { const query = ` SELECT slot_name, failover, synced, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = $1; `; const { rows } = await client.query<ReplicationSlotInfo>(query, [slotName]); if (rows.length === 0) { throw new Error( `Critical Invariant Violation: Replication slot ${slotName} does not exist. ` + `Refusing automatic recreation to prevent outbox data loss across failover.` ); } const slot = rows[0]; if (!slot.failover) { throw new Error(`Replication slot ${slotName} has failover = false; at risk of loss on promotion`); } const inRecoveryRes = await client.query<{ pg_is_in_recovery: boolean }>('SELECT pg_is_in_recovery()'); const isStandby = inRecoveryRes.rows[0].pg_is_in_recovery; if (isStandby && !slot.synced) { throw new Error(`Standby slot ${slotName} has not completed initial sync (synced = false).`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover Slot Sync Replication Invariant for Transactional Outbox

Postgres 17 Failover Slot Sync Replication Invariant for Transactional Outbox defines an industrial-grade behavioral standard for backend engineering. Enforces failover logical replication slot synchronization natively introduced in Postgres 17 across streaming physical standby instances. Guarantees zero message loss and prevents silent outbox replication stream drops or catastrophic offset regression during planned or unplanned 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: Creating unsynced physical/logical replication slots causing silent event drops or duplicate re-emission 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	Creating unsynced physical/logical replication slots causing silent event drops or duplicate re-emission 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 `failover = true` using `pg_create_logical_replication_slot()` when consuming transactional outbox streams in PostgreSQL 17+ environments.

Engineers MUST ALWAYS provision logical replication slots with `failover = true` using `pg_create_logical_replication_slot()` when consuming transactional outbox streams 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.