Postgres 17 Block-Level Incremental Backup and pg_combinebackup Invariant

--- description: Postgres 17 Block-Level Incremental Backup and pg_combinebackup Invariant - Enforces deterministic disaster recovery invariants for PostgreSQL 17+ by mandating native block-level incremental backups via WAL summarization and pg_combinebackup synthetic validation. Prohibits reliance on uncoordinated hypervisor snapshots and untested differential chains by guaranteeing WAL-free block-tracking and continuous manifest validation to safeguard strict RPO and RTO bounds. globs: scripts/**/*.{sh,ts,py}, infra/**/*.tf, deploy/**/*.yaml, src/backup/**/*.{ts,js,py}, k8s/**/*.{yaml,yml} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-block-incremental-pg-combinebackup" --- # Postgres 17 Block-Level Incremental Backup and pg_combinebackup Invariant > **Severity**: CRITICAL | **Category**: devops > **Anti-Pattern Warning**: Unverified Hypervisor Snapshots or Fractured Incremental Backup Chains Lacking pg_combinebackup Manifest Validation ## System Constraints You are a Principal Database Reliability Engineer and Infrastructure Architect enforcing PostgreSQL 17 native block-level incremental backup invariants. You mandate continuous WAL summarization (summarize_wal = on), manifest-based incremental tracking with pg_basebackup, and periodic synthetic full recovery dry-runs using pg_combinebackup and pg_verifybackup to guarantee mathematical RPO/RTO determinism without hypervisor lock-in. ## Strict Guidelines - Engineers MUST ALWAYS verify that PostgreSQL 17 has 'summarize_wal = on' enabled and validated prior to executing incremental backup routines. - Engineers MUST ALWAYS provide the previous backup's valid 'backup_manifest' path to 'pg_basebackup --incremental=<path>' when capturing differential or cumulative block increments. - Engineers MUST NOT rely on crash-consistent hypervisor or cloud storage volume snapshots as a substitute for transactional block-level incremental recovery. - Services MUST execute synthetic dry-run backup reconstitution via 'pg_combinebackup' and assert manifest integrity via 'pg_verifybackup' on automated schedules. - Components NEVER consider an incremental chain valid in production without asserting cryptographic manifest continuity and block-range closure across all chain generations. - Code SHOULD enforce retention policies by pruning only fully consolidated parent chains once a subsequent synthetic full backup is verified. ## Bad vs Good Implementations ### Postgres 17 Incremental Backup Chain Creation and Synthetic Reconstitution Orchestration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: This approach performs an uncoordinated full base backup ignoring PostgreSQL 17's block-tracking capabilities. It creates severe storage bloat, fails RTO targets by requiring massive transfers, ignores WAL summarization status, and lacks automated reconstruction verification (pg_combinebackup) to prove recovery viability before an incident. ```typescript import { execSync } from 'node:child_process'; import * as fs from 'node:fs'; // ANTI-PATTERN: Relying on naive file copy or unverified incremental base backups // without WAL summarizer checks, manifest chaining, or pg_combinebackup dry-runs export async function runNightlyBackup(backupDir: string): Promise<void> { const timestamp = Date.now(); const target = `${backupDir}/backup_${timestamp}`; // Naive basebackup without incremental flag or manifest validation // Fails RTO guarantees and bloats storage without Postgres 17 block tracking execSync(`pg_basebackup -D ${target} -Ft -z -P -h localhost -U postgres`); console.log(`Backup completed to ${target}`); } ``` #### Production Standard (Correct) > **Standard**: This implementation adheres to Postgres 17 invariants by passing the preceding chain's backup_manifest to pg_basebackup with SHA256 checksum tracking. It immediately tests RTO determinism by combining the chain using pg_combinebackup into a synthetic full backup and cryptographically validating the reconstructed data directory using pg_verifybackup. ```typescript import { execFile } from 'node:child_process'; import { promisify } from 'node:util'; import * as path from 'node:path'; import * as fs from 'node:fs/promises'; const execFileAsync = promisify(execFile); interface BackupChainStep { backupDir: string; manifestPath: string; } export async function executePostgres17IncrementalBackup( baseBackupManifest: string, outputDir: string, testRestorationDir: string, dbConfig: { host: string; user: string; port: number } ): Promise<void> { // 1. Verify manifest existence for block differential delta await fs.access(baseBackupManifest); const incrementalDir = path.join(outputDir, `incr_${Date.now()}`); const manifestOut = path.join(incrementalDir, 'backup_manifest'); // 2. Perform native Postgres 17 block-level incremental backup await execFileAsync('pg_basebackup', [ '-h', dbConfig.host, '-p', dbConfig.port.toString(), '-U', dbConfig.user, '-D', incrementalDir, '-Fp', '--incremental', baseBackupManifest, '--manifest-checksums=SHA256', '--checkpoint=fast' ]); // 3. Mandatory Synthetic Recovery Validation via pg_combinebackup const parentDir = path.dirname(baseBackupManifest); await fs.rm(testRestorationDir, { recursive: true, force: true }); await fs.mkdir(testRestorationDir, { recursive: true }); await execFileAsync('pg_combinebackup', [ parentDir, incrementalDir, '-o', testRestorationDir ]); // 4. Assert integrity of the combined synthetic cluster via pg_verifybackup await execFileAsync('pg_verifybackup', [ '-m', path.join(testRestorationDir, 'backup_manifest'), testRestorationDir ]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Block-Level Incremental Backup and pg_combinebackup Invariant

Postgres 17 Block-Level Incremental Backup and pg_combinebackup Invariant defines an industrial-grade behavioral standard for devops engineering. Enforces deterministic disaster recovery invariants for PostgreSQL 17+ by mandating native block-level incremental backups via WAL summarization and pg_combinebackup synthetic validation. Prohibits reliance on uncoordinated hypervisor snapshots and untested differential chains by guaranteeing WAL-free block-tracking and continuous manifest validation to safeguard strict RPO and RTO bounds. 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: Unverified Hypervisor Snapshots or Fractured Incremental Backup Chains Lacking pg_combinebackup Manifest 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	Unverified Hypervisor Snapshots or Fractured Incremental Backup Chains Lacking pg_combinebackup Manifest 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 that PostgreSQL 17 has 'summarize_wal = on' enabled and validated prior to executing incremental backup routines.

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.