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-synchronization-invariant-postgres-17-fail-333

PostgreSQL 17 Failover Slot Synchronization and CDC Loss Prevention Invariant

Enforces failover-safe logical replication slot synchronization across PostgreSQL 17 high-availability clusters using the native failover slot sync protocol (`sync_replication_slots` and `pg_sync_replication_slots`). Guarantees downstream Change Data Capture (CDC) engines avoid data loss, non-recoverable slot invalidation, or massive duplicate replay bursts upon standby promotion. Mandates strict verification of `synchronized = true` on hot standbys and physical slot synchronization prerequisites before promoting standby nodes.

Prohibited Anti-Pattern: Unsynchronized Logical Replication Slots Triggering CDC Rewind, Duplicate Streaming, or Silent Event Loss Post-Failover

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Failover Slot Synchronization and CDC Loss Prevention Invariant - Enforces failover-safe logical replication slot synchronization across PostgreSQL 17 high-availability clusters using the native failover slot sync protocol (`sync_replication_slots` and `pg_sync_replication_slots`). Guarantees downstream Change Data Capture (CDC) engines avoid data loss, non-recoverable slot invalidation, or massive duplicate replay bursts upon standby promotion. Mandates strict verification of `synchronized = true` on hot standbys and physical slot synchronization prerequisites before promoting standby nodes.
globs: src/infra/database/**/*.ts, src/database/**/*.ts, src/services/cdc/**/*.ts, src/scripts/ha/**/*.ts, infra/**/*.tf, k8s/**/*.yaml
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-failover-slot-synchronization-invariant-postgres-17-fail-333"
---

# PostgreSQL 17 Failover Slot Synchronization and CDC Loss Prevention Invariant

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unsynchronized Logical Replication Slots Triggering CDC Rewind, Duplicate Streaming, or Silent Event Loss Post-Failover

## System Constraints
You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 failover slot synchronization invariants. PostgreSQL 17 introduces native logical replication slot synchronization across physical standbys via `sync_replication_slots = true` and `pg_create_logical_replication_slot(..., failover => true)`. You must reject any high-availability failover automation, database provisioning script, or CDC connector setup that omits slot synchronization guarantees, fails to verify `synchronized` flags on standby instances, or risks promoting a standby whose confirmed flush LSN lags behind the primary's logical slots.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with `failover => true` on PostgreSQL 17 primary instances intended for CDC consumers (e.g., Debezium, Kafka Connect, custom tailers).
- Engineers MUST ALWAYS configure standby PostgreSQL 17 instances with `sync_replication_slots = true` and configure `synchronized_standby_slots` on the primary instance to include physical replication standbys prior to logical advancement.
- Engineers MUST NOT execute automated failover promotion (via Patroni, pg_auto_failover, or custom orchestrators) without asserting that `pg_replication_slots.synchronized` is `true` for all critical downstream CDC slots on the candidate standby node.
- CDC consumer reconnection logic MUST gracefully handle PostgreSQL 17 LSN rewinds within the boundary of the standby's confirmed flush LSN without treating non-monotonic transient LSN resets as fatal stream corruptions.
- Services MUST NEVER assume logical slots created without `failover => true` survive failover or remain recoverable on the promoted replica node.

## Bad vs Good Implementations
### PostgreSQL 17 HA Pre-Promotion Slot Synchronization Check and Slot Creation

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Creating logical slots without specifying `failover => true` in PostgreSQL 17 prevents the primary from marking the slot eligible for standby synchronization. Promoting a standby without verifying `synchronized = true` and checking `confirmed_flush_lsn` causes CDC clients to either crash with missing slot errors or ingest duplicate/corrupted stream segments upon failover.
```typescript
import { Client } from 'pg';

export async function promoteStandbyUnsafe(standbyClient: Client): Promise<void> {
  // Anti-Pattern: Blind promotion without verifying slot sync status
  // Logical slots may not exist on standby or may lag severely, breaking downstream CDC
  await standbyClient.query('SELECT pg_promote();');
  console.log('Standby promoted unsafely without slot synchronization check.');
}

export async function createCdcSlotUnsafe(primaryClient: Client, slotName: string): Promise<void> {
  // Anti-Pattern: Creating logical slot without PG17 failover support
  await primaryClient.query(
    `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`,
    [slotName]
  );
}
```

#### Production Standard (Correct)
> **Standard**: Creates logical slots with PostgreSQL 17's native `failover => true` flag. During failover, it triggers `pg_sync_replication_slots()`, asserts every required CDC slot exists and is flagged `synchronized = true`, ensuring downstream CDC clients can seamlessly continue streaming from the newly promoted primary without data loss or slot recreation.
```typescript
import { Client } from 'pg';

interface ReplicationSlotStatus {
  slot_name: string;
  plugin: string;
  active: boolean;
  synchronized: boolean;
  confirmed_flush_lsn: string;
}

export async function createCdcSlotSafe(primaryClient: Client, slotName: string): Promise<void> {
  // PG17 Invariant: Explicitly enable failover sync support
  await primaryClient.query(
    `SELECT pg_create_logical_replication_slot(
       slot_name => $1,
       plugin => 'pgoutput',
       temporary => false,
       twophase => false,
       failover => true
     );`,
    [slotName]
  );
}

export async function promoteStandbySafe(
  standbyClient: Client,
  requiredSlotNames: string[],
  maxLsnLagBytes: bigint = BigInt(1048576) // 1MB tolerance
): Promise<void> {
  // PG17 Invariant: Force synchronization check before promoting
  await standbyClient.query('SELECT pg_sync_replication_slots();');

  const { rows } = await standbyClient.query<ReplicationSlotStatus>(
    `SELECT slot_name, plugin, active, synchronized, confirmed_flush_lsn
     FROM pg_replication_slots
     WHERE slot_type = 'logical' AND slot_name = ANY($1);`,
    [requiredSlotNames]
  );

  const foundSlotNames = new Set(rows.map(r => r.slot_name));
  for (const name of requiredSlotNames) {
    if (!foundSlotNames.has(name)) {
      throw new Error(`Promotion blocked: Required logical slot '${name}' is missing on standby.`);
    }
  }

  for (const slot of rows) {
    if (!slot.synchronized) {
      throw new Error(`Promotion blocked: Logical slot '${slot.slot_name}' is not marked synchronized.`);
    }
  }

  // Invariant verified: Standby slots are synchronized and ready for CDC failover
  await standbyClient.query('SELECT pg_promote(wait => true);');
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres Cluster Inspector MCP
Validates live Postgres cluster replication parameters, slot sync states, and physical standby lag metrics.
Recommended Agent Skills (askills.dev)

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

Patroni HA Orchestrator CLI
Validates failover slot synchronization hooks and automatic failover switchover invariants on high-availability PostgreSQL clusters.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Synchronization and CDC Loss Prevention Invariant

PostgreSQL 17 Failover Slot Synchronization and CDC Loss Prevention Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces failover-safe logical replication slot synchronization across PostgreSQL 17 high-availability clusters using the native failover slot sync protocol (`sync_replication_slots` and `pg_sync_replication_slots`). Guarantees downstream Change Data Capture (CDC) engines avoid data loss, non-recoverable slot invalidation, or massive duplicate replay bursts upon standby promotion. Mandates strict verification of `synchronized = true` on hot standbys and physical slot synchronization prerequisites before promoting standby nodes. 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: Unsynchronized Logical Replication Slots Triggering CDC Rewind, Duplicate Streaming, or Silent Event Loss Post-Failover 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 DriftUnsynchronized Logical Replication Slots Triggering CDC Rewind, Duplicate Streaming, or Silent Event Loss Post-FailoverCritical
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` on PostgreSQL 17 primary instances intended for CDC consumers (e.g., Debezium, Kafka Connect, custom tailers).

Engineers MUST ALWAYS provision logical replication slots with `failover => true` on PostgreSQL 17 primary instances intended for CDC consumers (e.g., Debezium, Kafka Connect, custom tailers).

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 ALWAYS configure standby PostgreSQL 17 instances with `sync_replication_slots = true` and configure `synchronized_standby_slots` on the primary instance to include physical replication standbys prior to logical advancement.

Engineers MUST ALWAYS configure standby PostgreSQL 17 instances with `sync_replication_slots = true` and configure `synchronized_standby_slots` on the primary instance to include physical replication standbys prior to logical advancement.

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 automated failover promotion (via Patroni, pg_auto_failover, or custom orchestrators) without asserting that `pg_replication_slots.synchronized` is `true` for all critical downstream CDC slots on the candidate standby node.

Engineers MUST NOT execute automated failover promotion (via Patroni, pg_auto_failover, or custom orchestrators) without asserting that `pg_replication_slots.synchronized` is `true` for all critical downstream CDC slots on the candidate standby node.

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 reconnection logic MUST gracefully handle PostgreSQL 17 LSN rewinds within the boundary of the standby's confirmed flush LSN without treating non-monotonic transient LSN resets as fatal stream corruptions.

CDC consumer reconnection logic MUST gracefully handle PostgreSQL 17 LSN rewinds within the boundary of the standby's confirmed flush LSN without treating non-monotonic transient LSN resets as fatal stream corruptions.

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

Services MUST NEVER assume logical slots created without `failover => true` survive failover or remain recoverable on the promoted replica node.

Services MUST NEVER assume logical slots created without `failover => true` survive failover or remain recoverable on the promoted replica node.

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 Failover Slot Synchronization and CDC Loss Prevention Invariant critical for AI coding assistants?

Enforces failover-safe logical replication slot synchronization across PostgreSQL 17 high-availability clusters using the native failover slot sync protocol (`sync_replication_slots` and `pg_sync_replication_slots`). Guarantees downstream Change Data Capture (CDC) engines avoid data loss, non-recoverable slot invalidation, or massive duplicate replay bursts upon standby promotion. Mandates strict verification of `synchronized = true` on hot standbys and physical slot synchronization prerequisites before promoting standby nodes. 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 (Unsynchronized Logical Replication Slots Triggering CDC Rewind, Duplicate Streaming, or Silent Event Loss Post-Failover) 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