PostgreSQL 17 Logical Replication Failover Slot Synchronization

--- description: PostgreSQL 17 Logical Replication Failover Slot Synchronization - Enforces configuration, verification, and migration guardrails for PostgreSQL 17 native logical replication failover slot synchronization. Ensures all logical replication slots on primary instances are created with failover enabled (`failover = true`) and synchronized to physical standbys via `sync_replication_slots` and `synchronized_standby_slots`. This prevents subscriber rewind, stream invalidation, and data duplication or silent data loss when a physical standby is promoted. globs: src/database/**/*.{ts,js,sql}, infrastructure/**/*.{tf,hcl,yaml,json}, scripts/db/**/*.{sh,ts,py}, migrations/**/*.{sql,ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-failover-slot-sync-postgres-17-logi-121" --- # PostgreSQL 17 Logical Replication Failover Slot Synchronization > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating unsynced logical replication slots or promoting physical standbys without synchronized LSN verification ## System Constraints You are a Principal Database Reliability Engineer enforcing PostgreSQL 17 Logical Replication Slot Synchronization invariants. Your objective is to ensure zero data corruption, eliminate silent CDC subscriber lag drift, and prevent catastrophic subscriber rewind during planned or unplanned failovers. Enforce strict adherence to PG17 native logical failover configuration: `failover = true` on `pg_create_logical_replication_slot()`, population of `synchronized_standby_slots` on primary nodes, enabling of `sync_replication_slots = on` on physical standby instances, and pre-failover verification of synchronized slots before client redirection. ## Strict Guidelines - Engineers MUST ALWAYS supply `failover => true` when calling `pg_create_logical_replication_slot()` or executing `CREATE_REPLICATION_SLOT ... (FAILOVER)` in Postgres 17+. - Engineers MUST configure `synchronized_standby_slots` on the primary instance containing the physical replication application names of standbys that must confirm writes before logical slots advance. - Standby configurations MUST set `sync_replication_slots = on` and configure valid `primary_conninfo` containing `dbname` with appropriate replication permissions to allow the `slotsync worker` process to synchronize logical slots. - Application failover orchestrators and health checks MUST query `pg_replication_slots` on standby nodes to assert `sync_state = 's'` (synchronized) and verify `confirmed_flush_lsn` parity before promoting the standby to primary. - Engineers MUST NOT rely on external cron jobs or custom polling daemons (e.g., pg_failover_slots) when running on PostgreSQL 17+, and MUST NOT bypass `synchronized_standby_slots` on write-heavy primary clusters. - Subscribers and CDC consumers (e.g., Debezium, pg_recvlogical) MUST NOT reconnect to a newly promoted primary until the orchestrated failover confirms logical slot preservation and status. - Database migration scripts that declare replication slots MUST validate the server version (`SHOW server_version_num >= 170000`) before invoking failover-specific slot syntax. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Slot Provisioning & Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting the failover flag prevents PostgreSQL 17's native slot synchronization engine from tracking and propagating the logical slot to physical standbys. During failover, the slot disappears on the promoted primary, forcing subscribers to resync from scratch or corrupting downstream CDC pipelines. ```typescript import { Client } from 'pg'; export async function setupLogicalReplicationSlot(client: Client, slotName: string): Promise<void> { // ANTI-PATTERN: Creates legacy logical slot without PG17 failover attribute // Physical standbys will NOT replicate this slot, causing fatal divergence and missing slots on failover await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } ``` #### Production Standard (Correct) > **Standard**: Validates engine compatibility (Postgres 17+), explicitly configures `failover => true` using named parameters in `pg_create_logical_replication_slot`, and performs an assertion on `pg_replication_slots.failover` to guarantee failover replication safety. ```typescript import { Client } from 'pg'; interface ReplicationSlotConfig { slotName: string; plugin?: string; failover?: boolean; } export async function setupLogicalReplicationSlot( client: Client, { slotName, plugin = 'pgoutput', failover = true }: ReplicationSlotConfig ): Promise<{ slotName: string; consistentPoint: string }> { // Invariant: Verify Postgres version >= 17.0.0 (170000) const versionRes = await client.query<{ server_version_num: string }>( 'SHOW server_version_num;' ); const versionNum = parseInt(versionRes.rows[0].server_version_num, 10); if (versionNum < 170000) { throw new Error(`Logical failover slots require PostgreSQL 17+. Detected: ${versionNum}`); } // Create logical slot with explicit failover enabled const result = await client.query<{ slot_name: string; consistent_point: string; }>( `SELECT slot_name, consistent_point FROM pg_create_logical_replication_slot( slot_name => $1, plugin => $2, temporary => false, two_phase => true, failover => $3 );`, [slotName, plugin, failover] ); // Verify slot status in pg_replication_slots catalog const verifyRes = await client.query<{ slot_name: string; failover: boolean; synced: boolean; }>( `SELECT slot_name, failover, synced FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (!verifyRes.rows[0]?.failover) { throw new Error(`Invariant violation: Slot ${slotName} created without failover enabled.`); } return { slotName: result.rows[0].slot_name, consistentPoint: result.rows[0].consistent_point, }; } ``` ### Pre-Failover Standby Slot Synchronization Gate #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing pg_promote() without confirming slot synchronization status ('synced' = true and sync worker activity) risks advancing the standby timeline past the subscriber's confirmed flush LSN, permanently breaking logical replication streams. ```typescript import { Client } from 'pg'; export async function promoteStandbyToPrimary(standbyClient: Client): Promise<void> { // ANTI-PATTERN: Promoting standby immediately without verifying logical slot sync status // Any lagging synchronized slot may miss transactions or get discarded await standbyClient.query('SELECT pg_promote();'); } ``` #### Production Standard (Correct) > **Standard**: Ensures that required logical slots on the physical standby have `synced = true` and `conflicting = false` in `pg_replication_slots` prior to issuing `pg_promote(wait => true)`, preventing slot loss or consumer data corruption. ```typescript import { Client } from 'pg'; export async function promoteStandbyToPrimary( standbyClient: Client, requiredSyncedSlots: string[], maxWaitMs: number = 30000 ): Promise<void> { const startTime = Date.now(); // Assert standby status const inRecoveryRes = await standbyClient.query<{ pg_is_in_recovery: boolean }>( 'SELECT pg_is_in_recovery();' ); if (!inRecoveryRes.rows[0]?.pg_is_in_recovery) { throw new Error('Target instance is not in recovery (not a standby).'); } // Poll until all required logical slots are marked as synced while (Date.now() - startTime < maxWaitMs) { const slotRes = await standbyClient.query<{ slot_name: string; synced: boolean; conflicting: boolean; }>( `SELECT slot_name, synced, conflicting FROM pg_replication_slots WHERE slot_name = ANY($1::text[]);`, [requiredSyncedSlots] ); const syncedCount = slotRes.rows.filter( (r) => r.synced && !r.conflicting ).length; if (syncedCount === requiredSyncedSlots.length) { // Standby logical slots are completely synced and safe for promotion await standbyClient.query('SELECT pg_promote(wait => true);'); return; } await new Promise((resolve) => setTimeout(resolve, 500)); } throw new Error( `Standby promotion aborted: Logical replication slots failed to sync within ${maxWaitMs}ms.` ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Logical Replication Failover Slot Synchronization

PostgreSQL 17 Logical Replication Failover Slot Synchronization defines an industrial-grade behavioral standard for backend engineering. Enforces configuration, verification, and migration guardrails for PostgreSQL 17 native logical replication failover slot synchronization. Ensures all logical replication slots on primary instances are created with failover enabled (`failover = true`) and synchronized to physical standbys via `sync_replication_slots` and `synchronized_standby_slots`. This prevents subscriber rewind, stream invalidation, and data duplication or silent data loss when a physical standby is promoted. 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: Creating unsynced logical replication slots or promoting physical standbys without synchronized LSN verification 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	Creating unsynced logical replication slots or promoting physical standbys without synchronized LSN verification	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 supply `failover => true` when calling `pg_create_logical_replication_slot()` or executing `CREATE_REPLICATION_SLOT ... (FAILOVER)` in Postgres 17+.

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.