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-failover-synchronized-logical-replication-slots

Postgres 17 Failover-Synchronized Logical Replication Slots Guardrail

Enforces failover-synchronized logical replication slot configurations and consumer reconnection contracts introduced in PostgreSQL 17. Guarantees zero data loss and eliminates manual slot rewinding or schema re-syncing during physical replica promotion.

Prohibited Anti-Pattern: Creating unflagged logical replication slots without failover coordination or primary synchronized standby validation

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 Failover-Synchronized Logical Replication Slots Guardrail - Enforces failover-synchronized logical replication slot configurations and consumer reconnection contracts introduced in PostgreSQL 17. Guarantees zero data loss and eliminates manual slot rewinding or schema re-syncing during physical replica promotion.
globs: src/**/*.ts, infra/**/*.ts, migrations/**/*.sql
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-failover-synchronized-logical-replication-slots"
---

# Postgres 17 Failover-Synchronized Logical Replication Slots Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Creating unflagged logical replication slots without failover coordination or primary synchronized standby validation

## System Constraints
You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 failover-synchronized logical replication slot configurations. You must prevent data loss, double-processing, and manual schema re-syncs during physical standby promotion by enforcing the failover parameter on logical slots, verifying synchronized_standby_slots synchronization on the primary, and validating standby slot-sync worker health before CDC consumers reconnect.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with the `failover := true` parameter when invoking `pg_create_logical_replication_slot` on PostgreSQL 17+.
- Engineers MUST NOT permit CDC consumers to connect to replication slots until confirming that physical standby instances are registered in `synchronized_standby_slots` on the primary.
- Code SHOULD verify the `synced` column in `pg_replication_slots` on physical standbys to confirm the standby sync worker (`sync_replication_slots = on`) is actively mirroring slot positions.
- Services MUST implement exponential backoff reconnection strategies that preserve downstream confirmed flush LSNs rather than recreating dropped or missing slots upon failover.
- Components NEVER execute destructive slot recreation (`pg_drop_replication_slot`) on failover error paths; missing synchronized slots must trigger failover alerts to maintain RPO=0 invariants.

## Bad vs Good Implementations
### Provisioning and Connecting to Failover-Ready Logical Slots in Postgres 17

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Legacy `pg_create_logical_replication_slot` invocations omit PostgreSQL 17's `failover` boolean parameter. When the primary physical instance fails, promoted standbys possess no state for this slot, forcing downstream consumers to rebuild state from scratch and risk data loss or massive duplication.
```typescript
import { Client } from 'pg';

export async function setupCdcStream(client: Client, slotName: string): Promise<void> {
  // ANTI-PATTERN: Creates legacy logical slot without failover parameter.
  // Standbys will not mirror this slot; replica promotion requires a full re-snapshot.
  const slotExists = await client.query(
    'SELECT 1 FROM pg_replication_slots WHERE slot_name = $1',
    [slotName]
  );

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

  // Unchecked consumption begins without checking primary/standby sync coordination
  console.log(`Consuming from slot: ${slotName}`);
}
```

#### Production Standard (Correct)
> **Standard**: Verifies that PostgreSQL 17's `synchronized_standby_slots` includes the physical standby application name to prevent primary WAL advancing beyond what the standby has mirrored. Uses `failover := true` when creating the logical slot, ensuring physical standbys replicate the slot via the built-in slot-sync worker.
```typescript
import { Client } from 'pg';

interface ReplicationSlotConfig {
  slotName: string;
  plugin?: string;
  primaryPhysicalStandbyAppName: string;
}

export async function setupFailoverSynchronizedSlot(
  client: Client,
  config: ReplicationSlotConfig
): Promise<void> {
  const { slotName, plugin = 'pgoutput', primaryPhysicalStandbyAppName } = config;

  // 1. Verify primary cluster enforces synchronized standby slots for HA safety
  const syncSlotsRes = await client.query(
    "SELECT current_setting('synchronized_standby_slots', true) AS sync_standbys"
  );
  const configuredStandbys = syncSlotsRes.rows[0]?.sync_standbys ?? '';

  if (!configuredStandbys.includes(primaryPhysicalStandbyAppName)) {
    throw new Error(
      `Invariant violation: physical standby '${primaryPhysicalStandbyAppName}' ` +
      `is not configured in PostgreSQL 17 'synchronized_standby_slots'. Failover synchronization disabled.`
    );
  }

  // 2. Provision or validate Postgres 17 slot with failover := true
  const existingSlot = await client.query(
    'SELECT slot_name, failover, synced FROM pg_replication_slots WHERE slot_name = $1',
    [slotName]
  );

  if (existingSlot.rowCount === 0) {
    // PG 17 syntax: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)
    await client.query(
      'SELECT pg_create_logical_replication_slot($1, $2, false, false, true)',
      [slotName, plugin]
    );
  } else {
    const slot = existingSlot.rows[0];
    if (!slot.failover) {
      throw new Error(
        `Logical slot '${slotName}' exists but was created with failover=false. Re-creation required.`
      );
    }
  }

  console.info(`Slot '${slotName}' verified with failover-synchronization enabled.`);
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres MCP Server
Introspects live PostgreSQL 17 server parameters, replication slots, and sync worker statuses.
Recommended Agent Skills (askills.dev)

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

Patroni CLI
High-availability clustering tool that automates standby promotion and logical slot synchronization.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover-Synchronized Logical Replication Slots Guardrail

Postgres 17 Failover-Synchronized Logical Replication Slots Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-synchronized logical replication slot configurations and consumer reconnection contracts introduced in PostgreSQL 17. Guarantees zero data loss and eliminates manual slot rewinding or schema re-syncing during physical replica promotion. 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 unflagged logical replication slots without failover coordination or primary synchronized standby validation 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 unflagged logical replication slots without failover coordination or primary synchronized standby validationCritical
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

Engineers MUST ALWAYS provision logical replication slots with the `failover := true` parameter when invoking `pg_create_logical_replication_slot` on 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.
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 permit CDC consumers to connect to replication slots until confirming that physical standby instances are registered in `synchronized_standby_slots` on the primary.

Engineers MUST NOT permit CDC consumers to connect to replication slots until confirming that physical standby instances are registered in `synchronized_standby_slots` on the primary.

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

Code SHOULD verify the `synced` column in `pg_replication_slots` on physical standbys to confirm the standby sync worker (`sync_replication_slots = on`) is actively mirroring slot positions.

Code SHOULD verify the `synced` column in `pg_replication_slots` on physical standbys to confirm the standby sync worker (`sync_replication_slots = on`) is actively mirroring slot 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.
4

Services MUST implement exponential backoff reconnection strategies that preserve downstream confirmed flush LSNs rather than recreating dropped or missing slots upon failover.

Services MUST implement exponential backoff reconnection strategies that preserve downstream confirmed flush LSNs rather than recreating dropped or missing slots upon failover.

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 destructive slot recreation (`pg_drop_replication_slot`) on failover error paths

Components NEVER execute destructive slot recreation (`pg_drop_replication_slot`) on failover error paths; missing synchronized slots must trigger failover alerts to maintain RPO=0 invariants.

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 Failover-Synchronized Logical Replication Slots Guardrail critical for AI coding assistants?

Enforces failover-synchronized logical replication slot configurations and consumer reconnection contracts introduced in PostgreSQL 17. Guarantees zero data loss and eliminates manual slot rewinding or schema re-syncing during physical replica promotion. 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 unflagged logical replication slots without failover coordination or primary synchronized standby validation) 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