PostgreSQL 17 Failover Slot Synchronization & WAL Retention Guardrail

--- description: PostgreSQL 17 Failover Slot Synchronization & WAL Retention Guardrail - Enforces PostgreSQL 17 native logical replication slot synchronization on physical standbys alongside deterministic max_slot_wal_keep_size thresholds. Guarantees zero downstream logical subscriber rebuilds upon regional primary promotion while strictly preventing primary cluster storage exhaustion from disconnected slots. globs: infra/postgres/**/*.conf, src/db/**/*.ts, migrations/**/*.sql, docker-compose*.yml, terraform/**/*.tf alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-failover-slot-sync-wal-retention-guardrail" --- # PostgreSQL 17 Failover Slot Synchronization & WAL Retention Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Standby Slots with Unbounded WAL Retention Crashing Disks During Failovers ## System Constraints You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 failover slot synchronization and bounded WAL retention. All logical replication topologies spanning physical replicas MUST configure failover slots (pg_create_logical_replication_slot with failover=true), sync_replication_slots on standbys, and explicit max_slot_wal_keep_size boundaries to prevent catastrophic disk exhaustion and replication restarts. ## Strict Guidelines - Engineers MUST ALWAYS enable failover = true when creating logical replication slots in PostgreSQL 17 (pg_create_logical_replication_slot(slot_name, plugin, false, false, failover => true)). - Engineers MUST ALWAYS set sync_replication_slots = true on physical standbys and configure synchronized_standby_slots on the primary to ensure LSN alignment prior to promotion. - Engineers MUST NEVER leave max_slot_wal_keep_size set to -1 (unlimited) in production environments; explicit boundaries (e.g., 64GB) MUST be enforced alongside automated lag alerting. - Standby sync workers MUST be authorized via pg_monitor or physical replication credentials with access to read the primary's slots over the primary_conninfo channel. - Code orchestrating promotion or health checks MUST verify that pg_replication_slots.sync_state is 'synced' and standby wal_status is 'normal' before issuing pg_promote(). ## Bad vs Good Implementations ### Logical Slot Provisioning & Failover Synchronization Config #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creates a legacy un-synchronized slot. Upon cross-region physical standby promotion, the logical slot does not exist on the new primary, forcing catastrophic full initial re-syncs. Furthermore, max_slot_wal_keep_size = '-1' risks cluster-wide disk exhaustion if consumer stalls. ```typescript import { Client } from 'pg'; export async function setupLogicalSlot(client: Client, slotName: string): Promise<void> { // Anti-pattern: Missing failover parameter (defaults to false) and unbounded WAL retention await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); // Primary can crash if subscriber disconnects and fills disk await client.query(`ALTER SYSTEM SET max_slot_wal_keep_size = '-1';`); await client.query(`SELECT pg_reload_conf();`); } ``` #### Production Standard (Correct) > **Standard**: Explicitly activates PostgreSQL 17's native failover parameter to persist slot state across physical replicas while bounding WAL retention to 64GB, preventing disk exhaustion. ```typescript import { Client } from 'pg'; export async function setupLogicalSlot(client: Client, slotName: string): Promise<void> { // PG17 Invariant: Bounded retention protects disk storage await client.query(`ALTER SYSTEM SET max_slot_wal_keep_size = '65536MB';`); await client.query(`SELECT pg_reload_conf();`); // PG17 Invariant: failover = true enables replication slot syncing across standbys await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => false, failover => true );`, [slotName] ); } ``` ### Safe Promotion Pre-Flight Check for Synchronized Slots #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Promoting a physical standby before verifying slot synchronization will strand downstream subscribers if the sync worker is lagging behind the primary LSN. ```typescript import { Client } from 'pg'; export async function promoteStandbyUnchecked(standbyClient: Client): Promise<void> { // Anti-pattern: Blind promotion without verifying slot sync status await standbyClient.query('SELECT pg_promote();'); } ``` #### Production Standard (Correct) > **Standard**: Validates that all logical replication slots on the standby indicate synced=true and wal_status='normal' before triggering pg_promote, preventing subscriber data divergence. ```typescript import { Client } from 'pg'; interface SlotStatus { slot_name: string; synced: boolean; wal_status: string; } export async function promoteStandbySafe(standbyClient: Client): Promise<void> { // Invariant: Verify all critical logical slots have synchronized and have valid WAL const { rows } = await standbyClient.query<SlotStatus>( `SELECT slot_name, synced, wal_status FROM pg_replication_slots WHERE slot_type = 'logical';` ); const unreadySlots = rows.filter(s => !s.synced || s.wal_status !== 'normal'); if (unreadySlots.length > 0) { throw new Error(`Standby slot sync incomplete: ${JSON.stringify(unreadySlots)}`); } await standbyClient.query('SELECT pg_promote(wait => true);'); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Synchronization & WAL Retention Guardrail

PostgreSQL 17 Failover Slot Synchronization & WAL Retention Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 native logical replication slot synchronization on physical standbys alongside deterministic max_slot_wal_keep_size thresholds. Guarantees zero downstream logical subscriber rebuilds upon regional primary promotion while strictly preventing primary cluster storage exhaustion from disconnected slots. 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 Standby Slots with Unbounded WAL Retention Crashing Disks During Failovers 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 Standby Slots with Unbounded WAL Retention Crashing Disks During Failovers	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 enable failover = true when creating logical replication slots in PostgreSQL 17 (pg_create_logical_replication_slot(slot_name, plugin, false, false, failover => 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.