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-replication-slot-synchronization-standard

PostgreSQL 17 Native Logical Replication Slot Failover Synchronization Standard

Enforces strict configuration and runtime verification of PostgreSQL 17 native failover-enabled logical replication slots. Prevents data loss, message duplication storms, and unrecoverable consumer drift in high-availability CDC pipelines by mandating 'failover = true' on slots and synchronizing physical standby slots before promoting secondary nodes.

Prohibited Anti-Pattern: Unsynchronized Logical Slots Causing CDC Pipeline Invalidation and Lost Changes Post-Failover

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Native Logical Replication Slot Failover Synchronization Standard - Enforces strict configuration and runtime verification of PostgreSQL 17 native failover-enabled logical replication slots. Prevents data loss, message duplication storms, and unrecoverable consumer drift in high-availability CDC pipelines by mandating 'failover = true' on slots and synchronizing physical standby slots before promoting secondary nodes.
globs: src/**/*.ts, migrations/**/*.sql, infra/**/*.tf, deploy/**/*.yaml
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-failover-replication-slot-synchronization-standard"
---

# PostgreSQL 17 Native Logical Replication Slot Failover Synchronization Standard

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unsynchronized Logical Slots Causing CDC Pipeline Invalidation and Lost Changes Post-Failover

## System Constraints
You are a Principal Database Reliability Engineer enforcing PostgreSQL 17 logical replication failover standards. In PostgreSQL 17+, logical replication slots can and must survive primary failovers without external tool hacks. You must reject any logical slot creation that omits the 'failover => true' parameter, verify that primary configurations define 'synchronized_standby_slots', and enforce that standby nodes enable 'sync_replication_slots'. Always ensure CDC ingestion engines (e.g., Debezium, custom PG streaming workers) assert slot sync state prior to resuming consumption after a cluster topology transition.

## Strict Guidelines
- Engineers MUST ALWAYS create logical replication slots with 'failover => true' in PostgreSQL 17+ environments.
- Engineers MUST NOT depend on legacy user-space synchronization scripts or unverified slot copies across HA nodes.
- Primary database instances MUST configure 'synchronized_standby_slots' to include the physical standby applications streaming replication data.
- Standby instances MUST set 'sync_replication_slots = on' to automatically receive and synchronize logical replication slot metadata from the primary.
- CDC consumer initialization code MUST assert that the connected PostgreSQL server version is >= 170000 and that the assigned slot has 'failover' enabled and 'synced' set to false (confirming active primary state) or verified before consumption.
- Services MUST NEVER drop and recreate unsynchronized replication slots post-failover, as this causes silent data gaps across downstream Kafka/event topics.

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

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Legacy pg_create_logical_replication_slot call omits the PostgreSQL 17 failover parameter. When the primary goes down and a standby is promoted, the slot does not exist on the newly promoted primary, causing consumer fatal crash loops and requiring a manual snapshot that duplicates or loses inflight WAL records.
```typescript
import { Client } from 'pg';

export async function initCdcReplicationSlot(client: Client, slotName: string): Promise<void> {
  // FAILS: Creates legacy slot without failover persistence.
  // During an HA switchover or failover to a standby node,
  // this slot will not exist on the promoted node, breaking CDC and causing data loss.
  await client.query(
    `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`,
    [slotName]
  );
}
```

#### Production Standard (Correct)
> **Standard**: Explicitly verifies the cluster is on PostgreSQL 17+, confirms 'synchronized_standby_slots' is configured, and invokes 'pg_create_logical_replication_slot' with named parameter 'failover => true'. It also verifies the metadata via 'pg_replication_slots' before allowing the consumer to advance.
```typescript
import { Client } from 'pg';

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

export async function initCdcReplicationSlot(client: Client, slotName: string): Promise<void> {
  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(`PostgreSQL 17+ required for failover slots. Current version: ${versionNum}`);
  }

  // Verify standby slot sync prerequisites on primary
  const syncStandbysRes = await client.query<{ setting: string }>('SHOW synchronized_standby_slots;');
  if (!syncStandbysRes.rows[0]?.setting || syncStandbysRes.rows[0].setting === '') {
    throw new Error('CRITICAL: synchronized_standby_slots must be configured on primary.');
  }

  // Idempotently create slot with native PG17 failover synchronization
  await client.query(
    `SELECT pg_create_logical_replication_slot(
       slot_name => $1,
       plugin => 'pgoutput',
       failover => true
     )
     WHERE NOT EXISTS (
       SELECT 1 FROM pg_replication_slots WHERE slot_name = $1
     );`,
    [slotName]
  );

  // Invariant assertion: verify slot attributes
  const slotCheck = await client.query<ReplicationSlotRow>(
    `SELECT slot_name, plugin, failover, synced
     FROM pg_replication_slots
     WHERE slot_name = $1;`,
    [slotName]
  );

  const slot = slotCheck.rows[0];
  if (!slot || !slot.failover) {
    throw new Error(`Invariant violation: Slot ${slotName} does not have failover synchronization active.`);
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL Context Provider
Inspects running PostgreSQL database topology, cluster settings, and pg_replication_slots status.
Recommended Agent Skills (askills.dev)

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

PostgreSQL HA Replication Validator
Automated CLI checks to validate synchronized_standby_slots and sync_replication_slots consistency across primary and standby pools.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Native Logical Replication Slot Failover Synchronization Standard

PostgreSQL 17 Native Logical Replication Slot Failover Synchronization Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict configuration and runtime verification of PostgreSQL 17 native failover-enabled logical replication slots. Prevents data loss, message duplication storms, and unrecoverable consumer drift in high-availability CDC pipelines by mandating 'failover = true' on slots and synchronizing physical standby slots before promoting secondary 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 Slots Causing CDC Pipeline Invalidation and Lost Changes 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 Slots Causing CDC Pipeline Invalidation and Lost Changes 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 create logical replication slots with 'failover => true' in PostgreSQL 17+ environments.

Engineers MUST ALWAYS create logical replication slots with 'failover => true' in PostgreSQL 17+ environments.

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 depend on legacy user-space synchronization scripts or unverified slot copies across HA nodes.

Engineers MUST NOT depend on legacy user-space synchronization scripts or unverified slot copies across HA nodes.

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

Primary database instances MUST configure 'synchronized_standby_slots' to include the physical standby applications streaming replication data.

Primary database instances MUST configure 'synchronized_standby_slots' to include the physical standby applications streaming replication data.

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

Standby instances MUST set 'sync_replication_slots = on' to automatically receive and synchronize logical replication slot metadata from the primary.

Standby instances MUST set 'sync_replication_slots = on' to automatically receive and synchronize logical replication slot metadata from 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.
5

CDC consumer initialization code MUST assert that the connected PostgreSQL server version is >= 170000 and that the assigned slot has 'failover' enabled and 'synced' set to false (confirming active primary state) or verified before consumption.

CDC consumer initialization code MUST assert that the connected PostgreSQL server version is >= 170000 and that the assigned slot has 'failover' enabled and 'synced' set to false (confirming active primary state) or verified before consumption.

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

Services MUST NEVER drop and recreate unsynchronized replication slots post-failover, as this causes silent data gaps across downstream Kafka/event topics.

Services MUST NEVER drop and recreate unsynchronized replication slots post-failover, as this causes silent data gaps across downstream Kafka/event topics.

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 Native Logical Replication Slot Failover Synchronization Standard critical for AI coding assistants?

Enforces strict configuration and runtime verification of PostgreSQL 17 native failover-enabled logical replication slots. Prevents data loss, message duplication storms, and unrecoverable consumer drift in high-availability CDC pipelines by mandating 'failover = true' on slots and synchronizing physical standby slots before promoting secondary 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 Slots Causing CDC Pipeline Invalidation and Lost Changes 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