Coroot database replica cannot recover its PostgreSQL checkpoint #4662

🤖 Generated by the Agentic Engineer

Description

A Coroot metadata database replica repeatedly restarts during PostgreSQL recovery because it cannot locate a valid checkpoint. Database readiness is incomplete. The primary and a healthy replica remain ready in the bounded readback; application availability and data integrity have not been established by those readiness checks.

This is a distinct symptom from the connection-budget problem tracked in #4652. Its initiating cause and relationship to preceding recovery activity remain unproven.

Steps to observe

1. Read the existing Coroot database readiness and instance restart state without changing production.
2. Examine a finite tail from the failed replica's previous PostgreSQL startup.
3. Join the recovery failure with the operator's current primary, target primary and readiness conditions.

Expected behavior

Every declared database replica completes recovery, remains ready, and serves the intended replication role.

Actual behavior

Bounded observations on 2026-10-08 show repeated startup failures containing the checkpoint error and an operator readiness condition that remains false. WAL archiving and the last-backup conditions report success, but those conditions alone do not prove a backup restore or complete data integrity.

An independent recovery then completed a replacement bootstrap. Subsequent readback still shows incomplete replica readiness and repeated adoption of the completed bootstrap job. A storage-management handoff remains incomplete; this is a candidate immediate recovery blocker, not proof of the initiating checkpoint failure's cause.

Environment

Production Coroot metadata database managed by CloudNativePG 1.30.1. Raw logs, storage identifiers, checkpoint positions and recovery commands remain private.

Recovery and acceptance

• Preserve the healthy database members and retain diagnostic evidence from the failed replica.
• Establish the initiating failure using checkpoint/WAL and storage evidence; do not infer it from restart timing alone.
• Prepare and validate a supported, targeted replica recovery that retains the failed data for diagnosis and does not reset the primary's WAL or broaden a deploy exception.
• Read back complete database readiness and a useful Coroot application response after recovery. Recheck health after the next protected deployment.
• If a source or configuration defect is established, deliver its regression test and durable repair through the normal reviewed pipeline. Immediate recovery alone does not close this issue.

Recovery readback, 2026-10-08: A later bounded production read found every declared metadata database instance Ready and every declared Coroot server replica Ready. The readiness recovery happened independently; this run made no production changes. The earlier guarded storage handoff is stale and must not be applied. The initiating checkpoint failure cause remains unproven. The public application route returns its sign-in redirect, which does not prove a useful authenticated application response.

Remaining acceptance: establish the initiating checkpoint failure using retained checkpoint/WAL and storage evidence, and demonstrate supported durable prevention. The authenticated functional-response gap is cleared by the verification below; recovery readiness alone does not close this issue.

Part of #2043.

Verification checkpoint, 2026-10-11 00:36 UTC: The successful protected deployment was read from GitHub, and a fresh scoped cluster read confirmed readiness after it. Readiness does not demonstrate a useful authenticated application response, explain the original checkpoint failure, or prove prevention. No recovery or cluster mutation was performed; acceptance criteria and historical evidence are preserved.

Verification checkpoint, 2026-10-11 08:30 UTC: The existing browser session returned a populated Coroot application inventory and current alert data through the protected application route. A separate request without session credentials returned a sign-in redirect. Fresh scoped readback confirmed every declared metadata database member and every running workload in the observability namespace Ready. Native protected merge-group run 38085553374 was independently rechecked as successful. This establishes a useful application response after protected deployment. It does not establish the initiating checkpoint failure cause, durable prevention, data integrity or alert clearance. No recovery, credential, access-policy or production change was made; the historical recovery patch remains stale. The issue stays open for its remaining acceptance criteria.

Forensic checkpoint, 2026-10-12 21:20 UTC: Fresh scoped readback found all three declared database instances Ready, with zero restarts in their current Pod lifetimes. Those Pods were created after the 8 October incident. The affected persistent claim has the same identity as the retained incident receipt; that receipt records a successful replacement base backup before readiness recovered. The completed recovery job is no longer present, and the current event query contains no incident record. These facts do not prove that the original failed data or its causal storage evidence survived recovery.

Next investigation: use incident-time retained database and storage diagnostics, or an independently preserved snapshot, to correlate the invalid checkpoint with its WAL and storage state. The retained startup observation establishes the checkpoint symptom only. Current readiness, current Pod logs, successful archiving and a successful replacement bootstrap cannot establish the initiating cause. If historical evidence cannot discriminate a cause, record that limit before proposing a repair; do not infer corruption, select a WAL reset or repeat recovery from timing alone. The original cause and durable-prevention criteria remain open. No production or credential change was performed. Raw diagnostic details remain in the private operator store.