PostgreSQL 17 pg_combinebackup LSN Chain and Manifest Integrity Attestation

--- description: PostgreSQL 17 pg_combinebackup LSN Chain and Manifest Integrity Attestation - Enforces strict cryptographic verification and monotonic Log Sequence Number (LSN) continuity checks across cumulative and differential backup manifests before executing PostgreSQL 17 pg_combinebackup. Prevents silent database corruption, timeline collision, and catastrophic point-in-time recovery failures. globs: src/backup/**/*.{ts,js,py,go}, scripts/recovery/**/*.{sh,ts,py}, infra/k8s/operators/**/*.{ts,go} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-combinebackup-lsn-chain-attestation" --- # PostgreSQL 17 pg_combinebackup LSN Chain and Manifest Integrity Attestation > **Severity**: CRITICAL | **Category**: devops > **Anti-Pattern Warning**: Invoking pg_combinebackup directly on directory sets without pre-flight manifest cryptographic attestation and LSN range continuity validation. ## System Constraints You are a Principal Database Infrastructure Architect and Systems Safety Engineer enforcing PostgreSQL 17 backup integrity invariants. Mandate that any backup orchestrator or restore automation strictly validates `backup_manifest` checksums, System Identifiers, Timeline Identifiers (TLI), and contiguous `Start-LSN`/`End-LSN` ranges across all parent-child backup chains prior to invoking `pg_combinebackup`. Disallow any unverified filesystem concatenation or directory-order-only restoration pipelines. ## Strict Guidelines - Engineers MUST ALWAYS verify the SHA-256 signature of every `backup_manifest` using `pg_verifybackup` or programmatic attestation before extracting or combining backups. - Engineers MUST ALWAYS assert that `System-Identifier` is identical across the full base backup and all subsequent incremental backup manifests in the target chain. - Engineers MUST ALWAYS validate LSN continuity: the `Start-LSN` of incremental backup N must exactly match or overlap within the valid range of the `End-LSN` of backup N-1. - Engineers MUST NEVER invoke `pg_combinebackup` based purely on timestamp, directory naming convention, or lexicographical sorting without parsing the underlying WAL ranges. - Services MUST reject the restore operation and trigger an alert if any backup in the chain exhibits a timeline discontinuity without an explicit, validated promotion branch point. - Components MUST perform target directory pre-flight isolation, ensuring the restore target is clean, unmounted from running clusters, and possesses required POSIX permissions. ## Bad vs Good Implementations ### PostgreSQL 17 Incremental Chain Verification and Combination #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The anti-pattern sorts backup folders by path/date without parsing `backup_manifest`. It fails to verify the SHA-256 manifest checksums, does not check for matching `System-Identifier` values, and ignores potential LSN gaps. Running `pg_combinebackup` on an invalid or disjoint chain can result in unbootable clusters, silent block corruption, or unrecoverable WAL errors during PITR. ```typescript import { execFile } from 'node:child_process'; import { promisify } from 'node:util'; import * as fs from 'node:fs/promises'; const execFileAsync = promisify(execFile); // ANTI-PATTERN: Relies on folder naming/mtime without validating manifest cryptographic // integrity, system identifiers, or LSN sequence continuity before running pg_combinebackup. export async function restoreIncrementalBackups(backupDirs: string[], targetDir: string): Promise<void> { // Arbitrary sorting by folder name assumes sequence without LSN proof const sortedDirs = backupDirs.sort(); // Blind execution of pg_combinebackup risks corrupting the target cluster if an intermediate // incremental backup is missing, corrupted, or from an alternate timeline/system. await execFileAsync('pg_combinebackup', [...sortedDirs, '-o', targetDir]); } ``` #### Production Standard (Correct) > **Standard**: The production-grade implementation strictly validates every backup prior to running `pg_combinebackup`. It utilizes `pg_verifybackup` to confirm manifest cryptographic signatures, parses LSN strings into 64-bit integers for precise ordinal comparison, verifies uniform `System-Identifier` hashes across all chain elements, and mathematically asserts LSN continuity to guarantee no intermediate delta WAL segments are missing. ```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'; import * as crypto from 'node:crypto'; const execFileAsync = promisify(execFile); interface BackupManifest { 'PostgreSQL-Backup-Manifest-Version': number; 'System-Identifier': string; 'WAL-Ranges': Array<{ Timeline: number; 'Start-LSN': string; 'End-LSN': string; }>; 'Manifest-Checksum': string; } export interface VerifiedBackupNode { backupDir: string; manifest: BackupManifest; startLsn: bigint; endLsn: bigint; timeline: number; systemIdentifier: string; } export function parseLsn(lsn: string): bigint { const [hi, lo] = lsn.split('/').map((segment) => BigInt(`0x${segment}`)); return (hi << 32n) | lo; } export async function verifyAndCombineBackups( backupDirs: string[], targetDir: string ): Promise<void> { if (backupDirs.length === 0) { throw new Error('At least one base backup is required for pg_combinebackup.'); } const verifiedNodes: VerifiedBackupNode[] = []; for (const dir of backupDirs) { // 1. Attest manifest presence and run pg_verifybackup const manifestPath = path.join(dir, 'backup_manifest'); await fs.access(manifestPath); await execFileAsync('pg_verifybackup', ['-m', manifestPath, dir]); const rawManifest = await fs.readFile(manifestPath, 'utf-8'); const manifest = JSON.parse(rawManifest) as BackupManifest; if (!manifest['WAL-Ranges'] || manifest['WAL-Ranges'].length === 0) { throw new Error(`Invalid manifest structure in directory: ${dir}`); } const primaryRange = manifest['WAL-Ranges'][0]; verifiedNodes.push({ backupDir: dir, manifest, startLsn: parseLsn(primaryRange['Start-LSN']), endLsn: parseLsn(primaryRange['End-LSN']), timeline: primaryRange.Timeline, systemIdentifier: manifest['System-Identifier'], }); } // 2. Sort explicitly by Start-LSN ascending verifiedNodes.sort((a, b) => (a.startLsn < b.startLsn ? -1 : a.startLsn > b.startLsn ? 1 : 0)); // 3. Attest System-Identifier consistency, timeline continuity, and unbroken LSN chain const rootSystemId = verifiedNodes[0].systemIdentifier; for (let i = 0; i < verifiedNodes.length; i++) { const current = verifiedNodes[i]; if (current.systemIdentifier !== rootSystemId) { throw new Error( `System-Identifier mismatch at ${current.backupDir}. Expected: ${rootSystemId}, Got: ${current.systemIdentifier}` ); } if (i > 0) { const previous = verifiedNodes[i - 1]; if (current.timeline !== previous.timeline) { throw new Error(`Timeline break detected without valid fork: ${previous.timeline} -> ${current.timeline}`); } // Incremental start must not exceed previous end (no WAL gaps permitted) if (current.startLsn > previous.endLsn) { throw new Error( `LSN sequence gap detected between ${previous.backupDir} (End: ${previous.endLsn}) and ${current.backupDir} (Start: ${current.startLsn})` ); } } } // 4. Safe invocation of pg_combinebackup with verified monotonic sequence const orderedDirs = verifiedNodes.map((n) => n.backupDir); await execFileAsync('pg_combinebackup', [...orderedDirs, '-o', targetDir]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 pg_combinebackup LSN Chain and Manifest Integrity Attestation

PostgreSQL 17 pg_combinebackup LSN Chain and Manifest Integrity Attestation defines an industrial-grade behavioral standard for devops engineering. Enforces strict cryptographic verification and monotonic Log Sequence Number (LSN) continuity checks across cumulative and differential backup manifests before executing PostgreSQL 17 pg_combinebackup. Prevents silent database corruption, timeline collision, and catastrophic point-in-time recovery failures. 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: Invoking pg_combinebackup directly on directory sets without pre-flight manifest cryptographic attestation and LSN range 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	Invoking pg_combinebackup directly on directory sets without pre-flight manifest cryptographic attestation and LSN range 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 verify the SHA-256 signature of every `backup_manifest` using `pg_verifybackup` or programmatic attestation before extracting or combining backups.

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.