PostgreSQL 17 Failover Slot Sync and Logical Decoding Fence Enforcement

--- description: PostgreSQL 17 Failover Slot Sync and Logical Decoding Fence Enforcement - Enforces synchronized logical replication slots via Postgres 17 failover slot mechanics and mandates a fencing token mechanism on subscriber consumers during physical topology changes. Prevents duplicate event emissions, sequence regressions, and silent consumer state corruption caused by physical replica promotion before slot synchronization completes. globs: src/database/**/*.ts, src/replication/**/*.ts, src/cdc/**/*.ts, src/consumers/**/*.ts, infra/**/*.sql, infra/**/*.tf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-failover-slot-sync-logical-decoding-fence" --- # PostgreSQL 17 Failover Slot Sync and Logical Decoding Fence Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Decoding Across Uncoordinated Physical Failover Promoting State Divergence ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 logical replication slot synchronization and deterministic consumer fencing. You must strictly reject any CDC consumer configuration or failover automation that consumes directly from un-synced replication slots, relies on legacy uncoordinated WAL decoders across topology changes, or omits dual-barrier LSN epoch assertions before processing change data capture records. ## Strict Guidelines - Engineers MUST ALWAYS configure `failover = true` on logical replication slots created on PostgreSQL 17 primary instances and explicitly add them to `synchronized_standby_slots` in postgresql.conf. - Engineers MUST NOT allow logical replication workers or downstream CDC consumers to advance processing without verifying that the standby's synced slot confirmed_flush_lsn matches or exceeds the consumer checkpoint before replica promotion. - Services MUST implement an idempotent decoding fence using an explicit fencing epoch and monotonic Log Sequence Number (LSN) guard: incoming events with an LSN lower than or equal to the recorded committed LSN of the current epoch MUST be dropped. - Engineers MUST configure standby instances with `sync_replication_slots = on` to ensure the slot synchronization worker automatically maintains logical slot state from the primary via pg_sync_replication_slots. - Consumer connection pools MUST query `pg_replication_slots.synced` post-failover and MUST refuse connection initialization if `synced = true` persists on a promoted node that failed to transition the slot to a local un-synced state. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Slot Creation with Failover Synchronization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical replication slot without `failover = true` in PostgreSQL 17 prevents the internal slot synchronization worker from copying the slot metadata and confirmed_flush_lsn to physical standby nodes, leading to data loss or consumer desynchronization during a failover. ```typescript import { Client } from 'pg'; export async function initSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Missing failover parameter in PG17; slot will not propagate to physical standbys await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); } ``` #### Production Standard (Correct) > **Standard**: Supplies the fifth argument (failover = true) to pg_create_logical_replication_slot, ensuring PostgreSQL 17 coordinates the slot across physical standbys specified in synchronized_standby_slots. ```typescript import { Client } from 'pg'; export async function initSlot(client: Client, slotName: string): Promise<void> { // Production PG17: create slot with failover=true for slot sync worker propagation await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput', false, false, true);`, [slotName] ); } ``` ### Consumer LSN Fencing and Failover Slot State Verification Barrier #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Downstream consumer accepts events without checking LSN monotonicity or slot synchronization readiness, causing duplicate message handling and sequence regression when failover switches to a standby whose slot lags by even a few bytes. ```typescript import { Client } from 'pg'; interface ReplicationEvent { lsn: string; payload: Record<string, unknown>; } export class EventConsumer { async processEvent(event: ReplicationEvent): Promise<void> { // Anti-pattern: No epoch or LSN monotonic check. Duplicates after failover corrupt downstream state. await this.applyBusinessLogic(event.payload); } private async applyBusinessLogic(payload: Record<string, unknown>): Promise<void> { // Mutates state directly } } ``` #### Production Standard (Correct) > **Standard**: Validates that the promoted node has converted the slot from synced status, and tracks monotonic (epoch, LSN) coordinates to strictly drop duplicated or replayed transactions occurring across the failover boundary. ```typescript import { Client } from 'pg'; interface ReplicationEvent { lsn: bigint; epoch: number; payload: Record<string, unknown>; } export class ResilientEventConsumer { private lastCommittedLsn: bigint = 0n; private currentEpoch: number = 0; async verifySlotSyncReadiness(client: Client, slotName: string): Promise<void> { const res = await client.query( `SELECT slot_name, confirmed_flush_lsn, synced, temporary FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); const slot = res.rows[0]; if (!slot) { throw new Error(`Replication slot ${slotName} does not exist.`); } // If node was promoted, synced must transition to false to enable local consumption if (slot.synced === true) { throw new Error(`Slot ${slotName} is still marked as synced. Promotion fence incomplete.`); } } async processEvent(event: ReplicationEvent): Promise<void> { if (event.epoch < this.currentEpoch) { return; // Ignore events from past topology epochs } if (event.epoch === this.currentEpoch && event.lsn <= this.lastCommittedLsn) { return; // Deduplicate replayed LSNs across failover boundary } await this.applyBusinessLogic(event.payload); this.lastCommittedLsn = event.lsn; this.currentEpoch = event.epoch; } private async applyBusinessLogic(payload: Record<string, unknown>): Promise<void> { // Mutates state idempotently } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Sync and Logical Decoding Fence Enforcement

PostgreSQL 17 Failover Slot Sync and Logical Decoding Fence Enforcement defines an industrial-grade behavioral standard for backend engineering. Enforces synchronized logical replication slots via Postgres 17 failover slot mechanics and mandates a fencing token mechanism on subscriber consumers during physical topology changes. Prevents duplicate event emissions, sequence regressions, and silent consumer state corruption caused by physical replica promotion before slot synchronization completes. 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: Unsynchronized Logical Decoding Across Uncoordinated Physical Failover Promoting State Divergence 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	Unsynchronized Logical Decoding Across Uncoordinated Physical Failover Promoting State Divergence	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 configure `failover = true` on logical replication slots created on PostgreSQL 17 primary instances and explicitly add them to `synchronized_standby_slots` in postgresql.conf.

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.