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-logical-replication-failover-slot-boundary

PostgreSQL 17 Logical Replication Failover Slot Synchronization Boundary

Guarantees zero data loss, continuous CDC consumption, and subscriber continuity across physical high-availability failovers in PostgreSQL 17. Mandates the use of failover-enabled logical replication slots (`failover = true`) coupled with physical standby synchronization (`synchronized_standby_slots`) and pre-promotion slot parity validation. Prevents silent message loss, slot invalidation, and catastrophic subscriber re-initialization when a standby is promoted to primary.

Prohibited Anti-Pattern: Promoting a physical replica without verifying logical replication slot sync or omitting failover attributes in PG17

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 Logical Replication Failover Slot Synchronization Boundary - Guarantees zero data loss, continuous CDC consumption, and subscriber continuity across physical high-availability failovers in PostgreSQL 17. Mandates the use of failover-enabled logical replication slots (`failover = true`) coupled with physical standby synchronization (`synchronized_standby_slots`) and pre-promotion slot parity validation. Prevents silent message loss, slot invalidation, and catastrophic subscriber re-initialization when a standby is promoted to primary.
globs: src/**/*.{ts,js,py,go,sql}, migrations/**/*.{sql,ts}, deployments/**/*.{yaml,yml,conf}, infra/**/*.{tf,hcl,yaml}
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-logical-replication-failover-slot-boundary"
---

# PostgreSQL 17 Logical Replication Failover Slot Synchronization Boundary

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Promoting a physical replica without verifying logical replication slot sync or omitting failover attributes in PG17

## System Constraints
You are a Principal Database Infrastructure Engineer enforcing PostgreSQL 17 Logical Replication Failover Slot Synchronization Boundaries. You must mandate that all logical replication slots intended to survive physical failovers are explicitly provisioned with `failover = true`, monitored via `synchronized_standby_slots` on the primary, synchronized on physical standbys via `sync_replication_slots = true`, and verified via `pg_replication_slots` before promotion orchestration executes.

## Strict Guidelines
- Engineers MUST ALWAYS provision logical replication slots with `failover = true` on PostgreSQL 17+ when the consumer requires cross-failover continuity (e.g., `SELECT pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, true);`).
- Engineers MUST configure `synchronized_standby_slots` on the primary instance to include the application slots, ensuring physical standbys advance their LSN past logical slot checkpoints before transactions are committed.
- Engineers MUST enable `sync_replication_slots = true` in standby configuration (postgresql.conf) or invoke `pg_sync_replication_slots()` deterministically prior to executing a promotion command.
- Database orchestrators and failover handlers MUST verify that `sync_state = 's'` and `confirmed_flush_lsn` matches or exceeds the primary's logical flush LSN on the candidate standby before triggering `pg_ctl promote` or orchestrating Patroni switchover.
- Engineers MUST NOT allow logical replication consumers to connect directly to unpromoted physical standbys or consume from slots marked `synced = true` prior to promotion.
- Applications MUST configure their subscription connection strings with dynamic failover parameters (e.g., `target_session_attrs=read-write`) to cleanly redirect CDC consumers post-promotion without dropping the slot state.

## Bad vs Good Implementations
### PostgreSQL 17 Pre-Promotion Logical Slot Parity and Promotion Guard

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Promoting a physical replica without synchronizing logical slots causes downstream logical subscribers to fail upon reconnecting to the new primary. Prior to PG17, logical slots were completely lost upon failover; in PG17, slots require explicit sync verification and `failover = true` to guarantee zero data loss.
```typescript
import { Client } from 'pg';

export async function promoteStandbyUnsafely(standbyConnString: string): Promise<void> {
  const client = new Client({ connectionString: standbyConnString });
  await client.connect();
  
  // CRITICAL ANTI-PATTERN: Blindly promoting physical standby.
  // Logical replication slots will be stale, missing failover flag, or un-synchronized.
  // Downstream CDC pipelines (Debezium/pgoutput) will crash or lose state (ERR: replication slot does not exist).
  await client.query('SELECT pg_promote();');
  await client.end();
}
```

#### Production Standard (Correct)
> **Standard**: Invokes PG17's slot synchronization mechanics, asserts that all required logical slots have `failover = true` and `synced = true`, and halts promotion if the replica has not reached parity, preserving subscriber continuity and message ordering.
```typescript
import { Client } from 'pg';

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

export async function promoteStandbySafely(
  standbyConnString: string,
  criticalSlots: string[]
): Promise<void> {
  const client = new Client({ connectionString: standbyConnString });
  await client.connect();

  try {
    // Step 1: Force manual slot sync barrier to catch up to the latest primary LSN
    await client.query('SELECT pg_sync_replication_slots();');

    // Step 2: Validate slot synchronization invariant
    const query = `
      SELECT slot_name, confirmed_flush_lsn, failover, synced
      FROM pg_replication_slots
      WHERE slot_name = ANY($1);
    `;
    const res = await client.query<SlotStatus>(query, [criticalSlots]);

    const foundSlots = new Map(res.rows.map(r => [r.slot_name, r]));
    for (const slotName of criticalSlots) {
      const slot = foundSlots.get(slotName);
      if (!slot) {
        throw new Error(`CRITICAL: Slot ${slotName} not found on failover candidate.`);
      }
      if (!slot.failover) {
        throw new Error(`CRITICAL: Slot ${slotName} was not created with failover = true.`);
      }
      if (!slot.synced) {
        throw new Error(`CRITICAL: Slot ${slotName} is not in synced state on failover candidate.`);
      }
    }

    // Step 3: Standby boundary verified; safe to promote
    await client.query('SELECT pg_promote();');
  } finally {
    await client.end();
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL Context Server
Inspects PostgreSQL metadata, catalog views (pg_replication_slots), and runtime configuration.
Recommended Agent Skills (askills.dev)

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

patronictl
Validates cluster failover slot health, sync state, and controlled failover procedures.
Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Failover Slot Synchronization Boundary defines an industrial-grade behavioral standard for backend engineering. Guarantees zero data loss, continuous CDC consumption, and subscriber continuity across physical high-availability failovers in PostgreSQL 17. Mandates the use of failover-enabled logical replication slots (`failover = true`) coupled with physical standby synchronization (`synchronized_standby_slots`) and pre-promotion slot parity validation. Prevents silent message loss, slot invalidation, and catastrophic subscriber re-initialization when a standby is promoted to primary. 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: Promoting a physical replica without verifying logical replication slot sync or omitting failover attributes in PG17 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 DriftPromoting a physical replica without verifying logical replication slot sync or omitting failover attributes in PG17Critical
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+ when the consumer requires cross-failover continuity (e.g., `SELECT pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, true)

Engineers MUST ALWAYS provision logical replication slots with `failover = true` on PostgreSQL 17+ when the consumer requires cross-failover continuity (e.g., `SELECT pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, true);`).

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 the application slots, ensuring physical standbys advance their LSN past logical slot checkpoints before transactions are committed.

Engineers MUST configure `synchronized_standby_slots` on the primary instance to include the application slots, ensuring physical standbys advance their LSN past logical slot checkpoints before transactions are committed.

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 enable `sync_replication_slots = true` in standby configuration (postgresql.conf) or invoke `pg_sync_replication_slots()` deterministically prior to executing a promotion command.

Engineers MUST enable `sync_replication_slots = true` in standby configuration (postgresql.conf) or invoke `pg_sync_replication_slots()` deterministically prior to executing a promotion command.

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

Database orchestrators and failover handlers MUST verify that `sync_state = 's'` and `confirmed_flush_lsn` matches or exceeds the primary's logical flush LSN on the candidate standby before triggering `pg_ctl promote` or orchestrating Patroni switchover.

Database orchestrators and failover handlers MUST verify that `sync_state = 's'` and `confirmed_flush_lsn` matches or exceeds the primary's logical flush LSN on the candidate standby before triggering `pg_ctl promote` or orchestrating Patroni switchover.

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 NOT allow logical replication consumers to connect directly to unpromoted physical standbys or consume from slots marked `synced = true` prior to promotion.

Engineers MUST NOT allow logical replication consumers to connect directly to unpromoted physical standbys or consume from slots marked `synced = true` prior to 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.
6

Applications MUST configure their subscription connection strings with dynamic failover parameters (e.g., `target_session_attrs=read-write`) to cleanly redirect CDC consumers post-promotion without dropping the slot state.

Applications MUST configure their subscription connection strings with dynamic failover parameters (e.g., `target_session_attrs=read-write`) to cleanly redirect CDC consumers post-promotion without dropping the slot state.

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

Guarantees zero data loss, continuous CDC consumption, and subscriber continuity across physical high-availability failovers in PostgreSQL 17. Mandates the use of failover-enabled logical replication slots (`failover = true`) coupled with physical standby synchronization (`synchronized_standby_slots`) and pre-promotion slot parity validation. Prevents silent message loss, slot invalidation, and catastrophic subscriber re-initialization when a standby is promoted to primary. 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 (Promoting a physical replica without verifying logical replication slot sync or omitting failover attributes in PG17) 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