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: postgres-17-failover-slot-preservation

Postgres 17 CDC Logical Replication Failover Slot Synchronization & Invariant Guardrail

Enforces failover-safe logical replication slots introduced in PostgreSQL 17 to prevent replication stream corruption and data gaps during planned or unplanned database failovers. Mandates that CDC subscriber services initialize slots with failover enabled, synchronize slots on physical standby replicas, and verify LSN progression before reconnecting downstream consumers. Guarantees zero data loss across physical promotions by preventing subscriber consumption past un-replicated physical WAL boundaries.

Prohibited Anti-Pattern: Creating Logical Slots Without Failover Synchronization Causing Missing CDC Events and Slot Re-Creation on Replica Promotion

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 CDC Logical Replication Failover Slot Synchronization & Invariant Guardrail - Enforces failover-safe logical replication slots introduced in PostgreSQL 17 to prevent replication stream corruption and data gaps during planned or unplanned database failovers. Mandates that CDC subscriber services initialize slots with failover enabled, synchronize slots on physical standby replicas, and verify LSN progression before reconnecting downstream consumers. Guarantees zero data loss across physical promotions by preventing subscriber consumption past un-replicated physical WAL boundaries.
globs: src/database/**/*.{ts,js,sql}, src/cdc/**/*.{ts,js}, src/infrastructure/**/*.{ts,js,tf}, infra/**/*.{ts,js,tf,sql}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-failover-slot-preservation"
---

# Postgres 17 CDC Logical Replication Failover Slot Synchronization & Invariant Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Creating Logical Slots Without Failover Synchronization Causing Missing CDC Events and Slot Re-Creation on Replica Promotion

## System Constraints
You are a Principal Database Reliability Engineer enforcing Postgres 17 CDC Logical Replication Failover Slot Preservation. You must mandate that all logical replication slots utilize Postgres 17's native `failover = true` slot configuration, that physical standby nodes run `sync_replication_slots = true`, and that client CDC connection managers perform topology-aware reconnections that validate slot sync readiness before restarting logical consumption.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with `failover = true` in Postgres 17+ environments (`pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, false, true)` or via modern driver wrappers).
- Engineers MUST NOT create unmanaged, non-failover slots on primary instances when physical standby replicas exist, as promotion will wipe out subscriber state and force re-snapshots.
- Engineers MUST configure `synchronized_standby_slots` on the primary instance to include physical replication standbys, guaranteeing the primary does not emit logical changes before standbys have persisted corresponding WAL records.
- Engineers MUST configure `sync_replication_slots = true` on hot standby replicas to enable background slot synchronization via the slot sync worker.
- Client connection managers and CDC workers MUST verify `pg_replication_slots.sync_state` on promoted standbys before initiating streaming consumption to avoid consuming from stale LSN positions.
- Automated migration and provisioning code MUST assert that the target PostgreSQL engine major version is >= 17 before relying on native logical slot failover parameters.
- Health checks and orchestrators MUST monitor replication slot lag (`pg_current_wal_lsn() - confirmed_flush_lsn`) and alert if standby slot synchronization falls behind configured thresholds.

## Bad vs Good Implementations
### PostgreSQL 17 Failover Slot Initialization in Type-Safe CDC Connector

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Creating the slot with default parameters creates an unmanaged logical slot without the failover flag. Standby nodes will not synchronize this slot via sync_replication_slots, causing the slot to vanish when the primary crashes and a standby is promoted.
```typescript
import { Client } from 'pg';

export async function initCdcSlot(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Creates standard logical replication slot without Postgres 17 failover flag.
  // Standby nodes will not synchronize this slot, leading to data loss or broken streams on failover.
  await client.query(
    `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`,
    [slotName]
  );
}
```

#### Production Standard (Correct)
> **Standard**: Verifies engine version >= 170000 and explicitly invokes pg_create_logical_replication_slot with the 5th parameter set to failover = true, ensuring the slot is synchronized across standbys.
```typescript
import { Client } from 'pg';

interface SlotCreationOptions {
  slotName: string;
  plugin?: string;
  failover?: boolean;
}

export async function initCdcSlot(
  client: Client,
  options: SlotCreationOptions
): Promise<{ slotName: string; consistentPoint: string }> {
  const { slotName, plugin = 'pgoutput', failover = true } = options;

  // Assert PostgreSQL engine version >= 17 for native failover slot support
  const versionRes = await client.query('SHOW server_version_num;');
  const serverVersion = parseInt(versionRes.rows[0].server_version_num, 10);

  if (serverVersion < 170000 && failover) {
    throw new Error('Native logical replication failover slot preservation requires PostgreSQL 17 or higher.');
  }

  // Postgres 17 syntax: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)
  // Parameters: slot_name, plugin, temporary (false), two_phase (false), failover (true)
  const query = `
    SELECT slot_name, consistent_point 
    FROM pg_create_logical_replication_slot($1, $2, false, false, $3);
  `;
  const result = await client.query(query, [slotName, plugin, failover]);

  return {
    slotName: result.rows[0].slot_name,
    consistentPoint: result.rows[0].consistent_point,
  };
}
```

### Failover Health Guard: Validating Slot Sync State Before Consumer Attachment

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Does not verify whether the promoted node has fully caught up or whether the slot is in a valid synced state ('i' or 's'), risking consumer reconnection against stale WAL boundaries.
```typescript
import { Client } from 'pg';

export async function attachConsumerToSlot(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Blindly attaches CDC consumer to promoted node without verifying slot synchronization status
  const res = await client.query('SELECT in_use FROM pg_replication_slots WHERE slot_name = $1;', [slotName]);
  if (res.rows.length === 0) {
    throw new Error('Slot does not exist');
  }
  console.log(`Attached to ${slotName}`);
}
```

#### Production Standard (Correct)
> **Standard**: Validates the sync_state, synced status, active flag, and whether the node is a primary or standby prior to consumer attachment, ensuring data consistency across failover boundaries.
```typescript
import { Client } from 'pg';

export interface SlotSyncStatus {
  slotName: string;
  synced: boolean;
  confirmedFlushLsn: string;
  syncState: string | null;
}

export async function verifySlotReadyForConsumer(
  client: Client,
  slotName: string
): Promise<SlotSyncStatus> {
  const query = `
    SELECT 
      slot_name,
      synced,
      sync_state,
      confirmed_flush_lsn,
      active
    FROM pg_replication_slots
    WHERE slot_name = $1;
  `;
  const { rows } = await client.query(query, [slotName]);

  if (rows.length === 0) {
    throw new Error(`Replication slot '${slotName}' does not exist on target database node.`);
  }

  const slot = rows[0];

  if (slot.active) {
    throw new Error(`Replication slot '${slotName}' is currently active and locked by another process.`);
  }

  // Check if server is in recovery (standby)
  const recoveryRes = await client.query('SELECT pg_is_in_recovery() AS in_recovery;');
  const isStandby = recoveryRes.rows[0].in_recovery;

  if (isStandby) {
    // On standbys, logical slots cannot be directly consumed; sync_state must be 's' (synchronized)
    if (slot.sync_state !== 's') {
      throw new Error(`Standby slot '${slotName}' is not synchronized (sync_state: ${slot.sync_state}). Cannot prepare for failover.`);
    }
  }

  return {
    slotName: slot.slot_name,
    synced: slot.synced,
    confirmedFlushLsn: slot.confirmed_flush_lsn,
    syncState: slot.sync_state,
  };
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres Inspector MCP
Direct read-only inspection of Postgres 17 replication slot states, sync workers, and LSN progression.
Recommended Agent Skills (askills.dev)

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

Postgres Slot Sync Validator CLI
Validates failover slots, synchronized standby slots parameter configuration, and standby synchronization latency.
Architectural Specification & Invariant Rationale

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

Postgres 17 CDC Logical Replication Failover Slot Synchronization & Invariant Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-safe logical replication slots introduced in PostgreSQL 17 to prevent replication stream corruption and data gaps during planned or unplanned database failovers. Mandates that CDC subscriber services initialize slots with failover enabled, synchronize slots on physical standby replicas, and verify LSN progression before reconnecting downstream consumers. Guarantees zero data loss across physical promotions by preventing subscriber consumption past un-replicated physical WAL boundaries. 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 Logical Slots Without Failover Synchronization Causing Missing CDC Events and Slot Re-Creation on Replica Promotion 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 DriftCreating Logical Slots Without Failover Synchronization Causing Missing CDC Events and Slot Re-Creation on Replica PromotionCritical
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` in Postgres 17+ environments (`pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, false, true)` or via modern driver wrappers).

Engineers MUST ALWAYS provision logical replication slots with `failover = true` in Postgres 17+ environments (`pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, false, true)` or via modern driver wrappers).

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 create unmanaged, non-failover slots on primary instances when physical standby replicas exist, as promotion will wipe out subscriber state and force re-snapshots.

Engineers MUST NOT create unmanaged, non-failover slots on primary instances when physical standby replicas exist, as promotion will wipe out subscriber state and force re-snapshots.

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

Engineers MUST configure `synchronized_standby_slots` on the primary instance to include physical replication standbys, guaranteeing the primary does not emit logical changes before standbys have persisted corresponding WAL records.

Engineers MUST configure `synchronized_standby_slots` on the primary instance to include physical replication standbys, guaranteeing the primary does not emit logical changes before standbys have persisted corresponding WAL records.

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

Engineers MUST configure `sync_replication_slots = true` on hot standby replicas to enable background slot synchronization via the slot sync worker.

Engineers MUST configure `sync_replication_slots = true` on hot standby replicas to enable background slot synchronization via the slot sync worker.

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

Client connection managers and CDC workers MUST verify `pg_replication_slots.sync_state` on promoted standbys before initiating streaming consumption to avoid consuming from stale LSN positions.

Client connection managers and CDC workers MUST verify `pg_replication_slots.sync_state` on promoted standbys before initiating streaming consumption to avoid consuming from stale LSN positions.

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.
6

Automated migration and provisioning code MUST assert that the target PostgreSQL engine major version is >= 17 before relying on native logical slot failover parameters.

Automated migration and provisioning code MUST assert that the target PostgreSQL engine major version is >= 17 before relying on native logical slot failover parameters.

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.
7

Health checks and orchestrators MUST monitor replication slot lag (`pg_current_wal_lsn() - confirmed_flush_lsn`) and alert if standby slot synchronization falls behind configured thresholds.

Health checks and orchestrators MUST monitor replication slot lag (`pg_current_wal_lsn() - confirmed_flush_lsn`) and alert if standby slot synchronization falls behind configured thresholds.

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 Postgres 17 CDC Logical Replication Failover Slot Synchronization & Invariant Guardrail critical for AI coding assistants?

Enforces failover-safe logical replication slots introduced in PostgreSQL 17 to prevent replication stream corruption and data gaps during planned or unplanned database failovers. Mandates that CDC subscriber services initialize slots with failover enabled, synchronize slots on physical standby replicas, and verify LSN progression before reconnecting downstream consumers. Guarantees zero data loss across physical promotions by preventing subscriber consumption past un-replicated physical WAL boundaries. 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 (Creating Logical Slots Without Failover Synchronization Causing Missing CDC Events and Slot Re-Creation on Replica Promotion) 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

Postgres