Research: VolumeSnapshot crash-consistency for in-cluster databases

[vriesdemichael]

Context

Phase 2 of the blue-green upgrade strategy (PR #639) introduces VolumeSnapshot-based pre-upgrade backups for the "managed" database tier (in-cluster PostgreSQL with a PVC in the same namespace as Keycloak).

Problem

A VolumeSnapshot of a PVC backing a running PostgreSQL instance produces a crash-consistent image, not a clean application-consistent backup. PostgreSQL can generally recover from this via WAL replay, but:

1. This assumption needs to be validated for PostgreSQL specifically
2. Other popular in-cluster databases (e.g., MySQL, MariaDB) may have different crash-recovery guarantees
3. The operator should understand the blast radius if WAL replay fails or if the snapshot catches a partially-written page

Research Tasks

• Document PostgreSQL crash-recovery guarantees from a VolumeSnapshot (WAL replay, full_page_writes, data directory layout)
• Research whether a pre-snapshot CHECKPOINT command improves reliability (and whether we can/should issue one)
• Investigate MySQL/MariaDB crash-consistency behavior with VolumeSnapshots
• Survey what other operators do (e.g., Zalando postgres-operator, Percona operators) for snapshot-based backups
• Determine if CSI snapshot hooks or pre/post-snapshot scripts are available in common CSI drivers
• Document findings as an ADR with clear recommendations for which databases are safe for VolumeSnapshot backup

Acceptance Criteria

• ADR documenting crash-consistency guarantees per database engine
• Clear guidance on whether the operator should attempt a CHECKPOINT/FLUSH before snapshotting
• List of supported database engines for the VolumeSnapshot backup tier
• If VolumeSnapshot is deemed unreliable for certain engines, recommend alternatives (e.g., require CNPG for automated backup)

Labels

Phase 2, blue-green, backup, research