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-cdc-synchronization

PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail

Enforces failover-enabled logical replication slots ('failover = true') and physical standby synchronization ('sync_replication_slots = on') introduced in PostgreSQL 17. Eliminates orphaned slots and duplicate change data capture (CDC) streams during physical standby failovers by strictly locking LSN boundaries across the cluster.

Prohibited Anti-Pattern: Orphaned Logical Slots on Standby Promotion Triggering CDC Duplication or WAL Data Loss

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail - Enforces failover-enabled logical replication slots ('failover = true') and physical standby synchronization ('sync_replication_slots = on') introduced in PostgreSQL 17. Eliminates orphaned slots and duplicate change data capture (CDC) streams during physical standby failovers by strictly locking LSN boundaries across the cluster.
globs: src/**/*.ts, src/cdc/**/*.ts, src/database/**/*.ts, infra/**/*.sql
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-failover-slot-cdc-synchronization"
---

# PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Orphaned Logical Slots on Standby Promotion Triggering CDC Duplication or WAL Data Loss

## System Constraints
You are a Principal Software Architect enforcing PostgreSQL 17 Streaming Logical Replication Failover Slot Synchronization. In distributed Change Data Capture (CDC) architectures, promote zero-loss failover by guaranteeing that logical slots carry 'failover = true' and standby synchronization ('sync_replication_slots') invariants are validated before consumers bind to connection streams.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with the 'failover' parameter set to true via pg_create_logical_replication_slot or CREATE_REPLICATION_SLOT in Postgres 17+.
- Engineers MUST configure 'synchronized_standby_slots' on primary instances to match physical replication slot names, ensuring physical replication never surges past downstream logical consumer LSNs.
- Standby configurations MUST set 'sync_replication_slots = on' to continuously persist logical slot metadata and prevent slot loss upon physical promotion.
- CDC consumer initialization code MUST verify that the target node slot has 'failover' enabled and is not currently in a read-only synchronized state ('synced = true') before attempting replication attachment.
- Engineers MUST NEVER drop and re-create a missing replication slot upon failover; the promoted replica MUST inherit the existing synchronized slot to prevent re-snapshotting or dropped WAL segments.

## Bad vs Good Implementations
### PostgreSQL 17 Failover-Safe Logical Replication Slot Provisioner and Validator

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Creates a legacy logical slot lacking the PostgreSQL 17 'failover' parameter. When the primary fails over, the standby has no synced slot record, resulting in silent stream abandonment, stream duplication, or data loss.
```typescript
import { Client } from 'pg';

export async function initCdcSlot(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Pre-PG17 pattern without failover flag.
  // On physical failover, this slot disappears from the replica,
  // causing CDC subscribers to crash or re-snapshot from scratch.
  const checkQuery = `SELECT slot_name FROM pg_replication_slots WHERE slot_name = $1;`;
  const res = await client.query(checkQuery, [slotName]);

  if (res.rowCount === 0) {
    await client.query(
      `SELECT pg_create_logical_replication_slot($1, 'pgoutput', false);`,
      [slotName]
    );
  }
}
```

#### Production Standard (Correct)
> **Standard**: Validates engine version >= 17, enforces 'failover = true' on creation, and inspects the 'synced' column to ensure downstream consumers only attach to valid, promoted standalone slots without duplicating data.
```typescript
import { Client } from 'pg';

interface ReplicationSlotRow {
  slot_name: string;
  plugin: string;
  failover: boolean;
  synced: boolean;
  active: boolean;
}

export async function initFailoverSafeCdcSlot(client: Client, slotName: string): Promise<void> {
  // Invariant: Verify Postgres 17+ and inspect failover capability
  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('Postgres 17+ required for failover slot synchronization');
  }

  const slotQuery = `
    SELECT slot_name, plugin, failover, synced, active
    FROM pg_replication_slots
    WHERE slot_name = $1;
  `;
  const res = await client.query<ReplicationSlotRow>(slotQuery, [slotName]);

  if (res.rowCount === 0) {
    // Postgres 17 signature: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)
    await client.query(
      `SELECT pg_create_logical_replication_slot($1, 'pgoutput', false, false, true);`,
      [slotName]
    );
    return;
  }

  const slot = res.rows[0];
  if (!slot.failover) {
    throw new Error(`Replication slot '${slotName}' exists but failover is disabled. Reconfiguration required.`);
  }

  // When connecting to a promoted standby, ensure slot is converted from synced copy to local active slot
  if (slot.synced) {
    throw new Error(`Standby slot '${slotName}' is marked as synced and cannot be directly consumed until failover promotion completes.`);
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres MCP Server
Inspects PostgreSQL catalogs (pg_replication_slots, pg_stat_replication) to audit failover slot invariants.
Recommended Agent Skills (askills.dev)

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

Postgres Replication Health Verifier
Validates synchronized_standby_slots and sync_replication_slots parameters across cluster nodes.
Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-enabled logical replication slots ('failover = true') and physical standby synchronization ('sync_replication_slots = on') introduced in PostgreSQL 17. Eliminates orphaned slots and duplicate change data capture (CDC) streams during physical standby failovers by strictly locking LSN boundaries across the cluster. 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: Orphaned Logical Slots on Standby Promotion Triggering CDC Duplication or WAL Data Loss 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 DriftOrphaned Logical Slots on Standby Promotion Triggering CDC Duplication or WAL Data LossCritical
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 the 'failover' parameter set to true via pg_create_logical_replication_slot or CREATE_REPLICATION_SLOT in Postgres 17+.

Engineers MUST ALWAYS provision logical replication slots with the 'failover' parameter set to true via pg_create_logical_replication_slot or CREATE_REPLICATION_SLOT in Postgres 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.
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 configure 'synchronized_standby_slots' on primary instances to match physical replication slot names, ensuring physical replication never surges past downstream logical consumer LSNs.

Engineers MUST configure 'synchronized_standby_slots' on primary instances to match physical replication slot names, ensuring physical replication never surges past downstream logical consumer LSNs.

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

Standby configurations MUST set 'sync_replication_slots = on' to continuously persist logical slot metadata and prevent slot loss upon physical promotion.

Standby configurations MUST set 'sync_replication_slots = on' to continuously persist logical slot metadata and prevent slot loss upon physical promotion.

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

CDC consumer initialization code MUST verify that the target node slot has 'failover' enabled and is not currently in a read-only synchronized state ('synced = true') before attempting replication attachment.

CDC consumer initialization code MUST verify that the target node slot has 'failover' enabled and is not currently in a read-only synchronized state ('synced = true') before attempting replication attachment.

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

Engineers MUST NEVER drop and re-create a missing replication slot upon failover

Engineers MUST NEVER drop and re-create a missing replication slot upon failover; the promoted replica MUST inherit the existing synchronized slot to prevent re-snapshotting or dropped WAL segments.

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 Guardrail critical for AI coding assistants?

Enforces failover-enabled logical replication slots ('failover = true') and physical standby synchronization ('sync_replication_slots = on') introduced in PostgreSQL 17. Eliminates orphaned slots and duplicate change data capture (CDC) streams during physical standby failovers by strictly locking LSN boundaries across the cluster. 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 (Orphaned Logical Slots on Standby Promotion Triggering CDC Duplication or WAL Data Loss) 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