PostgreSQL backup tool with Point-In-Time-Recovery and restore verification
PostgreSQL Physical Backup Failover Specification
Purpose
Keep PostgreSQL physical backups recoverable when the source moves to a new timeline. Databasus rejects incremental backups that would extend an obsolete chain, creates a replacement FULL, and preserves the WAL needed during the transition.
Requirements
Requirement: Incremental backups use their root FULL timeline
The system SHALL start an incremental backup only when the live PostgreSQL timeline matches the timeline of the chain's root FULL.
Scenario: Timeline matches the root FULL
- WHEN an incremental backup starts and the live timeline matches its root FULL timeline
- THEN the system proceeds with the incremental backup
Scenario: Promotion happened before the incremental started
- WHEN the live timeline is newer than the root FULL timeline
- THEN the system refuses the incremental backup as
CHAIN_BROKENwith reasonTIMELINE_SWITCH_DETECTED
Scenario: Source timeline regressed
- WHEN the live timeline is older than the root FULL timeline
- THEN the system refuses the incremental backup with reason
TIMELINE_REGRESSION
Scenario: Cataloged history is newer than the root FULL
- WHEN a history file for the live timeline is already cataloged but the root FULL belongs to an older timeline
- THEN the system still refuses the incremental backup as
TIMELINE_SWITCH_DETECTED
Requirement: Backup failures are rechecked for promotion
The system SHALL check PostgreSQL cluster identity again when a physical backup stream fails unexpectedly while the backup is still active.
Scenario: Promotion interrupts an incremental stream
- WHEN the timeline advances after incremental preflight and the stream fails
- THEN the system records the incremental as
CHAIN_BROKENwith reasonTIMELINE_SWITCH_DETECTED
Scenario: Promotion interrupts a FULL stream
- WHEN the timeline advances after FULL preflight and the stream fails
- THEN the system records the FULL as
ERRORwith reasonFAILOVER_DURING_BACKUP
Scenario: Backup was canceled
- WHEN an operator or configuration change cancels the backup
- THEN the system preserves the cancellation result and does not classify it as a promotion
Scenario: Recheck cannot connect
- WHEN the second identity check fails
- THEN the system preserves the original backup result
Scenario: Cluster identity changed
- WHEN the second identity check reaches a cluster with a different system identifier
- THEN the system rejects that cluster and pauses automatic backup scheduling until an operator requests a FULL that completes against the expected cluster
Scenario: Operator requests a FULL after cluster identity changed
- WHEN automatic scheduling is paused by a system identifier mismatch and an operator requests a FULL
- THEN the system runs preflight again and refuses to write a backup unless the live system identifier matches the configured database
Scenario: Forced FULL still reaches a different cluster
- WHEN the operator-requested FULL is refused with
SYSTEM_IDENTIFIER_MISMATCH - THEN automatic backup scheduling remains paused
Scenario: Forced FULL fails without proving cluster identity
- WHEN an operator-requested FULL fails for another reason or is canceled
- THEN automatic backup scheduling remains paused because no later FULL has completed against the expected cluster
Scenario: Forced FULL reaches the expected cluster
- WHEN an operator-requested FULL completes against the configured system identifier
- THEN that newer completed FULL supersedes earlier mismatch results and normal automatic scheduling resumes
Requirement: Timeline failures trigger one immediate replacement FULL
The system SHALL create one immediate replacement FULL after a backup ends with a timeline-related failure.
Scenario: Incremental chain broke after promotion
- WHEN an incremental ends as
CHAIN_BROKENwith reasonTIMELINE_SWITCH_DETECTEDand no later FULL attempt exists - THEN the system schedules a FULL without waiting for the configured incremental or FULL interval
Scenario: FULL was interrupted by promotion
- WHEN the latest FULL ends as
ERRORwith reasonFAILOVER_DURING_BACKUP - THEN the system schedules one replacement FULL without waiting for the configured interval
Scenario: Replacement FULL fails normally
- WHEN the immediate replacement FULL ends with a non-timeline error or is canceled
- THEN the system resumes its existing schedule and failed-FULL retry limits instead of scheduling a FULL on every scheduler tick
Scenario: Another promotion interrupts the replacement
- WHEN the timeline advances again during the replacement FULL
- THEN the system schedules one new immediate replacement FULL for the later promotion
Scenario: Two schedulers hold the same stale replacement decision
- WHEN two scheduler instances decide that the same terminal backup needs an immediate replacement
- THEN only the instance that confirms the failure is still current creates a FULL row
Requirement: Active FULL backups retain archived WAL
The system SHALL keep orphan cleanup from deleting WAL for a database while a FULL backup for that database is active.
Scenario: Cleaner runs during the first FULL
- WHEN WAL is archived while the database's first FULL is still running and orphan cleanup starts
- THEN the WAL remains cataloged and stored for use after the FULL completes
Scenario: Cleaner finishes before a FULL starts
- WHEN orphan cleanup starts before the scheduler starts a FULL for the same database
- THEN the FULL starts only after cleanup finishes
Requirement: Same-timeline PITR remains available during a newer FULL
The system SHALL allow a point-in-time restore from an older FULL when the target comes before a newer FULL completes on the same timeline.
Scenario: Target falls during the newer FULL
- WHEN the requested target is later than the older FULL completion and earlier than the newer FULL completion
- THEN the restore uses the older FULL and contiguous WAL through the target, even when that WAL crosses the newer FULL start position
Scenario: Target crosses a promotion
- WHEN reaching the target would require WAL from more than one timeline
- THEN the system reports that the target is not restorable until a FULL on the promoted timeline is available