PostgreSQL 17 Failover-Synchronized Logical Replication Slot Guardrail

--- description: PostgreSQL 17 Failover-Synchronized Logical Replication Slot Guardrail - Enforces failover-ready logical replication slots introduced in PostgreSQL 17 to prevent Change Data Capture (CDC) event stream divergence during physical replica promotions. Mandates failover configuration on primary logical slots and synchronizes standby slots using synchronized_standby_slots and sync_replication_slots. Guarantees zero data loss and prevents duplicate or skipped CDC events upon unplanned failovers. globs: src/**/*.ts, src/database/**/*.ts, src/infra/**/*.ts, src/cdc/**/*.ts, terraform/**/*.tf, ansible/**/*.yml, docker/**/*.conf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-failover-synchronized-logical-replication-guardrail" --- # PostgreSQL 17 Failover-Synchronized Logical Replication Slot Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating uncoordinated logical replication slots lacking synchronized failover metadata across physical standbys ## System Constraints You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 Failover-Synchronized Logical Replication Slot standards. In PostgreSQL 17+, logical slots can be synchronized to physical standbys using `failover = true` on the slot creation interface, accompanied by `synchronized_standby_slots` in postgresql.conf on the primary and `sync_replication_slots = true` on physical standbys. You must reject any raw slot creation commands (`pg_create_logical_replication_slot`) or migration scripts that omit `failover = true` or fail to verify standby slot coordination before allowing consumer ingestion. ## Strict Guidelines - Engineers MUST ALWAYS supply `failover = true` when calling `pg_create_logical_replication_slot` or configuring logical publication subscribers in PostgreSQL 17. - Engineers MUST NOT initialize CDC consumers (e.g., Debezium, Kafka Connect, custom WAL consumers) against uncoordinated primary slots without corresponding synchronized slots validated on all physical standbys. - Infrastructure configurations MUST define `synchronized_standby_slots` on the primary instance containing the physical application names of replication standbys required for high-availability quorum. - Physical standby instances MUST configure `sync_replication_slots = true` in postgresql.conf to allow the background slot synchronization worker to mirror primary logical slot metadata. - Application health checks and failover scripts MUST inspect `pg_replication_slots.sync_state` to verify standby slots are in 's' (synchronized) state before redirecting consumer connections post-promotion. - Components NEVER execute write operations relying on logical stream progress until confirming `confirmed_flush_lsn` matches or precedes the standby's promoted restart LSN. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Slot Provisioning & Validation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting the PG17 failover parameter creates a non-failover-ready logical slot. During a physical failover, the newly promoted replica will not contain this slot or its confirmed_flush_lsn, leading to lost or duplicated event streams upon CDC reconnect. ```typescript import { Client } from 'pg'; export async function initCdcSlot(client: Client, slotName: string, pluginName = 'pgoutput'): Promise<void> { // Anti-pattern: pg_create_logical_replication_slot without the failover boolean parameter. // In PG 17, omitting failover allows the slot to vanish or diverge when a physical standby promotes. const query = `SELECT slot_name, lsn FROM pg_create_logical_replication_slot($1, $2);`; await client.query(query, [slotName, pluginName]); } ``` #### Production Standard (Correct) > **Standard**: Invokes pg_create_logical_replication_slot with the 5th parameter (failover) set to true and validates that synchronized_standby_slots is actively defined, guaranteeing slot metadata persistence across standby promotion. ```typescript import { Client } from 'pg'; interface SlotCreationResult { slotName: string; consistentLsn: string; } export async function initFailoverSynchronizedSlot( client: Client, slotName: string, pluginName = 'pgoutput' ): Promise<SlotCreationResult> { // PG 17 Signature: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover) // Enforces failover = true to mirror the slot state to physical standbys specified in synchronized_standby_slots const createSlotQuery = ` SELECT slot_name, lsn FROM pg_create_logical_replication_slot($1, $2, false, false, true); `; const { rows } = await client.query(createSlotQuery, [slotName, pluginName]); const slot = rows[0]; // Verify that the primary has standby synchronization active const checkSyncQuery = ` SELECT setting FROM pg_settings WHERE name = 'synchronized_standby_slots'; `; const syncSettingRes = await client.query(checkSyncQuery); const configuredStandbys = syncSettingRes.rows[0]?.setting ?? ''; if (!configuredStandbys || configuredStandbys.trim() === '') { throw new Error( `Slot '${slotName}' created with failover=true, but 'synchronized_standby_slots' is empty on primary.` ); } return { slotName: slot.slot_name, consistentLsn: slot.lsn }; } ``` ### Post-Failover Consumer Reconnection Slot Validation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Failing to assert sync_state or failover readiness can cause a consumer to resume from an out-of-sync or uncoordinated slot, causing WAL replay divergence. ```typescript import { Client } from 'pg'; export async function assertStandbySlotReady(client: Client, slotName: string): Promise<boolean> { // Anti-pattern: Only checks existence; ignores sync_state on physical standbys const result = await client.query( 'SELECT slot_name FROM pg_replication_slots WHERE slot_name = $1;', [slotName] ); return result.rowCount !== null && result.rowCount > 0; } ``` #### Production Standard (Correct) > **Standard**: Guarantees that on standbys, the slot is verified to have failover enabled and is in sync state 's' before any failover handoff or client consumption occurs. ```typescript import { Client } from 'pg'; export async function assertStandbySlotReady(client: Client, slotName: string): Promise<boolean> { // Validates failover flag and sync_state ('s' indicates fully synchronized slot ready for promotion/consumption) const result = await client.query<{ slot_name: string; failover: boolean; sync_state: string | null; }>( `SELECT slot_name, failover, sync_state FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (result.rowCount === 0) { throw new Error(`Logical replication slot '${slotName}' does not exist on target host.`); } const slot = result.rows[0]; if (!slot.failover) { throw new Error(`Logical replication slot '${slotName}' exists but is not failover-ready (failover=false).`); } // When querying a standby prior to promotion, sync_state must be 's' (synchronized). // Post-promotion, sync_state becomes null/idle on the new primary. const isStandbyRes = await client.query<{ in_recovery: boolean }>('SELECT pg_is_in_recovery() AS in_recovery;'); const isStandby = isStandbyRes.rows[0]?.in_recovery ?? false; if (isStandby && slot.sync_state !== 's') { throw new Error(`Logical slot '${slotName}' is not synchronized (current sync_state: '${slot.sync_state}').`); } return true; } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Failover-Synchronized Logical Replication Slot Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-ready logical replication slots introduced in PostgreSQL 17 to prevent Change Data Capture (CDC) event stream divergence during physical replica promotions. Mandates failover configuration on primary logical slots and synchronizes standby slots using synchronized_standby_slots and sync_replication_slots. Guarantees zero data loss and prevents duplicate or skipped CDC events upon 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 uncoordinated logical replication slots lacking synchronized failover metadata across physical standbys 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 uncoordinated logical replication slots lacking synchronized failover metadata across physical standbys	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 supply `failover = true` when calling `pg_create_logical_replication_slot` or configuring logical publication subscribers in PostgreSQL 17.

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.