PostgreSQL 17 pg_createsubscriber Blue/Green Consensus & Zero-Downtime Slot Alignment

--- description: PostgreSQL 17 pg_createsubscriber Blue/Green Consensus & Zero-Downtime Slot Alignment - Mandates deterministic LSN catch-up verification, connection pooler fencing, and sequence state synchronization when promoting physical standbys to logical subscribers via PostgreSQL 17 pg_createsubscriber. Prevents split-brain mutations, logical slot divergence, and sequence ID collisions during zero-downtime blue/green database upgrades. globs: src/database/**/*.{ts,js,py}, src/migrations/**/*.{ts,js,py}, scripts/db/**/*.{ts,js,sh}, infra/cutover/**/*.{ts,js,py} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-createsubscriber-consensus-guardrail" --- # PostgreSQL 17 pg_createsubscriber Blue/Green Consensus & Zero-Downtime Slot Alignment > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing pg_createsubscriber without connection fencing, LSN catch-up verification, and sequence synchronization ## System Constraints You are a Principal Database Infrastructure Engineer enforcing safety invariants for PostgreSQL 17 pg_createsubscriber zero-downtime blue/green cutovers. You must strictly mandate that all physical-to-logical promotion pipelines verify zero WAL lag before conversion, enforce ingress connection fencing via pooler PAUSE operations, explicitly snapshot and forward all sequence counters to avoid duplicate keys, and validate replication slot health before traffic cutover. Reject any script or automation that executes pg_createsubscriber against a live, write-accepting topology or omits sequence synchronization. ## Strict Guidelines - Engineers MUST ALWAYS verify that the physical standby has replayed up to the publisher's current WAL LSN (pg_wal_lsn_diff == 0) immediately before triggering pg_createsubscriber. - Engineers MUST ALWAYS fence incoming write traffic via connection pooler commands (e.g., PgBouncer PAUSE or routing reroutes) prior to running pg_createsubscriber to prevent split-brain state during conversion. - Engineers MUST ALWAYS synchronize all sequence states from publisher to subscriber post-conversion using explicit sequence queries, as pg_createsubscriber and PostgreSQL logical replication DO NOT replicate sequence increments. - Engineers MUST NOT execute pg_createsubscriber on a standby node while background workers, cron schedulers, or direct connection strings bypass the connection pooler. - Code SHOULD verify publication and subscription status (pg_publication, pg_subscription, and pg_replication_slots) on both nodes before unpausing client connections. - Services MUST implement idempotent rollback procedures that drop newly provisioned logical replication slots if pg_createsubscriber fails during execution. ## Bad vs Good Implementations ### Postgres 17 Physical-to-Logical Standby Conversion Orchestrator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: This script executes pg_createsubscriber against an active topology without pausing write ingress, without confirming that replay lag is zero (risking diverging slots or dropped transactions), and fails to synchronize sequences. Because logical replication does not sync sequences, subsequent INSERT operations on the green cluster will fail with primary key collisions. ```typescript import { execSync } from 'node:child_process'; import { Client } from 'pg'; export async function promoteStandbyToLogical(standbyHost: string, publisherDsn: string): Promise<void> { // ANTI-PATTERN: Invoking pg_createsubscriber on a live node without pooler fencing, // without verifying zero WAL replay lag, and ignoring sequence synchronization. console.log('Converting standby directly...'); execSync(`pg_createsubscriber -D /var/lib/postgresql/data -P "${publisherDsn}" -d production_db`); // Standby promoted without sequence updates: next inserts will throw unique constraint violations! console.log('Conversion complete. Routing traffic immediately.'); } ``` #### Production Standard (Correct) > **Standard**: This implementation fences traffic via PgBouncer PAUSE, verifies zero WAL lag between publisher and standby, runs pg_createsubscriber with deterministic inputs, queries and updates all sequence counters on the newly formed logical subscriber, validates the active subscription state, and reliably unpauses traffic in the finally block. ```typescript import { execSync } from 'node:child_process'; import { Client } from 'pg'; interface ReplicationLagResult { replay_lag_bytes: string; } interface SequenceState { sequence_schema: string; sequence_name: string; last_value: string; is_called: boolean; } export async function promoteStandbyToLogical( publisherClient: Client, standbyClient: Client, pgBouncerAdminClient: Client, dataDir: string, publisherDsn: string, databaseName: string ): Promise<void> { // 1. Fence application writes at the ingress pooler layer await pgBouncerAdminClient.query(`PAUSE ${databaseName};`); try { // 2. Assert physical standby has caught up completely to publisher's LSN const lagQuery = ` SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), pg_last_wal_replay_lsn()) AS replay_lag_bytes; `; const lagResult = await standbyClient.query<ReplicationLagResult>(lagQuery); const lagBytes = BigInt(lagResult.rows[0]?.replay_lag_bytes ?? '1'); if (lagBytes !== 0n) { throw new Error(`Standby is not fully caught up. Remaining lag: ${lagBytes} bytes. Aborting conversion.`); } // 3. Execute PostgreSQL 17 pg_createsubscriber to convert standby to logical subscriber execSync( `pg_createsubscriber -D ${dataDir} -P "${publisherDsn}" -d ${databaseName} --verbose`, { stdio: 'inherit' } ); // 4. Synchronize all sequences (logical replication does not sync sequence values) const seqQuery = ` SELECT schemaname AS sequence_schema, sequencename AS sequence_name, last_value, is_called FROM pg_sequences; `; const sequences = await publisherClient.query<SequenceState>(seqQuery); for (const seq of sequences.rows) { const qualifiedName = `"${seq.sequence_schema}"."${seq.sequence_name}"`; await standbyClient.query( `SELECT setval($1::regclass, $2::bigint, $3::boolean);`, [qualifiedName, seq.last_value, seq.is_called] ); } // 5. Verify logical subscription is active and replicating const subCheck = await standbyClient.query( `SELECT subname, subenabled FROM pg_subscription WHERE subname LIKE '%_createsubscriber%';` ); if (subCheck.rowCount === 0 || !subCheck.rows[0].subenabled) { throw new Error('Logical subscription verification failed after pg_createsubscriber promotion.'); } } finally { // 6. Resume connection pooler traffic await pgBouncerAdminClient.query(`RESUME ${databaseName};`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 pg_createsubscriber Blue/Green Consensus & Zero-Downtime Slot Alignment

PostgreSQL 17 pg_createsubscriber Blue/Green Consensus & Zero-Downtime Slot Alignment defines an industrial-grade behavioral standard for backend engineering. Mandates deterministic LSN catch-up verification, connection pooler fencing, and sequence state synchronization when promoting physical standbys to logical subscribers via PostgreSQL 17 pg_createsubscriber. Prevents split-brain mutations, logical slot divergence, and sequence ID collisions during zero-downtime blue/green database upgrades. 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 pg_createsubscriber without connection fencing, LSN catch-up verification, and sequence synchronization 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 pg_createsubscriber without connection fencing, LSN catch-up verification, and sequence synchronization	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 verify that the physical standby has replayed up to the publisher's current WAL LSN (pg_wal_lsn_diff == 0) immediately before triggering pg_createsubscriber.

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.