PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail

--- description: PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail - Enforces failover-safe logical replication and Change Data Capture (CDC) streaming pipelines on PostgreSQL 17+. Mandates failover-enabled logical slots, primary synchronized_standby_slots barrier coordination, and standby slot synchronization to prevent missing WAL segment errors, LSN drift, and CDC corruption during physical replica promotions. globs: src/db/**/*.ts, src/cdc/**/*.ts, src/infra/**/*.ts, migrations/**/*.sql, scripts/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-failover-sync-guardrail" --- # PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots on HA Clusters Triggering WAL Starvation and CDC Corruption on Standby Promotion ## System Constraints You are a Principal Database Reliability Engineer and Distributed Systems Architect enforcing PostgreSQL 17 Logical Replication Failover Slot Synchronization standards. Logical slots in PostgreSQL historically resided strictly on the primary instance, leading to immediate CDC invalidation or catastrophic data loss when a physical standby was promoted. PostgreSQL 17 introduces native slot synchronization via `failover = true` on logical slots, `synchronized_standby_slots` on the primary, and standby slot synchronizers. You must enforce that all database bootstrap scripts, CDC slot allocators, and migration engines configure failover slots correctly, maintain standby barrier synchronization, and prevent logical consumers from consuming WAL ahead of physical replication standbys. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover = true` (via `pg_create_logical_replication_slot(..., failover => true)` or SQL command `CREATE_REPLICATION_SLOT ... (FAILOVER true)`) when targeting PostgreSQL 17+ HA clusters. - Engineers MUST ALWAYS register physical standby replication slot names into the primary's `synchronized_standby_slots` parameter before enabling downstream logical subscribers to guarantee the primary does not discard WAL or allow logical slots to advance beyond physical standbys. - Engineers MUST NEVER allow CDC consumers (e.g., Debezium, custom pgoutput streams) to connect using a single static hostname without configuring multi-host failover connection URIs (e.g., `postgresql://host1:5432,host2:5432/db?target_session_attrs=primary`) and verifying slot availability. - Services MUST verify slot synchronization state on physical standbys using `SELECT slot_name, synced, failover FROM pg_replication_slots` before initiating orchestrated failover or switchover procedures. - Code MUST explicitly reject slot creation on PostgreSQL 17 instances if `sync_replication_slots` is not enabled in standby configurations or if physical replication lag violates defined replication slot retention budgets. - Components NEVER drop or recreate failover-synced slots on a standby directly; slots marked `synced = true` are managed by the slot sync worker and must be modified exclusively on the primary or converted after permanent promotion. ## Bad vs Good Implementations ### PostgreSQL 17 CDC Slot Initialization and Synchronization Validator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical replication slot without failover parameter on PG 17 defaults to failover=false. When the primary node dies or undergoes maintenance failover, the newly promoted physical standby will not contain this slot. The subscriber pipeline will crash with fatal error 'replication slot does not exist', requiring an expensive, table-locking initial snapshot or causing silent data loss. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; plugin: string; } export async function initCdcSlot(client: Client, config: ReplicationSlotConfig): Promise<void> { // Flaw 1: Creates non-failover slot; upon primary node failure/switchover, // this logical slot will vanish on the promoted physical standby. // Flaw 2: Ignores PostgreSQL 17 synchronized_standby_slots verification. await client.query( `SELECT pg_create_logical_replication_slot($1, $2);`, [config.slotName, config.plugin] ); console.log(`Logical slot ${config.slotName} created without failover sync.`); } ``` #### Production Standard (Correct) > **Standard**: Validates that the host is PostgreSQL 17+, asserts that `synchronized_standby_slots` is active on the primary to protect WAL retention across physical replicas, and sets `failover => true` on the logical slot so the standby slot sync worker automatically mirrors the slot state. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; plugin?: string; twoPhase?: boolean; } export async function initPg17FailoverSlot(client: Client, config: ReplicationSlotConfig): Promise<void> { const plugin = config.plugin ?? 'pgoutput'; const twoPhase = config.twoPhase ?? true; // Step 1: Verify PostgreSQL engine version >= 170000 const versionRes = await client.query('SHOW server_version_num;'); const serverVersion = parseInt(versionRes.rows[0].server_version_num, 10); if (serverVersion < 170000) { throw new Error(`Target PostgreSQL version (${serverVersion}) does not support failover slots. Minimum required: 170000.`); } // Step 2: Ensure synchronized_standby_slots is configured on the primary const syncSlotsRes = await client.query('SHOW synchronized_standby_slots;'); const syncStandbys: string = syncSlotsRes.rows[0]?.synchronized_standby_slots ?? ''; if (!syncStandbys || syncStandbys.trim() === "''" || syncStandbys.trim() === '') { throw new Error('GUC synchronized_standby_slots is unconfigured on primary. Standby promotion will lead to WAL divergence.'); } // Step 3: Check if slot already exists and verify failover property const existingRes = await client.query( 'SELECT slot_name, failover, synced FROM pg_replication_slots WHERE slot_name = $1;', [config.slotName] ); if (existingRes.rowCount && existingRes.rowCount > 0) { const slot = existingRes.rows[0]; if (!slot.failover) { // PG 17 allows altering failover attribute dynamically await client.query('SELECT pg_alter_replication_slot($1, failover => true);', [config.slotName]); } return; } // Step 4: Create logical slot with explicit failover and two_phase guarantees await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => $2, temporary => false, two_phase => $3, failover => true );`, [config.slotName, plugin, twoPhase] ); } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Failover Slot Synchronization Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces failover-safe logical replication and Change Data Capture (CDC) streaming pipelines on PostgreSQL 17+. Mandates failover-enabled logical slots, primary synchronized_standby_slots barrier coordination, and standby slot synchronization to prevent missing WAL segment errors, LSN drift, and CDC corruption during physical replica promotions. 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 on HA Clusters Triggering WAL Starvation and CDC Corruption on 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 Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unsynchronized Logical Slots on HA Clusters Triggering WAL Starvation and CDC Corruption on 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 Inconsistency	AI 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:

Engineers MUST ALWAYS provision logical replication slots with `failover = true` (via `pg_create_logical_replication_slot(..., failover => true)` or SQL command `CREATE_REPLICATION_SLOT ... (FAILOVER true)`) when targeting PostgreSQL 17+ HA clusters.

Engineers MUST ALWAYS provision logical replication slots with `failover = true` (via `pg_create_logical_replication_slot(..., failover => true)` or SQL command `CREATE_REPLICATION_SLOT ... (FAILOVER true)`) when targeting PostgreSQL 17+ HA clusters.

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.