Postgres 17 Logical Replication Slot Synchronization & Zero-Data-Loss Failover

--- description: Postgres 17 Logical Replication Slot Synchronization & Zero-Data-Loss Failover - PostgreSQL 17 introduces native logical replication slot failover via physical standby synchronization ('failover => true' and 'sync_replication_slots'). Services and CDC orchestrators must strictly enforce slot synchronization state validation before standby cutover and mandate finite WAL retention limits to avoid either catastrophic disk exhaustion or silent data loss during failovers. globs: src/**/*.ts, src/database/**/*.ts, src/replication/**/*.ts, infra/**/*.sql, scripts/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-logical-slot-sync-guardrail" --- # Postgres 17 Logical Replication Slot Synchronization & Zero-Data-Loss Failover > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Uncoordinated Logical Slot Cutover and Unbounded WAL Accumulation Causing Split-Brain CDC and Node Crash ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 logical replication slot failover standards. Postgres 17 permits logical slots to be synced across physical standbys using `failover => true` and `synchronized_standby_slots`. You must enforce that all slot creation declares failover capabilities, that standby cutover verifications assert `sync_state = 's'`, that slot invalidation (`wal_status = 'lost'`) is handled defensively, and that primary WAL retention is strictly bounded by `max_slot_wal_keep_size`. ## Strict Guidelines - Engineers MUST ALWAYS create logical replication slots with `failover => true` on PostgreSQL 17 when consuming CDC from high-availability topologies. - Engineers MUST ALWAYS configure `synchronized_standby_slots` on the primary instance to include physical standby application names prior to enabling slot failover. - Engineers MUST ALWAYS enforce a bounded `max_slot_wal_keep_size` on primary and standby nodes to prevent disconnected or lagging slots from exhausting disk storage. - Services MUST verify that `sync_state = 's'` and `wal_status != 'lost'` on the standby replica before switching CDC consumers or acknowledging failover cutover. - Components NEVER resume CDC streaming on a promoted standby without confirming that the consumer's last committed LSN is equal to or ahead of the replica's `confirmed_flush_lsn`. - Engineers MUST NOT disable WAL safety mechanisms or create unmonitored persistent replication slots on production clusters. ## Bad vs Good Implementations ### PostgreSQL 17 Failover Slot Creation and Standby Readiness Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical slot without PG17's `failover => true` prevents the slot synchronization worker on physical standbys from mirroring the slot. Promoting the standby and attaching the CDC consumer causes missing slot errors, rewind anomalies, or duplicate events, while unmanaged WAL retention risks complete volume exhaustion. ```typescript import { Client } from 'pg'; // Anti-pattern: Default slot creation without PG17 failover flag export async function setupConsumerSlot(client: Client, slotName: string): Promise<void> { // Missing failover => true: standby will never synchronize this slot await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); } // Anti-pattern: Naive cutover without checking slot sync state or WAL invalidation export async function cutoverConsumer(standbyClient: Client, slotName: string): Promise<void> { // Assumes slot is immediately usable on standby, risking message loss or crash console.log(`Switching consumer to promoted standby using slot ${slotName}`); } ``` #### Production Standard (Correct) > **Standard**: Explicitly requests `failover => true` using Postgres 17 parameterization, verifies `sync_state === 's'` on the physical standby, inspects `wal_status` to detect slot invalidations from `max_slot_wal_keep_size`, and asserts LSN monotonic progression before cutover. ```typescript import { Client } from 'pg'; export interface StandbySlotVerification { slotName: string; isSynchronized: boolean; walStatus: 'normal' | 'extended' | 'unreserved' | 'lost'; confirmedFlushLsn: string; } // Production standard: Postgres 17 failover-enabled logical slot creation export async function createFailoverCapableSlot(primaryClient: Client, slotName: string): Promise<void> { await primaryClient.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, twophase => false, failover => true );`, [slotName] ); } // Production standard: Guardrail verifying standby synchronization before cutover export async function verifyStandbySlotCutover( standbyClient: Client, slotName: string, lastCommittedLsn: string ): Promise<StandbySlotVerification> { const result = await standbyClient.query<{ slot_name: string; sync_state: string; wal_status: 'normal' | 'extended' | 'unreserved' | 'lost'; confirmed_flush_lsn: string; failover: boolean; }>( `SELECT slot_name, sync_state, wal_status, confirmed_flush_lsn, failover FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (result.rows.length === 0) { throw new Error(`Slot '${slotName}' does not exist on standby. Check synchronized_standby_slots.`); } const slot = result.rows[0]; if (!slot.failover) { throw new Error(`Slot '${slotName}' is not configured with failover=true.`); } if (slot.wal_status === 'lost') { throw new Error(`Slot '${slotName}' invalidated (wal_status=lost). max_slot_wal_keep_size exceeded.`); } // sync_state 's' confirms slot is synchronized from primary if (slot.sync_state !== 's') { throw new Error(`Standby slot '${slotName}' is not synchronized (sync_state: '${slot.sync_state}').`); } // Validate standby replay has at least reached the consumer's known checkpoint const isLsnSafe = BigInt('0x' + slot.confirmed_flush_lsn.replace('/', '')) >= BigInt('0x' + lastCommittedLsn.replace('/', '')); if (!isLsnSafe) { throw new Error(`Standby LSN (${slot.confirmed_flush_lsn}) lags consumer LSN (${lastCommittedLsn}).`); } return { slotName: slot.slot_name, isSynchronized: true, walStatus: slot.wal_status, confirmedFlushLsn: slot.confirmed_flush_lsn }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication Slot Synchronization & Zero-Data-Loss Failover

Postgres 17 Logical Replication Slot Synchronization & Zero-Data-Loss Failover defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces native logical replication slot failover via physical standby synchronization ('failover => true' and 'sync_replication_slots'). Services and CDC orchestrators must strictly enforce slot synchronization state validation before standby cutover and mandate finite WAL retention limits to avoid either catastrophic disk exhaustion or silent data loss during 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: Uncoordinated Logical Slot Cutover and Unbounded WAL Accumulation Causing Split-Brain CDC and Node Crash 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	Uncoordinated Logical Slot Cutover and Unbounded WAL Accumulation Causing Split-Brain CDC and Node Crash	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` on PostgreSQL 17 when consuming CDC from high-availability topologies.

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.