AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: pg17-logical-replication-failover-slot-sync-fence

PostgreSQL 17 Logical Replication Failover Slot Synchronization Fence Guardrail

Mandates that all logical replication slots consumed by CDC workers, event streamers, and subscribers in PostgreSQL 17+ HA clusters explicitly enable failover synchronization and enforce physical standby fencing. Prevents logical subscribers from advancing past unapplied physical replica WAL positions, eliminating consumer offset skips and silent data loss upon primary failover.

Prohibited Anti-Pattern: Unfenced Logical Slot Creation and Unguarded CDC Streaming Leading to Post-Failover Split-Brain and Data Skipping

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Logical Replication Failover Slot Synchronization Fence Guardrail - Mandates that all logical replication slots consumed by CDC workers, event streamers, and subscribers in PostgreSQL 17+ HA clusters explicitly enable failover synchronization and enforce physical standby fencing. Prevents logical subscribers from advancing past unapplied physical replica WAL positions, eliminating consumer offset skips and silent data loss upon primary failover.
globs: src/**/*.ts, src/db/**/*.ts, src/services/**/*.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-fence"
---

# PostgreSQL 17 Logical Replication Failover Slot Synchronization Fence Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unfenced Logical Slot Creation and Unguarded CDC Streaming Leading to Post-Failover Split-Brain and Data Skipping

## System Constraints
You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 High Availability Logical Replication Invariants. You must ensure all logical replication slots are provisioned with failover enablement, synchronized with physical standby slots via synchronized_standby_slots, and that application consumers verify slot synchronization state and LSN bounds prior to streaming changes.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with `failover => true` (PostgreSQL 17+) to ensure slot metadata and LSN progress replicate to physical standbys.
- Engineers MUST NOT allow logical replication workers or CDC clients to consume from slots on a primary unless `synchronized_standby_slots` is configured to fence logical decoding behind physical standby confirmation.
- Services MUST verify slot attributes (`failover = true` and `synced = false` on primary, or `synced = true` on promoted standby) during connection handshake before emitting consumer ACKs.
- Code SHOULD assert that the consumer's persisted flush LSN is within the target standby's valid WAL boundaries during post-failover reconnection, rejecting stale or split-brain connections.
- Components NEVER execute raw `CREATE_REPLICATION_SLOT` without the `FAILOVER` parameter or invoke `pg_create_logical_replication_slot` without setting `failover => true` on PostgreSQL 17 instances.

## Bad vs Good Implementations
### PostgreSQL 17 Logical Replication Slot Provisioning & HA Sync Fence Check

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Creates a logical replication slot omitting PostgreSQL 17's `failover` parameter. The slot is not synced to physical standby nodes via `sync_replication_slots`, and no check is made for `synchronized_standby_slots`. On HA failover to a replica, the consumer loses its sync boundary and encounters data loss or catastrophic offset mismatches.
```typescript
import { Client } from 'pg';

export async function bootstrapLogicalSlot(client: Client, slotName: string): Promise<void> {
  // VIOLATION: Creates standard logical slot without PG17 failover support.
  // Logical subscriber can race ahead of physical standbys.
  // If failover occurs, promoted standby will not contain this slot or its LSN,
  // causing CDC consumer offset skips or fatal crash on reconnection.
  await client.query(
    `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`,
    [slotName]
  );
}
```

#### Production Standard (Correct)
> **Standard**: Enforces PostgreSQL 17 requirements: verifies server version, confirms the primary's `synchronized_standby_slots` fences logical progression behind physical replicas, explicitly supplies `failover => true` to `pg_create_logical_replication_slot`, and asserts failover status in `pg_replication_slots`.
```typescript
import { Client } from 'pg';

interface SlotMetadata {
  slot_name: string;
  plugin: string;
  failover: boolean;
  confirmed_flush_lsn: string | null;
}

export async function bootstrapLogicalSlotSafe(
  client: Client,
  slotName: string,
  physicalStandbySlotName: string
): Promise<void> {
  // 1. Verify PostgreSQL 17+ server version
  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 17+ required for failover slots. Found version: ${versionNum}`);
  }

  // 2. Ensure primary fences logical slots behind physical standby slots
  const gucRes = await client.query<{ synchronized_standby_slots: string }>(
    'SHOW synchronized_standby_slots;'
  );
  const syncSlots = gucRes.rows[0].synchronized_standby_slots
    .split(',')
    .map((s) => s.trim());

  if (!syncSlots.includes(physicalStandbySlotName)) {
    throw new Error(
      `Failover fence violation: 'synchronized_standby_slots' must include '${physicalStandbySlotName}'`
    );
  }

  // 3. Create logical slot with failover enabled (PostgreSQL 17 syntax)
  await client.query(
    `SELECT pg_create_logical_replication_slot(
       slot_name => $1,
       plugin => 'pgoutput',
       temporary => false,
       twophase => true,
       failover => true
     );`,
    [slotName]
  );

  // 4. Assert slot status meets failover guarantees
  const slotCheck = await client.query<SlotMetadata>(
    `SELECT slot_name, plugin, failover, confirmed_flush_lsn
     FROM pg_replication_slots
     WHERE slot_name = $1;`,
    [slotName]
  );

  if (!slotCheck.rows[0]?.failover) {
    throw new Error(`Invariant failed: slot ${slotName} was not created with failover=true`);
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL MCP Server
Enables inspection of PostgreSQL catalogs, standby sync status, and replication slot configurations.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Postgres HA Failover Replication Audit CLI
CLI diagnostic utility for inspecting `pg_replication_slots`, `pg_stat_replication`, and PostgreSQL 17 `pg_sync_replication_slots()` state across HA topologies.
Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Failover Slot Synchronization Fence Guardrail defines an industrial-grade behavioral standard for backend engineering. Mandates that all logical replication slots consumed by CDC workers, event streamers, and subscribers in PostgreSQL 17+ HA clusters explicitly enable failover synchronization and enforce physical standby fencing. Prevents logical subscribers from advancing past unapplied physical replica WAL positions, eliminating consumer offset skips and silent data loss upon primary 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: Unfenced Logical Slot Creation and Unguarded CDC Streaming Leading to Post-Failover Split-Brain and Data Skipping 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnfenced Logical Slot Creation and Unguarded CDC Streaming Leading to Post-Failover Split-Brain and Data SkippingCritical
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 InconsistencyAI 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:

1

Engineers MUST ALWAYS provision logical replication slots with `failover => true` (PostgreSQL 17+) to ensure slot metadata and LSN progress replicate to physical standbys.

Engineers MUST ALWAYS provision logical replication slots with `failover => true` (PostgreSQL 17+) to ensure slot metadata and LSN progress replicate to physical standbys.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST NOT allow logical replication workers or CDC clients to consume from slots on a primary unless `synchronized_standby_slots` is configured to fence logical decoding behind physical standby confirmation.

Engineers MUST NOT allow logical replication workers or CDC clients to consume from slots on a primary unless `synchronized_standby_slots` is configured to fence logical decoding behind physical standby confirmation.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Services MUST verify slot attributes (`failover = true` and `synced = false` on primary, or `synced = true` on promoted standby) during connection handshake before emitting consumer ACKs.

Services MUST verify slot attributes (`failover = true` and `synced = false` on primary, or `synced = true` on promoted standby) during connection handshake before emitting consumer ACKs.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Code SHOULD assert that the consumer's persisted flush LSN is within the target standby's valid WAL boundaries during post-failover reconnection, rejecting stale or split-brain connections.

Code SHOULD assert that the consumer's persisted flush LSN is within the target standby's valid WAL boundaries during post-failover reconnection, rejecting stale or split-brain connections.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Components NEVER execute raw `CREATE_REPLICATION_SLOT` without the `FAILOVER` parameter or invoke `pg_create_logical_replication_slot` without setting `failover => true` on PostgreSQL 17 instances.

Components NEVER execute raw `CREATE_REPLICATION_SLOT` without the `FAILOVER` parameter or invoke `pg_create_logical_replication_slot` without setting `failover => true` on PostgreSQL 17 instances.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is PostgreSQL 17 Logical Replication Failover Slot Synchronization Fence Guardrail critical for AI coding assistants?

Mandates that all logical replication slots consumed by CDC workers, event streamers, and subscribers in PostgreSQL 17+ HA clusters explicitly enable failover synchronization and enforce physical standby fencing. Prevents logical subscribers from advancing past unapplied physical replica WAL positions, eliminating consumer offset skips and silent data loss upon primary failover. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unfenced Logical Slot Creation and Unguarded CDC Streaming Leading to Post-Failover Split-Brain and Data Skipping) 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.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

PostgreSQL