PostgreSQL 17 Block-Level Incremental Backup LSN Chain Consistency Invariant

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Executing unverified pg_combinebackup invocations over broken manifest chains or unsummarized WAL intervals) 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.

--- description: PostgreSQL 17 Block-Level Incremental Backup LSN Chain Consistency Invariant - Enforces unbroken LSN sequence validation and WAL summarization verification across PostgreSQL 17 block-level incremental backup manifests. Guarantees byte-accurate synthetic full reconstruction via pg_combinebackup without unsummarized WAL voids or corrupted differential delta merges. globs: src/backup/**/*.{ts,js,py,go}, src/services/database/**/*.{ts,js,py,go}, src/infra/backup/**/*.{ts,js,py,go}, scripts/dr/**/*.{sh,ts,py} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-incremental-backup-lsn-consistency" --- # PostgreSQL 17 Block-Level Incremental Backup LSN Chain Consistency Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing unverified pg_combinebackup invocations over broken manifest chains or unsummarized WAL intervals ## System Constraints You are a Principal Software Architect enforcing zero-data-loss database disaster recovery standards for PostgreSQL 17. You mandate strict verification of WAL summarization (`summarize_wal = on`), strict monotonic LSN continuity checks between base manifests and incremental manifests, pre-flight `pg_verifybackup` validation for every backup generation, and atomic synthetic full reconstitution using `pg_combinebackup`. ## Strict Guidelines - Engineers MUST ALWAYS verify that `summarize_wal` is enabled and that WAL summary files cover the entire LSN range between parent and child backups before scheduling an incremental backup. - Engineers MUST ALWAYS execute `pg_verifybackup` against each individual backup manifest (`backup_manifest`) before feeding target directories into `pg_combinebackup`. - Engineers MUST ALWAYS validate LSN sequence continuity: the `Start-LSN` of incremental backup `N` must match or precede the `End-LSN` checkpoint recorded in backup `N-1` across matching timelines. - Engineers MUST NOT execute `pg_combinebackup` directly on unverified backup tiers or if any intermediate incremental manifest in the backup chain is missing, unreadable, or timeline-discontinuous. - Services MUST verify the cryptographic manifest hashes of the resulting synthetic full backup generated by `pg_combinebackup` before cataloging it as a viable restore point. - Components NEVER execute raw file copies or rsync-based delta syncs alongside Postgres 17 block-level incremental backups, as native dirty-block tracking depends strictly on consistent WAL summary state. ## Bad vs Good Implementations ### Postgres 17 Incremental Backup Chain Validation & Synthesis #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Fails to inspect backup_manifest files, verify cryptographic checksums with pg_verifybackup, or check LSN continuity across the chain. If an intermediate incremental backup failed or a timeline diverged, pg_combinebackup will either fail midway or write corrupted block combinations that manifest during disaster recovery. ```typescript import { execSync } from 'node:child_process'; import * as fs from 'node:fs'; interface BackupJob { fullBackupDir: string; incrementalDirs: string[]; outputDir: string; } export async function synthesizeFullBackup(job: BackupJob): Promise<void> { // Anti-pattern: Blindly executing pg_combinebackup without validating manifest LSN continuity, // verifying checksums, or validating that intermediate WAL summarization remained intact. // A single missing or corrupted intermediate backup yields an unusable synthetic full backup. const inputs = [job.fullBackupDir, ...job.incrementalDirs].join(' '); execSync(`pg_combinebackup ${inputs} -o ${job.outputDir}`); console.log('Synthetic full backup generated successfully'); } ``` #### Production Standard (Correct) > **Standard**: Validates the cryptographic manifest integrity of all parent and child backups, checks monotonic LSN continuity without gaps, enforces matching timeline bounds, and confirms that the reconstituted synthetic full backup is cryptographically verified before completion. ```typescript import { execFile } from 'node:child_process'; import { promisify } from 'node:util'; import * as fs from 'node:fs/promises'; import * as path from 'node:path'; const execFileAsync = promisify(execFile); interface BackupManifest { 'PostgreSQL-Backup-Manifest-Version': number; Files: Record<string, { Size: number; 'Last-Modified': string; 'Checksum-Algorithm': string; Checksum: string }>; WALRanges: Array<{ Timeline: number; StartLSN: string; EndLSN: string }>; ManifestChecksum: string; } interface BackupChainSpec { baseDir: string; incrementalDirs: string[]; outputDir: string; } export async function synthesizeFullBackup(spec: BackupChainSpec): Promise<void> { const orderedDirs = [spec.baseDir, ...spec.incrementalDirs]; let previousEndLsn: bigint | null = null; let activeTimeline: number | null = null; for (const dir of orderedDirs) { // 1. Verify manifest integrity with pg_verifybackup await execFileAsync('pg_verifybackup', ['-m', path.join(dir, 'backup_manifest'), dir]); // 2. Parse and validate manifest LSN continuity const manifestRaw = await fs.readFile(path.join(dir, 'backup_manifest'), 'utf8'); const manifest: BackupManifest = JSON.parse(manifestRaw); if (!manifest.WALRanges || manifest.WALRanges.length === 0) { throw new Error(`Invalid manifest in ${dir}: missing WALRanges`); } const primaryRange = manifest.WALRanges[0]; const [startFile, startOffset] = primaryRange.StartLSN.split('/').map((h) => BigInt(`0x${h}`)); const [endFile, endOffset] = primaryRange.EndLSN.split('/').map((h) => BigInt(`0x${h}`)); const startLsn = (startFile << 32n) | startOffset; const endLsn = (endFile << 32n) | endOffset; if (activeTimeline !== null && primaryRange.Timeline !== activeTimeline) { throw new Error(`Timeline divergence detected at ${dir}: expected ${activeTimeline}, got ${primaryRange.Timeline}`); } activeTimeline = primaryRange.Timeline; if (previousEndLsn !== null && startLsn > previousEndLsn) { throw new Error(`LSN gap detected: expected <= ${previousEndLsn.toString(16)}, got ${startLsn.toString(16)}`); } previousEndLsn = endLsn; } // 3. Atomically synthesize validated synthetic full backup await execFileAsync('pg_combinebackup', [...orderedDirs, '-o', spec.outputDir]); // 4. Assert integrity of the resultant synthetic full backup await execFileAsync('pg_verifybackup', ['-m', path.join(spec.outputDir, 'backup_manifest'), spec.outputDir]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Block-Level Incremental Backup LSN Chain Consistency Invariant

PostgreSQL 17 Block-Level Incremental Backup LSN Chain Consistency Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces unbroken LSN sequence validation and WAL summarization verification across PostgreSQL 17 block-level incremental backup manifests. Guarantees byte-accurate synthetic full reconstruction via pg_combinebackup without unsummarized WAL voids or corrupted differential delta merges. 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 unverified pg_combinebackup invocations over broken manifest chains or unsummarized WAL intervals 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 unverified pg_combinebackup invocations over broken manifest chains or unsummarized WAL intervals	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 `summarize_wal` is enabled and that WAL summary files cover the entire LSN range between parent and child backups before scheduling an incremental backup.

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.