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-sync-postgres-17-logi-490

Postgres 17 Logical Replication Failover Slot Synchronization Guardrail

Enforces strict failover safety for Change Data Capture (CDC) pipelines leveraging PostgreSQL 17 logical replication. Mandates the configuration of failover-enabled logical slots on the primary, synchronized slots on physical standbys via synchronized_standby_slots, and validation of confirmed_flush_lsn boundaries prior to promoting standbys, preventing silent data duplication or permanent data loss during HA cutovers.

Prohibited Anti-Pattern: Unsynchronized Logical Replication Slots During Physical Standby Failover Causing CDC Stream Invalidation or Data Loss

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 Logical Replication Failover Slot Synchronization Guardrail - Enforces strict failover safety for Change Data Capture (CDC) pipelines leveraging PostgreSQL 17 logical replication. Mandates the configuration of failover-enabled logical slots on the primary, synchronized slots on physical standbys via synchronized_standby_slots, and validation of confirmed_flush_lsn boundaries prior to promoting standbys, preventing silent data duplication or permanent data loss during HA cutovers.
globs: src/database/**/*.ts, src/infrastructure/**/*.ts, src/cdc/**/*.ts, src/migrations/**/*.sql, infra/**/*.tf
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-logical-replication-failover-slot-sync-postgres-17-logi-490"
---

# Postgres 17 Logical Replication Failover Slot Synchronization Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unsynchronized Logical Replication Slots During Physical Standby Failover Causing CDC Stream Invalidation or Data Loss

## System Constraints
You are a Principal Database Reliability Architect enforcing PostgreSQL 17 logical replication failover slot synchronization. You must reject any CDC infrastructure, database migration, or orchestration script that provisions or consumes logical slots without explicit failover configuration (FAILOVER = true in pg_create_logical_replication_slot), physical standby synchronization (synchronized_standby_slots parameter), and pre-promotion confirmation checks. Ensure zero data loss and eliminate duplicate event streaming during high availability cutovers.

## Strict Guidelines
- Engineers MUST ALWAYS create logical replication slots on PostgreSQL 17+ with the 'failover' parameter explicitly set to true via pg_create_logical_replication_slot(slot_name, plugin, false, false, false, true) or equivalent DDL.
- Engineers MUST ALWAYS configure 'synchronized_standby_slots' on the primary instance to reference physical standby replication slot names, ensuring physical replication never lags behind downstream logical consumers.
- Engineers MUST NEVER promote a physical standby to primary during planned or automated failover without asserting that 'pg_replication_slots.sync_state' is 'synced' and standby confirmed_flush_lsn matches the primary slot position.
- Services consuming CDC streams MUST implement idempotent message consumption keyed by primary key and transaction commit timestamp (pg_xact_commit_timestamp) to gracefully handle replay from synchronized flush positions.
- Infrastructure-as-Code and DB clients MUST verify 'hot_standby_feedback = on' is active across all physical replicas to prevent primary VACUUM cleanup from removing rows required by lagging synchronized slots.

## Bad vs Good Implementations
### Configuring PG17 Failover Slot Synchronization & Safe CDC Cutover

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Creating a logical slot without setting failover: true prevents Postgres 17 from synchronizing the slot metadata to physical standbys. Blindly promoting the standby causes the CDC engine to fail upon reconnecting or restart from an unverified LSN, leading to data loss or massive duplicate streams.
```typescript
import { Client } from 'pg';

export async function initCdcSlot(client: Client, slotName: string): Promise<void> {
  // Anti-pattern: Pre-PG17 default creation lacking failover persistence
  // Physical standbys will drop or corrupt this slot upon promotion
  await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]);
}

export async function executePromotion(standbyClient: Client): Promise<void> {
  // Anti-pattern: Blind promotion without verifying slot synchronization
  await standbyClient.query('SELECT pg_promote();');
}
```

#### Production Standard (Correct)
> **Standard**: The compliant code activates Postgres 17 native failover slot management with failover => true, validates that the slot is actively synchronized on the physical standby, and verifies synchronized_standby_slots before promotion, ensuring flawless zero-data-loss CDC handoff.
```typescript
import { Client } from 'pg';

export async function initCdcSlot(client: Client, slotName: string): Promise<void> {
  // Compliant: PG17 explicit failover slot creation with pg_create_logical_replication_slot
  // Signature: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover)
  await client.query(
    `SELECT pg_create_logical_replication_slot(
       slot_name => $1,
       plugin => 'pgoutput',
       temporary => false,
       two_phase => false,
       failover => true
     );`,
    [slotName]
  );
}

export async function verifyAndPromoteStandby(
  primaryClient: Client,
  standbyClient: Client,
  slotName: string
): Promise<void> {
  // 1. Verify primary has synchronized_standby_slots configured
  const syncSetting = await primaryClient.query(
    "SELECT current_setting('synchronized_standby_slots') AS sync_slots;"
  );
  if (!syncSetting.rows[0]?.sync_slots) {
    throw new Error('Precondition Failed: synchronized_standby_slots must be configured on primary');
  }

  // 2. Verify slot is fully synchronized on standby replica
  const slotCheck = await standbyClient.query(
    `SELECT slot_name, sync_state, confirmed_flush_lsn 
     FROM pg_replication_slots 
     WHERE slot_name = $1;`,
    [slotName]
  );

  const slot = slotCheck.rows[0];
  if (!slot || slot.sync_state !== 'synced') {
    throw new Error(`Standby replication slot '${slotName}' is not synchronized (sync_state: ${slot?.sync_state ?? 'missing'})`);
  }

  // 3. Promote standby safely guarantees no-loss failover for CDC consumer
  await standbyClient.query('SELECT pg_promote(wait => true, wait_seconds => 60);');
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL MCP Server
Inspects replication slots, confirmed_flush_lsn, and database system catalog parameters directly within the AI workspace.
Recommended Agent Skills (askills.dev)

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

Postgres Cluster Replication Auditor
CLI automation skill for querying replication lag, replication slots sync states, and HA standby promotion states.
Architectural Specification & Invariant Rationale

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

Postgres 17 Logical Replication Failover Slot Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict failover safety for Change Data Capture (CDC) pipelines leveraging PostgreSQL 17 logical replication. Mandates the configuration of failover-enabled logical slots on the primary, synchronized slots on physical standbys via synchronized_standby_slots, and validation of confirmed_flush_lsn boundaries prior to promoting standbys, preventing silent data duplication or permanent data loss during HA cutovers. 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 During Physical Standby Failover Causing CDC Stream Invalidation or Data Loss 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 During Physical Standby Failover Causing CDC Stream Invalidation or Data LossCritical
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 on PostgreSQL 17+ with the 'failover' parameter explicitly set to true via pg_create_logical_replication_slot(slot_name, plugin, false, false, false, true) or equivalent DDL.

Engineers MUST ALWAYS create logical replication slots on PostgreSQL 17+ with the 'failover' parameter explicitly set to true via pg_create_logical_replication_slot(slot_name, plugin, false, false, false, true) or equivalent DDL.

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 'synchronized_standby_slots' on the primary instance to reference physical standby replication slot names, ensuring physical replication never lags behind downstream logical consumers.

Engineers MUST ALWAYS configure 'synchronized_standby_slots' on the primary instance to reference physical standby replication slot names, ensuring physical replication never lags behind downstream logical consumers.

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 NEVER promote a physical standby to primary during planned or automated failover without asserting that 'pg_replication_slots.sync_state' is 'synced' and standby confirmed_flush_lsn matches the primary slot position.

Engineers MUST NEVER promote a physical standby to primary during planned or automated failover without asserting that 'pg_replication_slots.sync_state' is 'synced' and standby confirmed_flush_lsn matches the primary slot position.

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 consuming CDC streams MUST implement idempotent message consumption keyed by primary key and transaction commit timestamp (pg_xact_commit_timestamp) to gracefully handle replay from synchronized flush positions.

Services consuming CDC streams MUST implement idempotent message consumption keyed by primary key and transaction commit timestamp (pg_xact_commit_timestamp) to gracefully handle replay from synchronized flush 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.
5

Infrastructure-as-Code and DB clients MUST verify 'hot_standby_feedback = on' is active across all physical replicas to prevent primary VACUUM cleanup from removing rows required by lagging synchronized slots.

Infrastructure-as-Code and DB clients MUST verify 'hot_standby_feedback = on' is active across all physical replicas to prevent primary VACUUM cleanup from removing rows required by lagging synchronized slots.

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

Enforces strict failover safety for Change Data Capture (CDC) pipelines leveraging PostgreSQL 17 logical replication. Mandates the configuration of failover-enabled logical slots on the primary, synchronized slots on physical standbys via synchronized_standby_slots, and validation of confirmed_flush_lsn boundaries prior to promoting standbys, preventing silent data duplication or permanent data loss during HA cutovers. 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 During Physical Standby Failover Causing CDC Stream Invalidation or Data Loss) 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