Postgres 17 Incremental Backup LSN Monotonicity & Checkpoint Manifest Guardrail

--- description: Postgres 17 Incremental Backup LSN Monotonicity & Checkpoint Manifest Guardrail - Enforces strict monotonic Log Sequence Number (LSN) validation, timeline continuity, and cryptographic backup_manifest verification across PostgreSQL 17 incremental backup chains. Prevents storage-engine corruption and silent block divergence during pg_combinebackup delta synthetic full roll-forwards. globs: src/backup/**/*.{ts,js,py,sh}, scripts/db/**/*.{ts,js,py,sh}, infra/postgres/**/*.{ts,tf,yaml}, packages/db-admin/**/*.{ts,tsx} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-incremental-backup-lsn-monotonicity-standard" --- # Postgres 17 Incremental Backup LSN Monotonicity & Checkpoint Manifest Guardrail > **Severity**: CRITICAL | **Category**: devops > **Anti-Pattern Warning**: Blind Concatenation of Incremental Backups Without LSN Monotonicity and Manifest Cryptographic Verification ## System Constraints You are a Principal Database Infrastructure Architect enforcing the Postgres 17 Incremental Backup LSN Monotonicity Checkpoint Standard. PostgreSQL 17 native incremental backups rely on pg_combinebackup and cumulative LSN tracking recorded in backup_manifest files. You must mandate that every incremental backup stage verifies data block checksums, strictly validates that Child.Start-LSN <= Parent.Stop-LSN without timeline divergence, and executes pg_verifybackup prior to invoking pg_combinebackup. Reject any backup orchestration code that invokes delta roll-forwards without explicit LSN boundary assertions. ## Strict Guidelines - Engineers MUST ALWAYS verify cryptographic manifest checksums using pg_verifybackup on both the full base backup and all delta backups before initiating pg_combinebackup. - Engineers MUST ALWAYS parse and validate Log Sequence Numbers (LSN) as 64-bit unsigned integers to assert strict monotonicity: ChildBackup.StartLSN MUST be less than or equal to ParentBackup.EndLSN on the identical Timeline ID. - Engineers MUST NOT execute pg_combinebackup with arbitrary directories or unverified manifest chains, as missing WAL ranges or split-brain checkpoints corrupt page LSN headers. - Code SHOULD confirm that 'data_checksums = on' or 'wal_log_hints = on' is active on the source Postgres cluster to prevent silent block divergence during incremental block-reference extraction. - Services MUST abort delta reconstitution immediately and trigger a full tier-0 base backup if any LSN gap, timeline divergence, or manifest checksum mismatch is detected. ## Bad vs Good Implementations ### Postgres 17 Incremental Chain Roll-Forward Orchestrator #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The bad example blindly passes backup directories to pg_combinebackup without executing pg_verifybackup and without parsing backup_manifest to assert monotonic LSN boundaries. If an intermediate WAL range was missed or a split timeline occurred, pg_combinebackup can output a corrupted PGDATA directory without early abort guarantees. ```typescript import { execSync } from 'node:child_process'; import * as fs from 'node:fs'; interface BackupJob { fullBackupDir: string; deltaDirs: string[]; targetOutputDir: string; } export function restoreIncrementalChain(job: BackupJob): void { // Anti-Pattern: Invoking pg_combinebackup blindly without verifying // backup_manifest checksums or monotonic LSN continuity. const chainArgs = [job.fullBackupDir, ...job.deltaDirs].join(' '); console.log(`Combining backups into ${job.targetOutputDir}...`); execSync(`pg_combinebackup ${chainArgs} -o ${job.targetOutputDir}`, { stdio: 'inherit', }); } ``` #### Production Standard (Correct) > **Standard**: The good implementation parses hex LSN values into 64-bit bigints, verifies cryptographic integrity using pg_verifybackup, validates timeline stability, and strictly asserts the continuity condition (child.startLsn <= parent.endLsn) before synthesizing the target cluster with pg_combinebackup. ```typescript import { execFileSync } from 'node:child_process'; import * as fs from 'node:fs'; import * as path from 'node:path'; interface BackupManifest { 'PostgreSQL-Backup-Manifest-Version': number; 'Start-LSN': string; 'End-LSN': string; 'Timeline-History': Array<{ Timeline: number; LSN: string }>; [key: string]: unknown; } interface ValidatedBackupNode { dir: string; startLsn: bigint; endLsn: bigint; timeline: number; } function parseLsn(lsn: string): bigint { const [high, low] = lsn.split('/').map((part) => BigInt(`0x${part}`)); return (high << 32n) | low; } function inspectAndVerifyManifest(backupDir: string): ValidatedBackupNode { const manifestPath = path.join(backupDir, 'backup_manifest'); if (!fs.existsSync(manifestPath)) { throw new Error(`Integrity Violation: Missing backup_manifest in ${backupDir}`); } // Verify cryptographic checksums against the manifest execFileSync('pg_verifybackup', ['-m', manifestPath, backupDir], { stdio: ['ignore', 'pipe', 'pipe'], }); const raw = fs.readFileSync(manifestPath, 'utf-8'); const parsed = JSON.parse(raw) as BackupManifest; const currentTimeline = parsed['Timeline-History']?.length ? parsed['Timeline-History'][parsed['Timeline-History'].length - 1].Timeline : 1; return { dir: backupDir, startLsn: parseLsn(parsed['Start-LSN']), endLsn: parseLsn(parsed['End-LSN']), timeline: currentTimeline, }; } export function restoreIncrementalChainSecure( fullBackupDir: string, incrementalDirs: string[], targetOutputDir: string ): void { const fullBackup = inspectAndVerifyManifest(fullBackupDir); let currentParent = fullBackup; const verifiedChain = [fullBackup.dir]; for (const incDir of incrementalDirs) { const child = inspectAndVerifyManifest(incDir); if (child.timeline !== currentParent.timeline) { throw new Error( `Timeline mismatch: Parent timeline ${currentParent.timeline} !== Child timeline ${child.timeline}` ); } // Invariant: Child Start-LSN must not exceed Parent End-LSN if (child.startLsn > currentParent.endLsn) { throw new Error( `LSN Gap Detected: Child Start-LSN (${child.startLsn}) exceeds Parent End-LSN (${currentParent.endLsn}). Aborting.` ); } verifiedChain.push(child.dir); currentParent = child; } execFileSync('pg_combinebackup', [...verifiedChain, '-o', targetOutputDir], { stdio: ['ignore', 'inherit', 'pipe'], }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Incremental Backup LSN Monotonicity & Checkpoint Manifest Guardrail

Postgres 17 Incremental Backup LSN Monotonicity & Checkpoint Manifest Guardrail defines an industrial-grade behavioral standard for devops engineering. Enforces strict monotonic Log Sequence Number (LSN) validation, timeline continuity, and cryptographic backup_manifest verification across PostgreSQL 17 incremental backup chains. Prevents storage-engine corruption and silent block divergence during pg_combinebackup delta synthetic full roll-forwards. 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: Blind Concatenation of Incremental Backups Without LSN Monotonicity and Manifest Cryptographic Verification 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	Blind Concatenation of Incremental Backups Without LSN Monotonicity and Manifest Cryptographic Verification	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 cryptographic manifest checksums using pg_verifybackup on both the full base backup and all delta backups before initiating pg_combinebackup.

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.