PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unvalidated LSN Gap in Incremental Backup Chains and Blind pg_combinebackup Execution) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment

--- description: PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment - PostgreSQL 17 native block-level incremental backups depend on contiguous, monotonically increasing Log Sequence Numbers (LSN) recorded across successive pg_combinebackup manifests. Orchestration layers must strictly validate that an incremental backup's start LSN exactly matches or precedes the prior backup's stop LSN and verifies pg_waldump consistency before synthesising a full backup, preventing silent corruption during point-in-time recovery (PITR). globs: src/backup/**/*.{ts,js,py,go}, src/infrastructure/**/*.{ts,js,py,go}, services/storage/**/*.{ts,js,py,go}, scripts/db/**/*.{sh,ts,py} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-incremental-backup-lsn-alignment" --- # PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unvalidated LSN Gap in Incremental Backup Chains and Blind pg_combinebackup Execution ## System Constraints You are a Principal Database Reliability Engineer enforcing strict invariant constraints for PostgreSQL 17 block-level incremental backups. You must mandate validation of start_lsn, stop_lsn, and timeline ID (TLI) continuity between base backups and subsequent incrementals prior to triggering pg_combinebackup. Reject any backup orchestration code that relies solely on file timestamps or naive sequential ordering without parsing backup_manifest checksums and verifying WAL containment. ## Strict Guidelines - Engineers MUST ALWAYS verify that for every incremental backup step N, start_lsn(N) <= stop_lsn(N-1) within the identical Timeline ID (TLI), ensuring zero unreferenced WAL gaps. - Engineers MUST NOT execute pg_combinebackup without first verifying the cryptographic checksums of each input manifest via pg_verifybackup --manifest-path. - Code SHOULD enforce parsing of backup_manifest JSON files directly to assert LSN continuity before mounting or persisting synthetic full artifacts. - Services MUST fail fast and trigger an immediate fallback to a full pg_basebackup if an LSN gap or timeline divergence is detected across the backup chain. - Components NEVER execute raw file synchronization or snapshot merges if any intermediate incremental backup manifest reports missing WAL segments or mismatched system identifiers. ## Bad vs Good Implementations ### Validating Contiguous LSN Intervals in Incremental Backup Chains Before Synthetic Full Consolidation #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Sorting backups by wall-clock timestamp ignores PostgreSQL timeline transitions, promotion forks, and non-contiguous LSN ranges. If an incremental backup was aborted or skipped, pg_combinebackup might produce an inconsistent or corrupted synthetic full backup that fails silently during an emergency PITR. ```typescript import { execSync } from 'node:child_process'; import * as fs from 'node:fs'; interface BackupDirectory { path: string; timestamp: number; } // ANTI-PATTERN: Merging backups based on filesystem timestamps without LSN verification export async function consolidateBackups(backups: BackupDirectory[], targetDir: string): Promise<void> { // Sort naively by timestamp const sorted = backups.sort((a, b) => a.timestamp - b.timestamp); const backupPaths = sorted.map((b) => b.path).join(' '); // Blindly running pg_combinebackup risks corrupting the target if an incremental was skipped or LSN diverged execSync(`pg_combinebackup ${backupPaths} -o ${targetDir}`); } ``` #### Production Standard (Correct) > **Standard**: This implementation verifies manifests with pg_verifybackup, validates system identifiers across all chained layers, and computationally parses hex LSN numbers to enforce monotonic intervals (Start-LSN[i] <= End-LSN[i-1]). If an LSN gap exists, it raises a fatal error before pg_combinebackup can generate a broken image. ```typescript import { execSync } from 'node:child_process'; import * as fs from 'node:fs/promises'; import * as path from 'node:path'; interface Manifest { 'PostgreSQL-Backup-Manifest-Version': number; 'System-Identifier': string; Files: Record<string, unknown>; 'WAL-Ranges': Array<{ Timeline: number; 'Start-LSN': string; 'End-LSN': string; }>; 'Start-LSN': string; 'End-LSN': string; } function parseLSN(lsn: string): bigint { const [hi, lo] = lsn.split('/'); if (!hi || !lo) throw new Error(`Invalid LSN format: ${lsn}`); return (BigInt(`0x${hi}`) << 32n) | BigInt(`0x${lo}`); } export async function consolidateBackupsStrict( backupDirs: string[], targetDir: string ): Promise<void> { if (backupDirs.length === 0) { throw new Error('Backup chain cannot be empty'); } const parsedManifests: Array<{ dir: string; manifest: Manifest }> = []; // 1. Verify manifest validity and extract metadata for (const dir of backupDirs) { const manifestPath = path.join(dir, 'backup_manifest'); execSync(`pg_verifybackup -m "${manifestPath}" "${dir}"`, { stdio: 'pipe' }); const rawContent = await fs.readFile(manifestPath, 'utf8'); parsedManifests.push({ dir, manifest: JSON.parse(rawContent) as Manifest }); } // 2. Validate System-Identifier uniformity const primarySystemId = parsedManifests[0].manifest['System-Identifier']; for (let i = 1; i < parsedManifests.length; i++) { if (parsedManifests[i].manifest['System-Identifier'] !== primarySystemId) { throw new Error(`System-Identifier mismatch at index ${i}. Chain is corrupted.`); } } // 3. Enforce Monotonic LSN Continuity: Start-LSN(N) <= End-LSN(N-1) for (let i = 1; i < parsedManifests.length; i++) { const prevEnd = parseLSN(parsedManifests[i - 1].manifest['End-LSN']); const currentStart = parseLSN(parsedManifests[i].manifest['Start-LSN']); if (currentStart > prevEnd) { throw new Error( `LSN gap detected between backup ${i - 1} (End: ${parsedManifests[i - 1].manifest['End-LSN']}) ` + `and backup ${i} (Start: ${parsedManifests[i].manifest['Start-LSN']}). PITR impossible without full backup.` ); } } // 4. Safe consolidation via pg_combinebackup const dirArgs = parsedManifests.map((m) => `"${m.dir}"`).join(' '); execSync(`pg_combinebackup ${dirArgs} -o "${targetDir}"`, { stdio: 'pipe' }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment

PostgreSQL 17 Block-Level Incremental Backup LSN Monotonicity and Synthetic Full Alignment defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 native block-level incremental backups depend on contiguous, monotonically increasing Log Sequence Numbers (LSN) recorded across successive pg_combinebackup manifests. Orchestration layers must strictly validate that an incremental backup's start LSN exactly matches or precedes the prior backup's stop LSN and verifies pg_waldump consistency before synthesising a full backup, preventing silent corruption during point-in-time recovery (PITR). 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: Unvalidated LSN Gap in Incremental Backup Chains and Blind pg_combinebackup Execution 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	Unvalidated LSN Gap in Incremental Backup Chains and Blind pg_combinebackup Execution	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 for every incremental backup step N, start_lsn(N) <= stop_lsn(N-1) within the identical Timeline ID (TLI), ensuring zero unreferenced WAL gaps.

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.