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

Postgres 17 Failover Slot Logical Replication Synchronization Standard

Enforces failover-enabled logical replication slots and synchronized standby progression introduced in Postgres 17 for mission-critical Change Data Capture (CDC) architectures. Ensures downstream streaming consumers can immediately reconnect to promoted physical standbys without replication slot invalidation, stream rewind failures, or duplicate message amplification. Mandates explicit failover slot creation, standby slot synchronization configuration, and LSN-safe consumer reconnection protocols.

Prohibited Anti-Pattern: Creating unmanaged logical replication slots without failover sync, causing CDC consumer permanent failure or data loss upon standby promotion.

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 Failover Slot Logical Replication Synchronization Standard - Enforces failover-enabled logical replication slots and synchronized standby progression introduced in Postgres 17 for mission-critical Change Data Capture (CDC) architectures. Ensures downstream streaming consumers can immediately reconnect to promoted physical standbys without replication slot invalidation, stream rewind failures, or duplicate message amplification. Mandates explicit failover slot creation, standby slot synchronization configuration, and LSN-safe consumer reconnection protocols.
globs: src/database/**/*.{ts,js,py,go,sql}, migrations/**/*.{sql,ts}, infrastructure/**/*.{tf,yaml,yml,json}, src/cdc/**/*.{ts,js,py,go}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-failover-slot-cdc-invariant"
---

# Postgres 17 Failover Slot Logical Replication Synchronization Standard

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Creating unmanaged logical replication slots without failover sync, causing CDC consumer permanent failure or data loss upon standby promotion.

## System Constraints
You are a Principal Database Infrastructure Engineer enforcing Postgres 17 logical replication failover slot invariants. In PostgreSQL 17+, logical slots can be synchronized to physical standbys using `failover = true` and `sync_replication_slots = on`. You must mandate that all CDC initialization scripts, database migrations, connection lifecycle managers, and consumer orchestrators declare logical slots with `failover = true` and verify synchronization across `pg_replication_slots` prior to allowing physical standby promotion. Reject any raw logical slot provisioning that relies on pre-17 manual slot copying or ignores standby slot sync lag.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with the `failover` parameter set to `true` via `pg_create_logical_replication_slot(slot_name, plugin, false, false, true)` in Postgres 17+.
- Engineers MUST configure `synchronized_standby_slots` on the primary instance to include physical standbys that track downstream CDC consumers to prevent the primary from consuming WAL beyond standby replication checkpoints.
- Engineers MUST NOT execute physical failovers or promotions without verifying that `pg_replication_slots.sync_state` is `'s'` (synchronized) on the standby candidate to prevent WAL gap invalidation.
- CDC consumer clients MUST implement an idempotent reconnection loop with restart-LSN validation, ensuring that after primary failover, the client verifies `confirmed_flush_lsn` before consuming WAL blocks.
- Database migration scripts and terraform modules MUST NEVER drop or recreate logical slots during routine rollouts without verifying that `sync_replication_slots` is active in postgresql.conf on all standbys.

## Bad vs Good Implementations
### PostgreSQL 17 Failover Slot Creation & Dynamic Promotion Handler

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Legacy slot creation without the 5th boolean parameter (failover = true) prevents Postgres 17's native slot synchronization worker from copying the slot metadata and restart_lsn to physical standbys. Upon failover, downstream consumers fail permanently because the slot does not exist on the new primary.
```typescript
import { Client } from 'pg';

export async function setupCdcSlot(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Pre-PG17 style slot creation without failover=true
  // When primary dies, this slot does not exist on the promoted standby,
  // causing CDC engines (e.g. Debezium) to throw fatal 'slot does not exist' or drop data.
  await client.query(`
    SELECT slot_name, lsn 
    FROM pg_create_logical_replication_slot($1, 'pgoutput');
  `, [slotName]);
}

export async function reconnectAfterPromotion(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Blindly re-reading without checking slot synchronization status
  await client.query('LISTEN cdc_events;');
}
```

#### Production Standard (Correct)
> **Standard**: Leverages Postgres 17 native `failover = true` slot provisioning and inspects the `synced` and `failover` columns on failover targets before resuming ingestion, preventing broken CDC streams and data duplication.
```typescript
import { Client } from 'pg';

interface SlotStatus {
  slot_name: string;
  plugin: string;
  failover: boolean;
  synced: boolean;
  confirmed_flush_lsn: string;
}

export async function setupCdcSlotPg17(client: Client, slotName: string): Promise<void> {
  // Invariant: Postgres 17 pg_create_logical_replication_slot accepts:
  // (slot_name, plugin, temporary, two_phase, failover)
  // Setting failover = true ensures pg_sync_replication_slots syncs this to physical standbys.
  await client.query(`
    SELECT slot_name, lsn 
    FROM pg_create_logical_replication_slot($1, 'pgoutput', false, false, true);
  `, [slotName]);
}

export async function verifyAndResumeCdcConsumer(
  client: Client,
  slotName: string,
  lastAckedLsn: string
): Promise<void> {
  const result = await client.query<SlotStatus>(`
    SELECT slot_name, plugin, failover, synced, confirmed_flush_lsn
    FROM pg_replication_slots
    WHERE slot_name = $1;
  `, [slotName]);

  if (result.rows.length === 0) {
    throw new Error(`CRITICAL: Logical slot ${slotName} missing on current node. Failover sync failed.`);
  }

  const slot = result.rows[0];
  if (!slot.failover) {
    throw new Error(`CRITICAL: Slot ${slotName} exists but is not marked failover-safe.`);
  }

  // Ensure consumer does not resume before the synchronized confirmed flush LSN
  if (BigInt(`0x${slot.confirmed_flush_lsn.replace('/', '')}`) < BigInt(`0x${lastAckedLsn.replace('/', '')}`)) {
    throw new Error('CRITICAL: Target node restart LSN is behind last processed LSN. Potential duplicate storm or data loss.');
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL Diagnostic MCP Server
Executes diagnostic queries against pg_replication_slots, pg_stat_replication, and pg_stat_subscription to monitor failover slot lag.
Recommended Agent Skills (askills.dev)

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

Patroni HA Orchestration CLI
Validates synchronized slots before automated primary election and standby promotion in HA topologies.
Architectural Specification & Invariant Rationale

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

Postgres 17 Failover Slot Logical Replication Synchronization Standard defines an industrial-grade behavioral standard for backend engineering. Enforces failover-enabled logical replication slots and synchronized standby progression introduced in Postgres 17 for mission-critical Change Data Capture (CDC) architectures. Ensures downstream streaming consumers can immediately reconnect to promoted physical standbys without replication slot invalidation, stream rewind failures, or duplicate message amplification. Mandates explicit failover slot creation, standby slot synchronization configuration, and LSN-safe consumer reconnection protocols. 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 unmanaged logical replication slots without failover sync, causing CDC consumer permanent failure or data loss upon standby 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 unmanaged logical replication slots without failover sync, causing CDC consumer permanent failure or data loss upon standby promotion.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 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(slot_name, plugin, false, false, true)` in Postgres 17+.

Engineers MUST ALWAYS provision logical replication slots with the `failover` parameter set to `true` via `pg_create_logical_replication_slot(slot_name, plugin, false, false, true)` 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 the primary instance to include physical standbys that track downstream CDC consumers to prevent the primary from consuming WAL beyond standby replication checkpoints.

Engineers MUST configure `synchronized_standby_slots` on the primary instance to include physical standbys that track downstream CDC consumers to prevent the primary from consuming WAL beyond standby replication checkpoints.

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 NOT execute physical failovers or promotions without verifying that `pg_replication_slots.sync_state` is `'s'` (synchronized) on the standby candidate to prevent WAL gap invalidation.

Engineers MUST NOT execute physical failovers or promotions without verifying that `pg_replication_slots.sync_state` is `'s'` (synchronized) on the standby candidate to prevent WAL gap invalidation.

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 clients MUST implement an idempotent reconnection loop with restart-LSN validation, ensuring that after primary failover, the client verifies `confirmed_flush_lsn` before consuming WAL blocks.

CDC consumer clients MUST implement an idempotent reconnection loop with restart-LSN validation, ensuring that after primary failover, the client verifies `confirmed_flush_lsn` before consuming WAL blocks.

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

Database migration scripts and terraform modules MUST NEVER drop or recreate logical slots during routine rollouts without verifying that `sync_replication_slots` is active in postgresql.conf on all standbys.

Database migration scripts and terraform modules MUST NEVER drop or recreate logical slots during routine rollouts without verifying that `sync_replication_slots` is active in postgresql.conf on all 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.
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 Slot Logical Replication Synchronization Standard critical for AI coding assistants?

Enforces failover-enabled logical replication slots and synchronized standby progression introduced in Postgres 17 for mission-critical Change Data Capture (CDC) architectures. Ensures downstream streaming consumers can immediately reconnect to promoted physical standbys without replication slot invalidation, stream rewind failures, or duplicate message amplification. Mandates explicit failover slot creation, standby slot synchronization configuration, and LSN-safe consumer reconnection protocols. 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 unmanaged logical replication slots without failover sync, causing CDC consumer permanent failure or data loss upon standby 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