Postgres 17 pg_createsubscriber Physical-to-Logical Replica Cutover Standard

--- description: Postgres 17 pg_createsubscriber Physical-to-Logical Replica Cutover Standard - Governs zero-downtime database migrations by enforcing safe physical-to-logical replica cutovers using PostgreSQL 17's pg_createsubscriber. Mandates pre-flight replication invariants—including wal_level=logical, slot headroom, and replica identities—to eliminate copy-phase lag and catastrophic data loss during major upgrades. globs: scripts/**/*.ts, src/database/**/*.ts, src/infra/**/*.ts, tools/migration/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-pg-createsubscriber-cutover-standard" --- # Postgres 17 pg_createsubscriber Physical-to-Logical Replica Cutover Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing Unvalidated pg_createsubscriber Invocations or Falling Back to Initial-Data pg_dump Logical Subscriptions ## System Constraints You are a Principal Database Infrastructure Engineer enforcing strict standards for PostgreSQL 17 zero-downtime upgrades via pg_createsubscriber. You must ensure all automation scripts and backend orchestration layers strictly validate target standbys, primary WAL configurations, and publication boundaries prior to physical-to-logical transformation. ## Strict Guidelines - Engineers MUST ALWAYS verify that 'wal_level' is set to 'logical' on the source primary node before attempting pg_createsubscriber cutover. - Engineers MUST ALWAYS execute 'pg_createsubscriber --dry-run' within automation pipelines prior to running the non-reversible physical-to-logical conversion. - Engineers MUST ALWAYS confirm all mutated tables have a defined PRIMARY KEY or explicit REPLICA IDENTITY (USING INDEX or FULL) prior to conversion to prevent subscriber replication deadlocks. - Engineers MUST NEVER execute pg_createsubscriber against an active, running standby node without ensuring automation properly coordinates server shutdown or manages the -D data directory state as required by Postgres 17. - Orchestration scripts MUST verify sufficient 'max_replication_slots' and 'max_logical_replication_workers' capacity on both source and target nodes before spawning subscription workers. - Automation pipelines MUST explicitly track and drop temporary physical replication slots after conversion to prevent WAL disk bloat on the primary cluster. ## Bad vs Good Implementations ### Postgres 17 Cutover Orchestration Pre-flight and Execution #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Directly invokes pg_createsubscriber without checking that wal_level=logical on the primary, without executing a dry run, without verifying replication lag or replica identity constraints, and without validating slot headroom. Any missing constraint halts the upgrade mid-process, potentially leaving the standby in an inconsistent state. ```typescript import { execSync } from 'node:child_process'; interface CutoverConfig { primaryHost: string; standbyDataDir: string; databaseName: string; } export async function runReplicaCutover(config: CutoverConfig): Promise<void> { // Flaw 1: Running conversion without preflight check (wal_level, replication slots) // Flaw 2: No --dry-run validation // Flaw 3: Hardcoded execution with no safety checks for replica lag or table keys const cmd = `pg_createsubscriber -D ${config.standbyDataDir} -P "host=${config.primaryHost} dbname=${config.databaseName}" -d ${config.databaseName}`; execSync(cmd, { stdio: 'inherit' }); } ``` #### Production Standard (Correct) > **Standard**: Validates primary `wal_level`, ensures all user tables have valid replica identities, executes `pg_createsubscriber --dry-run` to detect configuration conflicts safely, and passes dedicated publication/subscription parameters before committing the cutover. ```typescript import { execSync } from 'node:child_process'; import { Client } from 'pg'; interface CutoverConfig { primaryConnString: string; standbyDataDir: string; databaseName: string; publicationName: string; subscriptionName: string; } export async function runReplicaCutover(config: CutoverConfig): Promise<void> { const primaryClient = new Client({ connectionString: config.primaryConnString }); await primaryClient.connect(); try { // 1. Verify primary wal_level is set to logical const walRes = await primaryClient.query<{ wal_level: string }>("SHOW wal_level;"); if (walRes.rows[0]?.wal_level !== 'logical') { throw new Error("Preflight failed: Primary 'wal_level' must be 'logical'"); } // 2. Validate tables have Primary Keys or Replica Identities to avoid UPDATE/DELETE stalls const unidentifiableTables = await primaryClient.query<{ relname: string }>( `SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND c.relreplident = 'd' AND NOT EXISTS ( SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary );` ); if (unidentifiableTables.rows.length > 0) { const names = unidentifiableTables.rows.map(r => r.relname).join(', '); throw new Error(`Preflight failed: Tables lack PK or explicit REPLICA IDENTITY: ${names}`); } } finally { await primaryClient.end(); } const baseArgs = [ `-D "${config.standbyDataDir}"`, `-P "${config.primaryConnString}"`, `-d "${config.databaseName}"`, `-p "${config.publicationName}"`, `-s "${config.subscriptionName}"` ].join(' '); // 3. Perform dry-run first to guarantee metadata consistency execSync(`pg_createsubscriber --dry-run ${baseArgs}`, { stdio: 'pipe' }); // 4. Perform actual atomic transformation execSync(`pg_createsubscriber ${baseArgs}`, { stdio: 'inherit' }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 pg_createsubscriber Physical-to-Logical Replica Cutover Standard

Postgres 17 pg_createsubscriber Physical-to-Logical Replica Cutover Standard defines an industrial-grade behavioral standard for backend engineering. Governs zero-downtime database migrations by enforcing safe physical-to-logical replica cutovers using PostgreSQL 17's pg_createsubscriber. Mandates pre-flight replication invariants—including wal_level=logical, slot headroom, and replica identities—to eliminate copy-phase lag and catastrophic data loss during major 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 Unvalidated pg_createsubscriber Invocations or Falling Back to Initial-Data pg_dump Logical Subscriptions 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 Unvalidated pg_createsubscriber Invocations or Falling Back to Initial-Data pg_dump Logical Subscriptions	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 'wal_level' is set to 'logical' on the source primary node before attempting pg_createsubscriber cutover.

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.