PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization Guardrail

--- description: PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization Guardrail - Enforces PostgreSQL 17 native logical slot failover mechanics (failover => true, primary synchronized_standby_slots, and standby sync_replication_slots = on) across database provisioning, connection initializers, and migration harnesses. Prevents downstream CDC stream invalidation, full table re-snapshots, and silent message omission during physical primary failovers. globs: src/database/**/*.ts, src/cdc/**/*.ts, src/infra/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-replication-failover-slot-sync-postgresql-17-fa-807" --- # PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Ephemeral Logical Slot Provisioning Lacking Failover Preservation Across Physical Standby Promotions ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Logical Replication Slot Synchronization Guardrails. Logical replication slots on PostgreSQL 17 MUST be created with failover => true, synchronized across physical replicas via synchronized_standby_slots on the primary, and synced on physical standbys via sync_replication_slots = on. Never allow logical slots to be created via legacy non-failover procedures in systems where physical standby promotions occur. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with failover => true in PostgreSQL 17 (pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)). - Engineers MUST verify that primary postgresql.conf defines synchronized_standby_slots containing all critical physical standbys before attaching downstream CDC pipelines. - Engineers MUST verify physical standbys enable sync_replication_slots = on and hot_standby_feedback = on to preserve logical decoding state. - Data pipeline orchestrators MUST query pg_replication_slots to verify 'failover = true' and 'synced = true' (on standby) prior to acknowledging pipeline bootstrap. - Engineers MUST NOT drop, re-create, or consume logical slots directly against physical standbys while sync_replication_slots is actively syncing those slots from the primary. - Services MUST catch error 55000 (object_in_use) or 42710 (duplicate_object) and gracefully assert slot failover metadata rather than blindly recreating slots. ## Bad vs Good Implementations ### PostgreSQL 17 Failover-Aware Logical Replication Slot Provisioner #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting the failover argument defaults failover to false. When a physical failover occurs, the logical replication slot vanishes from the newly promoted primary, breaking downstream consumers and causing irrecoverable replication lag or duplicate reads. ```typescript import { Client } from 'pg'; export async function initializeCdcSlot(client: Client, slotName: string): Promise<void> { // VULNERABLE: Legacy pre-PG17 logical slot creation without failover support. // When physical primary fails over to a standby, this slot does not exist on // the promoted standby, causing the CDC pipeline to crash or require full table re-snapshotting. await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } ``` #### Production Standard (Correct) > **Standard**: Validates engine compatibility, ensures primary-level standby synchronization settings are active, and explicitly invokes pg_create_logical_replication_slot with failover => true, ensuring the slot survives standby promotion with synchronized LSN state. ```typescript import { Client } from 'pg'; interface SlotMetadata { slot_name: string; plugin: string; failover: boolean; synced: boolean; confirmed_flush_lsn: string | null; } export async function initializeCdcSlot( client: Client, slotName: string, plugin: string = 'pgoutput' ): Promise<void> { // 1. Verify PostgreSQL version is 17+ 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(`PostgreSQL 170000+ required for failover slots. Found: ${versionNum}`); } // 2. Assert synchronized_standby_slots configuration on primary const syncSettingRes = await client.query<{ setting: string }>( "SELECT setting FROM pg_settings WHERE name = 'synchronized_standby_slots';" ); const syncSlots = syncSettingRes.rows[0]?.setting ?? ''; if (!syncSlots || syncSlots.trim() === '') { throw new Error( 'Invariant violation: synchronized_standby_slots must be configured on primary.' ); } // 3. Inspect if slot already exists and satisfies failover requirements const existingRes = await client.query<SlotMetadata>( `SELECT slot_name, plugin, failover, synced, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (existingRes.rowCount && existingRes.rowCount > 0) { const slot = existingRes.rows[0]; if (!slot.failover) { throw new Error( `Logical slot '${slotName}' exists but has failover=false. Manual intervention required.` ); } return; } // 4. Create logical slot with explicit failover => true and two_phase => false (or true if configured) await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => $2, temporary => false, two_phase => false, failover => true );`, [slotName, plugin] ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization Guardrail

PostgreSQL 17 Failover-Safe Logical Replication Slot Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 native logical slot failover mechanics (failover => true, primary synchronized_standby_slots, and standby sync_replication_slots = on) across database provisioning, connection initializers, and migration harnesses. Prevents downstream CDC stream invalidation, full table re-snapshots, and silent message omission during physical primary 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: Ephemeral Logical Slot Provisioning Lacking Failover Preservation Across Physical Standby Promotions 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	Ephemeral Logical Slot Provisioning Lacking Failover Preservation Across Physical Standby Promotions	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 in PostgreSQL 17 (pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)).

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.