PostgreSQL 17 Logical Replication Slot Failover Synchronization Invariant

Enforces failover synchronization invariants for logical replication slots introduced in PostgreSQL 17 to prevent replica divergence, silent subscriber data skipping, and WAL retention bloat during primary-standby failovers. Demands strict verification of the `failover = true` slot configuration, mandatory `synchronized_standby_slots` alignment on the primary, and explicit barrier checks via `pg_sync_replication_slots()` prior to subscriber promotion or reconnection. Ensures zero-divergence high-availability topologies by blocking uncontrolled failovers when physical and logical slot watermarks are misaligned.

--- description: PostgreSQL 17 Logical Replication Slot Failover Synchronization Invariant - Enforces failover synchronization invariants for logical replication slots introduced in PostgreSQL 17 to prevent replica divergence, silent subscriber data skipping, and WAL retention bloat during primary-standby failovers. Demands strict verification of the `failover = true` slot configuration, mandatory `synchronized_standby_slots` alignment on the primary, and explicit barrier checks via `pg_sync_replication_slots()` prior to subscriber promotion or reconnection. Ensures zero-divergence high-availability topologies by blocking uncontrolled failovers when physical and logical slot watermarks are misaligned. globs: src/database/**/*.{ts,js,py,go}, src/infrastructure/**/*.{ts,js,py,go}, src/services/db/**/*.{ts,js,py,go}, scripts/failover/**/*.{sh,ts,py} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-slot-failover-sync-guardrail" --- # PostgreSQL 17 Logical Replication Slot Failover Synchronization Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing Standby Promotion Without Logical Slot Synchronization and Failover Flags ## System Constraints You are a Principal Database Reliability Architect enforcing PostgreSQL 17 logical replication failover invariants. You must prevent catastrophic data loss and replication drift during failovers. Mandate that all logical replication slots intended to survive primary promotion are created with `failover = true`, monitored via `pg_replication_slots.synced`, and constrained by the primary's `synchronized_standby_slots`. Ensure all orchestration scripts and automation layers execute and verify `pg_sync_replication_slots()` before repointing subscribers or completing promotion procedures. ## Strict Guidelines - Engineers MUST ALWAYS create logical replication slots with `failover = true` on PostgreSQL 17+ primary instances when downstream subscribers must survive physical standby failover. - Engineers MUST configure `synchronized_standby_slots` on the primary instance to reference the physical standby replication slot, preventing the primary from advancing WAL past unsynced logical slots. - Code executing failover orchestration MUST call `SELECT pg_sync_replication_slots();` on the physical standby and assert zero delta between physical LSN and logical `confirmed_flush_lsn` before promoting the standby. - Services MUST NOT repoint logical subscribers to a newly promoted standby until confirming that all critical logical replication slots exhibit `synced = false` (or standard promoted state) and matching consumer LSNs. - Components NEVER issue asynchronous subscriber failovers or rely solely on pg_recvlogical without explicit slot barrier synchronization checks. ## Bad vs Good Implementations ### Failover Orchestration with pg_sync_replication_slots Barrier Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Promoting the physical standby without first invoking `pg_sync_replication_slots()` and verifying slot synchronization state causes the newly promoted primary to lack updated `confirmed_flush_lsn` markers. The subscriber reconnects and either misses transactions or crashes due to missing slot state. ```typescript import { Client } from 'pg'; export async function promoteStandbyToPrimary(standbyConfig: object, subscriberConfig: object): Promise<void> { const standbyClient = new Client(standbyConfig); await standbyClient.connect(); // VULNERABLE: Promoting physical standby without syncing logical slots. // Logical slots will lag or be absent, causing subscriber data skipping or divergence. await standbyClient.query('SELECT pg_promote();'); await standbyClient.end(); const subClient = new Client(subscriberConfig); await subClient.connect(); // Repointing subscriber immediately to the promoted node without LSN verification await subClient.query("ALTER SUBSCRIPTION sub_orders CONNECTION 'host=new-primary dbname=prod';"); await subClient.end(); } ``` #### Production Standard (Correct) > **Standard**: The production implementation explicitly invokes `pg_sync_replication_slots()` on the standby, validates that `synced = true` for all designated failover slots, waits synchronously for promotion completion (`wait => true`), and safely updates the subscription without triggering divergence or unnecessary full table copies. ```typescript import { Client } from 'pg'; interface ReplicationSlotRow { slot_name: string; synced: boolean; confirmed_flush_lsn: string; } export async function promoteStandbyToPrimary(standbyConfig: object, subscriberConfig: object): Promise<void> { const standbyClient = new Client(standbyConfig); await standbyClient.connect(); try { // 1. Force explicit slot synchronization barrier from primary to standby await standbyClient.query('SELECT pg_sync_replication_slots();'); // 2. Assert that failover slots are fully synced const slotRes = await standbyClient.query<ReplicationSlotRow>( `SELECT slot_name, synced, confirmed_flush_lsn FROM pg_replication_slots WHERE failover = true AND slot_type = 'logical';` ); if (slotRes.rows.length === 0) { throw new Error('Precondition Failed: No logical failover slots discovered on standby.'); } const unsyncedSlots = slotRes.rows.filter(row => !row.synced); if (unsyncedSlots.length > 0) { throw new Error(`Replication divergence detected. Unsynced slots: ${unsyncedSlots.map(s => s.slot_name).join(', ')}`); } // 3. Complete promotion barrier await standbyClient.query('SELECT pg_promote(wait => true);'); } finally { await standbyClient.end(); } // 4. Safely repoint subscriber const subClient = new Client(subscriberConfig); await subClient.connect(); try { await subClient.query("ALTER SUBSCRIPTION sub_orders CONNECTION 'host=new-primary dbname=prod';"); await subClient.query('ALTER SUBSCRIPTION sub_orders REFRESH PUBLICATION WITH (copy_data = false);'); } finally { await subClient.end(); } } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Logical Replication Slot Failover Synchronization Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces failover synchronization invariants for logical replication slots introduced in PostgreSQL 17 to prevent replica divergence, silent subscriber data skipping, and WAL retention bloat during primary-standby failovers. Demands strict verification of the `failover = true` slot configuration, mandatory `synchronized_standby_slots` alignment on the primary, and explicit barrier checks via `pg_sync_replication_slots()` prior to subscriber promotion or reconnection. Ensures zero-divergence high-availability topologies by blocking uncontrolled failovers when physical and logical slot watermarks are misaligned. 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: Executing Standby Promotion Without Logical Slot Synchronization and Failover Flags 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	Executing Standby Promotion Without Logical Slot Synchronization and Failover Flags	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 create logical replication slots with `failover = true` on PostgreSQL 17+ primary instances when downstream subscribers must survive physical standby failover.

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.