Postgres 17 Logical Replication Failover Slot Synchronization Guardrail

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unsynchronized Logical Slots and Missing Standby Slot Coordinates Leading to Broken Replication Streams on Failover) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Postgres 17 Logical Replication Failover Slot Synchronization Guardrail

Enforces failover-ready logical replication architectures on PostgreSQL 17+ by mandating synchronized replication slots (`sync_replication_slots = true`), physical standby slot retention (`standby_slot_names`), and persistent `failover = true` creation flags. This architectural invariant eliminates data loss, duplicate stream processing, and expensive full-table resynchronizations when downstream logical subscribers transition across promoted physical standby replicas during primary node failover.

--- description: Postgres 17 Logical Replication Failover Slot Synchronization Guardrail - Enforces failover-ready logical replication architectures on PostgreSQL 17+ by mandating synchronized replication slots (`sync_replication_slots = true`), physical standby slot retention (`standby_slot_names`), and persistent `failover = true` creation flags. This architectural invariant eliminates data loss, duplicate stream processing, and expensive full-table resynchronizations when downstream logical subscribers transition across promoted physical standby replicas during primary node failover. globs: infra/**/*.sql, migrations/**/*.sql, src/database/**/*.ts, src/infra/**/*.ts, docker/postgres/**/*.conf, helm/**/*.yaml alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-failover-slot-sync-invariant-postgres-17-fail-725" --- # Postgres 17 Logical Replication Failover Slot Synchronization Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots and Missing Standby Slot Coordinates Leading to Broken Replication Streams on Failover ## System Constraints You are a Principal Database Reliability Engineer and Distributed Systems Architect enforcing the Postgres 17 Logical Replication Failover Slot Synchronization Invariant. Your mandate is to prevent silent data corruption, lost CDC offsets, and broken logical replication pipelines during primary database promotions. You must reject any logical replication provisioning code, migration scripts, or cluster configuration lacking explicit failover preservation, synchronized standby coordination, or pg_createsubscriber / sync_replication_slots compliance. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with the `failover = true` parameter enabled (`pg_create_logical_replication_slot(slot_name, plugin, false, false, true)`) on PostgreSQL 17+ primaries. - Engineers MUST ALWAYS register physical replication standby slot names in the primary's `standby_slot_names` configuration array to guarantee that WAL records are never recycled before physical standbys consume them. - Engineers MUST ALWAYS enable `sync_replication_slots = true` in physical standby configuration (`postgresql.conf`) to ensure background worker processes synchronize logical slot LSN positions continuously from the primary. - Code MUST NEVER connect logical subscribers directly to physical replicas without validating that the targeted slot has `synced = true` via `pg_replication_slots`. - Infrastructure orchestration scripts MUST NOT perform manual database failovers without asserting `pg_sync_replication_slots()` completion or confirming standby lag is zero across all logical decoding slots. - Database connection pooling and dynamic routing logic MUST ensure logical subscribers reconnect to the promoted standby using the exact same slot name without executing a destructive `pg_drop_replication_slot` or triggering a table re-initialization (`copy_data = true`). ## Bad vs Good Implementations ### Postgres 17 Logical Replication Slot Provisioning & Standby Coordination #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical slot without the 5th parameter `failover = true` prevents the slot from being marked for synchronization to physical standbys. Upon primary failover, the promoted node lacks the slot metadata and confirmed flush LSN, forcing downstream subscribers to abort and requiring complete, service-impacting table re-syncs. ```typescript import { Client } from 'pg'; export async function setupLogicalSlot(client: Client, slotName: string): Promise<void> { // Anti-Pattern: Omits PostgreSQL 17 failover parameter; // logical slot will NOT exist on the standby after failover promotion. await client.query(` SELECT pg_create_logical_replication_slot($1, 'pgoutput'); `, [slotName]); // Subscriber is created without failover awareness await client.query(` CREATE SUBSCRIPTION app_subscription CONNECTION 'host=pg-primary dbname=app user=rep_user password=secret' PUBLICATION app_publication WITH (create_slot = false, slot_name = $1); `, [slotName]); } ``` #### Production Standard (Correct) > **Standard**: Validates engine version >= 17, enforces that WAL retention covers physical replicas via `standby_slot_names`, and invokes `pg_create_logical_replication_slot` with `failover = true` alongside matching subscription options. Standby nodes synchronize this slot seamlessly, allowing instant subscriber continuity post-failover without data loss or table resynchronization. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; plugin?: string; standbySlotName: string; } export async function setupFailoverReadyLogicalSlot( primaryClient: Client, config: ReplicationSlotConfig ): Promise<void> { const plugin = config.plugin ?? 'pgoutput'; // 1. Verify PostgreSQL server major version is 17+ const verRes = await primaryClient.query('SHOW server_version_num;'); const versionNum = parseInt(verRes.rows[0].server_version_num, 10); if (versionNum < 170000) { throw new Error('Failover slot synchronization requires PostgreSQL 17 or higher.'); } // 2. Validate physical standby is tracked in standby_slot_names to prevent WAL truncation const standbyRes = await primaryClient.query('SHOW standby_slot_names;'); const activeStandbySlots: string = standbyRes.rows[0].standby_slot_names || ''; if (!activeStandbySlots.includes(config.standbySlotName)) { throw new Error(`Standby slot ${config.standbySlotName} missing from primary standby_slot_names invariant.`); } // 3. Create logical slot with explicit failover enabled: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover) await primaryClient.query(` SELECT slot_name, lsn FROM pg_create_logical_replication_slot( $1, $2, false, false, true ); `, [config.slotName, plugin]); // 4. Create subscription configured with failover = true matching upstream contract await primaryClient.query(` CREATE SUBSCRIPTION app_subscription CONNECTION 'host=pg-primary dbname=app user=rep_user password=secret' PUBLICATION app_publication WITH ( create_slot = false, slot_name = $1, failover = true ); `, [config.slotName]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Replication Failover Slot Synchronization Guardrail

Postgres 17 Logical Replication Failover Slot Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-ready logical replication architectures on PostgreSQL 17+ by mandating synchronized replication slots (`sync_replication_slots = true`), physical standby slot retention (`standby_slot_names`), and persistent `failover = true` creation flags. This architectural invariant eliminates data loss, duplicate stream processing, and expensive full-table resynchronizations when downstream logical subscribers transition across promoted physical standby replicas during primary node failover. 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 Slots and Missing Standby Slot Coordinates Leading to Broken Replication Streams on Failover 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 Slots and Missing Standby Slot Coordinates Leading to Broken Replication Streams on Failover	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 the `failover = true` parameter enabled (`pg_create_logical_replication_slot(slot_name, plugin, false, false, true)`) on PostgreSQL 17+ primaries.

Engineers MUST ALWAYS provision logical replication slots with the `failover = true` parameter enabled (`pg_create_logical_replication_slot(slot_name, plugin, false, false, true)`) on PostgreSQL 17+ primaries.

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.