Postgres 17 Failover Slot Sync WAL Retention Quarantine and Budget Bounding

--- description: Postgres 17 Failover Slot Sync WAL Retention Quarantine and Budget Bounding - Mandates strict WAL retention caps (`max_slot_wal_keep_size`) and automated quarantine controls for PostgreSQL 17 synchronized failover slots. Prevents catastrophic disk exhaustion on primary instances when logical replication subscribers or standby failover synchronization daemons stall. Enforces proactive invalidation and state tracking across `pg_replication_slots` to guarantee cluster availability over indefinite replication retention. globs: src/db/**/*.ts, src/infra/**/*.ts, src/services/database/**/*.ts, migrations/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-failover-slot-sync-wal-retention-quarantine" --- # Postgres 17 Failover Slot Sync WAL Retention Quarantine and Budget Bounding > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Synced Replication Slots Inducing Primary Disk Exhaustion During Standby or Subscriber Stalls ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 Failover Slot Sync WAL Retention Quarantine. Mandate bounded WAL budgets, failover slot sync safeguards, automated quarantine routines, and strict cluster survival invariants over replica retention. ## Strict Guidelines - Engineers MUST ALWAYS configure `max_slot_wal_keep_size` to a finite, bounded threshold (e.g., between 50GB and 250GB depending on primary volume size) rather than default `-1` (unbounded) when enabling `sync_replication_slots` in Postgres 17. - Engineers MUST NOT allow logical replication slots marked with `failover = true` to hold WAL indefinitely on the primary if physical standby nodes configured in `synchronized_standby_slots` disconnect or lag. - Services managing slot creation MUST verify that `wal_status` does not transition to `extended` or `unreserved` without triggering immediate observability alerts and quarantine procedures. - Database orchestration code MUST implement automated quarantine hooks that identify lagging synced slots (`pg_replication_slots.synced = true`), drop or invalidate them when WAL threshold utilization exceeds 85%, and emit critical telemetry before filesystem storage exhaustion. - Components NEVER execute raw `pg_create_logical_replication_slot` with `failover = true` without validating the cluster-wide presence of active `synchronized_standby_slots` and verified physical replica health. ## Bad vs Good Implementations ### Failover Slot Synchronization and WAL Quarantine Monitor #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a failover-synced logical slot without checking `max_slot_wal_keep_size` and physical standby health risks cluster-wide outage. If `max_slot_wal_keep_size` is -1 (unbounded) or the standby crashes, the primary accumulates WAL segments indefinitely, filling the disk and crashing PostgreSQL with ENOSPC. ```typescript import { Client } from 'pg'; // ANTI-PATTERN: Creates failover-enabled logical slot without validating WAL bounds, // ignores physical standby synchronization constraints, and omits quarantine safeguards. export async function registerSubscriberSlot(client: Client, slotName: string): Promise<void> { // Creates logical replication slot with failover=true without verifying max_slot_wal_keep_size // or checking whether physical standby slots are lagging. await client.query(` SELECT pg_create_logical_replication_slot( $1, 'pgoutput', false, false, true -- failover = true in PG17 ); `, [slotName]); console.log(`Slot ${slotName} created without WAL budget guardrails.`); } ``` #### Production Standard (Correct) > **Standard**: Enforces pre-flight checks ensuring `max_slot_wal_keep_size` and `synchronized_standby_slots` are defined prior to provisioning. Implements an explicit quarantine cleaner that inspects unreserved/lost states and enforces a hard WAL retention budget, dropping inactive slots before the primary node crashes from disk exhaustion. ```typescript import { Client } from 'pg'; interface SlotHealthMetrics { slotName: string; walStatus: 'reserved' | 'extended' | 'unreserved' | 'lost'; walBytesRetained: bigint; isSynced: boolean; isFailover: boolean; } const WAL_RETENTION_QUARANTINE_BUDGET_BYTES = BigInt(100 * 1024 * 1024 * 1024); // 100 GB cap export async function registerAndQuarantineFailoverSlots( client: Client, slotName: string ): Promise<void> { // 1. Invariant: Validate bounded WAL keep size on PG17 cluster const configRes = await client.query<{ setting: string }>( "SELECT setting FROM pg_settings WHERE name = 'max_slot_wal_keep_size'" ); const maxWalKeepMb = parseInt(configRes.rows[0]?.setting ?? '-1', 10); if (maxWalKeepMb <= 0) { throw new Error( 'FATAL: max_slot_wal_keep_size must be set to a positive bounded MB value before provisioning failover slots.' ); } // 2. Invariant: Verify synchronized_standby_slots configured const standbyRes = await client.query<{ setting: string }>( "SELECT setting FROM pg_settings WHERE name = 'synchronized_standby_slots'" ); if (!standbyRes.rows[0]?.setting || standbyRes.rows[0].setting.trim() === '') { throw new Error( 'FATAL: synchronized_standby_slots must be configured before creating failover-enabled logical slots.' ); } // 3. Create slot with explicit failover parameter in PG17 await client.query( `SELECT pg_create_logical_replication_slot( $1, 'pgoutput', false, false, true );`, [slotName] ); } export async function quarantineLaggingFailoverSlots( client: Client ): Promise<string[]> { // Inspect replication slot statuses against WAL quarantine thresholds const { rows } = await client.query<SlotHealthMetrics>(` SELECT slot_name AS "slotName", wal_status AS "walStatus", pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS "walBytesRetained", synced AS "isSynced", failover AS "isFailover" FROM pg_replication_slots WHERE active = false; `); const quarantinedSlots: string[] = []; for (const slot of rows) { const exceedsBudget = slot.walBytesRetained > WAL_RETENTION_QUARANTINE_BUDGET_BYTES; const isEndangered = slot.walStatus === 'unreserved' || slot.walStatus === 'lost'; if (exceedsBudget || isEndangered) { // Execute quarantine drop to protect primary storage integrity await client.query('SELECT pg_drop_replication_slot($1)', [slot.slotName]); quarantinedSlots.push(slot.slotName); } } return quarantinedSlots; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Failover Slot Sync WAL Retention Quarantine and Budget Bounding

Postgres 17 Failover Slot Sync WAL Retention Quarantine and Budget Bounding defines an industrial-grade behavioral standard for backend engineering. Mandates strict WAL retention caps (`max_slot_wal_keep_size`) and automated quarantine controls for PostgreSQL 17 synchronized failover slots. Prevents catastrophic disk exhaustion on primary instances when logical replication subscribers or standby failover synchronization daemons stall. Enforces proactive invalidation and state tracking across `pg_replication_slots` to guarantee cluster availability over indefinite replication retention. 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: Unbounded Synced Replication Slots Inducing Primary Disk Exhaustion During Standby or Subscriber Stalls 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	Unbounded Synced Replication Slots Inducing Primary Disk Exhaustion During Standby or Subscriber Stalls	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 configure `max_slot_wal_keep_size` to a finite, bounded threshold (e.g., between 50GB and 250GB depending on primary volume size) rather than default `-1` (unbounded) when enabling `sync_replication_slots` in Postgres 17.

Engineers MUST ALWAYS configure `max_slot_wal_keep_size` to a finite, bounded threshold (e.g., between 50GB and 250GB depending on primary volume size) rather than default `-1` (unbounded) when enabling `sync_replication_slots` 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.