PostgreSQL 17 Incremental Backup Cumulative LSN Chain and Manifest Verification

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Executing pg_combinebackup blindly without pre-validating manifest checksums, timeline histories, and contiguous LSN ranges across backup segments.) 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 Incremental Backup Cumulative LSN Chain and Manifest Verification

--- description: PostgreSQL 17 Incremental Backup Cumulative LSN Chain and Manifest Verification - Mandates cryptographic manifest integrity checks, timeline alignment, and contiguous Log Sequence Number (LSN) chain verification before and after invoking PostgreSQL 17 pg_combinebackup. Prevents silent data corruption, invalid block overlays, and unrecoverable point-in-time recovery states in enterprise backup orchestration. globs: src/disaster-recovery/**/*.{ts,js,py}, src/backup/**/*.{ts,js,py}, scripts/backup/**/*.{sh,py,ts}, infra/backup/**/*.{ts,js} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-incremental-backup-lsn-chain-verification" --- # PostgreSQL 17 Incremental Backup Cumulative LSN Chain and Manifest Verification > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing pg_combinebackup blindly without pre-validating manifest checksums, timeline histories, and contiguous LSN ranges across backup segments. ## System Constraints You are a Principal Software Architect enforcing the PostgreSQL 17 Incremental Backup Cumulative LSN Chain Verification Standard. You must mandate that any backup or disaster recovery orchestration service executing pg_combinebackup cryptographically validates backup manifests via pg_verifybackup, proves strict contiguous LSN intervals between base and incremental backups, asserts system identifier parity, and validates the resulting synthesized full backup. ## Strict Guidelines - Engineers MUST ALWAYS run pg_verifybackup on the base backup and all intermediate incremental backups prior to orchestrating pg_combinebackup. - Engineers MUST NEVER execute pg_combinebackup with disjoint, out-of-order, or gapped LSN intervals across sequential backup directories. - Services MUST verify that System-Identifier, Database-Version, and TimelineID in all backup_manifest files are strictly identical or follow valid timeline history switches. - Services MUST enforce that for every consecutive pair of backups (Backup N-1 and Backup N), the Start-LSN of Backup N is less than or equal to the End-LSN of Backup N-1, ensuring zero missing WAL/block intervals. - Components NEVER execute pg_combinebackup without capturing and persisting the combined backup's newly synthesized backup_manifest and immediately verifying it with pg_verifybackup. - Code SHOULD enforce strict numeric big-endian comparison of 64-bit LSN hex tokens (e.g., '16/B374D848') rather than standard lexicographical string sorting. ## Bad vs Good Implementations ### PostgreSQL 17 Incremental Backup Chain Verification and Combination #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Directly invoking pg_combinebackup without pre-flight validation can lead to silent block corruption if an incremental backup in the chain was taken against a divergent timeline, has missing intermediate delta blocks, or belongs to a different cluster instance. ```typescript import { execSync } from 'node:child_process'; import * as path from 'node:path'; interface BackupJob { baseDir: string; incrementalDirs: string[]; outputDir: string; } export async function restoreBackup(job: BackupJob): Promise<void> { // Anti-pattern: Blindly passing directories into pg_combinebackup without // verifying manifests, system identifiers, or LSN chain continuity. const inputs = [job.baseDir, ...job.incrementalDirs].join(' '); execSync(`pg_combinebackup ${inputs} -o ${job.outputDir}`); } ``` #### Production Standard (Correct) > **Standard**: Cryptographically validates each segment with pg_verifybackup, checks cluster System-Identifier uniformity, validates 64-bit LSN continuity without gaps, ensures timeline stability, and verifies the synthesized full backup upon completion. ```typescript import { execFileSync } from 'node:child_process'; import * as fs from 'node:fs/promises'; import * as path from 'node:path'; interface ManifestWALRange { 'Timeline': number; 'Start-LSN': string; 'End-LSN': string; } interface BackupManifest { 'PostgreSQL-Backup-Manifest-Version': number; 'System-Identifier': string; 'WAL-Ranges': ManifestWALRange[]; } interface BackupChainSpec { baseDir: string; incrementalDirs: string[]; targetOutputDir: string; } function parseLSN(lsn: string): bigint { const [high, low] = lsn.split('/'); if (!high || !low) throw new Error(`Malformed LSN string: ${lsn}`); return (BigInt(`0x${high}`) << 32n) | BigInt(`0x${low}`); } export async function combinePostgres17Backups(spec: BackupChainSpec): Promise<void> { const allDirs = [spec.baseDir, ...spec.incrementalDirs]; let expectedSystemId: string | null = null; let previousEndLSN: bigint | null = null; let previousTimeline: number | null = null; for (const dir of allDirs) { // 1. Cryptographically verify manifest and block checksums execFileSync('pg_verifybackup', ['-m', path.join(dir, 'backup_manifest'), dir], { stdio: 'pipe', }); // 2. Parse backup_manifest and assert strict chain invariants const manifestContent = await fs.readFile(path.join(dir, 'backup_manifest'), 'utf-8'); const manifest = JSON.parse(manifestContent) as BackupManifest; if (expectedSystemId === null) { expectedSystemId = manifest['System-Identifier']; } else if (manifest['System-Identifier'] !== expectedSystemId) { throw new Error(`Cluster System-Identifier mismatch in backup dir: ${dir}`); } const walRange = manifest['WAL-Ranges'][0]; if (!walRange) { throw new Error(`Missing WAL range metadata in manifest: ${dir}`); } const currentStartLSN = parseLSN(walRange['Start-LSN']); const currentEndLSN = parseLSN(walRange['End-LSN']); if (previousEndLSN !== null) { if (walRange.Timeline !== previousTimeline) { throw new Error(`Timeline divergence detected between consecutive backups`); } // Continuous chain assertion: Start of current increment must not exceed end of prior if (currentStartLSN > previousEndLSN) { throw new Error( `LSN gap detected: prior End-LSN (${previousEndLSN}) < current Start-LSN (${currentStartLSN})` ); } } previousEndLSN = currentEndLSN; previousTimeline = walRange.Timeline; } // 3. Perform atomic pg_combinebackup execFileSync('pg_combinebackup', [...allDirs, '-o', spec.targetOutputDir], { stdio: 'pipe', }); // 4. Verify resulting synthetic base backup manifest execFileSync( 'pg_verifybackup', ['-m', path.join(spec.targetOutputDir, 'backup_manifest'), spec.targetOutputDir], { stdio: 'pipe' } ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Incremental Backup Cumulative LSN Chain and Manifest Verification

PostgreSQL 17 Incremental Backup Cumulative LSN Chain and Manifest Verification defines an industrial-grade behavioral standard for backend engineering. Mandates cryptographic manifest integrity checks, timeline alignment, and contiguous Log Sequence Number (LSN) chain verification before and after invoking PostgreSQL 17 pg_combinebackup. Prevents silent data corruption, invalid block overlays, and unrecoverable point-in-time recovery states in enterprise backup orchestration. 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_combinebackup blindly without pre-validating manifest checksums, timeline histories, and contiguous LSN ranges across backup segments. 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_combinebackup blindly without pre-validating manifest checksums, timeline histories, and contiguous LSN ranges across backup segments.	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 run pg_verifybackup on the base backup and all intermediate incremental backups prior to orchestrating 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.