PostgreSQL 17 Failover Slot Sync for HA Logical Replication Invariant

--- description: PostgreSQL 17 Failover Slot Sync for HA Logical Replication Invariant - PostgreSQL 17 introduces synchronized failover replication slots that guarantee logical slots are preserved and updated across physical streaming standbys during unplanned failovers. Applications and change data capture (CDC) consumers must enforce failover-enabled logical slots tied to physical standby confirmation via synchronized slots, preventing consumer LSN rewind, ghost slot drop, and data divergence downstream. globs: **/*.sql, src/**/*.ts, src/**/*.py, src/**/*.go, infra/**/*.tf, infra/**/*.yaml alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-failover-slot-sync-ha-replication" --- # PostgreSQL 17 Failover Slot Sync for HA Logical Replication Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots on Primary Causing Silent Data Loss or Mass Duplicate Replay Post-Failover ## System Constraints You are a Principal Engineer enforcing PostgreSQL 17 Failover Slot Sync for High-Availability Logical Replication. Mandate failover-enabled slot creation, standby synchronization configuration, and LSN progression validation across CDC pipelines. Strictly reject legacy replication slot management that fails to leverage PG17 synchronized replication slots. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover => true` on PostgreSQL 17 primaries (e.g., `pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, true)`). - Engineers MUST NOT allow CDC ingestion or Debezium workers to connect to a new standby without verifying that the standby has `sync_replication_slots = on` and lists the physical standby application name in `synchronized_standby_slots` on the primary. - Database configurations MUST specify `sync_replication_slots = on` in `postgresql.conf` on physical standbys to enable the continuous slot synchronization worker process (`slotsync worker`). - Infrastructure orchestration MUST verify `pg_replication_slots.synced` is `true` on physical replicas before executing planned switchovers or routing logical consumers to newly promoted primaries. - CDC consumer wrappers MUST NEVER blindly rewind confirmed LSNs past the standby's synced `confirmed_flush_lsn`, throwing fatal alerts if slot progression drifts backwards post-promotion. ## Bad vs Good Implementations ### Configuring PG17 Failover-Enabled Logical Slot and Standby Sync Invariants #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical replication slot without explicitly setting the PostgreSQL 17 'failover' parameter (the 5th argument) defaults it to false. The physical standby will not synchronize this slot, leading to immediate pipeline disconnection or catastrophic data replay on failover. ```typescript import { Client } from 'pg'; export async function initLogicalReplicationSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: PG16 and older slot creation lacks 'failover' parameter; // during primary failover, this slot does not exist on the promoted standby, // causing CDC downstream pipeline crash or infinite re-streaming from WAL zero. await client.query(` SELECT pg_create_logical_replication_slot($1, 'pgoutput'); `, [slotName]); } ``` #### Production Standard (Correct) > **Standard**: Validates PG17 server version, confirms primary configuration prevents slot progression beyond standby ack (synchronized_standby_slots), and explicitly passes 'failover => true' to pg_create_logical_replication_slot. ```typescript import { Client } from 'pg'; export async function initLogicalReplicationSlot(client: Client, slotName: string): Promise<void> { // Invariant: Enforce PG17 failover-enabled slot and verify standby synchronization prerequisites const pgVersion = await client.query('SHOW server_version_num;'); const versionNum = parseInt(pgVersion.rows[0].server_version_num, 10); if (versionNum < 170000) { throw new Error('PostgreSQL 17.0+ is required for failover slot synchronization.'); } // Check synchronized_standby_slots has physical standby registered const standbyCheck = await client.query('SHOW synchronized_standby_slots;'); if (!standbyCheck.rows[0].synchronized_standby_slots || standbyCheck.rows[0].synchronized_standby_slots.trim() === '') { throw new Error('PRIMARY MISCONFIGURATION: synchronized_standby_slots must be populated before creating failover slots.'); } // Create logical replication slot: (slot_name, plugin, temporary, two_phase, failover) await client.query(` SELECT pg_create_logical_replication_slot( $1, 'pgoutput', false, false, true ); `, [slotName]); } ``` ### Verifying Replica Slot Synchronization Status Before Downstream Consumer Switchover #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Fails to inspect 'synced' boolean metadata and 'confirmed_flush_lsn' parity in pg_replication_slots on the promoted replica before consumer attachment. ```typescript import { Pool } from 'pg'; export async function reconnectCdcAfterFailover(promotedStandbyPool: Pool, slotName: string): Promise<void> { // Anti-pattern: Directly connects to promoted standby without asserting slot sync status. // If the slot is in an un-synced or transitional state, consumer may skip records. const res = await promotedStandbyPool.query( 'SELECT slot_name, active FROM pg_replication_slots WHERE slot_name = $1;', [slotName] ); if (res.rowCount === 0) { throw new Error('Slot missing on promoted standby!'); } } ``` #### Production Standard (Correct) > **Standard**: Asserts failover=true, confirms the slot exists on the target standby, and executes a pg_wal_lsn_diff check ensuring the standby slot state covers the CDC stream position without backward LSN jumps. ```typescript import { Pool } from 'pg'; interface ReplicationSlotStatus { slot_name: string; synced: boolean; confirmed_flush_lsn: string; failover: boolean; } export async function verifyAndAdoptSlotPostFailover( promotedStandbyPool: Pool, slotName: string, lastKnownConsumerLsn: string ): Promise<void> { const res = await promotedStandbyPool.query<ReplicationSlotStatus>( `SELECT slot_name, synced, confirmed_flush_lsn, failover FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (res.rowCount === 0) { throw new Error(`CRITICAL: Slot '${slotName}' was not synchronized to replica prior to failover.`); } const slot = res.rows[0]; if (!slot.failover) { throw new Error(`CRITICAL: Slot '${slotName}' was not configured with failover=true.`); } // Compare PostgreSQL LSN hex representations using pg_wal_lsn_diff semantics const diffRes = await promotedStandbyPool.query<{ lsn_diff: string }>( 'SELECT pg_wal_lsn_diff($1::pg_lsn, $2::pg_lsn) AS lsn_diff;', [slot.confirmed_flush_lsn, lastKnownConsumerLsn] ); const diffBytes = BigInt(diffRes.rows[0].lsn_diff); if (diffBytes < 0n) { throw new Error(`CRITICAL: Replica slot confirmed_flush_lsn is behind last processed CDC LSN. Lag bytes: ${diffBytes}`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Sync for HA Logical Replication Invariant

PostgreSQL 17 Failover Slot Sync for HA Logical Replication Invariant defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces synchronized failover replication slots that guarantee logical slots are preserved and updated across physical streaming standbys during unplanned failovers. Applications and change data capture (CDC) consumers must enforce failover-enabled logical slots tied to physical standby confirmation via synchronized slots, preventing consumer LSN rewind, ghost slot drop, and data divergence downstream. 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 Primary Causing Silent Data Loss or Mass Duplicate Replay 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 Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unsynchronized Logical Slots on Primary Causing Silent Data Loss or Mass Duplicate Replay Post-Failover	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` on PostgreSQL 17 primaries (e.g., `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.