Postgres 17 Incremental Backup Chain and LSN Continuity Verification

--- description: Postgres 17 Incremental Backup Chain and LSN Continuity Verification - Postgres 17 native block-level incremental backups reconstructed via pg_combinebackup require absolute continuity in Log Sequence Numbers (LSN) and valid manifest checksums across every delta in the chain. Failure to cryptographically verify backup manifests and validate start/end LSN bounds before combination risks synthesizing corrupt clusters and unrecoverable point-in-time recovery (PITR) states. globs: src/services/backup/**/*.ts, src/infrastructure/database/**/*.ts, scripts/backup/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-pg-combinebackup-chain-verification" --- # Postgres 17 Incremental Backup Chain and LSN Continuity Verification > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Blind Reconstruction of Incremental Backup Chains Without Manifest Checksums and LSN Continuity Validation ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 block-level incremental backup standards. You must mandate strict manifest checksum validation, monotonic LSN progression checks, and dry-run verification prior to any pg_combinebackup execution. ## Strict Guidelines - Engineers MUST ALWAYS execute pg_verifybackup on every individual incremental tier and base backup manifest before invoking pg_combinebackup. - Engineers MUST validate that Start-LSN of increment N is strictly less than or equal to End-LSN of increment N-1, ensuring unbroken WAL continuity without gap intervals. - Services MUST verify that all incremental backups in the combine chain share the identical Database System Identifier and belong to compatible timeline branches. - Engineers MUST NOT rely on filesystem timestamps or lexicographical directory sorting to establish backup chain ordering; manifests MUST be parsed to derive topological LSN order. - Reconstruction pipelines MUST run pg_combinebackup with dry-run verification or check output exit codes before swapping synthetic full backups into active recovery targets. ## Bad vs Good Implementations ### Unvalidated pg_combinebackup Execution #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: This function relies on directory name sorting instead of parsing backup manifests. It ignores LSN continuity, timeline IDs, and individual delta manifest checksums. If a middle incremental backup is corrupted or missing, pg_combinebackup may fail late or produce an inconsistent recovery image. ```typescript import { execFile } from 'node:child_process'; import { promisify } from 'node:util'; const execFileAsync = promisify(execFile); export async function synthesizeFullBackup(backupDirs: string[], outputDir: string): Promise<void> { // Anti-pattern: Sorting by folder name/mtime without reading manifests // and executing pg_combinebackup directly without prior pg_verifybackup validation. const sortedDirs = backupDirs.sort(); await execFileAsync('pg_combinebackup', [...sortedDirs, '-o', outputDir]); } ``` #### Production Standard (Correct) > **Standard**: The production-grade implementation runs pg_verifybackup on every chunk, verifies that all backups belong to the same Postgres system identifier, sorts them by Start-LSN parsed into 64-bit integers, verifies no LSN gap exists between adjacent deltas, and passes the strictly verified chain to pg_combinebackup. ```typescript import { execFile } from 'node:child_process'; import { readFile } from 'node:fs/promises'; import { join } from 'node:path'; import { promisify } from 'node:util'; const execFileAsync = promisify(execFile); interface BackupManifest { 'PostgreSQL-Backup-Manifest-Version': number; 'System-Identifier': string; 'Start-LSN': string; 'End-LSN': string; 'Timeline-History': Array<{ 'Timeline-ID': number; 'End-LSN': string }>; } function parseLsn(lsn: string): bigint { const [high, low] = lsn.split('/').map((hex) => BigInt(`0x${hex}`)); return (high << 32n) | low; } export async function synthesizeFullBackup(backupDirs: string[], outputDir: string): Promise<void> { // 1. Validate manifests and parse metadata const manifests: Array<{ dir: string; manifest: BackupManifest }> = []; for (const dir of backupDirs) { // Verify integrity of each backup chunk before combining await execFileAsync('pg_verifybackup', ['-m', join(dir, 'backup_manifest'), dir]); const rawManifest = await readFile(join(dir, 'backup_manifest'), 'utf-8'); const parsed: BackupManifest = JSON.parse(rawManifest); manifests.push({ dir, manifest: parsed }); } // 2. Validate identical System-Identifier const systemId = manifests[0].manifest['System-Identifier']; for (const item of manifests) { if (item.manifest['System-Identifier'] !== systemId) { throw new Error(`System Identifier mismatch in backup delta: ${item.dir}`); } } // 3. Sort strictly by Start-LSN progression manifests.sort((a, b) => { const lsnA = parseLsn(a.manifest['Start-LSN']); const lsnB = parseLsn(b.manifest['Start-LSN']); return lsnA < lsnB ? -1 : lsnA > lsnB ? 1 : 0; }); // 4. Assert monotonic LSN continuity across chain for (let i = 1; i < manifests.length; i++) { const prevEnd = parseLsn(manifests[i - 1].manifest['End-LSN']); const currStart = parseLsn(manifests[i].manifest['Start-LSN']); if (currStart > prevEnd) { throw new Error(`LSN gap detected between ${manifests[i - 1].dir} and ${manifests[i].dir}`); } } // 5. Run pg_combinebackup in verified topological order const orderedDirs = manifests.map((m) => m.dir); await execFileAsync('pg_combinebackup', [...orderedDirs, '-o', outputDir]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Incremental Backup Chain and LSN Continuity Verification

Postgres 17 Incremental Backup Chain and LSN Continuity Verification defines an industrial-grade behavioral standard for backend engineering. Postgres 17 native block-level incremental backups reconstructed via pg_combinebackup require absolute continuity in Log Sequence Numbers (LSN) and valid manifest checksums across every delta in the chain. Failure to cryptographically verify backup manifests and validate start/end LSN bounds before combination risks synthesizing corrupt clusters and unrecoverable point-in-time recovery (PITR) states. 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 Reconstruction of Incremental Backup Chains Without Manifest Checksums and LSN Continuity Validation 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 Reconstruction of Incremental Backup Chains Without Manifest Checksums and LSN Continuity Validation	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 execute pg_verifybackup on every individual incremental tier and base backup manifest before invoking 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.